MySQL：如何对每小时的数据进行分组并获取最新的小时数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6137024/
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
MySQL: How to group data per hour and get the latest hour
提问by tradyblix
I'm trying to do a query that fetches data per hourbut instead of the normal group by hour I want to narrow it down and only get the latesthour - meaning the newest data within that hour. With the picture shown below what I wanted to get is the rows with red boxes. If you will notice, first red row is 10:59:51which means it's the only row that's within 10:00:00and 10:59:59. For the rest of the rows that is on 12:00and above I wanted to get 12:37:14because it's the latest or newest for that hour range.
我正在尝试执行一个查询，每个查询获取数据，hour而不是按小时获取正常组，我想缩小范围并只获取latest小时 - 意味着该小时内的最新数据。如下图所示，我想要的是带有红色框的行。如果您会注意到，第一行是红色，10:59:51这意味着它是唯一在10:00:00和内的行10:59:59。对于12:00我想要获取的其余行，12:37:14因为它是该小时范围内的最新或最新行。
I have a simple query that groups the data by hourusing HOUR()like:
我有一个简单的查询，它hour使用以下方法对数据进行分组HOUR()：
SELECT userid, username, date_created
FROM user_accounts
WHERE date_created >= '2009-10-27 00:00:00' AND date_created < '2009-10-27 23:59:59'
GROUP BY HOUR(date_created)
The query, however, is just grouping it by hour 10and 12which returns id 24and 25- but what I needed is id24and 28. Any ideas?
但是，该查询只是按小时对其进行分组，10并12返回 id24和25- 但我需要的是id24和28。有任何想法吗？
回答by diEcho
Try
尝试
SELECT f.*
FROM (
SELECT MAX(UNIX_TIMESTAMP(date_created)) AS mts
FROM user_accounts
GROUP BY HOUR(date_created)
) s
JOIN user_accounts f
ON UNIX_TIMESTAMP(date_created) = s.mts
WHERE DATE(date_created) = '2009-10-27'
回答by Codler
Maybe this will work?
也许这会奏效？
SELECT userid, username, date_created
FROM user_accounts
WHERE userid IN (
SELECT MAX(userid)
FROM user_accounts
WHERE date_created >= '2009-10-27 00:00:00' AND date_created < '2009-10-27 23:59:59'
GROUP BY HOUR(date_created)
)
回答by DRapp
I would have to assume you would also want it by day too if spanning multiple days, otherwise a max() by an hour could give you something from a week ago with one hour vs three days ago another, and current day with yet another... That, all if you spanned outside your WHERE clause specifically limiting to your single day range. Its not by specific user you want, but whoever had the last activity for that hour... could be the same person, could be completely different every time. I'm tacking on the specific date as part of my group test just in case you ever wanted to span a date range, but you can take it out too...
如果跨越多天，我将不得不假设您也希望按天使用它，否则一个小时的 max() 可以为您提供一周前一小时与三天前另一小时以及当天另一小时的东西。 .. 那，所有如果你跨越你的 WHERE 子句特别限制你的单日范围。它不是你想要的特定用户，而是那个小时最后一次活动的人......可能是同一个人，每次都可能完全不同。我将特定日期作为小组测试的一部分，以防万一您想跨越一个日期范围，但您也可以将其删除...
select STRAIGHT_JOIN
ui.userid,
ui.username,
ui.date_created
from
( select
date( date_created ),
hour( date_created ),
max( date_created ) as LastPerHour
from
user_accounts
where
date( date_created ) = '2009-10-27'
group by
date( date_created),
hour( date_created )) PreQuery
join user_accounts ui
on PreQuery.LastPerHour = ui.date_created
Again, I've included date as a grouping too if you wanted to span multiple days, just make sure your table has an index on date_created by itself... or at least in the first position of the index.
同样，如果您想跨越多天，我也将日期作为一个分组包括在内，只需确保您的表在 date_created 上有一个单独的索引......或者至少在索引的第一个位置。
回答by Tim Seed
I use this solution
我使用这个解决方案
select date(PROCESS_DATE),hour(PROCESS_DATE),EVENT,COUNT(*) from statistics group by EVENT,date(PROCESS_DATE),time(PROCESS_TIME) order by 1 desc, 2 desc limit 1;
Hope this assists someone.
希望这有助于某人。
回答by inquam
Do you mean one hour from NOW or latest full hour? If it's latest full hour something like this might work?
你的意思是从现在起一小时还是最近一小时？如果是最近的一小时，这样的事情可能会奏效吗？
SELECT userid, username, date_created
FROM user_accounts
WHERE HOUR(date_created) = (SELECT HOUR(date_created) FROM user_accounts ORDER BY date_created DESC LIMIT 1);
EDIT: Ahhh, now I think I get what you want... The last added entry on every given hour between your date range?
编辑：啊，现在我想我得到了你想要的......在你的日期范围之间的每个给定小时最后添加的条目？
If so then Codler's query is what you want.
如果是这样，那么 Codler 的查询就是您想要的。
