在QuestDB中补全时间戳以覆盖整个查询范围

当查询1月1日至2月1日的日志时，数据库仅存在1月10日和20日的数据，但希望获得覆盖整个时间范围的空数据点。使用FILL (0)已能在现有时间戳间生成空白数据点，但仍需补全整个查询范围首尾的时间戳（即1月1日至1月10日、1月20日至2月1日的区间）。是否可仅通过QuestDB实现该需求，还是需后续手动添加时间戳？

原查询SQL：

SELECT event_type,
        COUNT(*) AS event_count,
        timestamp AS interval_start
FROM logs
WHERE project_id = '${projectId}'
AND timestamp >= '${startDate}'
AND timestamp <= '${endDate}'
AND event_type IN (${events.map((event: string) => `'${event}'`).join(', ')})
SAMPLE BY ${sampleBy} FILL (0)
ORDER BY interval_start, event_type;

可以完全通过QuestDB实现，无需手动补全时间戳。核心思路是生成覆盖全查询范围的时间序列虚拟表，再与聚合结果做左连接，同时确保每个时间点对应所有指定的event_type。

修改后的SQL #

WITH time_range AS (
  -- 生成覆盖整个查询区间、符合采样间隔的完整时间戳序列
  SELECT timestamp AS interval_start
  FROM generate_series(
    '${startDate}'::timestamp,
    '${endDate}'::timestamp,
    '${sampleBy}'::interval
  ) timestamp
),
all_event_types AS (
  -- 将指定的事件类型转为临时表，用于生成全量组合
  SELECT unnest(array[${events.map((event: string) => `'${event}'`).join(', ')}) AS event_type
),
full_grid AS (
  -- 交叉连接生成「每个时间点+每个事件类型」的全量网格
  SELECT tr.interval_start, aet.event_type
  FROM time_range tr
  CROSS JOIN all_event_types aet
),
aggregated_data AS (
  -- 原有的聚合查询，移除FILL(0)交由后续处理
  SELECT 
    event_type,
    COUNT(*) AS event_count,
    timestamp AS interval_start
  FROM logs
  WHERE 
    project_id = '${projectId}'
    AND timestamp >= '${startDate}'
    AND timestamp <= '${endDate}'
    AND event_type IN (${events.map((event: string) => `'${event}'`).join(', ')})
  SAMPLE BY ${sampleBy}
)
-- 左连接全量网格与聚合数据，空值补0
SELECT 
  fg.event_type,
  COALESCE(ad.event_count, 0) AS event_count,
  fg.interval_start
FROM full_grid fg
LEFT JOIN aggregated_data ad 
  ON fg.interval_start = ad.interval_start 
  AND fg.event_type = ad.event_type
ORDER BY fg.interval_start, fg.event_type;

关键逻辑说明 #

1. time_range CTE：通过generate_series生成从起始到结束时间、步长为${sampleBy}的完整时间戳序列，解决原查询无法覆盖首尾无数据区间的问题。
2. full_grid CTE：通过交叉连接时间序列与所有事件类型，生成每个时间点对应所有事件类型的全量组合，确保不会遗漏任何维度的空数据点。
3. 左连接+COALESCE：将聚合结果与全量网格左连接，用COALESCE把无数据的event_count替换为0，最终得到覆盖整个时间范围的完整结果。

注意事项 #

• 确保generate_series的时间参数与logs表中timestamp字段的精度一致（如纳秒级时间戳需使用对应格式）。
• 如果event_type的列表是动态生成的，需保证array[...]的语法正确，避免SQL注入风险（可通过参数绑定优化）。

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