含变量的MySQL最大连续登录天数查询转PostgreSQL遇问题求助

问题根源 #

你这套写法完全照搬了MySQL的会话变量逻辑，但PostgreSQL不支持MySQL那种@变量的赋值语法，同时还存在几个语法错误：

• PostgreSQL里没有@count这类会话变量的写法，你写的SELECT @count = @count +1会被解析成判断count列是否等于@count +1，但表中根本不存在count列，所以直接报错。
• PostgreSQL没有DATEDIFF函数，日期直接相减就能得到天数差（比如current_date - previous_date）。
• 你在SELECT语句里用SET赋值也是非法操作，PostgreSQL不允许在查询过程中这么做。

正确解决思路：用PostgreSQL窗口函数实现 #

PostgreSQL处理连续登录天数这类问题，最佳实践是用窗口函数替代MySQL的变量逻辑，核心思路是分组连续日期段，再统计每个段的长度：

1. 先获取用户去重的登录日期，按时间升序排列；
2. 用窗口函数标记连续日期的分组边界；
3. 按分组聚合统计连续天数，最终取最大值。

完整可运行的SQL #

版本1：分步标记分组

SELECT MAX(streak_length) AS max_streak
FROM (
    SELECT 
        login_at,
        -- 给每个连续登录段分配唯一分组ID
        SUM(CASE WHEN date_diff = 1 THEN 0 ELSE 1 END) OVER (ORDER BY login_at) AS group_id,
        -- 计算当前段的连续天数
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY login_at) AS streak_length
    FROM (
        SELECT 
            DISTINCT DATE(login_at) AS login_at,
            -- 计算当前日期与上一个登录日期的天数差
            DATE(login_at) - LAG(DATE(login_at)) OVER (ORDER BY DATE(login_at)) AS date_diff
        FROM login_histories
        WHERE user_id = ?
        ORDER BY login_at
    ) AS date_diffs
) AS streak_groups;

版本2：更简洁的分组键写法

SELECT MAX(group_count) AS max_streak
FROM (
    SELECT 
        COUNT(*) AS group_count
    FROM (
        SELECT 
            DATE(login_at),
            -- 生成分组键：同一连续登录段的结果会是同一个日期
            DATE(login_at) - INTERVAL '1 day' * ROW_NUMBER() OVER (ORDER BY DATE(login_at)) AS group_key
        FROM login_histories
        WHERE user_id = ?
        GROUP BY DATE(login_at)
    ) AS grouped_dates
    GROUP BY group_key
) AS streak_counts;

代码说明 #

• 版本2的核心技巧：对去重后的登录日期按顺序编号，用登录日期 - 编号*1天计算分组键，同一连续登录段的结果会完全相同（比如2024-01-01、2024-01-02、2024-01-03，编号1、2、3，计算后结果都是2023-12-31），以此快速划分连续段。
• 最后按分组键聚合，每组的行数就是该段的连续登录天数，取最大值即为用户的最长连续登录 streak。

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