sql - Oracle SQL 对值求和，直到达到另一个值

Question

我希望我能以一种易于理解的方式描述我的挑战。我在 Oracle Database 12c 上有两个表，如下所示:

表名称“发票”

I_ID | invoice_number |     creation_date     | i_amount
------------------------------------------------------
  1  |  10000000000   |  01.02.2016 00:00:00  |   30
  2  |  10000000001   |  01.03.2016 00:00:00  |   25
  3  |  10000000002   |  01.04.2016 00:00:00  |   13
  4  |  10000000003   |  01.05.2016 00:00:00  |   18
  5  |  10000000004   |  01.06.2016 00:00:00  |   12

表名称“付款”

P_ID |   reference    |     received_date     | p_amount
------------------------------------------------------
  1  |  PAYMENT01     |  12.02.2016 13:14:12  |   12
  2  |  PAYMENT02     |  12.02.2016 15:24:21  |   28
  3  |  PAYMENT03     |  08.03.2016 23:12:00  |    2
  4  |  PAYMENT04     |  23.03.2016 12:32:13  |   30
  5  |  PAYMENT05     |  12.06.2016 00:00:00  |   15

所以我想要一个选择语句(也许使用Oracle分析功能，但我不太熟悉它)，其中付款被汇总，直到达到发票金额，按日期排序。例如，如果两次付款的总和大于发票金额，则最后一次付款金额的其余部分应用于下一张发票。

在此示例中，结果应如下所示:

invoice_number | reference | used_pay_amount | open_inv_amount
----------------------------------------------------------
 10000000000   | PAYMENT01 |       12        |        18
 10000000000   | PAYMENT02 |       18        |         0
 10000000001   | PAYMENT02 |       10        |        15
 10000000001   | PAYMENT03 |        2        |        13
 10000000001   | PAYMENT04 |       13        |         0
 10000000002   | PAYMENT04 |       13        |         0
 10000000003   | PAYMENT04 |        4        |        14
 10000000003   | PAYMENT05 |       14        |         0
 10000000004   | PAYMENT05 |        1        |        11 

如果有一个带有“简单”select 语句的解决方案，那就太好了。

提前感谢您的宝贵时间...

Answer 1

Oracle 设置:

CREATE TABLE invoices ( i_id, invoice_number, creation_date, i_amount ) AS
SELECT 1, 100000000, DATE '2016-01-01', 30 FROM DUAL UNION ALL
SELECT 2, 100000001, DATE '2016-02-01', 25 FROM DUAL UNION ALL
SELECT 3, 100000002, DATE '2016-03-01', 13 FROM DUAL UNION ALL
SELECT 4, 100000003, DATE '2016-04-01', 18 FROM DUAL UNION ALL
SELECT 5, 100000004, DATE '2016-05-01', 12 FROM DUAL;

CREATE TABLE payments ( p_id, reference, received_date, p_amount ) AS
SELECT 1, 'PAYMENT01', DATE '2016-01-12', 12 FROM DUAL UNION ALL
SELECT 2, 'PAYMENT02', DATE '2016-01-13', 28 FROM DUAL UNION ALL
SELECT 3, 'PAYMENT03', DATE '2016-02-08',  2 FROM DUAL UNION ALL
SELECT 4, 'PAYMENT04', DATE '2016-02-23', 30 FROM DUAL UNION ALL
SELECT 5, 'PAYMENT05', DATE '2016-05-12', 15 FROM DUAL;

查询:

WITH total_invoices ( i_id, invoice_number, creation_date, i_amount, i_total ) AS (
  SELECT i.*,
         SUM( i_amount ) OVER ( ORDER BY creation_date, i_id )
  FROM   invoices i
),
total_payments ( p_id, reference, received_date, p_amount, p_total ) AS (
  SELECT p.*,
         SUM( p_amount ) OVER ( ORDER BY received_date, p_id )
  FROM   payments p
)
SELECT invoice_number,
       reference,
       LEAST( p_total, i_total )
         - GREATEST( p_total - p_amount, i_total - i_amount ) AS used_pay_amount,
       GREATEST( i_total - p_total, 0 ) AS open_inv_amount
FROM   total_invoices
       INNER JOIN
       total_payments
       ON (    i_total - i_amount < p_total
           AND i_total > p_total - p_amount );

说明:

