mysqlmariadbmariasql

Value Assign and Use IF-ELSE in SELECT Query in MySQL

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

Solution

  • This is 2 separate tasks, not one task:

    1. Find actual value for each name and each time. It has simple solution:
    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`
    1. Pivot above data. The 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`

    fiddle