MySQL - 如何将数据与前一行进行分组比较？

Question

我有一个包含以下数据的表格:

mysql> select * from playground order by macAddress, locatedAt;
+------------+---------------------+--------------+
| macAddress | locatedAt           | status       |
+------------+---------------------+--------------+
| device1    | 2014-08-11 01:20:27 | connected    |
| device1    | 2014-08-11 01:30:27 | connected    |
| device1    | 2014-08-11 01:40:27 | disconnected |
| device1    | 2014-08-11 01:49:27 | connected    |
| device1    | 2014-08-11 01:50:27 | disconnected |
| device1    | 2014-08-11 01:55:27 | disconnected |
| device1    | 2014-08-11 02:05:27 | disconnected |
| device1    | 2014-08-11 02:17:27 | disconnected |
| device1    | 2014-08-11 02:27:27 | disconnected |
| device1    | 2014-08-11 02:47:27 | connected    |
| device1    | 2014-08-11 02:57:27 | connected    |
| device1    | 2014-08-11 03:50:27 | disconnected |

我需要总结如下:

date         | hour | connected | disconnected | previouslyConnected
2014-08-11   |  1   |     1     |      0       |      0
2014-08-11   |  2   |     1     |      0       |      0
2014-08-11   |  3   |     0     |      0       |      1

只要设备每小时连接一次，断开连接就没有关系。如果设备在给定时间内仅具有“断开连接”条目，则它处于“断开连接”状态。如果设备断开连接，并且同一设备在前一小时内处于“已连接”状态，则该设备将进入 previousConnected 状态。否则它本身就会处于“断开连接”状态。

在第三行中，device1 处于“previouslyConnected”状态，因为该设备在前一小时(第 2 小时)已连接。

我在获取“previouslyConnected”状态设备时遇到问题；

通过我当前的查询，我可以获得除最后一列之外的所有内容。

SELECT TEMP.day, TEMP.hour, 
    SUM(CASE WHEN TEMP.connected > 0 THEN 1 ELSE 0 END) as connected,
    SUM(CASE WHEN TEMP.unconnected > 0 AND TEMP.connected < 1 THEN 1 ELSE 0 END) as unconnected
    from 

    (SELECT
        DATE(locatedAt) day,
        HOUR(locatedAt) hour,
        macAddress,
        SUM(CASE WHEN status='connected' THEN 1 ELSE 0 END) as connected,
        SUM(CASE WHEN status='disconnected' THEN 1 ELSE 0 END) as unconnected
    FROM
        playground
    GROUP BY 
        DATE(locatedAt), HOUR(locatedAt), macAddress) as TEMP

    GROUP BY TEMP.day, TEMP.hour;

有什么指点吗？

Answer 1

您可以使用用户定义的变量来检查结果的先前状态，在内部查询中我使用了 GROUP_CONCAT以 locatedAt 的降序连接所有状态因此它将按降序生成逗号分隔的状态字符串，并使用 SUBSTRING_INDEX我选择了第一个状态，即机器的最后一个状态，并且我存储了表达式 SUBSTRING_INDEX(GROUP_CONCAT( 中的最后一个状态。状态ORDER BY locatedAt DESC),',',1)在@prev变量，以及 CASE语句检查上一行是否已连接，显示 1 else 0

SELECT TEMP.day, TEMP.hour, 
    SUM(TEMP.connected > 0) as connected,
    SUM(TEMP.unconnected > 0 AND TEMP.connected < 1) as unconnected,
    TEMP.previouslyConnected
    FROM 
    (
SELECT
        DATE(locatedAt) day,
        HOUR(locatedAt) hour,
        macAddress,
        SUM(status='connected') as connected,
        SUM(status='disconnected') as unconnected,
        CASE WHEN @prev = 'connected' THEN 1 ELSE 0 END previouslyConnected,
       @prev:= SUBSTRING_INDEX(GROUP_CONCAT(`status` ORDER BY locatedAt DESC),',',1) 
    FROM
        playground
      JOIN (SELECT @prev:='') t
    GROUP BY 
        DATE(locatedAt), HOUR(locatedAt), macAddress
      ) as TEMP

    GROUP BY TEMP.day, TEMP.hour;

Fiddle Demo