SQL to derive subsequences
Tags: sql
Published: 2021-10-28 07:02:44

---
title: SQL to derive subsequences
published: true
description: example SQL to derive subsequences according to when some value in a table changes or stays the same
tags: SQL
---

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:

```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](https://stackoverflow.com/questions/27680999/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.

```SQL
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
;
```