Redshift中基于窗口函数实现移动平均的技术求助

首先修正原SQL中的笔误：where子句里的txn_source应为txn_src，避免过滤条件失效。

要实现每个txn_src按时间序列的移动平均（从该来源首次出现到当前日期的累计平均，匹配你给出的示例），可以在原统计逻辑的基础上，嵌套一层窗口函数计算移动平均。以下是完整SQL：

select
    "time",
    txn_src as src,
    count_payment as cnt,
    round(avg(count_payment) over (
        partition by txn_src 
        order by "time" 
        rows between unbounded preceding and current row
    ), 2) as mvng_avg
from
    (
    select
        txn_src,
        created_time_id::date as "time",
        count(1) as count_payment,
        row_number() over (
            partition by created_time_id
            order by count(1) desc
        ) as seqnum
    from
        my_table
    where
        created_time_id >= '1-1-2017' 
        and txn_src is not null 
    group by
        txn_src, created_time_id
     ) x
where
    seqnum <= 10
order by
    "time",
    cnt desc

关键逻辑说明： #

• 内层子查询x：完成原需求的按日期分组统计、取每个日期计数前10的txn_src，同时将created_time_id转换为日期类型（Redshift中直接用::date即可，无需先转char(8)）。
• 外层的avg(count_payment) over (...)：
  • partition by txn_src：确保仅对同一个来源的记录计算移动平均。
  • order by "time"：按时间顺序计算，保证平均是按时间序列累积的。
  • rows between unbounded preceding and current row：指定计算范围为当前来源的所有历史记录（从第一条到当前行），对应示例中的累计平均效果。如果需要固定窗口的移动平均（比如最近3天），可以将此部分改为rows between 2 preceding and current row（注意需保证日期连续，若有缺失日期需先补全）。
• round(..., 2)：对移动平均结果保留两位小数，符合示例格式。

匹配示例的输出结果： #

执行上述SQL后，会得到如下格式的结果（与你的示例一致）：

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