Oracle 5-minute or 30-minute segmentation method

In recent projects, there is a customer requirement that is divided into a total number of users every 5 minutes for all time points per day.

The data scenarios are:

A game in which all users online time data (of course simple summation, may have duplicate data). But in this focus is the method used by Oracle SQL to split at a certain time interval, the specific 5 minute split instance is as follows:

SELECT Tt.reasoncontent,to_char (tt.day_id, ' Hh24:mi ') daytime, tt.num from (
SELECT ll.day_id,ll.reasoncontent,count (*) num from (
SELECT D.day_id,dd.logtime,dd.groupname,dd.userid,dd.reasoncontent from (
SELECT I.logtime,i.gameid,i.groupname,i.userid,i.reason,case when dic.key_id was NULL then ' other reason ' else Dic.key_value END R Easoncontent from
Table I left JOIN
Tabledic dic on i.reason=dic.key_id) DD,
(SELECT to_date (' 2014-09-20 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') + (1/24/60 * * (ROWNUM-1)) day_id from DUAL
CONNECT by ROWNUM <= 288) D WHERE d.day_id-(1/24/60 *) <= DD. LogTime and d.day_id >= DD. LogTime) ll GROUP by Ll.day_id,ll.reasoncontent ORDER by ll.day_id) TT

Key code:

(SELECT to_date (' 2014-09-20 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') + (1/24/60 * 5 * (ROWNUM-1)) day_id from DUAL
CONNECT by ROWNUM <= 288) D WHERE d.day_id-(1/24/60 * 5) <= DD. LogTime and d.day_id >= DD. LogTime

Explanation: There are 24 hours a day and there are 288 5-minute points. Incremental segmentation is achieved by rownum combined with connect by.

If the increment is 5 minutes, then

(1/24/60 * 5 * (ROWNUM-1))

If the increment is 30 minutes, then

(1/24/60 * * (ROWNUM-1))

CONNECT by is a hierarchical query clause for Oracle, typically used for queries of tree or hierarchical result sets.

This article is from "Happy Chickadee" blog, please make sure to keep this source http://1723824.blog.51cto.com/1713824/1559407

Oracle 5-minute or 30-minute segmentation method