Trailing 12个月（TTM）计算结果不符合预期问题问询

我有一张存储每日业务数据的表，需要按product、currency以及截断至月份的date维度，添加Trailing 12个月（TTM）的收入求和列。测试了两种聚合写法，都没得到正确结果（正确值为66776，错误结果为558），现分析问题原因如下：

样本表与数据 #

create table test (day date, id int, product varchar, cur varchar, amount int);
insert into test
values
('10/1/2023', 259, 'A', 'USD', 558), ('9/30/2023', 1502, 'C', 'USD', 1991), ...（完整数据见原输入）

正确查询示例 #

select sum(amount)
from test
where product = 'A'
    and day >= '2022-11-01'

返回结果：66776

错误查询示例1 #

select sum(ttm_total) s
from (
    select
        id,
        product,
        cur,
        month,
        sum(monthly_total) over (partition by id, product, cur
                                order by month
                                rows between 12 preceding and current row)
                                as ttm_total
    from monthly) x
where product = 'A'
    and month = '2023-10-01'

返回结果：558

错误查询示例2 #

with monthly as
(
select
    id,
    product,
    cur,
    date_trunc('month', day) :: date as month,
    sum(amount) as monthly_total
from test
group by 1, 2, 3, 4
),

test as
(
    SELECT
        b1.id,
        b1.product,
        b1.cur,
        b1.month,
        (
            SELECT SUM(monthly_total) AS ttm_total
            FROM monthly AS b2
            WHERE date_diff('month', b2.month, b1.month) <= 12
                AND b1.id = b2.id
                AND b1.product = b2.product
                AND b1.cur = b2.cur
        ) AS ttm_total
    FROM monthly AS b1
)
select
    sum(ttm_total) s -- 558
from test
where product = 'A'
    and month = '2023-10-01';

返回结果：558

问题原因分析 #

1. 核心逻辑偏差：错误引入id维度的分组/关联
   正确查询是对所有product='A'的记录，不区分id，直接统计2022-11-01至今的收入总和。但两个错误查询都额外加入了id维度的处理：

   • 错误查询1的窗口函数使用partition by id, product, cur，导致每个id单独计算自己的12个月滚动和。而2023-10-01的product='A'数据仅存在于id=259的记录中，该id只有当月的558收入，因此它的TTM值就是558，最终sum结果自然为558。
   • 错误查询2的子查询通过b1.id = b2.id关联，同样只计算每个id自身的TTM。筛选后仅id=259的记录符合条件，sum后得到558。

2. 时间范围逻辑的隐性问题
   错误查询2中date_diff('month', b2.month, b1.month) <= 12的条件会包含超过12个月的数据（比如b2.month是2022-10-01，b1.month是2023-10-01，date_diff结果是12，会被包含），不过这不是本次结果错误的主因，主因还是id维度的错误引入。

要修正错误，只需去掉查询中所有关于id的分组、分区或关联条件，按product、cur、month维度计算TTM即可。

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