时间戳

首页 > 解决方案 > 在 SQL 中将行转换为列的查询

sql - 在 SQL 中将行转换为列的查询

问题描述

这是我的查询：

与 Q1 作为
 (
     选择 *，
         LAG(ignition) over (ORDER BY [timestamp]) 作为 PrevStatus，
         LEAD(ignition) over (order by [timestamp]) as NextStatus
     FROM车辆历史表
                            WHERE plateno='Crematory' 和时间戳在 '2021-09-02 00:00:10.00' 和 '2021-09-02 23:59:59.00' 之间
),
 Q2 为
 (
     选择 *，
         ROW_NUMBER() over（按时间戳排序）为 rn，
         LAG([volume1]) OVER (ORDER BY [timestamp]) AS VolumeIgOff                    
        
            
     从第一季度开始
     其中 PrevStatus 为空或 NextStatus 是否为空（点火 = PrevStatus 和点火 = NextStatus）
 ),
 Q3 为
 (
     选择 a.timestamp、a.ignition、a.volume1
     从 Q2 作为
     在 b.rn = a.rn + 1 上将 Q2 内连接为 b
     其中 a.rn % 2 0
 )
 选择  
 时间戳 AS IgnitionOn,
 点火，
[第 1 卷] AS FuelLevelON                        
 从第三季度开始
 按时间戳排序

这是我目前的结果：

点火点火 FuelLevelON
2021-09-02 00:00:10.00 1 251.35
2021-09-02 00:39:09.00 0 251.35
2021-09-02 00:48:34.00 1 251.35
2021-09-02 03:24:31.00 0 208.1178
2021-09-02 03:37:22.00 1 208.1178
2021-09-02 04:59:37.00 0 181.4747
2021-09-02 05:17:14.00 1 181.4747
2021-09-02 06:20:27.00 0 159.8586
2021-09-02 06:22:29.00 1 159.8586
2021-09-02 07:30:41.00 0 140.2533
2021-09-02 11:29:38.00 1 141.2587
2021-09-02 12:41:52.00 0 117.6318
2021-09-02 12:43:22.00 1 117.6318
2021-09-02 14:07:43.00 0 254.3662
2021-09-02 14:23:49.00 1 254.3662
2021-09-02 15:31:14.00 0 238.2798
2021-09-02 15:41:02.00 1 238.2798
2021-09-02 16:46:55.00 0 218.6745
2021-09-02 16:48:25.00 1 218.6745
2021-09-02 17:55:06.00 0 196.5557
2021-09-02 18:09:03.00 1 196.5557
2021-09-02 19:09:37.00 0 174.9396
2021-09-02 19:30:26.00 1 174.9396
2021-09-02 20:56:53.00 0 152.8208
2021-09-02 21:01:40.00 1 152.8208
2021-09-02 22:08:59.00 0 135.729

这是我的预期结果：

点火开 点火关    
2021-09-02 00:00:10.00 2021-09-02 00:39:09.00
2021-09-02 00:48:34.00 2021-09-02 03:24:31.00
2021-09-02 03:37:22.00 2021-09-02 04:59:37.00
2021-09-02 05:17:14.00 2021-09-02 06:20:27.00
2021-09-02 06:22:29.00 2021-09-02 07:30:41.00
2021-09-02 11:29:38.00 2021-09-02 12:41:52.00
2021-09-02 12:43:22.00 2021-09-02 14:07:43.00
2021-09-02 14:23:49.00 2021-09-02 15:31:14.00
2021-09-02 15:41:02.00 2021-09-02 16:46:55.00
2021-09-02 16:48:25.00 2021-09-02 17:55:06.00
2021-09-02 18:09:03.00 2021-09-02 19:09:37.00
2021-09-02 19:30:26.00 2021-09-02 20:56:53.00
2021-09-02 21:01:40.00 2021-09-02 22:08:59.00

这是我的源数据车辆历史表：

