PostgreSQL如何按device_id-date分组生成has_alarm状态列

针对需求，这里提供两种简洁的PostgreSQL查询实现方式，都能按device_id和date分组，生成符合规则的has_alarm1列：

方案一：使用BOOL_OR聚合函数（推荐） #

利用PostgreSQL内置的BOOL_OR函数，只要分组内存在任意一行满足指定条件就返回true，逻辑清晰且代码简洁：

SELECT
    device_id,
    date,
    CASE
        WHEN BOOL_OR(alarm1 > 0) THEN 1
        WHEN BOOL_OR(alarm1 = 0) THEN 0
        ELSE NULL
    END AS has_alarm1
FROM
    log_table  -- 替换为你的实际表名
GROUP BY
    device_id,
    date;

方案二：使用MAX+CASE的组合方式 #

如果习惯用通用SQL语法，也可以用这种兼容性更强的写法：

SELECT
    device_id,
    date,
    CASE
        WHEN MAX(CASE WHEN alarm1 > 0 THEN 1 ELSE 0 END) = 1 THEN 1
        WHEN MAX(CASE WHEN alarm1 = 0 THEN 1 ELSE 0 END) = 1 THEN 0
        ELSE NULL
    END AS has_alarm1
FROM
    log_table  -- 替换为你的实际表名
GROUP BY
    device_id,
    date;

逻辑说明 #

两种方案的核心逻辑一致：

1. 优先判断分组内是否存在alarm1 > 0的行，存在则has_alarm1 = 1
2. 若没有符合上一条的行，再判断是否存在alarm1 = 0的行，存在则has_alarm1 = 0
3. 若前两个条件都不满足（即所有行的alarm1都是Null），则返回Null

示例验证 #

• 当device_id=1在2022-01-15存在alarm1=55和0时，BOOL_OR(alarm1 > 0)返回true，所以has_alarm1=1
• 当device_id=2在2022-01-19所有行alarm1都是Null时，两个BOOL_OR都返回false，所以has_alarm1=Null
• 当device_id=1在2022-01-16所有行alarm1都是0时，第一个BOOL_OR返回false，第二个返回true，所以has_alarm1=0

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