如何编写SQL查询统计最近一天管道的成功、错误、警告数量用于看板可视化

统计表中最近一天的成功、错误、警告三类运行状态的数量，用于看板生成可视化图表，最终实现用户查看当日健康管道和不健康管道数量的目标。

原有SQL存在逻辑冗余问题：同时按status和日期分组后又单独聚合三类状态的求和值，会导致同一个日期返回3条重复数据，且重复嵌套的cast转换逻辑可读性差。

SELECT status, COUNT(*) as Total, cast(cast(time as Timestamp) as date) as time,
SUM(CASE WHEN Status = 'Success' THEN 1 ELSE 0 END) as Success,
SUM(CASE WHEN Status = 'Warning' THEN 1 ELSE 0 END) as Warning,
SUM(CASE WHEN Status = 'Error' THEN 1 ELSE 0 END) as Error
FROM table-name
where (cast(cast(time as Timestamp) as date) in ({{ Time }}) or {{ Time }} = 'All')
GROUP BY 1, cast(cast(time as Timestamp) as date)
order by cast(cast(time as Timestamp) as date) desc

场景1：仅统计最近一天数据（无需时间筛选） #

SELECT
  SUM(CASE WHEN status = 'Success' THEN 1 ELSE 0 END) AS success_count,
  SUM(CASE WHEN status = 'Warning' THEN 1 ELSE 0 END) AS warning_count,
  SUM(CASE WHEN status = 'Error' THEN 1 ELSE 0 END) AS error_count,
  -- 直接输出业务要求的健康/不健康管道数量
  SUM(CASE WHEN status = 'Success' THEN 1 ELSE 0 END) AS health_pipeline_count,
  SUM(CASE WHEN status IN ('Warning','Error') THEN 1 ELSE 0 END) AS unhealth_pipeline_count
FROM table-name
-- 可根据实际时区调整日期取值逻辑，此处取当前日期的前一天作为最近一天
WHERE CAST(CAST(time AS TIMESTAMP) AS DATE) = CURRENT_DATE - INTERVAL '1 day'

场景2：保留看板时间筛选能力（支持选任意日期/全部日期） #

WITH pipeline_base AS (
  -- 抽离公共转换逻辑，减少重复代码
  SELECT 
    status,
    CAST(CAST(time AS TIMESTAMP) AS DATE) AS run_date
  FROM table-name
  WHERE (CAST(CAST(time AS TIMESTAMP) AS DATE) IN ({{ Time }}) OR {{ Time }} = 'All')
)
SELECT
  run_date,
  SUM(CASE WHEN status = 'Success' THEN 1 ELSE 0 END) AS success_count,
  SUM(CASE WHEN status = 'Warning' THEN 1 ELSE 0 END) AS warning_count,
  SUM(CASE WHEN status = 'Error' THEN 1 ELSE 0 END) AS error_count,
  SUM(CASE WHEN status = 'Success' THEN 1 ELSE 0 END) AS health_pipeline_count,
  SUM(CASE WHEN status IN ('Warning','Error') THEN 1 ELSE 0 END) AS unhealth_pipeline_count,
  COUNT(*) AS total_pipeline_count
FROM pipeline_base
GROUP BY run_date
ORDER BY run_date DESC

• 删除了冗余的status分组逻辑，单日期仅返回1行统计结果，直接适配看板图表渲染要求
• 用CTE抽离重复的日期转换逻辑，代码可读性更高
• 新增健康/不健康管道数量的预计算字段，直接匹配最终业务需求，无需二次处理数据
• 兼容原有看板的{{ Time }}参数逻辑，不影响原有筛选功能

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