如何在Snowflake中统计各计划还款对应的NSF Fee数量？

我有一个Snowflake数据库，存储了贷款的计划还款（Scheduled Payment）及各类费用数据，示例表数据如下：

需要统计每个计划还款周期内的NSF Fee数量，示例中3笔计划还款分别对应1笔、2笔、0笔NSF Fee。

当前使用的查询语句：

with payment_schedule as (select * from TABLE where action = 'Scheduled Payment'),
nsf_fees as (select * from TABLE where action = 'NSF Fee')
select pay.date, count(nsf.date) over (partition by pay.date) as nsf_count 
from payment_schedule pay 
join nsf_fees nsf on pay.loan_id = nsf.loan_id

现在需要得到包含每个计划还款日期及对应NSF Fee计数的正确结果，请问该如何编写查询语句？

你的原查询存在两个核心问题：

1. 用JOIN会过滤掉无NSF Fee的计划还款记录（比如2023-01-22的记录），无法得到计数为0的结果
2. 未明确"计划还款周期"的范围——NSF Fee需要归属到最近的、早于它的计划还款日期对应的周期

正确查询语句 #

WITH payment_schedule AS (
    SELECT 
        Date AS scheduled_date,
        Loan_ID,
        -- 获取下一个计划还款日期，作为当前周期的结束边界
        LEAD(Date) OVER (PARTITION BY Loan_ID ORDER BY Date) AS next_scheduled_date
    FROM your_table_name
    WHERE Action = 'Scheduled Payment'
),
nsf_fees AS (
    SELECT Date AS nsf_date, Loan_ID
    FROM your_table_name
    WHERE Action = 'NSF Fee'
)
SELECT 
    ps.scheduled_date,
    COUNT(nf.nsf_date) AS nsf_count
FROM payment_schedule ps
LEFT JOIN nsf_fees nf 
    ON ps.Loan_ID = nf.Loan_ID
    -- 匹配当前计划还款周期内的NSF Fee：日期大于等于当前计划日，且小于下一个计划日（最后一笔无下一日则无上限）
    AND nf.nsf_date >= ps.scheduled_date
    AND (nf.nsf_date < ps.next_scheduled_date OR ps.next_scheduled_date IS NULL)
GROUP BY ps.scheduled_date, ps.Loan_ID, ps.next_scheduled_date
ORDER BY ps.scheduled_date;

逻辑说明 #

1. 定义周期边界：用LEAD()窗口函数获取每个计划还款的下一个计划日，当前周期为[scheduled_date, next_scheduled_date)区间；最后一笔计划还款的周期为该日期往后的所有时间（可根据业务调整）
2. 保留所有计划记录：用LEFT JOIN确保即使没有NSF Fee，也能返回计数为0的结果
3. 精准匹配统计：通过日期条件过滤出对应周期内的NSF Fee，再分组统计数量

示例结果 #

执行后将得到符合预期的结果：

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