SQL技术问题：如何按客户统计每日过去7天的营收总和

问题描述 #

需要按客户统计每个日期对应的过去7天营收总和，存在以下难点：

• 部分客户存在日期缺失情况
• 无法使用LAG函数实现需求
• 仅按Customer_ID分区的窗口函数无法满足日期范围筛选要求

示例输入数据 #

预期输出结果 #

解决方案 #

方法1：自连接（兼容多数SQL数据库） #

先将带$的Revenue转换为数值类型，再通过自连接匹配同一客户、日期在当前日期前6天到当天的记录，最后分组求和：

SELECT
    t1.Customer_ID,
    t1.Date,
    CONCAT('$', SUM(CAST(REPLACE(t2.Revenue, '$', '') AS DECIMAL(10,2)))) AS Revenue
FROM
    your_table t1
JOIN
    your_table t2 ON t1.Customer_ID = t2.Customer_ID
        AND t2.Date BETWEEN DATE_SUB(STR_TO_DATE(t1.Date, '%d/%m/%y'), INTERVAL 6 DAY) 
        AND STR_TO_DATE(t1.Date, '%d/%m/%y')
GROUP BY
    t1.Customer_ID, t1.Date
ORDER BY
    t1.Customer_ID, STR_TO_DATE(t1.Date, '%d/%m/%y');

方法2：窗口函数（支持RANGE窗口的数据库，如PostgreSQL、BigQuery等） #

利用窗口函数按客户分区，同时通过日期范围定义窗口，实现滑动求和：

SELECT
    Customer_ID,
    Date,
    CONCAT('$', SUM(CAST(REPLACE(Revenue, '$', '') AS DECIMAL(10,2))) 
        OVER (PARTITION BY Customer_ID 
              ORDER BY STR_TO_DATE(Date, '%d/%m/%y') 
              RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW)) AS Revenue
FROM
    your_table
ORDER BY
    Customer_ID, STR_TO_DATE(Date, '%d/%m/%y');

注意事项 #

• 需先处理Revenue格式：去掉$符号并转换为数值类型才能正确求和
• 日期函数（如DATE_SUB、STR_TO_DATE）会因数据库类型不同略有差异，例如SQL Server用DATEADD，PostgreSQL用INTERVAL直接计算
• 自连接兼容性更强，窗口函数性能更优，适合大数据量场景

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