如何在具有聚合函数的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.

查看这两个函数的手册页以了解它们的参数，或访问本文，其中包含有关如何执行

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屋！