如何在标准SQL中为连续相同值生成重置式序号

我有如下结构的数据表：

WITH DATA AS (
  SELECT 45 AS user_id, '2023-07-15' AS date, true AS had_session
  UNION ALL
  SELECT 45, '2023-07-16', false
  UNION ALL
  SELECT 45, '2023-07-17', true
  UNION ALL
  SELECT 45, '2023-07-18', true
  UNION ALL
  SELECT 45, '2023-07-19', true
  UNION ALL
  SELECT 45, '2023-07-20', false
  UNION ALL
  SELECT 45, '2023-07-21', true
  UNION ALL
  SELECT 45, '2023-07-22', true
  UNION ALL
  SELECT 45, '2023-07-23', false
  UNION ALL
  SELECT 45, '2023-07-24', false
  UNION ALL
  SELECT 45, '2023-07-25', false
  UNION ALL
  SELECT 45, '2023-07-26', false
)
SELECT *,
       -- 此处需添加序号子查询 AS consequente_counter
  FROM DATA
ORDER BY date

需要新增一列consequente_number，规则是：针对had_session的true/false值，每当当前行值与前一行不同时，序号从1开始；连续相同值则序号依次递增。要求用标准SQL实现，不能用游标或存储过程。

期望结果如下：

用窗口函数就能实现，核心是先给连续相同值的行打分组标签，再在分组内计数。完整代码如下：

WITH DATA AS (
  SELECT 45 AS user_id, '2023-07-15' AS date, true AS had_session
  UNION ALL
  SELECT 45, '2023-07-16', false
  UNION ALL
  SELECT 45, '2023-07-17', true
  UNION ALL
  SELECT 45, '2023-07-18', true
  UNION ALL
  SELECT 45, '2023-07-19', true
  UNION ALL
  SELECT 45, '2023-07-20', false
  UNION ALL
  SELECT 45, '2023-07-21', true
  UNION ALL
  SELECT 45, '2023-07-22', true
  UNION ALL
  SELECT 45, '2023-07-23', false
  UNION ALL
  SELECT 45, '2023-07-24', false
  UNION ALL
  SELECT 45, '2023-07-25', false
  UNION ALL
  SELECT 45, '2023-07-26', false
),
grouped_data AS (
  SELECT *,
         -- 标记分组：当前行与前一行值不同时加1，累加得到分组ID
         SUM(CASE WHEN had_session = LAG(had_session) OVER (ORDER BY date) THEN 0 ELSE 1 END) 
         OVER (ORDER BY date) AS group_id
  FROM DATA
)
SELECT user_id, date, had_session,
       -- 每个分组内按日期生成递增序号
       ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY date) AS consequente_number
FROM grouped_data
ORDER BY date;

思路说明 #

1. 生成分组ID：用LAG()窗口函数获取上一行的had_session值，和当前行对比。如果值不一样，就返回1，否则返回0。然后用SUM() OVER (ORDER BY date)累加这些值，每遇到一次值变化，累加结果就会加1，这样所有连续相同值的行就会被分到同一个group_id里。
2. 分组内计数：针对每个group_id，用ROW_NUMBER()窗口函数按日期排序，生成从1开始的递增序号，也就是我们需要的consequente_number。

内容的提问来源于stack exchange，提问作者Damir