MySQL - 如何查询涉及前面计算值的表结果

Question

假设我有一个表 items 按 date 升序排序

<表类="s-表"><头><日>日期 项目数量费用<正文>2022-12-01铅笔1210.002022-12-02铅笔1010.002022-12-04铅笔510.002022-12-06橡皮擦104.002022-12-10橡皮擦504.002022-12-15橡皮擦254.00

我需要编写一个 SQL 查询来返回一个名为 calculated_cost 的计算字段，其中的表达式只是 quantity * cost

然后我需要为每一行递增 calculated_cost 并将其保存到名为 accumulated_cost 的字段中

然而，这就是挑战，我还需要存储一个名为 previous_accumulated_cost 的字段，它将前面的 accumulated_cost 存储为一个值。

请注意，我还需要通过基于 item 的分区和按 date 排序来计算这些，这意味着我需要重置 accumulated_cost 和previous_accumulated_cost 当我到达一个新项目时。

基本上我需要生成这样的输出。

<表类="s-表"><头><日>日期 项目数量费用计算成本累计成本previous_accumulated_cost<正文>2022-12-01铅笔1210.00120.00120.000.002022-12-02铅笔1010.00100.00220.00120.002022-12-04铅笔510.0050.00270.00220.002022-12-06橡皮擦104.0040.0040.000.002022-12-10橡皮擦504.00200.00240.0040.002022-12-15橡皮擦254.00100.00340.00240.00

我已经尝试过这样的 SQL 查询

SELECT *,
   (i.quantity * i.cost) AS calculated_cost,
   SUM(i.quantity * i.cost) OVER (PARTITION BY i.item ORDER BY i.date) AS accumulated_cost,
   IFNULL(LAG(i2.accumulated_cost) OVER (PARTITION BY i.item ORDER BY i.date), 0) AS previous_accumulated_cost
FROM items i
LEFT JOIN (
   SELECT item, SUM(quantity * cost) OVER (PARTITION BY item ORDER BY date) AS accumulated_cost
   FROM items 
) i2 ON i.item = i2.item

但这不起作用，因为项目条目的数量会不断增加，我不确定如何继续引用 previous_accumulated_cost

希望得到一些帮助。谢谢!

Answer 1

您使用分析函数的直觉是正确的。我建议使用这个版本:

SELECT
    quantity * cost AS calculated_cost,
    SUM(quantity * cost) OVER (PARTITION BY item ORDER BY date) AS accumulated_cost,
    SUM(quantity * cost) OVER (PARTITION BY item ORDER BY date) - (quantity * cost) AS previous_accumulated_cost
FROM items
ORDER BY item, date;

请注意，previous_accumulated_cost 只是 accumulated_cost 减去 calculated_cost。