Example table
| 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
When the value of A, B, C is changed, it is saved.
So example table, '2020-11-19 11:11:34' save only B, because A and C is not change on '2020-11-19 11:10:13'
I want get select A, B, C value group by TIME
Example result is here
| 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
first step, 'Get Value By Time and Name' is done
And first result is like this.
| TIME | A | B | C |
-----------------------+-------+-------+-----------
2020-11-19 11:10:13 | 12.3 | 14.1 | 10.3
2020-11-19 11:11:34 |(NULL) | 21.3 | (NULL)
2020-11-19 11:12:45 | 32.1 |(NULL) | 40.3
2020-11-19 11:13:05 | 31.1 |(NULL) | (NULL)
It get value only that time.
So I created a stored procedure to get the most recent value before the search time.
CREATE PROCEDURE `GetValuesByTime`(
IN `StartDate` DATETIME,
IN `EndDate` DATETIME
)
BEGIN
DECLARE lastA DOUBLE;
DECLARE lastB DOUBLE;
DECLARE lastC DOUBLE;
SET @lastA = 0;
SET @lastB = 0;
SET @lastC = 0;
SELECT VALUE
INTO lastA
FROM data_log
WHERE TIME <= StartDate order by TIME desc LIMIT 1;
SELECT VALUE
INTO lastB
FROM data_log
WHERE TIME <= StartDate order by TIME desc LIMIT 1;
SELECT VALUE
INTO lastC
FROM data_log
WHERE TIME <= StartDate order by TIME desc LIMIT 1;
SELECT
TIME,
IFNULL(sum(NAME) = 'A' THEN VALUE END), lastA) AS 'A',
IFNULL(sum(NAME) = 'B' THEN VALUE END), lastB) AS 'B',
IFNULL(sum(NAME) = 'C' THEN VALUE END), lastC) AS 'C',
FROM data_log
WHERE TIME >= StartDate AND TIME <= EndDate
group BY TIME;
END
But it has problem yet...lastA, lastB, lastC is not updated when VALUE is not null
That procedure result is here
| 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 | 14.1 | 40.3
2020-11-19 11:13:05 | 31.1 | 14.1 | 10.3
'2020-11-19 11:13:05' C is not 10.3, it is 40.3, because there is a newer value.
But it hasn't changed.
In this section,
SELECT
TIME,
IFNULL(sum(NAME) = 'A' THEN VALUE END), lastA) AS 'A',
IFNULL(sum(NAME) = 'B' THEN VALUE END), lastB) AS 'B',
IFNULL(sum(NAME) = 'C' THEN VALUE END), lastC) AS 'C',
FROM data_log
WHERE TIME >= StartDate AND TIME <= EndDate
group BY TIME;
Can I use IF-ELSE like this?
SELECT
TIME,
IF (sum(NAME) = 'A' THEN VALUE END) = null THEN lastA
ELSE VALUE, lastA := VALUE AS 'A',
IF (sum(NAME) = 'B' THEN VALUE END) = null THEN lastB
ELSE VALUE, lastB := VALUE
IF (sum(NAME) = 'C' THEN VALUE END) = null THEN lastC
ELSE VALUE, lastC := VALUE
FROM data_log
WHERE TIME >= StartDate AND TIME <= EndDate
group BY TIME;
Is it possible way?
How do I get the results I want?
EDIT) I use MariaDB 10.5
This is 2 separate tasks, not one task:
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 column list is static, so we may use conditional aggregation over the result from previous task: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`