时间戳点火量1   
2021-09-02 22:10:11.00 0 135.729  
2021-09-02 22:09:11.00 0 135.729  
2021-09-02 22:08:59.00 0 135.729  
2021-09-02 21:01:40.00 1 152.8208
2021-09-02 21:01:23.00 0 152.8208
2021-09-02 21:01:12.00 0 152.8208
2021-09-02 21:00:13.00 0 152.8208
2021-09-02 20:59:11.00 0 153.3235
2021-09-02 20:58:15.00 0 153.3235
2021-09-02 20:57:11.00 0 152.8208
2021-09-02 20:56:53.00 0 152.8208
2021-09-02 19:36:13.00 1 174.9396
2021-09-02 19:35:15.00 1 174.9396
2021-09-02 19:34:11.00 1 174.9396
2021-09-02 19:33:10.00 1 174.9396
2021-09-02 19:32:17.00 1 174.9396
2021-09-02 19:31:24.00 1 174.9396
2021-09-02 19:30:42.00 1 174.9396
2021-09-02 19:30:26.00 1 174.9396
2021-09-02 19:16:15.00 0 174.9396
2021-09-02 19:14:12.00 0 174.9396
2021-09-02 19:10:11.00 0 174.9396
2021-09-02 19:09:37.00 0 174.9396
2021-09-02 19:09:13.00 1 174.9396
2021-09-02 18:09:03.00 1 196.5557
2021-09-02 18:01:15.00 0 196.5557
2021-09-02 17:59:11.00 0 196.5557
2021-09-02 17:58:11.00 0 196.5557
2021-09-02 17:57:17.00 0 196.5557
2021-09-02 17:56:12.00 0 196.5557
2021-09-02 17:55:18.00 0 196.5557
2021-09-02 17:55:06.00 0 196.5557
2021-09-02 16:48:25.00 1 218.6745
2021-09-02 16:47:11.00 0 218.6745
2021-09-02 16:46:55.00 0 218.6745
2021-09-02 16:46:11.00 1 218.6745
2021-09-02 15:57:11.00 1 232.2474
2021-09-02 15:41:19.00 1 238.2798
2021-09-02 15:41:02.00 1 238.2798
2021-09-02 15:35:11.00 0 238.2798
2021-09-02 15:31:14.00 0 238.2798
2021-09-02 14:24:05.00 1 254.3662
2021-09-02 14:23:49.00 1 254.3662
2021-09-02 14:07:43.00 0 254.3662
2021-09-02 12:44:10.00 1 117.6318
2021-09-02 12:43:33.00 1 117.6318
2021-09-02 12:43:22.00 1 117.6318
2021-09-02 12:42:10.00 0 117.6318
2021-09-02 12:41:52.00 0 117.6318
2021-09-02 11:29:38.00 1 141.2587
2021-09-02 07:30:41.00 0 140.2533
2021-09-02 06:22:29.00 1 159.8586
2021-09-02 06:22:10.00 0 159.8586
2021-09-02 06:21:17.00 0 159.8586
2021-09-02 06:20:27.00 0 159.8586
2021-09-02 05:17:14.00 1 181.4747
2021-09-02 04:59:37.00 0 181.4747
2021-09-02 03:37:22.00 1 208.1178
2021-09-02 03:24:31.00 0 208.1178
2021-09-02 00:48:34.00 1 251.35   
2021-09-02 00:39:09.00 0 251.35   
2021-09-02 00:03:09.00 1 251.35   
2021-09-02 00:02:10.00 1 251.35   
2021-09-02 00:01:11.00 1 251.35   
2021-09-02 00:00:10.00 1 251.35

标签： sqlsql-timestamp

解决方案

这就是所谓的差距和孤岛问题。

由于您只有两种状态，因此您只需查找从0to的转换1。随着时间的推移，总结会给你一个'partition_id'；每个分区以连续的 1 开始并以连续的 0 结束。

拥有这些组后，条件聚合将找到组中的第一个 1（打开）和组中的第一个 0（关闭）。

WITH
  transitions AS
(
  SELECT
    vehiclehistorytable.*,
    CASE WHEN LAG(ignition) OVER (ORDER BY timestamp) = ignition OR ignition = 0 THEN 0 ELSE 1 END   AS switch_on
  FROM
    vehiclehistorytable
),
  ignition_phases AS
(
  SELECT
    transitions.*,
    SUM(switch_on) OVER (ORDER BY timestamp)   AS partition_id
  FROM
    transitions
)
SELECT
  MIN(CASE WHEN ignition = 1 THEN timestamp END)   AS ignition_on,
  MIN(CASE WHEN ignition = 0 THEN timestamp END)   AS ignition_off
FROM
  ignition_phases
GROUP BY
  partition_id
ORDER BY
  partition_id

如果您想知道这些时间的卷，请在第一个 cte 中添加一个检查何时发生 switch_off ...

WITH
  transitions AS
(
  SELECT
    vehiclehistorytable.*,
    CASE WHEN LAG(ignition) OVER (ORDER BY timestamp) = ignition OR ignition = 0 THEN 0 ELSE 1 END   AS switch_on,
    CASE WHEN LAG(ignition) OVER (ORDER BY timestamp) = ignition OR ignition = 1 THEN 0 ELSE 1 END   AS switch_off
  FROM
    vehiclehistorytable
),
  ignition_phases AS
(
  SELECT
    transitions.*,
    SUM(switch_on) OVER (ORDER BY timestamp)   AS partition_id
  FROM
    transitions
)
SELECT
  MIN(CASE WHEN switch_on  = 1 THEN timestamp END)   AS ignition_on,
  MIN(CASE WHEN switch_on  = 1 THEN volume    END)   AS ignition_on_volume,
  MIN(CASE WHEN switch_off = 1 THEN timestamp END)   AS ignition_off, 
  MIN(CASE WHEN switch_off = 1 THEN volume    END)   AS ignition_off_volume
FROM
  ignition_phases
GROUP BY
  partition_id
ORDER BY
  partition_id

编辑：处理来自的 NULL 的反转逻辑LAG()

演示：https ://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=04a5ee6c62671cde5884a392831e0f16

推荐阅读