如何在SQL中计算前4行processed_value的平均值作为previous_value

需求说明 #

原SQL通过lag()函数仅获取上一行的processed_value作为previous_value，现需调整逻辑：

• 对于parent_rank=1的行，以同分区（rloi_id+qualifier）内parent_rank为2-5的4行processed_value的平均值作为previous_value
• 其他行（如parent_rank=2）的previous_value保持为NULL

原查询片段（单行前置值逻辑） #

...
lag(rs.processed_value, 1) OVER (
  PARTITION BY rs.rloi_id, rs.qualifier
  ORDER BY rs.rloi_id, rs.qualifier, rs.timestamp
) AS previous_value,
rs.parent_rank FROM rs WHERE rs.parent_rank = ANY (
  ARRAY[1::bigint, 2::bigint]
)
...

原查询结果表 #

中间查询（已获取前4行数据） #

调整parent_rank过滤条件后，得到包含parent_rank1-5的中间结果：

...
rs.parent_rank FROM rs WHERE rs.parent_rank = ANY (
  ARRAY[1::bigint, 2::bigint, 3::bigint, 4::bigint, 5::bigint]
)
...

中间结果表 #

期望结果表 #

注：带的值为平均值，0.73=(0.70+0.72+0.74+0.76)/4，0.63=(0.60+0.62+0.64+0.66)/4*

修改后的完整SQL查询 #

WITH ranked_all_value_summaries AS (
    SELECT tvp.rloi_id,
        tvp.qualifier,
        tv.processed_value,
        tv.timestamp,
        tv.error,
        rank() OVER (PARTITION BY tvp.rloi_id, tvp.qualifier ORDER BY tv.timestamp DESC, tv.telemetry_value_id DESC) AS parent_rank
    FROM sls_telemetry_value tv
    JOIN sls_telemetry_value_parent tvp ON tv.telemetry_value_parent_id = tvp.telemetry_value_parent_id
    WHERE lower(tvp.parameter) = 'water level'::text
      AND lower(tvp.units) !~~ '%deg%'::text
      AND lower(tvp.qualifier) !~~ '%height%'::text
      AND lower(tvp.qualifier) <> 'crest tapping'::text
),
latest_value_summaries_with_previous_value AS (
    SELECT r.rloi_id,
        r.qualifier,
        r.processed_value,
        r.timestamp,
        -- 针对parent_rank=1的行，计算同分区内parent_rank 2-5的平均值
        CASE
            WHEN r.parent_rank = 1 THEN
                AVG(CASE WHEN r_inner.parent_rank BETWEEN 2 AND 5 THEN r_inner.processed_value END) OVER (
                    PARTITION BY r.rloi_id, r.qualifier
                )
            ELSE NULL
        END AS previous_value,
        r.parent_rank
    FROM ranked_all_value_summaries r
    -- 关联同分区的所有行，用于计算平均值
    JOIN ranked_all_value_summaries r_inner 
        ON r.rloi_id = r_inner.rloi_id 
        AND r.qualifier = r_inner.qualifier
    WHERE r.parent_rank IN (1, 2) -- 仅保留需要的行：parent_rank=1和2
    GROUP BY r.rloi_id, r.qualifier, r.processed_value, r.timestamp, r.parent_rank
)
SELECT * FROM latest_value_summaries_with_previous_value
ORDER BY rloi_id, qualifier, timestamp DESC;

修改说明 #

1. 关联同分区数据：通过自连接ranked_all_value_summaries，获取同rloi_id和qualifier下的所有行，方便计算平均值
2. 条件聚合计算平均值：在CASE语句中，仅当当前行parent_rank=1时，计算parent_rank为2-5的processed_value的平均值
3. 过滤结果行：最终仅保留parent_rank=1和2的行，符合期望结果的输出要求
4. 排序优化：最后添加ORDER BY保证结果顺序与期望一致

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