I have a MariaDB database.
Inside that DB, I have the following table, 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
I have been asked to map the data to this new table, 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
The only difference from this situation is that I have 100 detector and unit combinations and 36 million rows. I have written code that can get the desired value for 1 detector but I can't figure out a way of doing multiple rows -> columns at the same time. No way I'm doing this manually, it would take weeks.
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 ;
This successfully translates the data from table1 where (detector=1, unit=1) to the column (detector11) with a good timestamp. However, now I have all the other columns except for id, timestamp, and detector11 to NULL.
Ideally, someone could help me to code something like that:
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) ;
Which would fill all the columns at the same time.
You can only join the separate Tables.
The problem is to join the values, there you have to see if your ON statement for the join is so valid
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<>fiddle here
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments