如何用SQL编写每日累计总营收的查询语句

你原有SQL的问题是未先按天聚合单日营收，直接对原始交易行使用窗口函数，会返回和原始交易记录数一致的结果，无法得到每日一行的统计输出。
正确的实现需要先分组统计每天的总营收，再基于单日营收计算累加的累计总营收，标准SQL写法如下：

WITH daily_revenue AS (
    -- 先聚合计算每日总营收
    SELECT 
        DATE_TRUNC('day', "Timestamp") AS day,
        SUM("Price") AS price
    FROM cleandataset
    GROUP BY day
)
SELECT 
    day,
    price,
    -- 按日期排序计算累计营收
    SUM(price) OVER (ORDER BY day ASC) AS cumulativetotalrevenue
FROM daily_revenue
ORDER BY day ASC;

如果你的数据库不支持CTE（公共表表达式），可以改用子查询实现：

SELECT 
    day,
    price,
    SUM(price) OVER (ORDER BY day ASC) AS cumulativetotalrevenue
FROM (
    SELECT 
        DATE_TRUNC('day', "Timestamp") AS day,
        SUM("Price") AS price
    FROM cleandataset
    GROUP BY DATE_TRUNC('day', "Timestamp")
) AS daily_stats
ORDER BY day ASC;

适配注意事项 #

• 如果你使用的数据库不支持DATE_TRUNC函数（比如MySQL），可以将日期解析部分替换为DATE(STR_TO_DATE("Timestamp", '%d/%m/%Y %H:%i'))，适配你数据集中日/月/年 时:分的时间格式
• 如果需要输出和示例一致的日/月/年格式日期，可以额外加日期格式化函数：PostgreSQL用TO_CHAR(day, 'DD/MM/YYYY')，MySQL用DATE_FORMAT(day, '%d/%m/%Y')

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