Oracle 5-minute or 30-minute segmentation method

In the recent project, there was a customer requirement for data at all time points of the day, divided into a total number of users every 5 minutes.

The data scenario is:

The online time data of all users in a game (of course, a simple sum, there may be duplicate data). However, the key point is the method used in Oracle SQL to segment according to a certain time interval. Specific examples of 5-minute segmentation are as follows:

SELECT tt.reasonContent,to_char (tt.day_id,'hh34:mi') daytime, tt.num FROM (

SELECT ll.day_id,ll.reasonContent,COUNT (*) num FROM (

SELECT d.daylightidddd.logtimejue dd.groupnameddd.useriddiddd.reasonContent FROM (

SELECT i.logtime WHEN dic.key_id IS NULL THEN i.gameidreparing i.GroupnameRecence i.useridrect i.reason case case 'other reasons' ELSE dic.key_value END reasonContent FROM

Table i LEFT JOIN

TableDic dic ON i.reason=dic.key_id) dd

(SELECT TO_DATE ('2014-09-20 00 ROWNUM) DAY_ID FROM DUAL) + (1 / 24 / 60 * 30 * (ROWNUM-1))

CONNECT BY ROWNUM