如何在PrestoSQL中统计日期区间内各小时事件数的最值？

要实现需求，需要分两步聚合：先按日期+小时统计单日每小时的事件数，再基于该结果按小时计算最值。以下是适配PrestoSQL的完整查询语句：

SELECT
  -- 将小时数字格式化为HH:00的形式
  CONCAT(LPAD(CAST(hour_of_day AS VARCHAR), 2, '0'), ':00') AS hour,
  MIN(count_per_hour) AS min,
  MAX(count_per_hour) AS max
FROM (
  -- 内层子查询：统计每天每个小时的事件数
  SELECT
    DATE(transaction_timestamp) AS event_date,
    HOUR(transaction_timestamp) AS hour_of_day,
    COUNT(*) AS count_per_hour
  FROM my_table
  -- 过滤指定日期区间，根据实际需求修改起止日期
  WHERE transaction_timestamp BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59'
  GROUP BY event_date, hour_of_day
) daily_hourly_counts
GROUP BY hour_of_day
ORDER BY hour_of_day;

语句解释： #

1. 内层子查询：

   • 用DATE(transaction_timestamp)提取事务的日期部分，HOUR(transaction_timestamp)提取小时部分
   • 按日期+小时分组，统计该组的事务数量count_per_hour，得到单日每小时的事件数

2. 外层查询：

   • 仅按hour_of_day分组，对每个小时对应的所有日期的count_per_hour计算最小值MIN和最大值MAX
   • 用CONCAT+LPAD将小时数字（如14）格式化为14:00的字符串格式，匹配你需要的输出样式

示例输出： #

hour  | min | max 
----------------
14:00 | 200 | 550
15:00 | 300 | 700
16:00 | 150 | 300

内容的提问来源于stack exchange，提问作者David López