Geek asks and answers

Grouping data by time interval

I want to group data in a table by time interval. The table contains order information, which includes user ID, item name, model, quantity, activity date, etc. Now I want to group this data by time intervals. The time interval can be about 5 minutes, 10 minutes, 15, etc. In addition, the request should return only those users (all column data) who placed orders more than once during this 5-minute interval. Is it possible to achieve this in a single SQL query? I am using Oracle.

thank

Edit:

Data examples

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

**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
+5
source share
2 answers

. , . datetime. , , , (). :

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.

, 6:08 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

, 6:00 .

+7

- , :

-- 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
...
+3

More articles:


All Articles