计算不同时间戳下每种产品的数量差异

debugcn 发表于 Dev

美智

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", "250"),
("2020-01-15", "Product_C", "600"),

("2020-01-16", "Product_A", "130"),
("2020-01-16", "Product_B", "250"),
("2020-01-16", "Product_C", "580"),
("2020-01-16", "Product_D", "400"),

("2020-01-17", "Product_A", "130"),
("2020-01-17", "Product_B", "250"),
("2020-01-17", "Product_C", "900"),
("2020-01-17", "Product_D", "260");

预期结果：

time_stamp   |   product      |    difference
-------------|----------------|---------------------------
2020-01-14   |   Product_A    |       0     (100-100)
2020-01-14   |   Product_B    |     -50     (250-300)
2020-01-14   |   Product_C    |       0     (600-600)
-------------|----------------|---------------------------
2020-01-15   |   Product_A    |      30     (130-100)
2020-01-15   |   Product_B    |       0     (250-250)
2020-01-15   |   Product_C    |     -20     (580-600)
-------------|----------------|---------------------------
2020-01-16   |   Product_A    |       0     (130-130) 
2020-01-16   |   Product_B    |       0     (250-250)
2020-01-16   |   Product_C    |     320     (900-580) 
2020-01-16   |   Product_D    |    -140     (260-400)

我想计算difference的的sales_quantity每product之间timestamps。
我尝试使用此查询，但未返回预期结果：

SELECT
t1.time_stamp,
t1.product,
t1.sum_sales_quantity - coalesce(lag(t1.sum_sales_quantity) over(order by t1.time_stamp, t1.product), t1.sum_sales_quantity) AS difference
FROM (
    SELECT 
    time_stamp,
    product,
    SUM(sales_quantity) AS sum_sales_quantity
    FROM sales
    GROUP BY 1,2) t1
ORDER BY 1,2;

我必须如何修改查询才能使其正常工作？

戈登·利诺夫

看起来像您想要的lead()：

SELECT s.*,
       (s.sales_quantity -
        LEAD(s.sales_quantity) OVER (PARTITION BY s.product ORDER BY s.time_stamp)
       ) as diff
FROM sales s;
ORDER BY 1, 2;

注意：这包括的数据'2020-01-17'。如果要删除它，最简单的方法是子查询：

SELECT s.*,
       (next_sales - s.sales_quantity) as diff
FROM (SELECT s.*,
             LEAD(s.sales_quantity) OVER (PARTITION BY s.product ORDER BY s.time_stamp) as next_sales
      FROM sales s
     ) s
WHERE next_sales IS NOT NULL
ORDER BY 1, 2;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。