PostgreSQL中基于输入时间戳计算前一日对应时段impacted_users平均值

输入参数为timestamp类型（示例：2022-10-29 11:00:00），需要获取该时间戳前一日对应时刻及前后各一小时的impacted_users字段值，计算这三个值的平均值。现有SQL仅查询当前时间戳的数据，需修改实现需求。

需要获取的目标时间点：

• 前一日对应时刻减1小时：2022-10-28 10:00:00
• 前一日对应时刻：2022-10-28 11:00:00
• 前一日对应时刻加1小时：2022-10-28 12:00:00

现有查询代码：

WITH rc_pt AS (
    SELECT
        procedure_type_rc.timestamp AS timestamp,
        procedure_type_rc.rc_id AS rc_id,
        procedure_type_rc.pt_id AS pt_id,
        procedure_type_rc.impacted_users AS impacted_users,
        -- find avg. of impacted users for previous days values*********
        tmop_comb.stage AS stage
    FROM
        pt_rc AS procedure_type_rc
        INNER JOIN tmop_comb ON procedure_type_rc.rc_id = tmop_comb."RC"
    WHERE
        procedure_type_rc.timestamp = '{}'
        AND tmop_comb.stage = 1
        AND procedure_type_rc.rc_id IN '{}'
)
SELECT
    *
FROM
    rc_pt

注：原代码末尾的.format(self.timestamp, self.nw_rc_ad_tuple)是Python字符串格式化语句。

WITH rc_pt AS (
    SELECT
        procedure_type_rc.timestamp AS timestamp,
        procedure_type_rc.rc_id AS rc_id,
        procedure_type_rc.pt_id AS pt_id,
        procedure_type_rc.impacted_users AS impacted_users,
        tmop_comb.stage AS stage
    FROM
        pt_rc AS procedure_type_rc
        INNER JOIN tmop_comb ON procedure_type_rc.rc_id = tmop_comb."RC"
    WHERE
        -- 匹配前一日对应时刻及前后1小时的三个时间点
        procedure_type_rc.timestamp IN (
            DATE_TRUNC('hour', '{}'::TIMESTAMP) - INTERVAL '1 day 1 hour',
            DATE_TRUNC('hour', '{}'::TIMESTAMP) - INTERVAL '1 day',
            DATE_TRUNC('hour', '{}'::TIMESTAMP) - INTERVAL '1 day -1 hour'
        )
        AND tmop_comb.stage = 1
        AND procedure_type_rc.rc_id IN {}
)
SELECT
    rc_id,
    pt_id,
    stage,
    -- 计算三个时间点的impacted_users平均值
    AVG(impacted_users) AS avg_impacted_users
FROM
    rc_pt
GROUP BY
    rc_id,
    pt_id,
    stage

注：Python格式化时，self.nw_rc_ad_tuple不需要加单引号，因为IN后面的元组会自动生成正确的SQL语法，比如('RC1', 'RC2')。

• 时间条件调整：把原有的单一时间匹配改为IN子句，通过时间函数生成目标三个时间点：
  • DATE_TRUNC('hour', '{}'::TIMESTAMP)先将输入时间戳截断到小时（确保是整点）
  • 分别减去1天1小时、1天、1天-1小时（即加1小时）得到三个目标时间
• 聚合计算平均值：外层查询通过AVG()函数计算impacted_users的平均值，并按rc_id、pt_id、stage分组，确保每个维度的平均值正确
• IN子句语法修正：原代码中IN '{}'会导致语法错误，修改为IN {}，因为Python元组格式化后会直接生成(值1, 值2)的正确格式

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