基于正贷款触发重置的条件累计求和（间隙与孤岛问题）

现有数据 #

每行代表一笔交易，支出金额为负，收入为正。交易类型包括：消费（event='spend'）、贷款发放（amount>0且event='loan'）、贷款还款（amount<0且event='loan'），具体数据如下：

目标结果 #

需要生成包含cumulative_sum字段的结果表：

逻辑要求 #

• 仅对以下交易累计金额：(amount < 0 AND event = 'spend') OR (amount > 0 AND event = 'loan')
• 累计求和需在**正贷款金额（即amount>0且event='loan'）**出现时重置，忽略该贷款前的所有交易，仅统计当前贷款覆盖的消费交易

我的尝试 #

WITH tmp AS (
  SELECT
  1 AS id, 
  '2021-01-01' AS created,
  -200 AS amount,
  'spend' AS event

  UNION ALL

  SELECT
  1 AS id, 
  '2022-01-02' AS created,
  1000 AS amount,
  'loan' AS event

  UNION ALL

  SELECT
  1 AS id, 
  '2022-01-03' AS created,
  -200 AS amount,
  'spend' AS event
  
  UNION ALL

  SELECT
  1 AS id, 
  '2022-01-04' AS created,
  -500 AS amount,
  'spend' AS event
  
  UNION ALL

  SELECT
  1 AS id, 
  '2022-01-05' AS created,
  -500 AS amount,
  'loan' AS event
  
  UNION ALL

  SELECT
  1 AS id, 
  '2022-01-06' AS created,
  100 AS amount,
  'spend' AS event

  UNION ALL

  SELECT
  1 AS id, 
  '2022-01-07' AS created,
  -500 AS amount,
  'spend' AS event

  UNION ALL

  SELECT
  1 AS id, 
  '2022-01-08' AS created,
  1000 AS amount,
  'loan' AS event

  UNION ALL

  SELECT
  1 AS id, 
  '2022-01-09' AS created,
  -100 AS amount,
  'spend' AS event

)

SELECT 
*,
SUM(CASE WHEN (event != 'loan' AND amount<0) OR (event = 'loan' AND amount > 0) THEN amount ELSE 0 END)
    OVER (PARTITION BY id ORDER BY created ASC) AS cumulative_sum_spend
 FROM tmp

问题 #

如何实现累计求和在正贷款金额出现时自动重置（不依赖行号，仅依据正贷款事件）？

解决方案 #

核心思路是用窗口函数生成分组标识：计算每一行之前（包括当前行）出现的正贷款事件的次数，以此作为分组依据，每次正贷款出现时，分组标识会递增，累计求和就会在新分组内重新计算。

修改后的SQL代码如下：

WITH tmp AS (
  SELECT
  1 AS id, 
  '2022-01-01' AS created,
  -200 AS amount,
  'spend' AS event

  UNION ALL

  SELECT
  1 AS id, 
  '2022-01-02' AS created,
  1000 AS amount,
  'loan' AS event

  UNION ALL

  SELECT
  1 AS id, 
  '2022-01-03' AS created,
  -200 AS amount,
  'spend' AS event
  
  UNION ALL

  SELECT
  1 AS id, 
  '2022-01-04' AS created,
  -500 AS amount,
  'spend' AS event
  
  UNION ALL

  SELECT
  1 AS id, 
  '2022-01-05' AS created,
  -500 AS amount,
  'loan' AS event
  
  UNION ALL

  SELECT
  1 AS id, 
  '2022-01-06' AS created,
  100 AS amount,
  'spend' AS event

  UNION ALL

  SELECT
  1 AS id, 
  '2022-01-07' AS created,
  -500 AS amount,
  'spend' AS event

  UNION ALL

  SELECT
  1 AS id, 
  '2022-01-08' AS created,
  1000 AS amount,
  'loan' AS event

  UNION ALL

  SELECT
  1 AS id, 
  '2022-01-09' AS created,
  -100 AS amount,
  'spend' AS event
),
-- 生成分组标识：统计每行之前的正贷款次数
grouped_data AS (
  SELECT
    *,
    -- 累计正贷款事件的数量，作为分组key
    SUM(CASE WHEN event = 'loan' AND amount > 0 THEN 1 ELSE 0 END) 
      OVER (PARTITION BY id ORDER BY created ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS loan_group
  FROM tmp
)
SELECT
  row_number() OVER (ORDER BY created) AS "row number",
  id,
  created,
  amount,
  event,
  -- 按分组key进行累计求和，只计算符合条件的金额
  SUM(CASE 
        WHEN (event = 'spend' AND amount < 0) OR (event = 'loan' AND amount > 0) THEN amount 
        ELSE 0 
      END) OVER (PARTITION BY id, loan_group ORDER BY created ASC) AS cumulative_sum
FROM grouped_data
ORDER BY created;

代码说明 #

1. loan_group字段：通过窗口函数累计当前行及之前出现的正贷款次数，每次出现正贷款时，该值会+1，形成新的分组。
2. 累计求和：将PARTITION BY条件改为id, loan_group，这样每次进入新的贷款分组时，求和会从0开始重新计算，实现重置效果。
3. 忽略无关交易：通过CASE语句只对符合要求的交易（负消费、正贷款）进行求和，贷款还款和正消费不会计入累计。

内容的提问来源于stack exchange，提问作者Shervin Rad