mysql - 使用 group by 子句的查询排序不正确

Question

所以我有以下查询:

SELECT sensor.id as `sensor_id`,
       sensor_reading.id as `reading_id`,
       sensor_reading.reading as `reading`,
       from_unixtime(sensor_reading.reading_timestamp) as `reading_timestamp`,
       sensor_reading.lower_threshold as `lower_threshold`,
       sensor_reading.upper_threshold as `upper_threshold`,
       sensor_type.units as `unit`
FROM sensor
LEFT JOIN sensor_reading ON sensor_reading.sensor_id = sensor.id
LEFT JOIN sensor_type ON sensor.sensor_type_id = sensor_type.id
WHERE sensor.company_id = 1
GROUP BY sensor_reading.sensor_id
ORDER BY sensor_reading.reading_timestamp DESC

这里有三张 table 。 sensor_type表，仅用于单个显示字段(单位)，sensor表，包含传感器信息，以及sensor_reading 表，其中包含传感器的各个读数。有多个读数适用于单个传感器，因此 sensor_reading 表中的每个条目都有一个 sensor_id，它链接到传感器表中的 ID 字段键约束。

理论上，此查询应返回每个唯一传感器的最新 sensor_reading。相反，它会返回每个传感器的第一个读数。我在这里看到了一些类似问题的帖子，但无法使用他们的任何答案来解决这个问题。理想情况下，查询需要尽可能高效，因为该表有数千个读数(并且还在继续增长)。

有谁知道我可以如何更改此查询以返回最新读数？如果我删除 GROUP BY 子句，它会返回正确的顺序，但我必须筛选数据以获得每个传感器的最新数据。

理想情况下，我不想运行子查询，因为这会大大降低速度，而速度是这里的一个重要因素。

谢谢!

Answer 1

In theory, this query should return the most recent sensor_reading for EACH unique sensor.

这是对 MySQL Group by extension 的一个相当普遍的误解。 ，它允许您选择不包含在 group by 子句中的没有聚合的列。文档说明的内容是:

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause

因此，由于您按 sensor_reading.sensor_id 分组，MySQL 将为每个 sensor_id 从 sensor_reading 选择任何行>，然后在为每个 sensor_id 选择一行后，它将对所选行应用排序。

由于您只需要每个传感器的最新行，一般方法是:

SELECT  *
FROM    sensor_reading AS sr
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    sensor_reading AS sr2
            WHERE   sr2.sensor_id = sr.sensor_id
            AND     sr2.reading_timestamp > sr.reading_timestamp
        );

然而，MySQL will optimise LEFT JOIN/IS NULL better than NOT EXISTS所以 MySQL 特定的解决方案是:

SELECT  sr.*
FROM    sensor_reading AS sr
        LEFT JOIN sensor_reading AS sr2
            ON sr2.sensor_id = sr.sensor_id
            AND sr2.reading_timestamp > sr.reading_timestamp
WHERE   sr2.id IS NULL;

因此将其合并到您的查询中，您最终会得到:

SELECT sensor.id as `sensor_id`,
       sensor_reading.id as `reading_id`,
       sensor_reading.reading as `reading`,
       from_unixtime(sensor_reading.reading_timestamp) as `reading_timestamp`,
       sensor_reading.lower_threshold as `lower_threshold`,
       sensor_reading.upper_threshold as `upper_threshold`,
       sensor_type.units as `unit`
FROM    sensor
        LEFT JOIN sensor_reading 
            ON sensor_reading.sensor_id = sensor.id
        LEFT JOIN sensor_type 
            ON sensor.sensor_type_id = sensor_type.id
        LEFT JOIN sensor_reading AS sr2
            ON sr2.sensor_id = sensor_reading.sensor_id
            AND sr2.reading_timestamp > sensor_reading.reading_timestamp
WHERE sensor.company_id = 1
AND sr2.id IS NULL
ORDER BY sensor_reading.reading_timestamp DESC;

获取每组最大值的另一种方法是内部连接回到最新的行，比如:

SELECT  sr.*
FROM    sensor_reading AS sr
        INNER JOIN
        (   SELECT  sensor_id, MAX(reading_timestamp) AS reading_timestamp
            FROM    sensor_reading
            GROUP BY sensor_id
        ) AS sr2
            ON sr2.sensor_id = sr.sensor_id
            AND sr2.reading_timestamp = sr.reading_timestamp;

您可能会发现这比其他方法更有效，也可能不会，YMMV。它基本上取决于您的数据和索引，正如您所说，子查询在 MySQL 中可能是一个问题，因为完整的结果最初是具体化的。