SQL - INSERT INTO从多个选择的多个列

Question

我有一个MariaDB数据库。

在这个DB中，我有下面的表table1:

|     id       |     timestamp     |  unit |  detector  |    value    |
-------------------------------------------------------------------------
UUID()         2020-12-02          1        1             0.1
UUID2()        2020-12-02          1        2             0.2
UUID3()        2020-12-02          2        1             0.3
UUID4()        2020-12-02          2        2             0.4
UUID5()        2020-12-03          1        1             0.5
UUID6()        2020-12-03          1        2             0.6
UUID7()        2020-12-03          2        1             0.7
UUID8()        2020-12-03          2        2             0.8

我被要求将数据映射到这个新表table2

|     id       |     timestamp     |  detector 11   |   detector 12   |  detector 21  |  detector 22 |
----------------------------------------------------------------------------------------------------
UUI9()         2020-12-02            0.1              0.2             0.3             0.4
UUID10()        2020-12-03            0.5              0.6             0.7             0.8

与这种情况的唯一区别是，我有100个检测器和单元组合以及3600万行。我已经编写了代码，可以获得1个检测器所需的值，但我无法找出一种方法来做多行->同时列。我不可能手动完成，这需要几个星期。

INSERT INTO table2
(id, timestamp, detector11)
SELECT UUID(), t1.timestamp, t1.value FROM table1 t1
WHERE t1.unit='1' AND t1.detector='1'
ORDER BY timestamp ;

成功地将数据从表1 (detector=1, unit=1)转换为具有良好时间戳的列(detector11)。但是，现在除了id、时间戳和detector11之外，所有其他列都为NULL。

理想情况下，有人可以帮助我编写这样的代码:

INSERT INTO table2
(id, timestamp, detector11, detector12, detector21, detector22)
SELECT UUID(), t1.timestamp, 
VALUES(t1.value FROM table1 t1
WHERE t1.unit='1' AND t1.detector='1'
ORDER BY timestamp,
t1.value FROM table1 t1
WHERE t1.unit='1' AND t1.detector='2'
ORDER BY timestamp,
t1.value FROM table1 t1
WHERE t1.unit='2' AND t1.detector='1'
ORDER BY timestamp,
t1.value FROM table1 t1
WHERE t1.unit='2' AND t1.detector='2'
ORDER BY timestamp) ;

将同时填充所有列。

Answer 1

您只能连接单独的表。

问题是连接这些值，你必须看看你的ON语句是否有效

CREATE TABLE table2 (id varchar(20), `timestamp` TIMESTAMP, detector11 DECIMAL(4,2), detector12 DECIMAL(4,2)
, detector21 DECIMAL(4,2), detector22 DECIMAL(4,2))

CREATE TABLE table1 (`timestamp`TIMESTAMP, value DECIMAL(4,2), unit INT,detector INT)

INSERT INTO table2
(id, timestamp, detector11, detector12, detector21, detector22)
SELECT UUID(),t1a.timestamp, t1a.detector11,t1b.detector12,t1c.detector21,t1d.detector22
FROM
(SELECT
t1.timestamp, t1.value as detector11 FROM table1 t1
WHERE t1.unit='1' AND t1.detector='1'
ORDER BY timestamp ) t1a
JOIN
(SELECT t1.timestamp, t1.value AS detector12 FROM table1 t1
WHERE t1.unit='1' AND t1.detector='2'
ORDER BY timestamp) t1b
ON t1a.timestamp = t1b.timestamp
JOIN       
(SELECT t1.timestamp,t1.value AS detector21 FROM table1 t1
WHERE t1.unit='2' AND t1.detector='1'
ORDER BY timestamp) t1c
ON t1a.timestamp = t1c.timestamp
JOIN
(SELECT t1.timestamp,t1.value AS detector22 FROM table1 t1
WHERE t1.unit='2' AND t1.detector='2'
ORDER BY timestamp) t1d
ON t1a.timestamp = t1d.timestamp

<>以前✓

db<此处小提琴>

我们可以尝试另一种方法，但这种方法也不会用于数百列，所以你必须自己测试

CREATE TABLE table1
(`id` varchar(7), `timestamp` varchar(10), `unit` int, `detector` int, `value` DECIMAL(10,1))
;

INSERT INTO table1
(`id`, `timestamp`, `unit`, `detector`, `value`)
VALUES
('UUID()', '2020-12-02', 1, 1, 0.1),
('UUID2()', '2020-12-02', 1, 2, 0.2),
('UUID3()', '2020-12-02', 2, 1, 0.3),
('UUID4()', '2020-12-02', 2, 2, 0.4),
('UUID5()', '2020-12-03', 1, 1, 0.5),
('UUID6()', '2020-12-03', 1, 2, 0.6),
('UUID7()', '2020-12-03', 2, 1, 0.7),
('UUID8()', '2020-12-03', 2, 2, 0.8)
;

SET SESSION group_concat_max_len=4294967295;
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
"MAX(CASE WHEN `unit` = ",
`unit`,
" AND `detector` = ",`detector`," THEN `value` ELSE 0  END) AS 'Detector",

`unit`,`detector`,"'"
)
) INTO @sql

FROM `table1`;
SET @sql = CONCAT("SELECT uuid(),`timestamp`, ",@sql," from table1
group by `timestamp`");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

<>以前✓✓✓✓✓uuid() | timestamp | Detector11 | Detector12 | Detector21 | Detector22:----------------------------------- | :--------- | ---------: | ---------: | ---------: | ---------:2020-12-02 | 0.1 | 0.2 | 0.3 | 0.42020-12-03 | 0.5 | 0.6 | 0.7 | 0.8✓

db<此处小提琴>

Answer 2

这是对数据的反规范化。看起来像一个简单的CROSSTAB。

考虑:

TRANSFORM First(Table1.value) AS FirstOfvalue
SELECT Table1.timestamp
FROM Table1
GROUP BY Table1.timestamp
PIVOT "detector " & [unit] & [detector];

如果您想确保字段是按数字顺序排列的，例如:
PIVOT "detector " & Format([unit], "000") & Format([detector], "000");

使用该查询作为SELECT INTO的源。
SELECT *.Query1 INTO Test FROM Query1
唯一标识符字段必须在后面添加。或者直接导出查询，而不是保存到表中。

不知道性能会受到"百万"行。

显然MySQL表被限制为4096个字段。希望你的输出不会超过这个值。

嗯，我做了一些研究，表明PIVOT在MySQL中不可用，将不得不编程一个循环来构建查询。对不起!在Access, SQLServer, Oracle中使用PIVOT