PostgreSQL查询：带timestamp的最小/最大值及日均值（日报需求）

以下是满足需求的查询语句，假设你的数据表名为metrics，数值字段为value，时间戳字段为record_time，查询日期为2024-05-20：

WITH daily_data AS (
    -- 筛选指定日期当天的所有数据（从当天0点到次日0点）
    SELECT
        value,
        record_time
    FROM metrics
    WHERE record_time >= '2024-05-20'::TIMESTAMP
      AND record_time < '2024-05-21'::TIMESTAMP
),
min_entry AS (
    -- 获取最小值及对应的时间戳
    SELECT 
        record_time AS min_timestamp, 
        value AS min_value
    FROM daily_data
    WHERE value = (SELECT MIN(value) FROM daily_data)
    LIMIT 1 -- 若存在多个相同最小值，仅取其中一条的时间戳
),
max_entry AS (
    -- 获取最大值及对应的时间戳
    SELECT 
        record_time AS max_timestamp, 
        value AS max_value
    FROM daily_data
    WHERE value = (SELECT MAX(value) FROM daily_data)
    LIMIT 1 -- 若存在多个相同最大值，仅取其中一条的时间戳
),
avg_entry AS (
    -- 计算当天的平均值
    SELECT AVG(value) AS avg_value
    FROM daily_data
)
-- 合并结果输出
SELECT
    me.min_timestamp,
    me.min_value,
    mx.max_timestamp,
    mx.max_value,
    ae.avg_value
FROM min_entry me, max_entry mx, avg_entry ae;

关键说明： #

• 日期范围用>= 当天0点和< 次日0点，确保不会遗漏当天最后一秒的数据，也不会包含次日数据
• 若不需要限制极值的条数（比如要显示所有出现最小值的时间戳），可以去掉LIMIT 1
• 实际使用时，替换以下部分：
  • metrics：你的数据表名
  • value：存储目标数值的字段名
  • record_time：存储时间戳的字段名
  • '2024-05-20'：生成日报的目标日期，也可以用参数化方式动态传入（比如$1）

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