我有一個函式的輸入值作為時間戳。我想找到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)
uj5u.com熱心網友回復：
我猜你的結構是什么并使用我自己的測驗資料，但這是為了展示這個想法：
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'格式化為引數的所有實體。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/535308.html
標籤：postgresql