如何调整SQL查询以严格筛选A→B→C的传感器行程数据？

数据结构示例 #

以下是数据库中传感器原始测量数据的结构示例：

unique_id;sensor;timestamp
111;A;2024-10-10 08:00:00.000000
222;C;2024-10-10 08:00:00.000000
333;A;2024-10-10 08:00:00.000000
444;A;2024-10-10 08:00:00.000000
555;A;2024-10-10 08:00:00.000000
666;C;2024-10-10 08:00:01.000000
777;C;2024-10-10 08:00:01.000000
888;C;2024-10-10 08:00:01.000000
555;B;2024-10-10 08:00:09.000000
111;A;2024-10-10 08:00:21.000000
444;A;2024-10-10 08:00:24.000000
444;A;2024-10-10 08:00:38.000000
444;B;2024-10-10 08:00:40.000000
666;C;2024-10-10 08:00:42.000000
111;C;2024-10-10 08:00:42.000000
444;B;2024-10-10 08:00:48.000000
111;C;2024-10-10 08:01:02.000000
666;C;2024-10-10 08:01:02.000000
555;C;2024-10-10 08:01:05.000000
444;C;2024-10-10 08:01:09.000000
111;B;2024-10-10 08:01:23.000000
666;C;2024-10-10 08:01:23.000000
222;C;2024-10-10 08:01:23.000000
555;C;2024-10-10 08:01:27.000000
444;C;2024-10-10 08:01:35.000000
555;C;2024-10-10 08:01:38.000000
111;C;2024-10-10 08:01:43.000000
222;C;2024-10-10 08:01:43.000000
555;C;2024-10-10 08:02:00.000000
111;C;2024-10-10 08:02:04.000000
222;C;2024-10-10 08:02:04.000000
666;C;2024-10-10 08:02:04.000000
555;C;2024-10-10 08:02:20.000000
222;C;2024-10-10 08:02:24.000000
111;C;2024-10-10 08:02:24.000000
111;C;2024-10-10 08:02:45.000000
666;C;2024-10-10 08:02:45.000000
666;C;2024-10-10 08:03:05.000000
222;C;2024-10-10 08:03:05.000000
111;C;2024-10-10 08:03:05.000000
111;C;2024-10-10 08:03:26.000000
666;C;2024-10-10 08:03:26.000000
222;C;2024-10-10 08:03:26.000000

需求说明 #

• 查询每个unique_id从传感器A经B到C的行程时间
• 严格遵循A→B→C的顺序规则：
  1. 该ID的首次出现记录属于传感器A
  2. 该ID的末次出现记录属于传感器C
  3. 排除以下违规ID：经过B后重回A，或经过C后重回B

现有查询的问题 #

当前使用的SQL查询仅验证了A、B、C的首次/末次时间先后，无法过滤像unique_id=111这类存在B之后出现A的违规记录，导致结果不符合严格顺序要求。

原查询代码：

WITH sensor_sequences AS (
    SELECT 
        sm.unique_id,
        MIN(CASE WHEN sm.sensor = 'A' THEN sm.timestamp END) AS first_capture_A,
        MIN(CASE WHEN sm.sensor = 'B' THEN sm.timestamp END) AS first_capture_B,
        MAX(CASE WHEN sm.sensor = 'C' THEN sm.timestamp END) AS last_capture_C
    FROM sensor_measurement sm 
    WHERE 
        sm.sensor IN ('A', 'B', 'C')
        AND sm.timestamp BETWEEN '2024-10-10 07:00:00' AND '2024-10-10 12:00:00'
    GROUP BY sm.unique_id
    HAVING 
        MIN(CASE WHEN sm.sensor = 'A' THEN sm.timestamp END) IS NOT NULL 
        AND MIN(CASE WHEN sm.sensor = 'B' THEN sm.timestamp END) IS NOT NULL 
        AND MAX(CASE WHEN sm.sensor = 'C' THEN sm.timestamp END) IS NOT NULL 
        AND MIN(CASE WHEN sm.sensor = 'B' THEN sm.timestamp END) > MIN(CASE WHEN sm.sensor = 'A' THEN sm.timestamp END)  
        AND MAX(CASE WHEN sm.sensor = 'C' THEN sm.timestamp END) > MIN(CASE WHEN sm.sensor = 'B' THEN sm.timestamp END)  
        AND SECONDS_BETWEEN(MAX(CASE WHEN sm.sensor = 'C' THEN sm.timestamp END),MIN(CASE WHEN sm.sensor = 'A' THEN sm.timestamp END)) <= 300  --max time between two timestamps
)
SELECT 
    ss.unique_id,
    ss.first_capture_A AS sensor_A_time,
    ss.first_capture_B AS sensor_B_time,
    ss.last_capture_C AS sensor_C_time,
    SECONDS_BETWEEN(ss.last_capture_C, ss.first_capture_A) AS travel_time_seconds
FROM sensor_sequences ss
ORDER BY ss.first_capture_A;

修正后的SQL查询 #

通过在分组筛选中添加所有A记录早于第一个B记录、所有B记录早于最后一个C记录的条件，严格过滤违规序列：

WITH sensor_sequences AS (
    SELECT 
        sm.unique_id,
        MIN(CASE WHEN sm.sensor = 'A' THEN sm.timestamp END) AS first_capture_A,
        MAX(CASE WHEN sm.sensor = 'A' THEN sm.timestamp END) AS last_capture_A,
        MIN(CASE WHEN sm.sensor = 'B' THEN sm.timestamp END) AS first_capture_B,
        MAX(CASE WHEN sm.sensor = 'B' THEN sm.timestamp END) AS last_capture_B,
        MAX(CASE WHEN sm.sensor = 'C' THEN sm.timestamp END) AS last_capture_C
    FROM sensor_measurement sm 
    WHERE 
        sm.sensor IN ('A', 'B', 'C')
        AND sm.timestamp BETWEEN '2024-10-10 07:00:00' AND '2024-10-10 12:00:00'
    GROUP BY sm.unique_id
    HAVING 
        -- 确保存在A、B、C的记录
        first_capture_A IS NOT NULL 
        AND first_capture_B IS NOT NULL 
        AND last_capture_C IS NOT NULL 
        -- 严格顺序：所有A早于第一个B，所有B早于最后一个C
        AND last_capture_A < first_capture_B
        AND last_capture_B < last_capture_C
        -- 行程总时长不超过300秒
        AND SECONDS_BETWEEN(last_capture_C, first_capture_A) <= 300
)
SELECT 
    ss.unique_id,
    ss.first_capture_A AS sensor_A_time,
    ss.first_capture_B AS sensor_B_time,
    ss.last_capture_C AS sensor_C_time,
    SECONDS_BETWEEN(ss.last_capture_C, ss.first_capture_A) AS travel_time_seconds
FROM sensor_sequences ss
ORDER BY ss.first_capture_A;

修正说明 #

1. 新增last_capture_A（最后一次A的记录时间）和last_capture_B（最后一次B的记录时间）的计算
2. 在HAVING子句中添加：
   • last_capture_A < first_capture_B：确保所有A的记录都早于首次出现的B，排除B之后出现A的情况
   • last_capture_B < last_capture_C：确保所有B的记录都早于末次出现的C，排除C之后出现B的情况
3. 保留原有的行程时长限制和基础存在性检查，确保结果符合业务要求

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