时间戳

首页 > 解决方案 > 在 SQL Query 中仅对特定列进行分组和 FIFO 计算

mysql - 在 SQL Query 中仅对特定列进行分组和 FIFO 计算

问题描述

我有以下示例数据：

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

我知道我可以选择 group byorder_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

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

标签： mysqlsql

解决方案

你想要一个累积的总和：

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

推荐阅读