Rockset环境下稀疏时序数据稠密化SQL查询性能优化求助

性能问题根因 #

你当前写法的性能瓶颈来自slot_values里的两个相关子查询：每个时间槽都会独立遍历一遍全部readings数据集做条件匹配，时间复杂度为O(时间槽数量 * 读数数量)，数据量稍大就会出现明显的性能损耗。

优化方案 #

我们可以用「预聚合+窗口函数填充空值」的逻辑替换相关子查询，把复杂度降到线性水平，优化后完整SQL如下：

WITH initial_value AS (
  -- 单独提取窗口前的初始值
  SELECT
    value AS init_val
  FROM
    node_iot_attribute_values
  WHERE
    attributeId = 'cu937803-ne9de7df-nn7453b2-na2c7e14'
    AND DATE_TRUNC('second', TIMESTAMP_SECONDS(timestamp)) < TIMESTAMP '2021-10-26T08:42:06.000000Z'
  ORDER BY
    DATE_TRUNC('second', TIMESTAMP_SECONDS(timestamp)) DESC
  LIMIT 1
),
window_readings AS (
  -- 时间窗口内的读数
  SELECT
    timestamp AS timestamps,
    attributeId AS id,
    DATE_TRUNC('second', TIMESTAMP_SECONDS(timestamp)) AS ts,
    value AS value
  FROM
    node_iot_attribute_values
  WHERE
    attributeId = 'cu937803-ne9de7df-nn7453b2-na2c7e14'
    AND DATE_TRUNC('second', TIMESTAMP_SECONDS(timestamp)) > TIMESTAMP '2021-10-26T08:42:06.000000Z'
    AND DATE_TRUNC('second', TIMESTAMP_SECONDS(timestamp)) < TIMESTAMP '2021-10-26T09:42:06.000000Z'
),
slots AS (
  -- 生成指定分辨率的时间槽
  SELECT
    TIMESTAMP '2021-10-26T08:42:06.000000Z' + MINUTES(u.i - 1) AS last_ts,
    TIMESTAMP '2021-10-26T08:42:06.000000Z' + MINUTES(u.i) AS ts
  FROM
    UNNEST(SEQUENCE(0, 60, 1) AS i) AS u
),
slot_agg AS (
  -- 单次遍历读数，预聚合每个时间槽的最大值
  SELECT
    s.ts AS slot_ts,
    MAX(r.value) AS slot_agg_val
  FROM slots s
  LEFT JOIN window_readings r
    ON r.ts >= s.last_ts AND r.ts < s.ts
  GROUP BY s.ts
)
SELECT
  CAST(slot_ts AT TIME ZONE 'Europe/Paris' AS string) AS ts,
  COALESCE(
    slot_agg_val,
    -- 用带IGNORE NULLS的LAST_VALUE自动填充空槽，取最近的非空值
    LAST_VALUE(slot_agg_val IGNORE NULLS) OVER(ORDER BY slot_ts ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
    -- 全窗口无数据时用初始值兜底
    (SELECT init_val FROM initial_value)
  ) AS value
FROM slot_agg
ORDER BY slot_ts;

核心优化点 #

• 拆分初始值和窗口内读数，去掉不必要的UNION操作：两类数据没有重叠，不需要合并去重，减少计算开销
• 替换相关子查询为单次左连接聚合：仅需遍历一次窗口内读数就能计算出所有槽的最大值，避免多次重复扫描数据集
• 用LAST_VALUE窗口函数自动填充空值：直接利用窗口特性取最近的非空值，完全省略了逐行查找历史最新值的逻辑

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