两个子查询因子分解 ( WITH ... AS () ) 子句只是向 invoices 添加一个额外的虚拟列和payments包含发票/付款金额累计金额的表格。

您可以将一个范围与每张发票(或付款)关联起来，作为开具发票(付款)之前的累积欠款(已付)金额和之后的累积欠款(已付)金额。然后可以在这些范围重叠的地方连接两个表。

open_inv_amount是累计开具发票金额与累计支付金额之间的正差。

used_pay_amount稍微复杂一些，但您需要找出当前累计发票和付款总额中较低者与之前累计发票和付款总额中较高者之间的差异。

输出:

INVOICE_NUMBER REFERENCE USED_PAY_AMOUNT OPEN_INV_AMOUNT
-------------- --------- --------------- ---------------
     100000000 PAYMENT01              12              18
     100000000 PAYMENT02              18               0
     100000001 PAYMENT02              10              15
     100000001 PAYMENT03               2              13
     100000001 PAYMENT04              13               0
     100000002 PAYMENT04              13               0
     100000003 PAYMENT04               4              14
     100000003 PAYMENT05              14               0
     100000004 PAYMENT05               1              11

更新:

基于 mathguy 的使用方法 UNION为了加入数据，我想出了一个不同的解决方案，重新使用我的一些代码。

WITH combined ( invoice_number, reference, i_amt, i_total, p_amt, p_total, total ) AS (
  SELECT invoice_number,
         NULL,
         i_amount,
         SUM( i_amount ) OVER ( ORDER BY creation_date, i_id ),
         NULL,
         NULL,
         SUM( i_amount ) OVER ( ORDER BY creation_date, i_id )
  FROM   invoices
  UNION ALL
  SELECT NULL,
         reference,
         NULL,
         NULL,
         p_amount,
         SUM( p_amount ) OVER ( ORDER BY received_date, p_id ),
         SUM( p_amount ) OVER ( ORDER BY received_date, p_id )
  FROM   payments
  ORDER BY 7,
           2 NULLS LAST,
           1 NULLS LAST
),
filled ( invoice_number, reference, i_prev, i_total, p_prev, p_total ) AS (
  SELECT FIRST_VALUE( invoice_number )  IGNORE NULLS OVER ( ORDER BY ROWNUM ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ),
         FIRST_VALUE( reference )       IGNORE NULLS OVER ( ORDER BY ROWNUM ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ),
         FIRST_VALUE( i_total - i_amt ) IGNORE NULLS OVER ( ORDER BY ROWNUM ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ),
         FIRST_VALUE( i_total )         IGNORE NULLS OVER ( ORDER BY ROWNUM ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ),
         FIRST_VALUE( p_total - p_amt ) IGNORE NULLS OVER ( ORDER BY ROWNUM ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ),
         COALESCE(
           p_total,
           LEAD( p_total ) IGNORE NULLS OVER ( ORDER BY ROWNUM ),
           LAG( p_total )  IGNORE NULLS OVER ( ORDER BY ROWNUM )
         )
   FROM  combined
),
vals ( invoice_number, reference, upa, oia, prev_invoice ) AS (
  SELECT invoice_number,
         reference,
         COALESCE( LEAST( p_total - i_total ) - GREATEST( p_prev, i_prev ), 0 ),
         GREATEST( i_total - p_total, 0 ),
         LAG( invoice_number ) OVER ( ORDER BY ROWNUM )
  FROM   filled 
)
SELECT invoice_number,
       reference,
       upa AS used_pay_amount,
       oia AS open_inv_amount
FROM   vals
WHERE  upa > 0
OR     ( reference IS NULL AND invoice_number <> prev_invoice AND oia > 0 );

说明:

combined子查询分解子句使用 UNION ALL 连接两个表并生成发票和支付金额的累计总额。它所做的最后一件事是按行的升序累积总数对行进行排序(如果存在平局，它将按创建的顺序将付款放在发票之前)。

filled子查询分解子句将填充先前生成的表，以便如果值为空，则它将从下一个非空行中获取值(如果有一张没有付款的发票，那么它将找到先前的总和来自前几行的付款)。

vals子查询分解子句应用与我之前的查询相同的计算(见上文)。它还添加了 prev_invoice列以帮助识别完全未付款的发票。

决赛SELECT获取值并过滤掉不必要的行。