SQL如何基于另一列值计算多列统计：查询当日及7/14/30天前用户数

原有代码问题分析 #

你写的查询不符合预期主要有3个问题：

• 子查询没有和外层查询的日期做关联，内层dateid = dateid是恒真条件，会返回全表user_count的总和，不是当天的用户数
• 日期计算逻辑错误，dateid - 7是布尔判断语句，不会返回7天前的日期，也无法匹配到对应行的用户数
• uc30的子查询错误写为减去14天，逻辑正确的前提下也无法拿到30天前的数值

正确实现方案 #

方案1：窗口函数实现（支持MySQL8.0+、PostgreSQL、SQL Server等主流数据库，性能最优，适合日期连续的场景，与样例数据匹配） #

SELECT 
    dateid,
    user_count AS uc,
    COALESCE(LAG(user_count, 7) OVER (ORDER BY dateid ASC), 0) AS uc7,
    COALESCE(LAG(user_count, 14) OVER (ORDER BY dateid ASC), 0) AS uc14,
    COALESCE(LAG(user_count, 30) OVER (ORDER BY dateid ASC), 0) AS uc30
FROM num_users
ORDER BY dateid DESC;

说明：

• LAG(user_count, N) OVER (ORDER BY dateid ASC) 实现按日期升序排序后，取当前行往前第N行的user_count值，正好对应N天前的用户数
• COALESCE 函数会把不存在的历史值替换为0，和期望输出一致

方案2：自关联实现（兼容低版本不支持窗口函数的数据库） #

以下为MySQL语法写法，其他数据库调整日期减法函数即可：

SELECT 
    t1.dateid,
    t1.user_count AS uc,
    COALESCE(t7.user_count, 0) AS uc7,
    COALESCE(t14.user_count, 0) AS uc14,
    COALESCE(t30.user_count, 0) AS uc30
FROM num_users t1
LEFT JOIN num_users t7 ON t7.dateid = DATE_SUB(t1.dateid, INTERVAL 7 DAY)
LEFT JOIN num_users t14 ON t14.dateid = DATE_SUB(t1.dateid, INTERVAL 14 DAY)
LEFT JOIN num_users t30 ON t30.dateid = DATE_SUB(t1.dateid, INTERVAL 30 DAY)
ORDER BY t1.dateid DESC;

其他数据库日期减法参考：

• PostgreSQL：t7.dateid = t1.dateid - INTERVAL '7 day'
• SQL Server：t7.dateid = DATEADD(day, -7, t1.dateid)

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