MySQL 8按日/周/月统计登录用户数的SQL实现求助

根据你给出的用户定义和表结构，分别编写三条SQL语句来统计三类用户数：

1. 统计Daily用户数（每周有5条及以上记录的用户） #

按用户和自然周分组统计登录次数，筛选出存在至少一周登录次数≥5的用户：

SELECT COUNT(DISTINCT userId) AS daily
FROM (
    SELECT userId,
           YEARWEEK(date, 1) AS week_num, -- 1代表周一为周起始，可按需调整
           COUNT(*) AS login_count
    FROM logins
    GROUP BY userId, week_num
    HAVING login_count >= 5
) AS weekly_login_stats;

2. 统计Weekly用户数（每5-10天有1条记录的用户） #

计算用户相邻登录的天数间隔，筛选出间隔符合5-10天范围的用户（若需宽松统计，可调整条件允许少量不符合的间隔）：

SELECT COUNT(DISTINCT userId) AS weekly
FROM (
    SELECT userId,
           TIMESTAMPDIFF(DAY, LAG(date) OVER (PARTITION BY userId ORDER BY date), date) AS diff_days
    FROM logins
) AS login_intervals
WHERE diff_days BETWEEN 5 AND 10
GROUP BY userId
-- 确保用户所有登录间隔都符合要求，若只需大致统计可删除此HAVING子句
HAVING COUNT(*) = COUNT(CASE WHEN diff_days BETWEEN 5 AND 10 THEN 1 END);

3. 统计Mostly用户数（每月记录少于3条的用户） #

按用户和自然月分组统计登录次数，筛选出所有月份登录次数均少于3的用户：

SELECT COUNT(DISTINCT userId) AS mostly
FROM (
    SELECT userId,
           DATE_FORMAT(date, '%Y-%m') AS month_num,
           COUNT(*) AS login_count
    FROM logins
    GROUP BY userId, month_num
) AS monthly_login_stats
GROUP BY userId
HAVING MAX(login_count) < 3;

原有SQL的问题说明 #

你之前的SQL存在两个关键错误：

• 子查询中使用GROUP BY userId却同时选取date和窗口函数结果，违反了MySQL分组逻辑（ONLY_FULL_GROUP_BY模式下会直接报错）；
• 筛选条件diff_days < 4和“每周5条及以上记录”的定义不匹配，相邻登录间隔短仅能说明登录频繁，无法直接反映每周登录次数是否达标。

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