如何创建SQL视图展示(location,item)组合的累计数量与费用随时间变化

现有表结构与数据 #

我们有一张名为transactions的SQL表，主键为tx_date、location、item，表中数据如下：

tx_date       location    item         quantity    fee
2024-01-01    US          ITEM.123         1000     20
2024-02-04    EMEA        ITEM.456789       600     12
2024-03-09    US          ITEM.123         -750    -30
2024-04-12    EMEA        ITEM.456789       700     25
2024-05-16    EMEA        ITEM.456789      -800    -35

需求 #

需要创建一个视图，展示每个(location, item)组合在各tx_date的累计求和结果（net_quantity为累计数量，net_fee为累计费用），期望输出如下：

tx_date       location    item        net_quantity   net_fee
2024-01-01    US          ITEM.123            1000        20
2024-02-04    EMEA        ITEM.456789          600        12
2024-03-09    US          ITEM.123             250       -10
2024-04-12    EMEA        ITEM.456789         1300        37
2024-05-16    EMEA        ITEM.456789          500         2

解决方案 #

使用窗口函数SUM() OVER()可以轻松实现分组累计求和，按location和item分组后，按交易日期顺序计算累计值。创建视图的SQL语句如下：

CREATE VIEW transaction_net AS
SELECT
    tx_date,
    location,
    item,
    SUM(quantity) OVER (PARTITION BY location, item ORDER BY tx_date) AS net_quantity,
    SUM(fee) OVER (PARTITION BY location, item ORDER BY tx_date) AS net_fee
FROM transactions
ORDER BY tx_date;

关键说明 #

• PARTITION BY location, item：将数据按location和item的组合拆分，每组内独立计算累计值
• ORDER BY tx_date：保证每组内按照交易时间的先后顺序累加，符合时间维度的累计逻辑
• 生成的视图transaction_net会包含原表所有字段，同时新增累计计算后的net_quantity和net_fee列，结果与预期输出完全匹配

内容的提问来源于stack exchange，提问作者Phil-ZXX