SQL Server - номериране в групи

Не знам какъв е шанса някой да го прочете навереме и да има идея...
номерирането е от 1 до Н но в рамките на групиране. Прост пример
ако имаме категории 1,2,3,...
то трябва да номерираме от 1 до Н продуктите в категория 1 после в категория 2 и т.н.
Първото решение използва курсор , чудесно докато продуктите са по-малко от 1 милион, но после стана зле. клиента има само 1 час прозорец да пусне заявката а тя прави и други неща така че сме замомента загазили.

измерих времената за изпълнение и това е най-бавната част. ако някой се сеща дори и за малка оптимизация ще съм благодарен ;)
Долу има примерен код на това което се прави с курсор.

DECLARE @Tmp table(
DIN int,
RSN int,
AuditId int,
ASN int)

set nocount on

insert into @Tmp values (1,1,1,0)
insert into @Tmp values (1,2,2,0)
insert into @Tmp values (1,3,3,0)
insert into @Tmp values (1,4,4,0)
insert into @Tmp values (1,5,5,0)
insert into @Tmp values (2,1,1,0)
insert into @Tmp values (2,2,2,0)
insert into @Tmp values (2,3,3,0)

set nocount off

declare @DIN int, @RSN int, @AuditID int, @PrevDin int, @CalculatedASN int

DECLARE saiCursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT DIN,RSN, AuditId FROM @Tmp
ORDER BY DIN, RSN, AuditId

OPEN saiCursor

FETCH NEXT FROM saiCursor INTO @DIN, @RSN, @AuditID

SET @PrevDIN = 0
SET @CalculatedASN = 0

WHILE @@FETCH_STATUS = 0 BEGIN

IF @PrevDIN <> @DIN
BEGIN
SET @CalculatedASN = 1
SET @PrevDIN = @DIN
END
ELSE
BEGIN
IF @CalculatedASN = 9
BEGIN
SET @CalculatedASN = 1
END
ELSE
BEGIN
SET @CalculatedASN = @CalculatedASN + 1
END
END

UPDATE @Tmp
SET ASN = @CalculatedASN
WHERE DIN = @DIN AND RSN = @RSN AND AuditId = @AuditId

FETCH NEXT FROM saiCursor INTO @DIN, @RSN, @AuditID

END -- WHILE

CLOSE saiCursor

DEALLOCATE saiCursor

select * from @tmp

всъщност измислих една оптимизация:
избираме с дистинкт само полето/тата по което се групира след което правим всеизвестното номериране само за тази група във временна таблица и после ъпдейт на основната с джойн на временната сетваме полето както се полага.
в тоз случай ще имаме за всяка група по 1 криейт тейбъл 1 селект/инсърт и 1 ъпдейт с джойн. това земества потенциално до 1..Н итерации в уайл като всяка има 1 ъпдейт.
звучеше много добре докато не установих че Н с данните дето има в много редки случаи е 2, още по рядко 3, никога 4 и най-вече 1. т.е. този вариант ще влоши нещата.

Може да пробваш да сложиш и индекси на @tmp, за да е по бързо сканирането и сортирането (в момента отнемат съответно 50% и 17%), за това което съм написал. Кажи дали върши работа

DECLARE @Tmp table(
DIN int,
RSN int,
AuditId int,
ASN int)

DECLARE @Tmp2 table(
DIN int,
RSN int,
AuditId int,
ASN int IDENTITY(1,1))

set nocount on

insert into @Tmp values (1,1,1,0)
insert into @Tmp values (1,2,2,0)
insert into @Tmp values (1,3,3,0)
insert into @Tmp values (1,4,4,0)
insert into @Tmp values (1,5,5,0)
insert into @Tmp values (2,1,1,0)
insert into @Tmp values (2,2,2,0)
insert into @Tmp values (2,3,3,0)

INSERT INTO @Tmp2 (
DIN ,
RSN ,
AuditId
)
SELECT DIN,RSN, AuditId FROM @Tmp
ORDER BY DIN, RSN, AuditId



select * from @tmp2

Мерси за отделеното време ;)

обаче твоя код номерира от 1 до 8 в случая а мен ми трябва да номерира от 1 до 5 за Дин 1 и от 1 до 3 за Дин 2.

всъщност не съм казал всичко щото преправям тука като луд да не ме обвини някой че разпространявам таен код.
тмп има индекс по Дин РСН и ОдитИД

DECLARE @Tmp table(
DIN int,
RSN int,
AuditId int,
ASN int)

CREATE TABLE #TmpTable2 (
DIN int,
RSN int,
AuditId int,
ASN int IDENTITY(1,1))

DECLARE @maxDin int
DECLARE @whileDin int

set nocount on

