autoexcrement (11/13/2013)
I'm trying to wrap my head around this and would appreciate some plain-English explanation if anyone feels up to it. I'm trying to work from the inside out, and am getting stuck on the "G" line:
SELECT SEQ, VAL,ROW_NUMBER() OVER (ORDER BY SEQ) - ROW_NUMBER() OVER (PARTITION BY CASE WHEN VAL = 'Total' THEN 1 ELSE 0 END ORDER BY SEQ) + 1 G
FROM @t
I'm trying to see what the CASE statement is doing, specifically. But if I replace it with a "1" or "0", it doesn't seem to work the same...? E.G.
ROW_NUMBER() OVER (ORDER BY SEQ) - ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY SEQ) + 1 Gor
ROW_NUMBER() OVER (ORDER BY SEQ) - ROW_NUMBER() OVER (PARTITION BY 0 ORDER BY SEQ) + 1 G
Hi
The case statement is used to identify whether the row is a total or not.
Including this in the PARTITION clause causes ROW_NUMBER function to work over each group. I picked 1 and 0 as flags, they could as easily have been 'T' and 'D'.
So when we ROW_NUMBER over these groups we get 1 to 4 for the Totals (in sequence) and 1 to 11 for the non totals.
When we compare the ROW_NUMBER with no partition to the ROW_NUMBER with a partition we get an increasing difference each time a Total row is encountered. This difference will be 1 less than the partitioned row number for the totals.
When you changed the PARTITION clause to a constant, you will not get the difference as this is the same as doing an unpartitioned row number.
Hope the following query will show this better by breaking out each of the components.
SELECT SEQ,
VAL,
isTotal,
R rowNumberNoPartition,
CASE WHEN isTotal = 0 then T else NULL end PartionedRowNumberNonTotal,
CASE WHEN isTotal = 0 then R - T else NULL end nonTotalDifference,
CASE WHEN isTotal = 0 then R - T + 1 else NULL end nonTotalDifferencePlus1,
CASE WHEN isTotal = 1 then T else NULL end PartionedRowNumberTotal,
CASE WHEN isTotal = 1 then T else R - T + 1 end groupID
FROM (
SELECT SEQ, VAL,
CASE WHEN VAL = 'Total' THEN 1 ELSE 0 END isTotal,
ROW_NUMBER() OVER (ORDER BY SEQ) R,
ROW_NUMBER() OVER (PARTITION BY CASE WHEN VAL = 'Total' THEN 1 ELSE 0 END ORDER BY SEQ) T,
ROW_NUMBER() OVER (ORDER BY SEQ) - ROW_NUMBER() OVER (PARTITION BY CASE WHEN VAL = 'Total' THEN 1 ELSE 0 END ORDER BY SEQ) + 1 G
FROM @t
) a
ORDER BY seq