如何用SQL计算用户连续登录天数？求解决方案

首先解决你的核心需求：合并同一天的登录记录，再计算截至当前的连续登录天数。

步骤1：去重同一天的登录记录 #

用户一天内多次登录仅算1天，先提取日期并去重：

SELECT DISTINCT user_id, DATE(created_at) AS login_date
FROM logs
WHERE user_id = 123
ORDER BY login_date DESC;

这会得到用户123所有无重复的登录日期。

步骤2：识别连续日期区间 #

给去重后的日期按升序编号，用login_date - INTERVAL ROW_NUMBER() OVER (ORDER BY login_date) DAY计算分组标识——连续的日期减去对应行号后，结果会完全相同：

SELECT 
  user_id,
  login_date,
  login_date - INTERVAL ROW_NUMBER() OVER (ORDER BY login_date) DAY AS group_id
FROM (
  SELECT DISTINCT user_id, DATE(created_at) AS login_date
  FROM logs
  WHERE user_id = 123
) AS unique_dates;

步骤3：计算最新的连续登录天数 #

我们需要的是从最近登录日期往前的连续天数，找到最新日期对应的分组标识，统计该组的记录数即可：

WITH unique_login_dates AS (
  SELECT DISTINCT user_id, DATE(created_at) AS login_date
  FROM logs
  WHERE user_id = 123
),
date_groups AS (
  SELECT 
    user_id,
    login_date,
    MAX(login_date) OVER () AS latest_login,
    login_date - INTERVAL ROW_NUMBER() OVER (ORDER BY login_date) DAY AS group_id
  FROM unique_login_dates
)
SELECT 
  user_id,
  COUNT(*) AS streak
FROM date_groups
WHERE group_id = (
  SELECT group_id 
  FROM date_groups 
  WHERE login_date = latest_login
)
GROUP BY user_id;

执行以上SQL后，会得到和你期望一致的结果：用户123的连续登录天数为4。

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