sql - 连续出现次数及其长度(高级间隙和孤岛问题)

Question

我正在尝试找出特定用户操作的连续出现次数及其长度。假设这是每月登录。下面是我的表格的简化结构和我预期的最终结果。

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 的连胜我们想获得每个用户最长连续出现的次数

<表类="s-表"><头>条纹长度发生次数<正文>5142

我曾尝试研究过是否有人问过此类问题并找到了 this question .虽然答案很有帮助但不完全是我需要的

Answer 1

您可以按照以下步骤从 varchar 日期转到最终输出:

• 使用 DATE 数据类型格式化您的日期，以便进行日期比较
• 计算每个“user_id”的每个日期值不连续的时间，用 1 标记它，这将表示新分区的开始
• 根据您的标志计算运行总和，以生成您的分区值
• 检索每个分区中的最大日期数
• 计算您在上一步中找到了多少次

这些步骤中的每一个都在子查询中进行:

WITH cte AS (
    SELECT user_id, 
           TO_DATE(action_date || '-01','YYYY-MM-DD') AS date_as_date
    FROM user_actions
), islands AS ( 
    SELECT *, 
           CASE WHEN LAG(date_as_date) OVER(PARTITION BY user_id ORDER BY date_as_date) + INTERVAL '1 month' <> date_as_date
                THEN 1 ELSE 0 
           END AS new_partition
    FROM cte
), partitioned_islands AS (
    SELECT user_id, 
           SUM(new_partition) OVER(PARTITION BY user_id ORDER BY date_as_date) AS partitions
    FROM islands
), count_per_island AS (
    SELECT COUNT(*) AS num_consecutive_dates
    FROM partitioned_islands
    GROUP BY user_id, partitions
    ORDER BY ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY COUNT(*) DESC)
    FETCH FIRST 1 ROWS WITH TIES
)
SELECT num_consecutive_dates AS streakLength, 
       COUNT(*) AS numOfOccurrences  
FROM count_per_island
GROUP BY streakLength
ORDER BY streakLength DESC

查看演示 here .