高级间隔与孤岛问题：连续事件次数及长度统计需求

问题描述 #

需要统计用户每月登录行为的连续出现段（streak）长度，最终得到每个用户最长连续段长度的出现次数统计。给定的PostgreSQL表结构、测试数据及期望结果如下：

表结构 #

CREATE TABLE user_actions (
    action_date VARCHAR(255),
    user_id VARCHAR(255)
);

测试数据 #

INSERT INTO user_actions(action_date, user_id)
VALUES('2020-03', 'alex01'),
('2020-04', 'alex01'),
('2020-05', 'alex01'),
('2020-06', 'alex01'),
('2020-12', 'alex01'),
('2021-01', 'alex01'),
('2021-02', 'alex01'),
('2021-03', 'alex01'),
('2020-04', 'jon03'),
('2020-05', 'jon03'),
('2020-06', 'jon03'),
('2020-09', 'jon03'),
('2021-11', 'jon03'),
('2021-12', 'jon03'),
('2022-01', 'jon03'),
('2022-02', 'jon03'),
('2020-05', 'mark05'),
('2020-06', 'mark05'),
('2020-07', 'mark05'),
('2020-08', 'mark05'),
('2020-09', 'mark05');

用户连续行为说明 #

• alex01：2个长度为4的连续段
• jon03：3个长度分别为1、3、4的连续段
• mark05：1个长度为5的连续段

期望统计结果 #

解决方案 #

使用PostgreSQL的窗口函数和CTE（公共表表达式）实现需求，具体SQL如下：

WITH user_streaks AS (
    -- 转换日期格式并标记连续行为分组
    SELECT
        user_id,
        to_date(action_date, 'YYYY-MM') AS action_month,
        -- 通过日期与序号偏移的差值，将连续月份归为同一分组
        date_trunc('month', to_date(action_date, 'YYYY-MM')) - (row_number() OVER (PARTITION BY user_id ORDER BY to_date(action_date, 'YYYY-MM')) || ' months')::interval AS group_id
    FROM user_actions
),
streak_lengths AS (
    -- 计算每个连续分组的长度
    SELECT
        user_id,
        COUNT(*) AS streak_length
    FROM user_streaks
    GROUP BY user_id, group_id
),
user_max_streaks AS (
    -- 提取每个用户的最长连续段长度
    SELECT
        user_id,
        MAX(streak_length) AS max_streak
    FROM streak_lengths
    GROUP BY user_id
)
-- 统计各最长长度的出现次数
SELECT
    max_streak AS "Streak Length",
    COUNT(*) AS "# of occurrences"
FROM user_max_streaks
GROUP BY max_streak
ORDER BY max_streak DESC;

代码解释 #

1. user_streaks：将字符串类型的action_date转换为日期类型，通过row_number()生成用户行为的顺序序号，再用日期减去对应序号的月份偏移，得到连续行为的分组标识group_id——连续的月份会得到相同的group_id。
2. streak_lengths：按用户和分组标识分组，统计每个连续行为段的长度。
3. user_max_streaks：对每个用户，筛选出其所有连续段中的最长长度。
4. 最终统计：按最长长度分组，计算每个长度的出现次数，按长度降序排列。

内容的提问来源于stack exchange，提问作者Ali Ozan Öksüz