Getting the average values of the previous days value for a given input timestamp in the Postgres sq-CodePudding

I have an input value to a function as timestamp. I would like to find the values of particular column(here impacted_users) for the previous day's exact timestamp( I need one hour before and after that timestamp values as well). Based on this I would like to average these values of previous day.

avg_val_column = avg(val_at_one_hr_before,val_at_one_hr_before,value_at_given_timestamp_yesteday)

My query is as follows for the current timestamp but I am not sure what would I need to do get the previous days values(&get the avg of those) Timestamp format: 2022-10-29 11:00:00

Values to get for 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)

CodePudding user response：

I'm guessing what your structure is and using my own test data, but this is to show the idea:

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);

All instances of '2022-10-28 11:00:00' formatted in as a parameter.