Snowflake SQL关联查询问题：结果行拆分不符合预期

问题背景 #

使用Snowflake SQL关联查询时，得到多行拆分的结果，而预期是单行整合数据。相关表结构及数据如下：

表1（还款记录表） #

表2（分期信息表） #

当前问题 #

查询后返回两行结果：

但预期是单行整合数据：

解决方案 #

问题根源是表1中两条还款记录关联后拆分出两行，需要通过聚合或行转列将数据合并到单行。以下两种方法均可实现：

方法一：条件聚合直接关联 #

通过MAX(CASE...)筛选对应分期的还款数据，结合GROUP BY聚合单行：

SELECT
    t2.Application_ID,
    t2.Person_ID,
    t2.NUMBER_OF_INSTALLMENTS,
    t2.STARTING_BALANCE,
    MAX(t1.DUE_Date) AS due_date,
    t2.FIRST_INSTALLMENT_START_DATE,
    MAX(CASE WHEN t1.Repayment_date = t2.FIRST_INSTALLMENT_START_DATE THEN t1.Repayment_date END) AS FIRST_INSTALLMENT_REPAYMENT_DATE,
    MAX(CASE WHEN t1.Repayment_date = t2.FIRST_INSTALLMENT_START_DATE THEN t1.Repayment_amount END) AS FIRST_INSTALLMENT_REPAYMENT_AMOUNT,
    t2.SECOND_INSTALLMENT_DATE,
    MAX(CASE WHEN t1.Repayment_date >= t2.SECOND_INSTALLMENT_DATE THEN t1.Repayment_date END) AS SECOND_INSTALLMENT_REPAYMENT_DATE,
    MAX(CASE WHEN t1.Repayment_date >= t2.SECOND_INSTALLMENT_DATE THEN t1.Repayment_amount END) AS SECOND_INSTALLMENT_REPAYMENT_AMOUNT,
    t2.LAST_INSTALLMENT_DUE_DATE
FROM 表2 t2
LEFT JOIN 表1 t1 
    ON t2.Application_ID = t1.Application_Id 
    AND t2.Person_ID = t1.Person_ID
GROUP BY
    t2.Application_ID,
    t2.Person_ID,
    t2.NUMBER_OF_INSTALLMENTS,
    t2.STARTING_BALANCE,
    t2.FIRST_INSTALLMENT_START_DATE,
    t2.SECOND_INSTALLMENT_DATE,
    t2.LAST_INSTALLMENT_DUE_DATE;

方法二：先对表1行转列再关联 #

先用窗口函数给还款记录排序，再转成列后关联表2：

WITH pivoted_repayments AS (
    SELECT
        Application_Id,
        Person_ID,
        DUE_Date,
        MAX(CASE WHEN rn = 1 THEN Repayment_date END) AS first_repay_date,
        MAX(CASE WHEN rn = 1 THEN Repayment_amount END) AS first_repay_amount,
        MAX(CASE WHEN rn = 2 THEN Repayment_date END) AS second_repay_date,
        MAX(CASE WHEN rn = 2 THEN Repayment_amount END) AS second_repay_amount
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER(PARTITION BY Application_Id, Person_ID ORDER BY Repayment_date) AS rn
        FROM 表1
    ) t
    GROUP BY Application_Id, Person_ID, DUE_Date
)
SELECT
    pr.Application_Id,
    pr.Person_ID,
    t2.NUMBER_OF_INSTALLMENTS,
    t2.STARTING_BALANCE,
    pr.DUE_Date,
    t2.FIRST_INSTALLMENT_START_DATE,
    pr.first_repay_date AS FIRST_INSTALLMENT_REPAYMENT_DATE,
    pr.first_repay_amount AS FIRST_INSTALLMENT_REPAYMENT_AMOUNT,
    t2.SECOND_INSTALLMENT_DATE,
    pr.second_repay_date AS SECOND_INSTALLMENT_REPAYMENT_DATE,
    pr.second_repay_amount AS SECOND_INSTALLMENT_REPAYMENT_AMOUNT,
    t2.LAST_INSTALLMENT_DUE_DATE
FROM pivoted_repayments pr
JOIN 表2 t2 
    ON pr.Application_Id = t2.Application_ID 
    AND pr.Person_ID = t2.Person_ID;

两种方法都能将分散的还款数据整合到单行，符合预期结果。

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