sql - 从余额行递归减去存款

Question

考虑以下 2 个表:

表 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 中减去与 PIN 匹配的最早 REFERENCE_DATE。如果差异大于 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

只要 DEPOSIT 覆盖 BALANCE

就设置为 0

正如您所阐明的，您不需要 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
  • Querying count on daily basis with date constraints over multiple weeks

• 在最后的 SELECT 中，如果 last_sum 等于或大于 DEPOSIT，则返回原始的 BALANCE (已经花掉了)。 ELSE 返回剩余差额或 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);