如何基于task_activity表统计任务完成的连续周期（Streak）

表结构与数据 #

有一张task_activity表，用于记录特定日期完成的任务，执行查询select * from task_activity;得到如下数据：

id |    date    | task_id 
  1 | 2020-01-01 |       4
  2 | 2020-01-02 |       4
  3 | 2020-01-03 |       4
  4 | 2020-01-05 |       4
  5 | 2020-01-06 |       4
  6 | 2020-01-07 |       4
  7 | 2020-01-06 |       5
  8 | 2020-01-07 |       5
(8 rows)

期望输出 #

需要统计任务完成的连续周期，期望输出如下：

id |streak| started_on |  ended_on  | task_id
  1 | 3   | 2020-01-01 | 2020-01-03 |  4
  2 | 3   | 2020-01-05 | 2020-01-07 |  4
  3 | 2   | 2020-01-06 | 2020-01-07 |  5

当前尝试的SQL及错误结果 #

当前使用的SQL语句：

WITH task_done AS(
  SELECT DISTINCT                                        
  id,                                     
  date,task_id,
  RANK() OVER(PARTITION BY task_id ORDER BY date) rank
  FROM task_activity where task_id=4),
                                                        
  streak AS (                                
    SELECT *                                  
    FROM task_done
  ),        
                    
  output AS
  (                       
  SELECT DISTINCT 
  id,streak,
  MIN(date) started_on,
  MAX(date) ended_on
  FROM streak
  GROUP BY 1,2
  )             
     
  SELECT *
  FROM output;

得到的错误结果：

id |       streak       | started_on |  ended_on  
  1 | (1,2020-01-01,4,1) | 2020-01-01 | 2020-01-01
  2 | (2,2020-01-02,4,2) | 2020-01-02 | 2020-01-02
  3 | (3,2020-01-03,4,3) | 2020-01-03 | 2020-01-03
  4 | (4,2020-01-05,4,4) | 2020-01-05 | 2020-01-05
  5 | (5,2020-01-06,4,5) | 2020-01-06 | 2020-01-06
  6 | (6,2020-01-07,4,6) | 2020-01-07 | 2020-01-07

正确解决方案 #

核心思路是利用日期与排名的差值标识连续日期组：连续日期减去对应排名天数后会得到同一个值，以此分组计算每个连续周期的信息。

完整SQL语句：

WITH task_groups AS (
  SELECT
    task_id,
    date,
    -- 生成连续日期分组标识：连续日期的该值相同
    date - INTERVAL '1 day' * RANK() OVER (PARTITION BY task_id ORDER BY date) AS group_id
  FROM task_activity
),
streak_summary AS (
  SELECT
    task_id,
    MIN(date) AS started_on,
    MAX(date) AS ended_on,
    -- 计算连续天数：结束日期减开始日期加1
    (MAX(date) - MIN(date))::int + 1 AS streak
  FROM task_groups
  GROUP BY task_id, group_id
)
-- 添加结果集行号，匹配期望输出的id列
SELECT
  ROW_NUMBER() OVER (ORDER BY task_id, started_on) AS id,
  streak,
  started_on,
  ended_on,
  task_id
FROM streak_summary
ORDER BY id;

执行该SQL后，得到的结果为：

id | streak | started_on |  ended_on  | task_id
----+--------+------------+------------+---------
  1 |      3 | 2020-01-01 | 2020-01-03 |       4
  2 |      3 | 2020-01-05 | 2020-01-07 |       4
  3 |      2 | 2020-01-06 | 2020-01-07 |       5
(3 rows)

思路说明 #

1. 分组标识计算：通过date - INTERVAL '1 day' * RANK()，将连续日期映射到同一个group_id。例如task_id=4的2020-01-01、02、03，排名分别为1、2、3，计算后group_id均为2019-12-31；而2020-01-05的排名是4，计算后group_id为2020-01-01，和前一组区分开。
2. 汇总连续周期：按task_id和group_id分组，计算每个周期的开始/结束日期和连续天数。
3. 生成结果行号：用ROW_NUMBER()生成结果集的id列，匹配期望输出格式。

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