如何用单条SQL查询实现指定日期范围用户活动数据按日拆分统计

可以直接通过单条SQL实现按日维度的统计结果，仅需要在原有查询的基础上增加日期维度的提取和分组规则即可，优化后的查询语句如下：

SELECT 
    DATE(created_at) AS activity_date,
    user_id, 
    COUNT(user_id) AS total_logins,
    SUM(TIMESTAMPDIFF(SECOND, created_at, completed_at)) AS total_time_spent_seconds
FROM metrics_session 
WHERE created_at >= '2021-04-01' 
  AND created_at < '2021-05-01' -- 统计4月全月数据时使用该写法，避免遗漏4月30日的全天记录
  AND completed_at IS NOT NULL
GROUP BY activity_date, user_id
ORDER BY activity_date, user_id;

调整说明 #

• 新增DATE(created_at) AS activity_date字段，将datetime类型的会话创建时间转换为纯日期格式，作为日期分组的依据
• GROUP BY子句新增activity_date字段，实现「日期+用户ID」的双重分组，直接输出每日每个用户的统计明细
• 原有TIMEDIFF函数替换为TIMESTAMPDIFF(SECOND, created_at, completed_at)，返回两个时间点的差值秒数，避免TIMEDIFF返回值上限（838小时）导致的长会话统计溢出问题，后续在Python中可以很方便的将秒数转换为时分秒、小时等需要的展示格式
• 日期筛选条件调整为半开区间写法，避免用<=判断时漏掉目标日期当日非0点的记录

如果你的前端图表需要展示统计区间内所有日期（包括当日无任何用户会话的日期），可以提前维护一张日期维度表，左关联上述统计结果即可补全缺失日期的0值记录。

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