如何基于另一列值实现设备数据的条件聚合查询

问题背景 #

我有一张名为device_data的表，结构如下：

Column     |            Type             | Collation | Nullable |                     Default                     
----------------+-----------------------------+-----------+----------+-------------------------------------------------
 id             | integer                     |           |          | nextval('device_data_id_seq'::regclass)
 date           | timestamp without time zone |           |          | 
 packet_drop    | real                        |           |          | 
 jitter         | real                        |           |          | 
 latency        | real                        |           |          | 
 alert          | character varying(50)       |           |          | 

该表按分钟存储packet_drop、jitter、latency数据。alert列根据阈值存储HIGH、MEDIUM、LOW，未达阈值则为空字符串 。

原本的每小时平均值查询语句如下：

SELECT date_trunc('hour', date) AS hourly,
    avg(jitter),
    avg(latency),
    avg(packet_drop)
FROM device_data
WHERE date BETWEEN '2022-11-26 17:41:11' AND '2022-11-27 17:41:11'
GROUP BY hourly 
ORDER BY hourly;

当加入alert列分组查询时，语句为：

SELECT date_trunc('hour', date) as hourly,
    avg(jitter),
    avg(latency),
    avg(packet_drop),
    alert from device_data
WHERE date BETWEEN '2022-11-26 17:41:11' AND '2022-11-27 17:41:11'
GROUP BY hourly, alert
ORDER BY hourly;

此时会出现同一小时的重复行，因为alert列存在多值。

需求 #

计算所有数据的每小时平均值（不能过滤行影响平均值），同时检查该小时内alert列是否存在HIGH值，若存在则该行alert列赋值为HIGH，否则赋值为空字符串 ，期望输出示例：

hourly        |        avg         |        avg         |         avg         | alert 
---------------------+--------------------+--------------------+---------------------+----------------
 2022-11-26 17:00:00 |  3.52857138642243   |  2.771428568022592 |                   0 | 
 2022-11-26 18:00:00 |  2.484615419346553  |  2.815384602546692 |                   0 | 
 2022-11-26 19:00:00 |  2.218461540570626  |  2.723076921242934 |                   0 | 
 2022-11-26 20:00:00 |  5.098461512992015  | 2.7076923021903405 |                   0 |  HIGH
 2022-11-26 21:00:00 |  2.0060606116824076 | 2.6469696814363655 |                   0 | 
 2022-11-26 22:00:00 |  5.672307815345434  |  2.810769222332881 |                   0 |  HIGH
 2022-11-26 23:00:00 |  2.7828124976949766 |  2.893749985843897 |                   0 | 
 2022-11-27 00:00:00 |  2.6046153992414474 | 2.8030769238105187 |                   0 | 
 2022-11-27 01:00:00 |  3.846031717837803  | 2.8333333200878568 |                   0 |  HIGH
 ...
(25 rows) 

直接过滤alert = 'HIGH'会影响平均值计算，如何实现该需求？

解决方案 #

可以使用聚合函数MAX()结合CASE语句来实现，既保留全量数据计算平均值，又能判断小时内是否存在HIGH告警。

最终SQL语句如下：

SELECT 
    date_trunc('hour', date) AS hourly,
    avg(jitter) AS avg_jitter,
    avg(latency) AS avg_latency,
    avg(packet_drop) AS avg_packet_drop,
    CASE 
        WHEN MAX(alert) = 'HIGH' THEN 'HIGH'
        ELSE ''
    END AS alert
FROM device_data
WHERE date BETWEEN '2022-11-26 17:41:11' AND '2022-11-27 17:41:11'
GROUP BY hourly 
ORDER BY hourly;

语句解释 #

1. 按小时分组：date_trunc('hour', date)将时间戳截断到小时级别，作为分组依据。
2. 全量平均值计算：avg(jitter)、avg(latency)、avg(packet_drop)基于分组内的所有行计算，不会过滤任何数据，保证平均值的准确性。
3. 告警状态判断：
   • MAX(alert)会提取分组内alert列的最大值，由于字符串排序中HIGH优先级高于MEDIUM、LOW和空字符串，只要该小时存在HIGH值，就会返回HIGH。
   • 通过CASE语句统一输出格式：当最大值为HIGH时返回HIGH，否则返回空字符串，完全匹配需求。

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