如何编写AWS Redshift SQL查询实现车辆数据分段统计？

样本数据 #

timetransmitted_tz为时间戳，原始数据如下：

目标输出 #

需要转换为如下格式（设备单日可能存在多次状态变更）：

当前尝试的SQL #

以下是最接近结果的查询版本，但存在end_date为空的问题：

WITH start_end_day AS (
  SELECT
    device_uuid,
    MIN(CASE WHEN readingtype = 'Start of Day' THEN timetransmitted_tz END) AS start_date,
    MIN(CASE WHEN readingtype = 'Start of Day' THEN odometer END) AS start_odometer,
    MAX(CASE WHEN readingtype = 'End of Day' THEN timetransmitted_tz END) AS end_date,
    MAX(CASE WHEN readingtype = 'End of Day' THEN odometer END) AS end_odometer,
    TRUNC(timetransmitted_tz) AS day
  FROM "prodenv"."public"."telemetry_jurisdictionchange"
  WHERE readingtype IN ('Start of Day','End of Day')
  GROUP BY device_uuid, TRUNC(timetransmitted_tz)
)
SELECT
  A.vin,
  A.jurisdiction,
  B.start_date,
  COALESCE(MAX(CASE WHEN A.readingtype = 'Change of State' THEN A.timetransmitted_tz END), B.end_date) AS end_date,
  B.start_odometer,
  MAX(CASE WHEN A.readingtype = 'Change of State' THEN odometer END) AS end_odometer,
  A.device_uuid,
  A.client_uuid
FROM "prodenv"."public"."telemetry_jurisdictionchange" A
INNER JOIN start_end_day B
  ON A.device_uuid = B.device_uuid AND TRUNC(A.timetransmitted_tz) = B.day
WHERE A.readingtype IN ('Start of Day', 'Change of State')
GROUP BY A.vin, A.jurisdiction, B.start_date, B.start_odometer, B.end_odometer, A.device_uuid, A.client_uuid, B.end_date
ORDER BY A.device_uuid, A.vin, B.start_date;

问题分析 #

原查询的核心问题是错误地用按天聚合的方式处理事件序列，无法匹配“每个事件对应下一个事件”的关联逻辑：

1. 当单日没有Change of State事件时，MAX(CASE WHEN readingtype = 'Change of State' ...)返回null，即便用COALESCE关联start_end_day的end_date，也会因为分组字段（如A.jurisdiction）的匹配问题导致end_date为空。
2. 分组逻辑将Start of Day和当天所有Change of State归为一组，取最大时间作为结束，不符合目标输出中“每个事件单独对应下一个事件”的要求。

正确解决方案 #

无需按readingtype做提前过滤，而是将所有事件按时间排序，用窗口函数LEAD获取每条记录的下一个事件的时间和里程，即可实现目标输出：

WITH ordered_events AS (
  SELECT
    device_uuid,
    vin,
    jurisdiction,
    timetransmitted_tz AS start_date,
    odometer AS start_odometer,
    -- 按设备、VIN、日期分区，获取下一条事件的时间
    LEAD(timetransmitted_tz) OVER (
      PARTITION BY device_uuid, vin, TRUNC(timetransmitted_tz)
      ORDER BY timetransmitted_tz
    ) AS end_date,
    -- 获取下一条事件的里程
    LEAD(odometer) OVER (
      PARTITION BY device_uuid, vin, TRUNC(timetransmitted_tz)
      ORDER BY timetransmitted_tz
    ) AS end_odometer,
    readingtype
  FROM "prodenv"."public"."telemetry_jurisdictionchange"
)
SELECT
  device_uuid,
  vin,
  jurisdiction,
  start_date,
  end_date,
  start_odometer,
  end_odometer
FROM ordered_events
-- 仅保留起始事件（Start of Day 和 Change of State），End of Day作为最后一段的结束不需要单独成行
WHERE readingtype IN ('Start of Day', 'Change of State')
-- 确保结束时间不为空（单日最后一个起始事件的下一条必然是End of Day）
AND end_date IS NOT NULL
ORDER BY device_uuid, vin, start_date;

逻辑说明 #

1. ordered_events CTE中，按device_uuid、vin和日期（TRUNC(timetransmitted_tz)）分区，按时间排序后，用LEAD窗口函数获取每条记录的下一个事件的时间和里程，自动关联起每一段的起止。
2. 最后过滤出Start of Day和Change of State类型的记录，直接得到目标输出的分段结果，完全符合需求。

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