insert into @Tmp values (1,1,1,0)
insert into @Tmp values (1,2,2,0)
insert into @Tmp values (1,3,3,0)
insert into @Tmp values (1,4,4,0)
insert into @Tmp values (1,5,5,0)
insert into @Tmp values (2,1,1,0)
insert into @Tmp values (2,2,2,0)
insert into @Tmp values (2,3,3,0)

SET @maxDin = (
SELECT TOP 1 t.Din
FROM @Tmp t
ORDER BY t.Din DESC
)

SET @whileDin= 1



WHILE @whileDin <= @maxDin
BEGIN
INSERT INTO #TmpTable2 (
DIN ,
RSN ,
AuditId
)
SELECT DIN,RSN, AuditId
FROM @Tmp
WHERE DIN = @whileDin
ORDER BY RSN, AuditId

DBCC CHECKIDENT (#TmpTable2, RESEED, 0)

SET @whileDin= @whileDin +1
END



select * from #TmpTable2

DROP TABLE #TmpTable2

да сори, аз съм виновен, не съм обяснил достатъчно подробно.
тук има 2 неща
1-во Дин не е пореден номер. това лесно може да се преодолее като се направи дистинкт както предложих във второто мнение
освен това правиш същия курсор/цикъл който вече съществува и който е ужасно бавен щото има 800000 уникални дин-а и част от тях имат по 2 и 3 повторения. Всъщност цикъла ти по прилича на втория който написах с ходене само по уникалните динове с тази разлика че ти ходиш подред щото не ти казах че не са подред ;)

мерси за DBCC CHECKIDENT (#TmpTable2, RESEED, 0) не се бях сетил. ще пробвам но сигурно е по-бързо от дроп/криейт.

и последно в това твое решение накрая трябва да има един ъпдейт на АСН към тмп защото тмп всъщост има много повече полета и се ползва за други цели.

е ще пробвам. Мерси;)
така или иначе с тея данни ми отиват към 4 часа за 1 тест така че има време ;)

малко се светнах но още ми е мътно какво точно се иска , не ходи само по уникалните Din а селектва по тях, т-е връща подреден пореден селект за всички редове които имат такъв Din. Ако няма, не връща нищо.

като си напишеш сторед прок я пусни през index tuning wizard, той може да каже нещо интересно. може да си ребилднеш индексите преди да почнеш да мачкаш, да видиш как е.

Имам идея : предполагам че всеки от редовете ти има ПК (примерно PkID). Можеш да направиш нещо от рода на

UPDATE @table
SET ASN= 1
WHERE PkID IN (
SELECT PkID
FROM @table
WHERE -- по някакъв начин да се отделят тези които са за ASN = 1
)

и така за до ASN=4 маи това каза че ти е максималното.
За съжеление в момента не мога да се усетя за селекта

малко инфо за селекта

SELECT Din, Min(RSN), Min(AuditId)
FROM @Tmp
GROUP BY Din

утре ще има решение

от тук вече ще си играеш с индексите (може да пуснеш 1 скрипт на таблицата поне да я видим)

BEGIN TRANSACTION

CREATE TABLE #tmpTable (
IDtmpTable int IDENTITY(1,1) ,
DIN int ,
RSN int ,
AuditId int ,
ASN int
)

set nocount on

insert into #tmpTable (DIN, RSN, AuditId, ASN) values (1,2,2,0)
insert into #tmpTable (DIN, RSN, AuditId, ASN) values (1,1,1,0)
insert into #tmpTable (DIN, RSN, AuditId, ASN) values (1,3,3,0)
insert into #tmpTable (DIN, RSN, AuditId, ASN) values (1,4,4,0)
insert into #tmpTable (DIN, RSN, AuditId, ASN) values (1,5,5,0)
insert into #tmpTable (DIN, RSN, AuditId, ASN) values (2,1,1,0)
insert into #tmpTable (DIN, RSN, AuditId, ASN) values (2,2,2,0)
insert into #tmpTable (DIN, RSN, AuditId, ASN) values (2,3,3,0)

set nocount off

UPDATE t
SET ASN = 1 +
IsNull ((
SELECT COUNT (IDtmpTable)
FROM #tmpTable t1
WHERE t1.DIN = t.DIN AND
(
t1.RSN < t.RSN or
t1.RSN = t.RSN and
t1.AuditId < t.AuditId
)
), 0)
from #tmpTable t

SELECT *
FROM #tmpTable
ORDER BY Din, RSN, AuditId

DROP TABLE #tmpTable

ROLLBACK TRANSACTION

за човека, който питаше - това решение, в случай че имаш индекс по полетата е перфектно. На мен често ми се налага да използвам подобно номериране в таблици с доста редове и то работи много бързо.
Успех!

