SQL最长连续周数计算疑问及窗口函数逻辑咨询

测试数据 #

INSERT INTO martin_test (id, user_id, created_at) VALUES
  ('1bb20295-fd7b-4918-a496-313e5babd482', 'abc', '2024-01-04 15:54:51')
, ('08565423-3371-4720-abb3-80c7aef8333d', 'abc', '2024-01-11 15:54:51')
, ('b17443fe-5a4f-4b7c-934d-3d2910a65f44', 'abc', '2024-01-18 15:54:51')
, ('3d267dc3-ee86-44e9-b1fe-fd918a64b77c', 'abc', '2024-02-01 15:54:51')
, ('d28d73d3-bc9c-4192-998a-a6bafce604a5', 'abc', '2024-02-08 15:54:51')
, ('401d38f8-d277-4605-af33-b4a9bd2eef25', 'abc', '2024-02-22 15:54:51')
, ('b804fa29-23af-4d93-a5c9-187767fec3c9', 'abc', '2024-02-29 15:54:51')
;

当前查询语句 #

SELECT *,CASE WHEN gap_weeks > 1 THEN 1 END, avg(CASE WHEN gap_weeks > 1 THEN 7 END) OVER (PARTITION BY user_id ORDER BY week_number) AS grp_avg,
    sum(CASE WHEN gap_weeks > 1 THEN 6 END) OVER (PARTITION BY user_id ORDER BY week_number) AS grp_sum,
    COUNT(CASE WHEN gap_weeks > 1 THEN 1 END) OVER (PARTITION BY user_id ORDER BY week_number) AS grp
FROM (
  SELECT user_id,
         week_number,
         lag(week_number) OVER (PARTITION BY user_id ORDER BY week_number) AS pre_week_number, 
         week_number - lag(week_number) OVER (PARTITION BY user_id ORDER BY week_number) AS gap_weeks
  FROM (
    select distinct EXTRACT(
          WEEK 
          FROM 
            CAST(created_at AS DATE)
        ) AS week_number ,user_id  from martin_test ) AS a
) AS subquery1

需求与疑问 #

需求：找出最长连续周数。
存在疑问：

1. 当gap_weeks>1时，grp字段为何每次递增1而非3？
2. 为何week_number被分为1&2&3、5&6、8&9&10、12这些分组？
3. 对COUNT、AVG、SUM结合CASE WHEN与窗口函数的运行逻辑存在疑惑。

核心逻辑拆解：窗口函数+CASE WHEN的运行规则 #

窗口函数OVER (PARTITION BY user_id ORDER BY week_number)是按用户分组，按周数从小到大遍历每一行，计算从当前组第一行到当前行的聚合值。结合CASE WHEN时，只有满足条件的行会参与聚合，不满足的会被视为NULL（COUNT除外，COUNT只统计非NULL值）：

• COUNT(CASE WHEN gap_weeks >1 THEN 1 END)：仅当gap_weeks>1时返回非NULL的1，否则返回NULL；COUNT只统计非NULL的行数，所以每遇到一次gap_weeks>1就加1。
• SUM(CASE WHEN gap_weeks>1 THEN 6 END)：满足条件时累加6，否则累加NULL（SUM自动忽略NULL），每遇到一次gap_weeks>1总和加6。
• AVG(CASE WHEN gap_weeks>1 THEN7 END)：满足条件时取7，否则取NULL；AVG只计算非NULL值的平均，每多一个满足条件的行，就是多个7的平均值。

疑问1解答：grp字段为何每次递增1而非3？ #

先看subquery1的输出结果：

可以看到，gap_weeks>1的情况仅出现3次（对应week_number=5、8、12的行），每次只有一行满足条件，所以COUNT(...)会逐行递增1，而非一次性加3。

疑问2解答：week_number的分组逻辑 #

分组是由gap_weeks>1的位置决定的：当gap_weeks>1时，说明当前周和上一周不连续，是新分组的起点：

• week1-3：彼此之间gap都是1，属于连续段，归为同一组；
• week5-6：和前一组的week3 gap为2，连续被打断，成为新组；
• week8-10：和前一组的week6 gap为2，再次打断，成为新组；
• week12：和前一组的week10 gap为2，单独成组。

这是标记连续段的常见思路：用gap_weeks>1作为分段标记，通过累计标记数量生成组ID（比如grp字段），同一连续段内的行grp值相同，新段会自动递增。

补充：实现“找出最长连续周数”的最终SQL #

基于当前逻辑，可进一步处理得到结果：

WITH consecutive_groups AS (
  SELECT 
    user_id,
    week_number,
    COUNT(CASE WHEN gap_weeks >1 THEN 1 END) OVER (PARTITION BY user_id ORDER BY week_number) AS group_id
  FROM (
    SELECT 
      user_id,
      week_number,
      week_number - LAG(week_number) OVER (PARTITION BY user_id ORDER BY week_number) AS gap_weeks
    FROM (
      SELECT DISTINCT EXTRACT(WEEK FROM CAST(created_at AS DATE)) AS week_number, user_id 
      FROM martin_test
    ) AS a
  ) AS subquery1
)
SELECT user_id, MAX(consecutive_weeks) AS longest_consecutive_weeks
FROM (
  SELECT user_id, group_id, COUNT(*) AS consecutive_weeks
  FROM consecutive_groups
  GROUP BY user_id, group_id
) AS group_counts
GROUP BY user_id;

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