统计近一年不同登录次数用户数及现有SQL优化咨询

你的SQL统计的是用户所有成功登录的记录数，而非实际的登录次数——如果一个用户短时间内多次触发登录记录（比如重复提交、系统重复日志），就会出现登录次数异常偏高的情况。核心问题是没有对同一用户的重复登录行为做去重，以下是两种针对性优化方案：

方案1：按日期去重（统计用户登录天数） #

如果需求是「用户一天内多次登录只算1次」，可以先按用户ID+登录日期去重，再统计次数：

SELECT visit_count, COUNT(*) AS user_count
FROM (
    SELECT USER_ID, COUNT(DISTINCT DATE(log_time)) AS visit_count
    FROM database
    WHERE auth_succeeded = 'true' 
      AND log_time BETWEEN '2024-01-01' AND '2024-08-01'
    GROUP BY USER_ID
) AS visit_counts
GROUP BY visit_count
ORDER BY visit_count;

用COUNT(DISTINCT DATE(log_time))替换原有的COUNT(*)，将同一用户同一天的所有登录记录合并为1次，最终统计的是用户的登录天数。

方案2：按登录唯一标识去重（统计实际登录事件数） #

如果系统中有唯一标识每次登录请求的字段（比如login_session_id、request_id），用这个字段去重更精准，能完全排除重复日志导致的异常次数：

SELECT visit_count, COUNT(*) AS user_count
FROM (
    SELECT USER_ID, COUNT(DISTINCT login_session_id) AS visit_count
    FROM database
    WHERE auth_succeeded = 'true' 
      AND log_time BETWEEN '2024-01-01' AND '2024-08-01'
    GROUP BY USER_ID
) AS visit_counts
GROUP BY visit_count
ORDER BY visit_count;

COUNT(DISTINCT login_session_id)会统计每个用户的唯一登录会话数，完全匹配实际的登录事件次数。

排查异常记录建议 #

如果不确定重复记录的来源，可以先筛选出日志数异常的用户，分析具体原因：

SELECT USER_ID, COUNT(*) AS total_logs, COUNT(DISTINCT DATE(log_time)) AS distinct_days
FROM database
WHERE auth_succeeded = 'true' 
  AND log_time BETWEEN '2024-01-01' AND '2024-08-01'
GROUP BY USER_ID
HAVING COUNT(*) > 100 -- 筛选日志数异常多的用户
ORDER BY total_logs DESC;

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