如何在PostgreSQL中准确计算用户的每日发布连续天数？

核心思路 #

要获取用户当前连续发布天数，需从最新的Prompt日期开始往前遍历，直到找到用户未发布帖子的日期，统计这期间连续有发布的天数。区别于“最长连续天数”，当前连续天数只关注从最新日期开始的不间断序列。

正确SQL查询（PostgreSQL） #

WITH ranked_prompts AS (
    -- 给所有Prompt日期按从新到旧排序并编号
    SELECT 
        dateKey::date AS prompt_date,
        ROW_NUMBER() OVER (ORDER BY dateKey::date DESC) AS rn
    FROM prompt
),
user_post_dates AS (
    -- 获取目标用户所有发布过帖子的日期（去重，确保每天只算一次）
    SELECT DISTINCT
        pt.dateKey::date AS post_date
    FROM post p
    JOIN prompt pt ON p.promptId = pt.id
    WHERE p.authorId = 90 -- 替换为目标用户ID
),
streak_check AS (
    -- 关联日期并标记是否有发帖，同时计算断档分组
    SELECT 
        rp.prompt_date,
        CASE WHEN upd.post_date IS NOT NULL THEN 1 ELSE 0 END AS has_post,
        -- 遇到无帖日期时，分组编号递增，最新的连续序列分组为0
        SUM(CASE WHEN upd.post_date IS NULL THEN 1 ELSE 0 END) OVER (ORDER BY rp.rn) AS break_group
    FROM ranked_prompts rp
    LEFT JOIN user_post_dates upd ON rp.prompt_date = upd.post_date
)
-- 统计最新连续分组中用户有发帖的天数
SELECT COUNT(*) AS current_streak
FROM streak_check
WHERE break_group = 0 AND has_post = 1;

查询逻辑说明 #

1. ranked_prompts：将所有Prompt日期按从新到旧排序，给每个日期分配唯一序号，最新日期序号为1。
2. user_post_dates：提取目标用户所有发布过帖子的日期，通过DISTINCT保证每个日期只出现一次（符合“单个用户每日最多发布一篇”的规则）。
3. streak_check：关联上述两个结果，标记每个日期用户是否发帖；使用窗口函数SUM生成断档分组——只要遇到用户未发帖的日期，分组编号就会加1，这样最新的连续发帖序列会被归为break_group=0。
4. 最后统计break_group=0且有发帖的记录数量，即为当前连续发布天数。

性能优化建议 #

针对百万级Post表和千级Prompt表的场景，可通过以下方式提升性能：

• 给Post表创建复合索引：CREATE INDEX idx_post_author_prompt ON post(authorId, promptId);，快速筛选目标用户的所有帖子并关联Prompt。
• 给Prompt表创建索引：CREATE INDEX idx_prompt_date_id ON prompt(dateKey, id);，加速日期排序和关联操作。
• Prompt表仅1000行，全表扫描开销极低，核心优化点集中在Post表的索引上。

测试验证 #

针对你提供的示例数据：

查询会返回current_streak=2，符合预期结果。

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