如何用SQL窗口函数计算客户累计支付总额并生成Parquet表？

现有两张业务表：

• Table1：存储客户基础信息
• Table2：存储客户全部交易支付记录（每行对应客户某一日期的单笔支付信息及支付金额）

需求：生成Parquet格式结果表，要求截至2022-03-31，每个客户仅展示最后一笔支付的日期、支付序号，同时将pay_sum列替换为该客户所有支付的累计总额。

当前使用的SQL可筛选出每个客户的最后一笔支付记录，但pay_sum仅显示单笔金额：

create table new stored as parquet as select * from (Select * from
(select table1.id, name, pay_date, pay_num, pay_sum, row_number() over (partition by table2.id order by pay_date DESC) rn
from table1
inner join table2 on table1.id = table2.id
where pay_date <= cast("2022-03-31" as timestamp))
where rn = 1) t

尝试对金额求和未得到正确累计总额，如何用SQL窗口函数实现需求？

只需在子查询中添加一个基于客户ID分区的SUM()窗口函数，计算该客户所有符合条件的支付金额累计总额，同时保留原有的row_number()筛选逻辑即可。修改后的SQL如下：

CREATE TABLE new STORED AS PARQUET AS
SELECT 
    id,
    name,
    pay_date,
    pay_num,
    total_pay_sum AS pay_sum  -- 将累计总额重命名为pay_sum
FROM (
    SELECT 
        table1.id,
        name,
        pay_date,
        pay_num,
        SUM(pay_sum) OVER (PARTITION BY table2.id) AS total_pay_sum,  -- 计算客户累计支付总额
        ROW_NUMBER() OVER (PARTITION BY table2.id ORDER BY pay_date DESC) AS rn
    FROM table1
    INNER JOIN table2 ON table1.id = table2.id
    WHERE pay_date <= CAST("2022-03-31" AS TIMESTAMP)
) t
WHERE rn = 1;

关键说明： #

• SUM(pay_sum) OVER (PARTITION BY table2.id)：该窗口函数会对每个客户（按table2.id分区）的所有pay_sum进行求和，得到累计总额，且这个值会出现在该客户的每一行记录中。
• 保留ROW_NUMBER() OVER (PARTITION BY table2.id ORDER BY pay_date DESC)：用于标记每个客户的最后一笔支付记录（rn=1）。
• 最后筛选rn=1的行，此时对应的total_pay_sum就是该客户的累计总额，将其重命名为pay_sum即可满足需求。

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