在 SQL 查询中仅对特定列和 FIFO 计算进行分组

Question

我有以下示例数据：

order_id    receipt_id    receipt_amount    total_already_invoiced_amount    
14          36            30                150
14          37            30                150
15          42            30                30
16          45            30                60
16          46            40                60
17          50            40                60
17          51            40                60
17          52            40                60

列receipt_amount是该特定行收到的订单金额。 total_already_invoiced_amount列是订单开具发票的总金额。

我想将此表转换为一个新表，该表仅保留扣除发票总金额(先进先出(后剩余的已收到金额的行。 例如，如果我有 3 个收货行，每行 40 个，而我的发票总额为 60，那么我可以计算出第一个收货行已完全开票，第二个收货行有 20 个剩余的待开票，第三个根本没有开票。我无法汇总，我必须将receipt_id保留为索引，因为它们可以有不同的日期，我需要能够根据该日期进行区分。 此类查询的结果如下：

order_id    receipt_id    received_not_invoiced_amount    
16          46            10
17          51            20
17          52            40

我知道我可以按order_id选择组来获取聚合receipt_amount，但它也会聚合total_already_invoiced_amount，这不是我想要的。我正在尝试以下方法，但这不会执行FIFO计算....

SELECT order_id, 
receipt_id, 
(total_already_invoiced_amount - 
(SELECT receipt_amount FROM X GROUP BY order_id)
) total_already_invoiced_amount
FROM X
WHERE (total_already_invoiced_amount - 
(SELECT receipt_amount FROM X GROUP BY order_id)) < 0

我有点迷茫从哪里开始做这项工作。

Answer 1

在没有窗口函数(MySQL 5.7中不可用(的情况下，一种方法是执行自连接并计算订单所有收据的总和，直到第一个表的收据行。然后，我们可以使用条件语句相应地确定差异：

查询 #1 DB 小提琴上的视图

SELECT t1.order_id,
t1.receipt_id,
CASE
WHEN Coalesce(Sum(t2.receipt_amount), 0) <=
t1.total_already_invoiced_amount
THEN 0
ELSE Least(Coalesce(Sum(t2.receipt_amount), 0) -
t1.total_already_invoiced_amount,
t1.receipt_amount)
end AS received_not_invoiced_amount
FROM   X t1
LEFT JOIN X t2
ON t2.order_id = t1.order_id
AND t2.receipt_id <= t1.receipt_id
GROUP  BY t1.order_id,
t1.receipt_id,
t1.receipt_amount,
t1.total_already_invoiced_amount
HAVING received_not_invoiced_amount > 0;
| order_id | receipt_id | received_not_invoiced_amount |
| -------- | ---------- | ---------------------------- |
| 16       | 46         | 10                           |
| 17       | 51         | 20                           |
| 17       | 52         | 40                           |

为了获得良好的性能，可以定义以下复合索引：(order_id, receipt_id)。

---

另一种方法是使用用户定义的变量。这就像一种循环技术，当我们向下移动收据时，我们计算order_id的滚动(累积(总和。根据金额，我们相应地确定是否收到超额付款。有关此技术的更详细说明，您可以查看此答案：https://stackoverflow.com/a/53465139

查询 #2 在数据库小提琴上查看

SELECT order_id,
receipt_id,
received_not_invoiced_amount
FROM   (SELECT @s := IF(@o = order_id, @s + receipt_amount, receipt_amount) AS
cum_receipt_amount,
IF(@s <= total_already_invoiced_amount, 0,
Least(@s - total_already_invoiced_amount, receipt_amount))   AS
received_not_invoiced_amount,
@o := order_id                                               AS
order_id
,
receipt_id
FROM   (SELECT *
FROM   X
ORDER  BY order_id,
receipt_id) t1
CROSS JOIN (SELECT @o := 0,
@s := 0) vars) t2
WHERE  received_not_invoiced_amount > 0;
| order_id | receipt_id | received_not_invoiced_amount |
| -------- | ---------- | ---------------------------- |
| 16       | 46         | 10                           |
| 17       | 51         | 20                           |
| 17       | 52         | 40                           |

为了获得良好的性能，您可以定义相同的复合索引：(order_id, receipt_id)。

---

您可以对这两种方法进行基准测试以获得最佳性能。

Answer 2

您需要一个累积总和：

select order_id, receipt_id,
least(running_ra, total_already_invoiced_amount), receipt_amount)
from (select x.*,
sum(receipt_amount) over (partition by order_id order by receipt_id) as running_ra
from x
) x
where running_ra > total_already_invoiced_amount