如何使用聚合函数在 MySQL 查询中获取分组记录的第一条和最后一条记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1379565/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to fetch the first and last record of a grouped record in a MySQL query with aggregate functions?
提问by Jimmy
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.
但我想获得该组的第一个和最后一个记录。它可以通过执行大量请求来完成，但我有一张相当大的桌子。
Is there a (low processing time if possible) way to do this with MySQL?
是否有（如果可能的话，处理时间短）方法可以用 MySQL 做到这一点？
回答by Joao Costa
You want to use GROUP_CONCATand SUBSTRING_INDEX:
你想使用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 MySQLfor more explanations.
查看这两个函数的手册页以了解它们的参数，或访问这篇文章，其中包含如何在 MySQL 中进行时间帧转换的示例以获取更多解释。
回答by Charles Bretana
Try This to start with... :
试试这个从...开始：
Select YearWeek, Date, Min(Low_Price), Max(High_Price)
From
(Select YEARWEEK(date) YearWeek, Date, LowPrice, High_Price
From Symbols S
Where Date BETWEEN(.. ..)
GROUP BY YEARWEEK(date)) Z
Group By YearWeek, Date
回答by webmaster
Here is a great specific solution to this specific problem: http://topwebguy.com/first-and-last-in-mysql-a-working-solution/It's almost as simple as using FIRST and LAST in MySQL.
这是针对此特定问题的一个很好的特定解决方案：http: //topwebguy.com/first-and-last-in-mysql-a-working-solution/它几乎与在 MySQL 中使用 FIRST 和 LAST 一样简单。
I will include the code that actually provides the solution but you can look upi the whole text:
我将包含实际提供解决方案的代码，但您可以查找整个文本：
SELECT
word ,
(SELECT a.ip_addr FROM article a
WHERE a.word = article.word
ORDER BY a.updated LIMIT 1) AS first_ip,
(SELECT a.ip_addr FROM article a
WHERE a.word = article.word
ORDER BY a.updated DESC LIMIT 1) AS last_ip
FROM notfound GROUP BY word;
回答by james.c.funk
Assuming that you want the ids of the records with the lowest low_price and the highest high_price you could add these two columns to your query,
假设您想要具有最低 low_price 和最高 high_price 的记录的 ID，您可以将这两列添加到您的查询中，
SELECT
(SELECT id ORDER BY low_price ASC LIMIT 1) low_price_id,
(SELECT id ORDER BY high_price DESC LIMIT 1) high_price_id,
MIN(low_price), MAX(high_price), open, close
FROM symbols
WHERE date BETWEEN(.. ..)
GROUP BY YEARWEEK(date)
If efficiency is an issue you should add a column for 'year_week', add some covering indexes, and split the query in two.
如果效率是一个问题，您应该为“year_week”添加一列，添加一些覆盖索引，并将查询一分为二。
The 'year_week' column is just an INT set to the value of YEARWEEK(date) and updated whenever the 'date' column is updated. This way you don't have to recalculate it for each query and you can index it.
'year_week' 列只是一个设置为 YEARWEEK(date) 值的 INT，并在更新 'date' 列时更新。这样你就不必为每个查询重新计算它，你可以索引它。
The new covering indexes should look like this. The ordering is important. KEY yw_lp_id (year_week, low_price, id), KEY yw_hp_id (year_week, high_price, id)
新的覆盖索引应如下所示。顺序很重要。KEY yw_lp_id (year_week, low_price, id), KEY yw_hp_id (year_week, high_price, id)
You should then use these two queries
然后你应该使用这两个查询
SELECT
(SELECT id ORDER BY low_price ASC LIMIT 1) low_price_id,
MIN(low_price), open, close
FROM symbols
WHERE year_week BETWEEN(.. ..)
GROUP BY year_week
and
和
SELECT
(SELECT id ORDER BY high_price DESC LIMIT 1) high_price_id,
MAX(high_price), open, close
FROM symbols
WHERE year_week BETWEEN(.. ..)
GROUP BY year_week
Covering indexes are pretty useful. Check thisout for more details.
覆盖索引非常有用。查看此了解更多详细信息。
