Postgres:获取组中其他列的最大值对应的列的值

Question

我正在尝试编写一个postgres查询，它返回组中的最大值、最小值、中值、第一个值和最后一个值，以及每个聚合值的时间戳列

表

Id Timestamp_utc                  Value
1  2020-11-05 15:36:15.768388     10
1  2020-11-05 15:40:15.768388     20
1  2020-11-05 15:44:15.768388     30
1  2020-11-05 15:45:15.768388.    5
1  2020-11-05 15:59:15.768388     25
1  2020-11-05 16:59:15.768388     25

预期结果

Id Median Median_Timestamp Min Min_Timestamp               Max Max_TimeStamp
1  17.5.  15:44:15.768388  5   2020-11-05 15:45:15.768388  30   2020-11-05 15:44:15.768388

我有一个查询，它将不包括时间戳的数据分组

SELECT Id, time_bucket('60', timestamp_utc) AS bucket,
percentile_cont(0.5) within group (order by value) median_value,
min(value) min_value, 
max(value) max_value 
FROM rs.MyTable 
WHERE id IN ( 1111,123)
AND timestamp_utc Between '2020-11-05 10:00:15.748643' and '2020-11-05 16:35:48.750313'
GROUP BY id, bucket 
ORDER BY id, bucket

有没有一种方法可以在值最大时为聚合值(如时间戳_ utc col数据(获取时间戳列

Answer 1

一个选项使用子查询中的窗口函数，通过增加和减少value对时间戳进行排序，然后在外部查询中进行条件聚合，使相关值

select id, bucket,
percentile_cont(0.5) within group (order by value) median_value,
min(value) min_value, 
max(timestamp_utc) filter(where rn_asc = 1) min_timestamp,
max(value) max_value,
max(timestamp_utc) filter(where rn_desc = 1) max_timestamp
from (
select t.*, 
row_number() over(partition by id, bucket order by value) rn_asc,
row_number() over(partition by id, bucket order by value desc) rn_desc
from (
select t.*, time_bucket('60', timestamp_utc) as bucket 
from rs.mytable t
where 
id in (1111,123)
and timestamp_utc between '2020-11-05 10:00:15.748643'::timestamp 
and '2020-11-05 16:35:48.750313'::timestamp
) t
) t
group by id, bucket 
order by id, bucket

注意，我们需要首先计算bucket，并将其放入窗口函数的分区中。