如何修改SQL查询以计算多时间戳间sales_quantity的差值？

问题背景 #

我们有一张sales表，记录了不同日期各产品的销量，需要计算每个日期的总销量与前一日总销量的差值，第一天的差值为0。

测试数据 #

CREATE TABLE sales (
  id int auto_increment primary key,
  time_stamp DATE,
  product VARCHAR(255),
  sales_quantity INT
);

INSERT INTO sales (time_stamp, product, sales_quantity )
VALUES
("2020-01-14", "Product_A", "100"),
("2020-01-14", "Product_B", "300"),
("2020-01-14", "Product_C", "600"),
("2020-01-15", "Product_A", "100"),
("2020-01-15", "Product_B", "350"),
("2020-01-15", "Product_C", "600"),
("2020-01-16", "Product_A", "130"),
("2020-01-16", "Product_B", "350"),
("2020-01-16", "Product_C", "670"),
("2020-01-16", "Product_D", "400"),
("2020-01-17", "Product_A", "130"),
("2020-01-17", "Product_B", "350"),
("2020-01-17", "Product_C", "700"),
("2020-01-17", "Product_D", "450");

期望结果 #

解决方案 #

你的原查询硬编码了固定日期，无法动态处理连续日期的对比。我们可以通过**窗口函数LAG()**来实现需求，步骤如下：

1. 先计算每个日期的总销量；
2. 用LAG()获取前一日的总销量，默认值设为0（确保第一天差值为0）；
3. 计算当前日总销量与前一日总销量的差值。

完整SQL查询：

SELECT
  time_stamp,
  daily_total - COALESCE(LAG(daily_total) OVER (ORDER BY time_stamp), 0) AS difference
FROM (
  SELECT
    time_stamp,
    SUM(sales_quantity) AS daily_total
  FROM sales
  GROUP BY time_stamp
) AS daily_sales
ORDER BY time_stamp;

代码解释 #

• 子查询daily_sales：按日期分组，计算每日的总销量daily_total；
• LAG(daily_total) OVER (ORDER BY time_stamp)：按日期排序，获取前一行的daily_total值；
• COALESCE(..., 0)：如果是第一行（没有前一行），则用0代替NULL，这样第一天的差值就是daily_total - 0 = 0；
• 最终计算daily_total - 前一日总销量得到每日的差值。

执行这个查询后，就能得到你期望的结果啦。

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