Clickhouse按今日回溯X天分组计算累计销售总额

针对需求，这里提供两种符合要求的查询方案：

方案一：单条记录展示多时间范围累计值 #

该方案将三个时间范围的累计销量放在同一行，方便对比分析：

SELECT
    Shop,
    Product,
    SUM(CASE WHEN Realdate >= today() - INTERVAL 7 DAY THEN QuantitySold ELSE 0 END) AS SumSold7DaysFromToday,
    SUM(CASE WHEN Realdate >= today() - INTERVAL 14 DAY THEN QuantitySold ELSE 0 END) AS SumSold14DaysFromToday,
    SUM(CASE WHEN Realdate >= today() - INTERVAL 21 DAY THEN QuantitySold ELSE 0 END) AS SumSold21DaysFromToday
FROM TestSumSales
WHERE Realdate >= today() - INTERVAL 21 DAY -- 仅扫描最近21天数据，提升效率
GROUP BY Shop, Product
ORDER BY Shop, Product;

方案二：分记录展示各时间范围累计值 #

该方案输出格式与示例完全一致，每个时间范围对应一条记录：

SELECT
    Shop,
    Product,
    'SumSold7DaysFromToday' AS Period,
    SUM(QuantitySold) AS TotalSold
FROM TestSumSales
WHERE Realdate >= today() - INTERVAL 7 DAY
GROUP BY Shop, Product
UNION ALL
SELECT
    Shop,
    Product,
    'SumSold14DaysFromToday' AS Period,
    SUM(QuantitySold) AS TotalSold
FROM TestSumSales
WHERE Realdate >= today() - INTERVAL 14 DAY
GROUP BY Shop, Product
UNION ALL
SELECT
    Shop,
    Product,
    'SumSold21DaysFromToday' AS Period,
    SUM(QuantitySold) AS TotalSold
FROM TestSumSales
WHERE Realdate >= today() - INTERVAL 21 DAY
GROUP BY Shop, Product
ORDER BY Shop, Product, Period;

说明 #

• 使用today()函数获取当前日期，INTERVAL X DAY实现时间范围的回溯计算；
• 添加WHERE过滤条件可避免扫描全表，针对一年的交易数据能显著提升查询速度；
• 两种方案均按Shop和Product维度分组，确保累计销量是对应店铺和商品的合计值。

内容的提问来源于stack exchange，提问作者Morten V. Gade