余额行中存款的递归减法

Question

考虑以下两个表：

表A：

PIN | ENCOUNTER | BALANCE | REFERENCE_DATE 
------------------------------------------
P1  | ABC       | 100     | 11-19-2014     
P1  | HJI       | 300     | 11-20-2014     
P1  | PIY       | 700     | 11-21-2014     
P2  | CDO       | 200     | 11-20-2014     
P2  | NHG       | 200     | 11-21-2014    
P3  | CVB       | 500     | 11-20-2014
P3  | SJK       | 100     | 11-21-2014     

表B：

PIN | DEPOSIT
-------------
P1  | 1000
P2  | 400
P3  | 100

最初，表B的DEPOSIT值将从表A中的BALANCE中减去，其最早的REFERENCE_DATE与PIN匹配。如果差值大于0，则从下一行的BALANCE中减去该值，直到剩余的DEPOSIT小于或等于0为止。

从余额中减去存款后的结果将如下所示。我还包括了另一栏，其中的存款按遇到的情况划分：

PIN | ENCOUNTER | BALANCE | REFERENCE_DATE | DEPOSITS_BREAKDOWN
---------------------------------------------------------------
P1  | ABC       | 0       | 11-19-2014     | 100
P1  | HJI       | 0       | 11-20-2014     | 300
P1  | PIY       | 100     | 11-21-2014     | 600
P2  | CDO       | 0       | 11-20-2014     | 200
P2  | NHG       | 0       | 11-21-2014     | 200
P3  | CVB       | 400     | 11-20-2014     | 100
P3  | SJK       | 100     | 11-21-2014     | 0

我的Postgres版本是9.3。我很难为这个查询做好准备。

Answer 1

设置为0，直到DEPOSIT覆盖BALANCE为止。

正如您澄清的，您不希望运行BALANCE和，只需将其设置为0，直到使用DEPOSIT：

SELECT PIN, ENCOUNTER
     , CASE WHEN last_sum >= DEPOSIT THEN BALANCE
            ELSE GREATEST (last_sum + BALANCE - DEPOSIT, 0) END AS BALANCE
     , REFERENCE_DATE
     , CASE WHEN last_sum >= DEPOSIT THEN 0
            ELSE LEAST (BALANCE, DEPOSIT - last_sum) END AS DEPOSITS_BREAKDOWN
FROM (
   SELECT a.*
        , COALESCE(sum(a.BALANCE) OVER (
                         PARTITION BY PIN ORDER BY a.REFERENCE_DATE
                         ROWS BETWEEN UNBOUNDED PRECEDING
                                          AND 1 PRECEDING), 0) AS last_sum
        , COALESCE(b.DEPOSIT, 0) AS DEPOSIT
   FROM        table_a a
   LEFT   JOIN table_b b USING (pin)
   ) sub;

准确地返回所需的结果。

SQL Fiddle

• 我采纳了@vyegorov中简单连接的想法。
• 从LEFT JOIN到table_b --这样就有可能在table_b中找不到行。
• 在子查询中，计算BALANCE的运行和，直到最后一行(last_sum)。为此，请在窗口函数中使用自定义框架。在没有行的情况下，COALESCE默认为0。相关的答案以及对自定义框架的更多解释：

- [How to use a ring data structure in window functions](https://stackoverflow.com/questions/25030179/how-to-use-a-ring-data-structure-in-window-functions/25039137#25039137)
- [Querying count on daily basis with date constraints over multiple weeks](https://stackoverflow.com/questions/26899491/querying-count-on-daily-basis-with-date-constraints-over-multiple-weeks/26899977#26899977)

​

• 在最后一个SELECT中，如果last_sum等于或大于DEPOSIT (已使用)，则返回原始last_sum。否则返回剩余的差，或者0是BALANCE (last_sum + BALANCE)的运行和小于DEPOSIT。

运行和

使用BALANCE作为运行和(最后一行500而不是100)的先前(更简单)答案：

SELECT a.PIN, a.ENCOUNTER
     , GREATEST(sum(a.BALANCE) OVER (PARTITION BY PIN ORDER BY a.REFERENCE_DATE) 
                 - COALESCE(b.DEPOSIT, 0), 0) AS BALANCE
     , a.REFERENCE_DATE
FROM   table_a      a
LEFT   JOIN table_b b USING (pin);