By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE data_log (`time` DATETIME, name CHAR(1), value DECIMAL(3,1));
INSERT INTO data_log VALUES
('2020-11-19 11:10:13' , 'A' , 12.3),
('2020-11-19 11:10:13' , 'B' , 14.1),
('2020-11-19 11:10:13' , 'C' , 10.3),
('2020-11-19 11:11:34' , 'B' , 21.3),
('2020-11-19 11:12:45' , 'A' , 32.1),
('2020-11-19 11:12:45' , 'C' , 40.3),
('2020-11-19 11:13:05' , 'A' , 13.1);
SELECT *
FROM data_log;
SELECT *
FROM data_log
ORDER BY name, `time`;
| time | name | value |
|---|---|---|
| 2020-11-19 11:10:13 | A | 12.3 |
| 2020-11-19 11:10:13 | B | 14.1 |
| 2020-11-19 11:10:13 | C | 10.3 |
| 2020-11-19 11:11:34 | B | 21.3 |
| 2020-11-19 11:12:45 | A | 32.1 |
| 2020-11-19 11:12:45 | C | 40.3 |
| 2020-11-19 11:13:05 | A | 13.1 |
| time | name | value |
|---|---|---|
| 2020-11-19 11:10:13 | A | 12.3 |
| 2020-11-19 11:12:45 | A | 32.1 |
| 2020-11-19 11:13:05 | A | 13.1 |
| 2020-11-19 11:10:13 | B | 14.1 |
| 2020-11-19 11:11:34 | B | 21.3 |
| 2020-11-19 11:10:13 | C | 10.3 |
| 2020-11-19 11:12:45 | C | 40.3 |
WITH
cte1 AS ( SELECT DISTINCT name FROM data_log ),
cte2 AS ( SELECT DISTINCT `time` FROM data_log )
SELECT DISTINCT
cte1.name,
cte2.`time`,
FIRST_VALUE(data_log.value) OVER (PARTITION BY cte1.name, cte2.`time` ORDER BY data_log.`time` DESC) value
FROM cte1
CROSS JOIN cte2
LEFT JOIN data_log ON data_log.name = cte1.name
AND data_log.`time` <= cte2.`time`
ORDER BY name, `time`
| name | time | value |
|---|---|---|
| A | 2020-11-19 11:10:13 | 12.3 |
| A | 2020-11-19 11:11:34 | 12.3 |
| A | 2020-11-19 11:12:45 | 32.1 |
| A | 2020-11-19 11:13:05 | 13.1 |
| B | 2020-11-19 11:10:13 | 14.1 |
| B | 2020-11-19 11:11:34 | 21.3 |
| B | 2020-11-19 11:12:45 | 21.3 |
| B | 2020-11-19 11:13:05 | 21.3 |
| C | 2020-11-19 11:10:13 | 10.3 |
| C | 2020-11-19 11:11:34 | 10.3 |
| C | 2020-11-19 11:12:45 | 40.3 |
| C | 2020-11-19 11:13:05 | 40.3 |
WITH
cte1 AS ( SELECT DISTINCT name FROM data_log ),
cte2 AS ( SELECT DISTINCT `time` FROM data_log ),
cte3 AS ( SELECT DISTINCT
cte1.name,
cte2.`time`,
FIRST_VALUE(data_log.value) OVER (PARTITION BY cte1.name, cte2.`time` ORDER BY data_log.`time` DESC) value
FROM cte1
CROSS JOIN cte2
LEFT JOIN data_log ON data_log.name = cte1.name
AND data_log.`time` <= cte2.`time` )
SELECT `time`,
MAX(CASE WHEN name = 'A' THEN value END) A,
MAX(CASE WHEN name = 'B' THEN value END) B,
MAX(CASE WHEN name = 'C' THEN value END) C
FROM cte3
GROUP BY `time`
| time | A | B | C |
|---|---|---|---|
| 2020-11-19 11:10:13 | 12.3 | 14.1 | 10.3 |
| 2020-11-19 11:11:34 | 12.3 | 21.3 | 10.3 |
| 2020-11-19 11:12:45 | 32.1 | 21.3 | 40.3 |
| 2020-11-19 11:13:05 | 13.1 | 21.3 | 40.3 |