<PRE>
declare @D datetime
set @D=GetDate()

SET NOCOUNT ON


IF NOT OBJECT_ID('tempdb..#tmp') IS NULL DROP TABLE #tmp
CREATE TABLE #tmp
(
DIN INT
,RSN INT
,AuditId INT
,ASN INT DEFAULT(0)
)

CREATE CLUSTERED
INDEX tmpspeed ON #tmp (DIN, RSN, AuditId)


DECLARE @i INT, @j INT, @k INT

SET @i=1000000
SET @j=50000
SET @k=170000
WHILE @i>0
BEGIN
insert into #tmp(din,rsn,auditid)
select din = RAND(@i) * 50
,rsn = RAND(@j) * 50000
,auditid = RAND(@k) * 5000000
SET @i=@i-1
SET @j=@j+1
SET @k=@k-1
END



DECLARE @DIN INT
,@ASN INT
SET @DIN = -1


-- running sum
UPDATE t
SET @ASN = ASN =
CASE
WHEN @DIN <> DIN THEN 1
ELSE @ASN + 1
END
,@DIN = DIN
FROM #tmp t (INDEX (tmpspeed))


select *, DATEDIFF(ms, @d,GetDate())
from #tmp order by DIN, RSN, AuditID, ASN

</PRE>

Сложност O(n^2) suck-ва сериозно на десетки хиляди редове (само)

cheers,
</wqw>

UPDATE @var = @var + 1
FROM MyTable

... е мастер хак, който работи недерминистично, зависи от execution plan-а и има тенденция да се сдухва в момента в който degree of parallelism стане повече от 1.

cheers,
</wqw>

Я дай някаква идея ?

Преди да получим RowNo от yukon нямам никаква по добра. Ето моят тест:

SET         NOCOUNT ON



CREATE TABLE #Numbers (

            SeqNo       INT IDENTITY (1, 1)

            , Dummy     INT NULL

            )



INSERT      #Numbers(Dummy)

SELECT      TOP 5000 NULL

FROM        syscolumns s1, syscolumns s2



CREATE TABLE #MyTable (

            DIN         INT NOT NULL

            , RSN       INT NOT NULL

            , AuditId   INT NOT NULL

            , UNIQUE (DIN, RSN, AuditId)

            )



INSERT      #MyTable(DIN, RSN, AuditId)

SELECT      n.SeqNo

            , s.RSN

            , s.AuditId

FROM        (

            SELECT      2,2 UNION ALL

            SELECT      1,1 UNION ALL

            SELECT      1,3 UNION ALL

            SELECT      3,4 UNION ALL

            SELECT      2,5 UNION ALL

            SELECT      1,5 UNION ALL

            SELECT      1,2 UNION ALL

            SELECT      3,3

            ) s(RSN, AuditId)

CROSS JOIN  #Numbers n



PRINT { fn CURRENT_TIME() }



SELECT      t.*

            , 1 + ( SELECT      COUNT(*)

                    FROM        #MyTable t1

                    WHERE       t1.DIN = t.DIN

                                AND t1.RSN < t.RSN

                                OR t1.DIN = t.DIN

                                AND t1.RSN = t.RSN

                                AND t1.AuditId < t.AuditId  )       AS ASN

FROM        #MyTable t

ORDER BY    Din, RSN, AuditId



PRINT { fn CURRENT_TIME() }



SELECT      t.*

            , 1 + ( SELECT      COUNT(*)

                    FROM        #MyTable t1

                    WHERE       t1.DIN = t.DIN

                                AND t1.RSN < t.RSN  )

                + ( SELECT      COUNT(*)

                    FROM        #MyTable t1

                    WHERE       t1.DIN = t.DIN

                                AND t1.RSN = t.RSN

                                AND t1.AuditId < t.AuditId  )       AS ASN

FROM        #MyTable t

ORDER BY    Din, RSN, AuditId



PRINT { fn CURRENT_TIME() }



DROP TABLE #Numbers

DROP TABLE #MyTable

Само добри индекси оправят performance-а. Често разделянето на OR-а в два/три/etc. SELECT-а е по-бързо.

@OP: За повече примери търси ranking function в google.

cheers,
</wqw>

Разделянето на 'or' в 2 или повече селекта е по бързо , индексите е писано вече че трябва да си ги нагласи.
Интересува ме как изчисли сложноста : O(n^2), я дай линк или статия да хвърля 1 око.

Ами на око :-)) В смисъл N е брой записи в #MyTable и имаш correlated query в column list-а на SELECT клаузата. Определено константата пред N^2 e доста под 1 в общия случай.

А иначе за символа на Ландау виж wikipedia тук и тук.

cheers,
</wqw>