基于时间戳和布尔值的SQL表中的两行合并

Question

出于“历史性”原因，我有以下表格：

CREATE TABLE IF NOT EXISTS `measurement`
(
        `measureID`     int NOT NULL AUTO_INCREMENT ,
        `terraID`       int NOT NULL ,
        `pinID`         int NOT NULL ,
        `time`          timestamp NOT NULL ,
        `value`         float NULL ,
        `temperature`   boolean NOT NULL ,

        PRIMARY KEY (`measureID`),
        KEY `measure_terraID_FK` (`terraID`),
        KEY `measure_pinID_FK` (`pinID`),
        CONSTRAINT `measure_terraID_reference` FOREIGN KEY `measure_terraID_FK` (`terraID`) REFERENCES `terrarium` (`terraID`),
        CONSTRAINT `measure_pinID_reference` FOREIGN KEY `measure_pinID_FK` (`pinID`) REFERENCES `pins` (`pinID`)
) ENGINE=INNODB COLLATE=utf8mb4_unicode_ci;

| measureID | terraID | pinID | time                | value  | temperature |
|:----------|---------|-------|---------------------|--------|------------:|
|         1 |       1 |     9 | 2020-04-10 13:00:01 |   34.3 |           0 |
|         2 |       1 |     9 | 2020-04-10 13:00:01 |   26.5 |           1 |
|         3 |       2 |    10 | 2020-04-10 13:00:01 |   35.1 |           0 |
|         4 |       2 |    10 | 2020-04-10 13:00:01 |   32.9 |           1 |
|         5 |       1 |     9 | 2020-04-10 13:05:01 |   34.4 |           0 |
|         6 |       1 |     9 | 2020-04-10 13:05:01 |   26.6 |           1 |
|         7 |       2 |    10 | 2020-04-10 13:05:01 |     35 |           0 |
|         8 |       2 |    10 | 2020-04-10 13:05:01 |     33 |           1 |
[...]
|     38087 |       2 |    10 | 2020-08-31 12:50:02 |   35.9 |           0 |
|     38088 |       2 |    10 | 2020-08-31 12:50:02 |     35 |           1 |
|     38089 |       1 |    11 | 2020-08-31 12:50:02 | 25.187 |           1 |
|     38090 |       2 |    12 | 2020-08-31 12:50:02 | 28.312 |           1 |
|     38091 |       2 |    10 | 2020-08-31 12:55:01 |   35.8 |           0 |
|     38092 |       2 |    10 | 2020-08-31 12:55:01 |     35 |           1 |
|     38093 |       1 |    11 | 2020-08-31 12:55:01 |  25.25 |           1 |
|     38094 |       2 |    12 | 2020-08-31 12:55:01 | 28.375 |           1 |

并希望根据time和pinID将其插入到一个新表中，以便最终的表如下所示：

CREATE TABLE IF NOT EXISTS `measurement`
(
        `measureID`     int NOT NULL AUTO_INCREMENT ,
        `terraID`       int NOT NULL ,
        `pinID`         int NOT NULL ,
        `time`          timestamp NOT NULL ,
        `temperature`   float NULL ,
        `humidity`      float NULL ,

        PRIMARY KEY (`measureID`),
        KEY `measure_terraID_FK` (`terraID`),
        KEY `measure_pinID_FK` (`pinID`),
        CONSTRAINT `measure_terraID_reference` FOREIGN KEY `measure_terraID_FK` (`terraID`) REFERENCES `terrarium` (`terraID`),
        CONSTRAINT `measure_pinID_reference` FOREIGN KEY `measure_pinID_FK` (`pinID`) REFERENCES `pins` (`pinID`)
) ENGINE=INNODB COLLATE=utf8mb4_unicode_ci;

| measureID | terraID | pinID | time                | temperature | humidity |
|:----------|---------|-------|---------------------|--------------|---------:|
|         2 |       1 |     9 | 2020-04-10 13:00:01 |         26.5 |     34.3 |
|         4 |       2 |    10 | 2020-04-10 13:00:01 |         32.9 |     35.1 |
|         6 |       1 |     9 | 2020-04-10 13:05:01 |         26.6 |     34.4 |
|         8 |       2 |    10 | 2020-04-10 13:05:01 |           33 |       35 |
[...]
|     38088 |       2 |    10 | 2020-08-31 12:50:02 |           35 |     35.9 |
|     38089 |       1 |    11 | 2020-08-31 12:50:02 |       25.187 |     NULL |
|     38090 |       2 |    12 | 2020-08-31 12:50:02 |       28.312 |     NULL |
|     38092 |       2 |    10 | 2020-08-31 12:55:01 |           35 |     35.8 |
|     38093 |       1 |    11 | 2020-08-31 12:55:01 |        25.25 |     NULL |
|     38094 |       2 |    12 | 2020-08-31 12:55:01 |       28.375 |     NULL |

我的SQL版本是mariadb-10.3。

我不想丢失任何值，也不关心温度列，因为它是读取值的旧标识符。但我想修改所有旧条目，将其转换为新结构。但是您可以将它用于IF(temperature=1，value，NULL)作为温度，IF(temperature=0，value，'')作为湿度或某种程度。

terraID和pinID都与其他表相连接，并不是所有的传感器都能传递温度和湿度。

我没有运气在一些选择的神秘魔法中.也许我太笨了，没法对这个问题深信不疑。

你能不能带我找到正确的功能。只是一次触发的手工作业。

Answer 1

使用LEAD()窗口函数：

INSERT INTO new_measurement(measureID, terraID, pinID, time, temperature, humidity)
SELECT measureID, terraID, pinID, time, 
       CASE temperature WHEN 1 THEN value END, 
       value
FROM (
  SELECT *, 
         LEAD(terraID) OVER (ORDER BY time DESC) next_terraID,
         LEAD(pinID) OVER (ORDER BY time DESC) next_pinID
  FROM measurement 
) t  
WHERE terraID <> next_terraID OR pinID <> next_pinID