Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.