基于分组查询最后N行数据

Question

我在MySQL数据库中有以下表格：

Table temperature

id - sensor_id - value - created_at

1      1          4.5    04-11-2014
2      1          2.2    05-11-2014
3      1          3.3    06-11-2014
4      2          4.5    04-11-2014
5      2          2.2    05-11-2014
6      2          3.3    06-11-2014

我想要做的是获取每个sensor_id的最新N行数据。

我找到了许多不同的解决方案，但大多数都包括非常低效的连接，在我这种情况下不适用，因为我有超过一百万行的数据，查询非常缓慢。

我能想到的最有效的查询是这个：

set @num := 0, @sensor_id:= '';

select id, sensor_id, value, created_at,
  @num := if(@sensor_id = sensor_id, @num + 1, 1) as row_number,
  @sensor_id := sensor_id as dummy
from temperature
group by id, sensor_id, value, created_at
having row_number <= 2;

这个查询来源于这篇文章：http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/，但问题是它获取的是前N行，而不是最近的N行。如何添加ORDER BY语句才能获取最新的N行而不是前N行？如果你要查询最新的两行数据，期望的结果应该是这样的：

id - sensor_id - value - created_at

2      1          2.2    05-11-2014
3      1          3.3    06-11-2014
5      2          2.2    05-11-2014
6      2          3.3    06-11-2014

- Yasen Slavov

---

按照直接放置只会对最终结果进行排序。我需要的是在行被分组之前对它们进行排序。 - Yasen Slavov

在你的例子中，所有的测量似乎都是在同一时间进行的。也许如果你想要最后两行，你可以只检查“created_at > 05-11-2014”。 - JCalcines

谁说要用ORDER BY了！？！;-) 无论如何，想要的结果会是什么样子呢？ - Strawberry

编辑了问题以包括所需的结果。 - Yasen Slavov

Answer 1

您可以对结果进行排序，然后应用行编号逻辑。

set @num := 0, @sensor_id:= '';

select *,
  @num := if(@sensor_id = sensor_id, @num + 1, 1) as row_number,
  @sensor_id := sensor_id as dummy
from
(select id, sensor_id, value, created_at
from temperature
order by sensor_id, created_at desc) T
group by id, sensor_id, value, created_at

having row_number <= 2;

- radar

Answer 2

如果连接由于行数过多而效率低下，则使用用户变量也可能效率低下，因为查询需要检查每一行。

如果您在返回结果后稍微处理一下以使其符合您想要的格式，则还有另一种选择。

SELECT sensor_id, SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(':', id, sensor_id, value, created_at) ORDER BY created_at DESC SEPARATOR '#'). '#', 4)
FROM temperature
GROUP BY sensor_id

这里使用CONCAT_WS将行中所有值一起卷起来，用“：”分隔。然后使用GROUP_CONCAT将所有这些值按单个传感器ID连接在一起，以降序日期顺序分隔符“#”连接（假设日期是真实日期格式，而不是文本dd-mm-yyyy格式）。最后使用SUBSTRING_INDEX仅获取最后N行数据（在这种情况下，我只使用了4行）。如果您访问的数据包含任何“：”或“＃”字符，则可以轻松使用其他分隔符。

返回后，您需要将每个返回的行拆分回其各自的字段。

请注意，GROUP_CONCAT结果的最大长度为1024个字符（我认为）默认为。这可以更改，但根据所需数据量和所需行数可能不是问题。

- Kickstart