oracle 按时间间隔分组数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14781069/
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
Grouping data by time interval
提问by Mike
I want to group the data in a table by time interval. The table keeps the order info that includes the userid, item name, model, quantity, activity date, etc. Now I want to group this data by time intervals. The time interval could be anything like 5 min, 10 min, 15, etc. Also, the query should return only those users (all the column data) who made orders more than once within that 5 minutes interval. Is it possible to achieve this in one SQL query? I use Oracle.
我想按时间间隔对表中的数据进行分组。该表保留订单信息，包括用户 ID、项目名称、型号、数量、活动日期等。现在我想按时间间隔对这些数据进行分组。时间间隔可以是 5 分钟、10 分钟、15 分钟等。此外，查询应仅返回在该 5 分钟间隔内多次下订单的用户（所有列数据）。是否可以在一个 SQL 查询中实现这一目标？我使用甲骨文。
Thank you
谢谢
Edit:
编辑：
Sample data
样本数据
**userid item name model quantity order date**
abc calculator cdm83ss 1 02-FEB-2013 09:20:13
abc alarm clock actp001 1 02-FEB-2013 09:26:22
yyy iPhone iP4 1 02-FEB-2013 09:28:14
abc alarm clock actz321 2 02-FEB-2013 09:30:00
zzz backpack bp344tk 1 04-FEB-2013 13:15:00
zzz backpack bp234zz 2 04-FEB-2013 13:19:32
zzz camera cm234 1 04-FEB-2013 13:20:22
ttt tv fs45yup 1 04-FEB-2013 13:28:19
I expect to get:
我希望得到：
**userid item name model quantity order date**
abc calculator cdm83ss 1 02-FEB-2013 09:20:13
abc alarm clock actp001 1 02-FEB-2013 09:26:22
abc alarm clock actz321 2 02-FEB-2013 09:30:00
zzz backpack bp344tk 1 04-FEB-2013 13:15:00
zzz backpack bp234zz 2 04-FEB-2013 13:19:32
zzz camera cm234 1 04-FEB-2013 13:20:22
回答by Gordon Linoff
Yes. Presumably you want to see the result as a datetime value as well. This requires some datetime arithmetic. Basically, getting the number of minutes since midnight, dividing by the number of minutes and then multiplying again (to round down). Then adding back in the time at midnight:
是的。大概您也希望将结果视为日期时间值。这需要一些日期时间算法。基本上，获取自午夜以来的分钟数，除以分钟数，然后再次乘以（向下取整）。然后在午夜添加回时间：
select t.*
from (select t.*,
count(*) over (partition by userid, interval) as CntInInterval
from (select trunc(orderdate)+
(floor(((orderdate - trunc(orderdate))*24*60)/10)*10)/(24*60) as interval, t.*
from t
) t
) t
where cntInInterval > 1
To group by time intervals, you would use:
要按时间间隔分组，您可以使用：
select interval, count(*)
from (select trunc(orderdate)+floor(((orderdate - trunc(orderdate))*24*60)/10)*10 as interval, t.*
from t
) t
group by interval
In these queries, "10" stands for any number of minutes. Note that these are calculated since midnight, so a value like 17 always starts with the first 17 minutes of the day.
在这些查询中，“10”代表任意分钟数。请注意，这些是从午夜开始计算的，所以像 17 这样的值总是从一天的前 17 分钟开始。
The definition of interval is an arithmetic expression on dates.
间隔的定义是日期的算术表达式。
? ? ? ? ? trunc(orderdate)+floor(((orderdate - trunc(orderdate))*24*60)/10)*10 as interval,
? ? ? ? ? trunc(orderdate)+floor(((orderdate - trunc(orderdate))*24*60)/10)*10 作为间隔，
The first part, trunc(orderdate), is Oracle syntax for removing the time part of a date. This moves the date to midnight at the beginning of the day.
第一部分 ，trunc(orderdate)是用于删除日期时间部分的 Oracle 语法。这会将日期移至一天开始时的午夜。
The expression orderdate - trunc(orderdate)calculates the number of days since midnight -- this is a fractional part of one day. So, 0.25 would be 6:00 a.m. The *24*60converts this to minutes. So, 0.25 becomes 0.25*60*24 = 360 -- the number of minutes since midnight.
该表达式orderdate - trunc(orderdate)计算自午夜以来的天数——这是一天的小数部分。因此，0.25 将是 6:00 am 将其*24*60转换为分钟。因此，0.25 变为 0.25*60*24 = 360——自午夜以来的分钟数。
Then the expression floor(x/y)*ysimply "truncates" any value to the lower multiple of y. So, floor(118/10)is 11, and 11*10 is 110. In other words, this will map all values between a*y and (a+1)*y (up to not including) to the same value, a*y.
然后表达式floor(x/y)*y简单地将任何值“截断”为 y 的较低倍数。因此，floor(118/10)是 11，而 11*10 是 110。换句话说，这会将 a*y 和 (a+1)*y（最多不包括）之间的所有值映射到相同的值 a*y。
Consider the expression in practice, on 6:08 a.m. on 2013-01-01:
考虑实践中的表达，在 6:08 am on 2013-01-01：
`trunc(orderdate)` moves the date to midnight on 2013-01-01.
`orderdate - trunc(orderdate)` creates a number like 0.25.
`((orderdate - trunc(orderdate))*24*60)` produces the value 368
`floor(((orderdate - trunc(orderdate))*24*60)/10)*10` produces 360
`floor(((orderdate - trunc(orderdate))*24*60)/10)*10*(1/24*60)` produces 0.25
And when this is added to minight, the time turns into 6:00 a.m. again.
当这被添加到 minight 时，时间又变成了 6:00 am。
回答by Art
In case you need simpler way to get time interval - I cannot help you with anything else as i do not know your tables and data:
如果您需要更简单的方法来获得时间间隔 - 我无法帮助您做任何其他事情，因为我不知道您的表格和数据：
-- Time interval - every 15 min from midnight --
SELECT To_Char(trunc(SYSDATE) + (LEVEL/1440*15), 'HH24:MI') interval_15_min
FROM dual
CONNECT BY LEVEL <= 10 -- this is orbitraty
/
SQL>
INTERVAL_15_MIN
--------------
00:15
00:30
00:45
...
