postgresql 获取Postgres SQL中给定输入时间戳的前几天值的平均值

bq9c1y66 于 2022-11-23 发布在 PostgreSQL

关注(0)|答案(1)|浏览(131)

我有一个函数的输入值作为时间戳。我想找到前一天的确切时间戳的特定列（这里是impacted_users）的值（我还需要该时间戳值之前和之后一个小时的值）。基于此，我想对前一天的这些值取平均值。
avg_val_column = avg(val_at_one_hr_before,val_at_one_hr_before,value_at_given_timestamp_yesteday)
我对当前时间戳的查询如下所示，但我不确定我需要做什么来获取前几天的值（以及获取这些值的平均值）时间戳格式：2022-10-29 11:00:00
要为impacted_users-〉2022-10-28 10:00:00 + 2022-10-28 11:00:00 + + 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)

postgresql

来源：https://stackoverflow.com/questions/74442853/getting-the-average-values-of-the-previous-days-value-for-a-given-input-timestam

1条答案

按热度按时间

zf9nrax11#

我猜你的结构是什么，并使用my own test data，但这是为了说明的想法：

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,
    (   select avg(i.impacted_users) 
        from pt_rc i
            INNER JOIN tmop_comb t ON i.rc_id = t."RC"
        where i.timestamp 
            between '2022-10-28 11:00:00'::timestamp-'1 day 1 hour'::interval
            and '2022-10-28 11:00:00'::timestamp-'23 hours'::interval
        and i.rc_id=procedure_type_rc.rc_id
        and t.stage = 1) as avg_impacted_users_day_before,
    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 = '2022-10-28 11:00:00'
    AND tmop_comb.stage = 1
    AND procedure_type_rc.rc_id IN (1,2,3);

在中格式化为参数的'2022-10-28 11:00:00'的所有执行严修。

赞(0）回复(0）举报 2022-11-23

我来回答

postgresql 获取Postgres SQL中给定输入时间戳的前几天值的平均值

1条答案

相关问题

热门标签

最新问答