如何查询SQL按分钟分区后各分组的最大值、最小值等统计指标

基于现有CTE的查询实现 #

你已经完成了按小时、分钟的分区标记，直接对标记后的结果按日期、小时、分钟分组，调用聚合函数即可得到所需的统计结果，完整代码如下：

with t as (
    select * , extract(hour from t) as h,  extract(minute from t) as m
    from  some_date -- 修正你原代码的表名错误，你创建的表为some_date，而非price
), tt as (
select * , row_number() over(partition by h,m order by t) as t_sequence
from t)
select 
    dt as 统计日期,
    h as 统计小时,
    m as 统计分钟,
    max(price) as 价格最大值,
    min(price) as 价格最小值,
    round(avg(price),2) as 价格平均值,
    count(1) as 分钟内记录总数
from tt
group by dt, h, m
order by dt, h, m;

精简版实现（无需冗余row_number计算） #

如果你不需要t_sequence做其他逻辑处理，其实可以跳过row_number的计算，直接分组统计即可，代码更简洁高效：

select 
    dt as 统计日期,
    extract(hour from t) as 统计小时,
    extract(minute from t) as 统计分钟,
    max(price) as 价格最大值,
    min(price) as 价格最小值,
    round(avg(price),2) as 价格平均值,
    count(1) as 分钟内记录总数
from some_date
group by dt, extract(hour from t), extract(minute from t)
order by 统计日期, 统计小时, 统计分钟;

样本数据返回结果说明 #

执行上述代码后，针对你提供的样本数据会返回2条统计记录：

• 2021-11-17 06:39分的价格统计值
• 2021-11-17 06:40分的价格统计值

内容的提问来源于stack exchange，提问作者yaodav