如何在CrateDB中按时间范围分组查询？求避免子查询报错方案

问题背景 #

你尝试用嵌套子查询分别统计State_01和State_02的每日数据量，但CrateDB不支持返回多列的子查询作为字段值，触发错误：

Error! UnsupportedFeatureException[Subqueries with more than 1 column are not supported.]

原查询代码：

SELECT * FROM (
SELECT 
    (
    SELECT
      date_bin('1 day'::INTERVAL, time_index, 0) AS time_frame,
      count(*) FROM schema.status
    WHERE processstatus IN ('State_01')
    GROUP BY time_frame
    ORDER BY time_frame DESC
  ) AS parts_good,
    (
    SELECT
      date_bin('1 day'::INTERVAL, time_index, 0) AS time_frame,
      count(*) FROM schema.status
    WHERE processstatus IN ('State_02')
    GROUP BY time_frame
    ORDER BY time_frame DESC
  ) AS parts_bad
)

最优解决方案：条件聚合 #

用CASE WHEN实现条件计数，只需一次date_bin计算和分组，完全避免重复逻辑，性能更优且符合CrateDB语法：

SELECT
  date_bin('1 day'::INTERVAL, time_index, 0) AS time_frame,
  COUNT(CASE WHEN processstatus = 'State_01' THEN 1 END) AS parts_good,
  COUNT(CASE WHEN processstatus = 'State_02' THEN 1 END) AS parts_bad
FROM schema.status
WHERE processstatus IN ('State_01', 'State_02')
GROUP BY time_frame
ORDER BY time_frame DESC;

核心逻辑 #

• 仅执行一次时间分箱和分组操作，消除重复计算
• CASE WHEN会为符合状态的行返回1，不符合的返回NULL，而COUNT函数自动忽略NULL，刚好实现分组统计
• 外层WHERE过滤目标状态，减少无效数据扫描

备选：JOIN实现（基于CTE） #

如果偏好JOIN写法，可通过CTE先统一生成所有状态的每日统计，再自关联合并结果，同样避免重复逻辑：

WITH daily_stats AS (
  SELECT
    date_bin('1 day'::INTERVAL, time_index, 0) AS time_frame,
    processstatus,
    COUNT(*) AS cnt
  FROM schema.status
  WHERE processstatus IN ('State_01', 'State_02')
  GROUP BY time_frame, processstatus
)
SELECT
  base.time_frame,
  COALESCE(good.cnt, 0) AS parts_good,
  COALESCE(bad.cnt, 0) AS parts_bad
FROM (SELECT DISTINCT time_frame FROM daily_stats) base
LEFT JOIN daily_stats good
  ON base.time_frame = good.time_frame
  AND good.processstatus = 'State_01'
LEFT JOIN daily_stats bad
  ON base.time_frame = bad.time_frame
  AND bad.processstatus = 'State_02'
ORDER BY base.time_frame DESC;

JOIN方案说明 #

• CTEdaily_stats一次性计算所有目标状态的每日数据，避免重复调用date_bin和分组
• 基于去重的时间框架表base做左连接，确保所有日期都能显示，哪怕某状态当日无数据
• COALESCE将NULL（无数据）转换为0，保证结果格式统一

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