This is an example of using SQL to derive subsequences according to when some value in a table changes or stays the same.
It assumes:
- that we have a source table of
DbsNm.TblNm; - that there is some value (
ImportantSequentialValue) that always indicates which records come before others ; - that there is one or more category columns - given here as two columns:
Category_A&Category_B; - that there is a column or an expression which is the thing that variously changes or stays the same:
ExprValuThatMayChange.
The rough idea is to use multiple ROW_NUMBER calls:
- one for the whole category ;
- one for the changes within the category
- another one for each sequential instance of change within the category
and then use the differences between these to generate a unique ID for each subsequence.
The SQL I'm giving here derives identifiers for the subsequences - as ArbitraryChangeGroupId - and then uses that for an overall summary of them and their boundary points in a final SELECT and GROUP BY.
There is a display of example data further below.
With that preamble, here is the full example SQL:
WITH
Round_1 AS (
SELECT
SrcTbl.* ,
( ExprValuThatMayChange ) AS SubsetMarker ,
( -- Derive CounterPerCategory
ROW_NUMBER() OVER (
PARTITION BY
Category_A ,
Category_B
ORDER BY
ImportantSequentialValue )
) AS CounterPerCategory ,
( -- Derive CounterPerSubsetCategory
ROW_NUMBER() OVER (
PARTITION BY
Category_A ,
Category_B ,
SubsetMarker
ORDER BY
ImportantSequentialValue )
) AS CounterPerSubsetCategory ,
( -- Derive SubGrpNum
CounterPerCategory
-
CounterPerSubsetCategory
) AS SubGrpNum
FROM
DbsNm.TblNm AS SrcTbl
) ,
Round_2 AS (
SELECT
Round_1.* ,
( -- Derive ResetCounterPerCategorySubset
ROW_NUMBER() OVER (
PARTITION BY
Category_A ,
Category_B ,
SubGrpNum
ORDER BY
ImportantSequentialValue )
) AS ResetCounterPerCategorySubset ,
( -- Derive ArbitraryChangeGroupId
CounterPerSubsetCategory
-
ResetCounterPerCategorySubset
) AS ArbitraryChangeGroupId
FROM
Round_1
)
-- Example summary by the Arbitrary Change Groups SELECT
SELECT
Category_A ,
Category_B ,
ArbitraryChangeGroupId ,
MIN( ImportantSequentialValue ) AS SubGrp_MinSeqVal ,
MAX( ImportantSequentialValue ) AS SubGrp_MaxSeqVal ,
COUNT(*) AS SubGrp_Rw_Cnt
FROM
Round_2
GROUP BY
Category_A ,
Category_B ,
ArbitraryChangeGroupId
ORDER BY
SubGrp_MinSeqVal
;
And here are all the partial steps plotted out to show quite why it works.
- note that I've separated out, that which becomes column G, as E & F so you can see how the values in G come about;
- for simplicity I've given just one "category" column and we're only looking at one value in it.
| Cat | Seq | Valu | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|
| Eric | ab | This | 1 | 1 | 1 | 0 | 1 | 0 | |
| Eric | bc | This | 2 | 2 | 2 | 0 | 2 | 0 | |
| Eric | cd | That | 3 | 1 | 1 | 2 | 1 | 2 | |
| Eric | de | That | 4 | 2 | 2 | 2 | 2 | 2 | |
| Eric | ef | That | 5 | 3 | 3 | 2 | 3 | 2 | |
| Eric | fg | This | 6 | 3 | 3 | 3 | 1 | 5 | |
| Eric | gh | This | 7 | 4 | 4 | 3 | 2 | 5 | |
| Eric | hi | That | 8 | 4 | 4 | 4 | 1 | 7 | |
| Eric | jk | This | 9 | 5 | 5 | 4 | 1 | 8 | |
| Eric | kl | This | 10 | 6 | 6 | 4 | 2 | 8 | |
| Eric | lm | This | 11 | 7 | 7 | 4 | 3 | 8 |
About those columns:
| Column | In source | Meaning or derivation | Calc |
|---|---|---|---|
| Cat | Y | Category A | one or more such columns |
| Seq | Y | ImportantSequentialValue | is often a timestamp |
| Valu | Y | ExprValuThatMayChange | |
| D | - | CounterPerCategory | row# |
| E | - | CounterPerSubsetCategory for This | see G |
| F | - | CounterPerSubsetCategory for That | see G |
| G | - | CounterPerSubsetCategory for Either | row# |
| H | - | SubGrpNum | D minus G |
| I | - | ResetCounterPerCategorySubset | row# |
| J | - | ArbitraryChangeGroupId | D minus I |
Notes
You will see that the ArbitraryChangeGroupId values are not contiguous, but that for this purpose, that simply doesn't matter.
Do note that in the SQL I've separated out all the steps so that each partial result is present as a named column. In practice, only some of those need to be made explicit, the others can just exist as expressions within the derivations.
How it is best to do that will vary with the query engine being used and its approaches to optimisation. Some will just work around the named columns anyway, conversely some will become inefficient if unable to recognise that identical uses of ROW_NUMBER are happening. As the saying goes: your mileage may vary.
In my work, I have an SQL script processing tool that lets me store the script as a pro forma and then have text substitutions made at run time. Thus I can affirm that the above SQL really works, as I used it via such substitutions for an actual run on real data.
Background
I originally wrote a version of this method a great many years ago. Of course, old hacks like me tend to say that kind of thing, and I probably do have the code somewhere in my archive.
I can't quite remember whether I had access to ROW_NUMBER as a window function at the time, or had to derive something similar the hard way. I can say that the dialect I was using then didn't support CTE syntax, so I would have entirely used the derived table syntax.
Recently the problem of doing this kind of thing came up when I was asked for help out with some analysis. While I knew I had a method somewhere in my past, I couldn't quite remember how it went.
So, like anyone, I net searched and it was this posting that reminded me of the method.
Resetting Row number according to record data change
That only covers the reset counter aspect but it was enough to jog my memory for me to write out and test the full solution - so I'd like to give the link here in appreciation.
Teradata Calendar substitution
One of the test substitutions that I ran (see Notes section) was to apply the method to the Teradata System Calendar. Therefore this is only of use to those to those using a Teradata, but hey, I've no reason not to share this too. It was interesting coming up with a simple made-up category for the "change" values.
WITH
Round_1 AS (
SELECT
SrcTbl.* ,
( ( day_of_week + day_of_month + day_of_year + day_of_calendar + weekday_of_month ) MOD 2 ) AS SubsetMarker ,
( -- Derive CounterPerCategory
ROW_NUMBER() OVER (
PARTITION BY
year_of_calendar ,
quarter_of_calendar
ORDER BY
calendar_date )
) AS CounterPerCategory ,
( -- Derive CounterPerSubsetCategory
ROW_NUMBER() OVER (
PARTITION BY
year_of_calendar ,
quarter_of_calendar ,
SubsetMarker
ORDER BY
calendar_date )
) AS CounterPerSubsetCategory ,
( -- Derive SubGrpNum
CounterPerCategory
-
CounterPerSubsetCategory
) AS SubGrpNum
FROM
sys_calendar.calendar AS SrcTbl
) ,
Round_2 AS (
SELECT
Round_1.* ,
( -- Derive
ROW_NUMBER() OVER (
PARTITION BY
year_of_calendar ,
quarter_of_calendar ,
SubGrpNum
ORDER BY
calendar_date )
) AS ResetCounterPerCategorySubset ,
( CounterPerSubsetCategory
-
ResetCounterPerCategorySubset
) AS ArbitraryChangeGroupId
FROM
Round_1
)
-- Example summary by the Arbitrary Change Groups SELECT
SELECT
year_of_calendar ,
quarter_of_calendar ,
ArbitraryChangeGroupId ,
MIN( calendar_date ) AS SubGrp_MinSeqVal ,
MAX( calendar_date ) AS SubGrp_MaxSeqVal ,
COUNT(*) AS SubGrp_Rw_Cnt
FROM
Round_2
GROUP BY
year_of_calendar ,
quarter_of_calendar ,
ArbitraryChangeGroupId
ORDER BY
SubGrp_MinSeqVal
;
Top comments (0)