PrestoDB中非滚动聚合周度指标生成10周绩效宽表的SQL方法

date_trunc('week', date)方案匹配失败的核心原因是：该函数默认按自然周（固定周一/周日为周起点）做日期截断，和需求里「以个体入组日期为周起点的相对周统计逻辑」完全不匹配，不需要依赖自然周规则计算。
完整实现分三步，全程不需要复杂窗口函数，逻辑可直接在PrestoDB运行：

1. 周期标记逻辑 #

先关联人员基础信息表，给每条每日绩效记录标记所属统计周期：

• 基线期：记录日期落在「入组日期前28天到入组日当天前」的区间
• 入组后周次：记录日期落在「入组日到入组日后70天（共10周）」的区间，通过floor(记录日期与入组日的天数差/7)+1直接计算得到w1~w10的周编号，完全对齐入组起始的自定义周区间，不会出现自然周偏移问题

2. 分周期聚合 #

按人员ID、指标类型、周期标记做聚合，根据业务指标的定义选择聚合函数（均值/求和/最大值等，示例以均值为例匹配样表数值逻辑）。

3. 条件聚合行转列 #

用条件聚合直接实现pivot效果，不需要额外调用pivot语法，固定10周的场景下写法最简洁易维护。 #

完整可运行SQL示例：

with person_base as (
    -- 读取人员基础信息，替换为实际人员表名
    select 
        person_id,
        group_id,
        enroll_date
    from person_info_table
),
daily_with_period as (
    -- 关联入组日期，给每条绩效记录打周期标签
    select 
        d.person_id,
        d.metric,
        d.score,
        date_diff('day', p.enroll_date, d.date) as day_diff,
        case 
            when d.date >= date_add('day', -28, p.enroll_date) and d.date < p.enroll_date then 'baseline'
            when d.date >= p.enroll_date and d.date < date_add('day', 70, p.enroll_date) then concat('w', cast(floor(day_diff/7) + 1 as varchar))
            else null
        end as period_tag
    from daily_metric_table d -- 替换为实际每日绩效表名
    join person_base p on d.person_id = p.person_id
    -- 过滤掉不在统计区间内的记录，减少计算量
    where d.date >= date_add('day', -28, p.enroll_date) 
      and d.date < date_add('day', 70, p.enroll_date)
),
period_agg as (
    -- 按人、指标、周期聚合绩效值
    select 
        person_id,
        metric,
        period_tag,
        avg(score) as period_score -- 求和类指标替换为sum(score)即可
    from daily_with_period
    where period_tag is not null
    group by 1,2,3
)
-- 条件聚合行转列生成最终宽表
select 
    person_id,
    metric,
    max(case when period_tag = 'baseline' then period_score end) as baseline,
    max(case when period_tag = 'w1' then period_score end) as w1,
    max(case when period_tag = 'w2' then period_score end) as w2,
    max(case when period_tag = 'w3' then period_score end) as w3,
    max(case when period_tag = 'w4' then period_score end) as w4,
    max(case when period_tag = 'w5' then period_score end) as w5,
    max(case when period_tag = 'w6' then period_score end) as w6,
    max(case when period_tag = 'w7' then period_score end) as w7,
    max(case when period_tag = 'w8' then period_score end) as w8,
    max(case when period_tag = 'w9' then period_score end) as w9,
    max(case when period_tag = 'w10' then period_score end) as w10
from period_agg
group by 1,2
;

可选调整项 #

• 若需要将无绩效记录的周/基线值补0，可在最外层的每个周期字段外包一层coalesce(xxx, 0)
• 由于同组成员入组日期一致，数据量较大时可以先按group_id维度做周期聚合，再关联人员表映射到个人，能大幅提升计算性能
• 若周度统计需要排除入组当日，只需要调整周期判断里的日期边界条件即可

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