Postgres如何筛选仅存在FAILURE状态的日期

实现逻辑 #

要筛选出单日仅存在FAILURE状态、无其他状态的日期，只需满足两个分组后校验条件即可：

• 当日所有记录的状态去重后数量为1（确认当天只有一种状态）
• 该唯一状态为FAILURE

完整SQL（直接基于原表查询） #

SELECT
    date_trunc('day', time) AS group_day,
    project_name
FROM project_status
WHERE project_name = 'project_x'
GROUP BY date_trunc('day', time), project_name
HAVING COUNT(DISTINCT build_result) = 1
   AND MAX(build_result) = 'FAILURE'

注：如果使用MySQL类不支持date_trunc的数据库，可将date_trunc('day', time)替换为DATE(time)，其余逻辑完全通用。

基于现有查询的二次加工方案 #

如果需要保留现有分组逻辑做拓展，可以基于已有结果二次筛选：

WITH daily_distinct_status AS (
    -- 现有分组查询逻辑
    SELECT
        date_trunc('day', time) AS group_day,
        build_result,
        project_name
    FROM project_status
    WHERE project_name = 'project_x'
    GROUP BY date_trunc('day', time), project_name, build_result
)
SELECT group_day, project_name
FROM daily_distinct_status
GROUP BY group_day, project_name
HAVING COUNT(1) = 1 
   AND MAX(build_result) = 'FAILURE'

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