在MySQL中使用聚合函数检索分组数据的第一条和最后一条记录

在MySQL中，当使用聚合函数从分组记录中获取数据时，检索该组的第一个和最后一个记录可能具有挑战性。虽然多个查询可以完成此任务，但对于大型表来说效率可能较低。

为了优化此过程，MySQL 使用 GROUP_CONCAT 和 SUBSTRING_INDEX 函数提供了更有效的解决方案.

使用 GROUP_CONCAT 和 SUBSTRING_INDEX

通过一起使用这些函数，您可以连接组中的值，然后使用 SUBSTRING_INDEX 提取第一个和最后一个值。具体方法如下：

SUBSTRING_INDEX(GROUP_CONCAT(CAST(open AS CHAR) ORDER BY datetime), ',', 1) AS open
SUBSTRING_INDEX(GROUP_CONCAT(CAST(close AS CHAR) ORDER BY datetime DESC), ',', 1) AS close

工作原理：

• GROUP_CONCAT 将所有开盘或收盘值连接成单个字符串，并按日期时间列排序。
• SUBSTRING_INDEX 通过拆分连接的字符串来提取第一个和最后一个值在逗号 (',') 处。 '1' 参数表示第一次或最后一次出现。

示例：

考虑以下查询：

SELECT MIN(low_price), MAX(high_price),
SUBSTRING_INDEX(GROUP_CONCAT(CAST(open AS CHAR) ORDER BY datetime), ',', 1) AS open,
SUBSTRING_INDEX(GROUP_CONCAT(CAST(close AS CHAR) ORDER BY datetime DESC), ',', 1) AS close
FROM symbols
WHERE date BETWEEN(.. ..)
GROUP BY YEARWEEK(date)

此查询有效地检索每个组的第一条（打开）和最后一条（关闭）记录，从而最大限度地减少大型表的处理时间。