sql - Postgres:获取与组中其他列的最大值对应的列的值
问题描述
我正在尝试编写一个 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
当值为最大值时,有没有一种方法可以获取时间戳列以及诸如 timestamp_utc col 数据之类的聚合值?
解决方案
一种选择是在子查询中使用窗口函数通过递增和递减对时间戳进行排序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,然后放到window function的partition中。
推荐阅读
- lua - 如何使用 Lua 将 png 转换为灰度?
- python-3.x - NameError: name 'ntxtbox' 未定义,即使 ntxtbox 是一个全局变量
- python - 用 pandas 替换/删除部分字符串
- node.js - 使用 Express JS 后端反应原生应用程序定制?
- javascript - 谷歌标签管理器如何设置环境名称
- c++ - 什么是引用复制构造函数?
- linux - 这个高山版本遵循什么版本方案?
- c++ - 如何使我的“下标值”在 C++ 中成为一个数组
- swift - 在 onAppear() 中运行函数后,我的 @State 变量之一不会更新
- swift - 放大 GraphQL 错误未授权访问创建