sql - 按时间间隔对数据进行分组

Question

我想按时间间隔对表中的数据进行分组。该表保存了订单信息，包括用户 ID、商品名称、型号、数量、事件日期等。现在我想按时间间隔对这些数据进行分组。时间间隔可以是 5 分钟、10 分钟、15 分钟等。此外，查询应仅返回在 5 分钟间隔内多次下订单的用户(所有列数据)。是否可以通过一个 SQL 查询来实现这一目标？我使用 Oracle。

谢谢

编辑:

示例数据

**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

我期望得到:

**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 

Answer 1

是的。想必您也希望将结果视为日期时间值。这需要一些日期时间算术。基本上，获取自午夜以来的分钟数，除以分钟数，然后再次相乘(向下舍入)。然后添加回午夜时间:

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

要按时间间隔分组，您可以使用:

      select interval, count(*)
      from (select trunc(orderdate)+floor(((orderdate - trunc(orderdate))*24*60)/10)*10 as interval, t.*
            from t
           ) t
      group by interval

在这些查询中，“10”代表任意分钟数。请注意，这些是从午夜开始计算的，因此像 17 这样的值始终从当天的前 17 分钟开始。

间隔的定义是日期的算术表达式。

          trunc(orderdate)+floor(((orderdate - trunc(orderdate))*24*60)/10)*10 作为间隔，

第一部分 trunc(orderdate) 是用于删除日期时间部分的 Oracle 语法。这会将日期移至当天开始时的午夜。

表达式orderdate - trunc(orderdate)计算自午夜以来的天数——这是一天的小数部分。因此，0.25 就是上午 6:00。*24*60 将其转换为分钟。因此，0.25 变为 0.25*60*24 = 360——自午夜以来的分钟数。

然后表达式 floor(x/y)*y 只是将任何值“截断”为 y 的较小倍数。因此，floor(118/10) 为 11，11*10 为 110。换句话说，这将映射 a*y 和 (a+1)*y 之间的所有值(最多不包括)为相同的值，a*y。

考虑一下实践中的表达，2013年1月1日上午6:08:

`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

当这个时间添加到“分钟”时，时间又变成了早上 6:00。