如何获取指定事件及时区下的最新连续天数（Streak）

针对你的需求，以下是基于PostgreSQL的SQL解决方案，可计算指定事件在指定时区下的最新连续天数（streak），即使该连续周期已结束也能返回：

WITH timezone_unique_dates AS (
    -- 转换时区并去重，得到事件发生的唯一日期（单日多次事件仅记1天）
    SELECT DISTINCT
        event_name,
        DATE(timestamp AT TIME ZONE 'MST') AS event_date
    FROM action_events
    WHERE event_name = 'exercise'
),
streak_grouping AS (
    -- 通过日期与行号的差值分组，连续日期会被归为同一组
    SELECT
        event_name,
        event_date,
        event_date - (ROW_NUMBER() OVER (PARTITION BY event_name ORDER BY event_date) * INTERVAL '1 day') AS group_key
    FROM timezone_unique_dates
),
streak_summary AS (
    -- 计算每个连续分组的核心信息
    SELECT
        event_name,
        MIN(event_date) AS streak_start,
        MAX(event_date) AS streak_end,
        COUNT(*) AS streak_days
    FROM streak_grouping
    GROUP BY event_name, group_key
)
-- 取最新的连续分组（按结束日期倒序，仅返回第一条）
SELECT
    streak_days AS "连续天数计数",
    '"' || event_name || '"' AS "事件名称",
    '"MST"' AS "时区",
    TO_CHAR(streak_start, 'YYYY-MM-DD HH24:MI:SS') AS "开始日期",
    TO_CHAR(streak_end, 'YYYY-MM-DD HH24:MI:SS') AS "结束日期"
FROM streak_summary
ORDER BY streak_end DESC
LIMIT 1;

关键步骤说明 #

1. 时区转换与去重：timezone_unique_dates CTE 将原始timestamp转换为目标时区的日期，并通过DISTINCT去除单日重复的事件记录，确保一天只算一次。
2. 连续日期分组：streak_grouping CTE 使用窗口函数ROW_NUMBER()对日期排序，再用日期减去对应行号的天数，连续的日期会得到相同的group_key，以此区分不同的连续周期。
3. 计算连续周期详情：streak_summary CTE 按分组聚合，得到每个连续周期的开始日期、结束日期和总天数。
4. 获取最新周期：最后按结束日期倒序取第一条结果，即为最新的连续天数记录。

注意事项 #

• 若需要动态传入事件名和时区，可将'exercise'和'MST'替换为参数（如PostgreSQL中的$1、$2），适配应用程序调用场景。
• 示例中的2023-02-30为无效日期，属于笔误，实际查询会返回真实的最大日期。
• 若需保留时间部分（如示例中的09:00:00），可根据业务需求调整，例如将日期拼接为指定时间：streak_start + INTERVAL '9 hours'。

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