如何使用聚合函数在 MySQL 查询中获取分组记录的第一条和最后一条记录? [英] How to fetch the first and last record of a grouped record in a MySQL query with aggregate functions?

问题描述

我正在尝试获取分组"记录的第一条和最后一条记录.
更准确地说，我正在做这样的查询

I am trying to fetch the first and the last record of a 'grouped' record.
More precisely, I am doing a query like this

SELECT MIN(low_price), MAX(high_price), open, close
FROM symbols
WHERE date BETWEEN(.. ..)
GROUP BY YEARWEEK(date)

但我想获得该组的第一个和最后一个记录.它可以通过处理大量请求来完成，但我有一张很大的桌子.

but I'd like to get the first and the last record of the group. It could by done by doing tons of requests but I have a quite large table.

是否有(如果可能的话，处理时间短)方法可以用 MySQL 做到这一点?

Is there a (low processing time if possible) way to do this with MySQL?

推荐答案

您想使用 GROUP_CONCAT 和 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 

这避免了昂贵的子查询，而且我发现它对于这个特定问题通常更有效.

This avoids expensive sub queries and I find it generally more efficient for this particular problem.

查看这两个函数的手册页以了解它们的参数，或访问这篇文章，其中包含如何执行的示例 MySQL 中的时间帧转换 以获取更多解释.

Check out the manual pages for both functions to understand their arguments, or visit this article which includes an example of how to do timeframe conversion in MySQL for more explanations.

这篇关于如何使用聚合函数在 MySQL 查询中获取分组记录的第一条和最后一条记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！