如何用单条SQL查询整月每日的distinct登录用户数据

实现逻辑 #

• 将登录时间转换为目标-07:00时区后提取日期维度
• 调整时间过滤范围覆盖整月，无需拆分多次单日查询
• 按「日期+用户ID」分组实现单日用户去重，和原查询返回逻辑一致

方案1：逐行展示日期+对应登录用户（等价于31天单日结果拼接） #

SELECT 
    -- 提取-7时区的登录日期
    DATE(CONVERT_TZ(ul.login_time, '+00:00', '-07:00')) AS `LoginDate`,
    ul.user_id,
    u.first_name,
    u.last_name,
    u.email,
    -- 可按需取用户当日首次/末次登录时间，这里取首次
    MIN(CONVERT_TZ(ul.login_time, '+00:00', '-07:00')) AS `FirstLoginTimeOfDay`
FROM users u
-- 原查询LEFT JOIN无意义，WHERE过滤登录时间后等价于INNER JOIN，直接写INNER JOIN性能更优
INNER JOIN users_logins ul 
    ON u.id = ul.user_id
WHERE 
    -- 提前用UTC时间范围过滤，可命中login_time索引，性能远高于转换时区后过滤
    -- 以下时间对应-7时区2024年8月1日00:00:00 至 2024年8月31日23:59:59
    ul.login_time BETWEEN '2024-08-01 07:00:00' AND '2024-09-01 06:59:59'
    AND u.third_party = '0'
-- 按日期+用户维度分组，保证同一用户同一天只出现一次
GROUP BY `LoginDate`, ul.user_id, u.first_name, u.last_name, u.email
ORDER BY `LoginDate` ASC, `FirstLoginTimeOfDay` ASC;

如果不需要走索引优化，也可以直接用转换后的日期做过滤，写法更直观：

WHERE 
    DATE(CONVERT_TZ(ul.login_time, '+00:00', '-07:00')) BETWEEN '2024-08-01' AND '2024-08-31'
    AND u.third_party = '0'

方案2：按日期聚合展示当日所有登录用户 #

如果需要每行对应一天，整行展示当天的用户列表，可以用GROUP_CONCAT实现：

SELECT 
    DATE(CONVERT_TZ(ul.login_time, '+00:00', '-07:00')) AS `LoginDate`,
    GROUP_CONCAT(DISTINCT ul.user_id ORDER BY ul.user_id SEPARATOR ',') AS `DailyLoginUserIds`,
    COUNT(DISTINCT ul.user_id) AS `DailyLoginUserCount`
FROM users u
INNER JOIN users_logins ul ON u.id = ul.user_id
WHERE 
    ul.login_time BETWEEN '2024-08-01 07:00:00' AND '2024-09-01 06:59:59'
    AND u.third_party = '0'
GROUP BY `LoginDate`
ORDER BY `LoginDate` ASC;

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