时间戳

首页 > 解决方案 > MySQL中SELECT查询中的值分配和使用IF-ELSE

mysql - MySQL中SELECT查询中的值分配和使用IF-ELSE

问题描述

示例表

| 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

当 A、B、C 的值改变时，保存。

所以示例表“2020-11-19 11:11:34”只保存 B，因为 A 和 C 在“2020-11-19 11:10:13”上没有改变

我想按时间选择 A、B、C 值组

示例结果在这里

| 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

第一步，“按时间和名称获取价值”已完成

第一个结果是这样的。

| 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)

它只有在那个时候才有价值。

所以我创建了一个存储过程来获取搜索时间之前的最新值。

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

但它还有问题...当 VALUE 不为空时，不会更新 lastA、lastB、lastC

该程序结果在这里

| 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 不是 10.3，而是 40.3，因为有一个更新的值。

但它没有改变。

在这个部分，

    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;

我可以像这样使用 IF-ELSE 吗？

    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;

有可能吗？

如何获得我想要的结果？

编辑）我使用 MariaDB 10.5

标签： mysqlmariadbmariasql

解决方案

这是 2 个单独的任务，而不是一个任务：

  1. 查找每个名称和每次的实际值。它有简单的解决方案：
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. 透视数据。列name列表是静态的，因此我们可以对上一个任务的结果使用条件聚合：
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`

小提琴

推荐阅读