Real-time online query sql

Scenario: the records processed in the library are the number of users who were online at the time, a unique UUID that could indicate that this was an online user at the time. Now ask for a query

Real-time online users, this "real-time" is definitely the most recent time interval. We take this time interval of 5 minutes.

The data is also 5 minutes into the library. More precisely, our "live data" is the most recent complete 5-minute interval. Why should we emphasize integrity? Because the storage

Time does not synchronize with the refresh time, it is possible that the page is refreshing the data at this time, the last time has not been fully storage. Suppose the last 5 minutes end5min

And the second to last 5 minutes last5min, according to this situation, we will take the last5min and End5min each part of the data, and this time the situation should be to take

The data that is last5min the stage.

How do we meet our recent "complete" 5 minute data?

The records for the early morning to the current time are grouped in groups of 5 minutes.

24*60/5=288

In other words: a day can be divided into 288 5-minute segments.

Assuming that the morning time is date, the Time field for each record in the storage is logtime.

Logtime-to_date (date, ' Yyyy-mm-dd ')

This allows the storage time distance from the early hours of the day interval. This time interval is in days. This decimal can be seen as logtime distance from the early morning.

The proportion of time interval in this day. The logtime can then be grouped according to this interval. Because the grouping of the day is 288, if the LogTime

The proportion of this day is multiplied by 288 of the total number of subgroups, and the logtime can be attributed to the corresponding groupings.

Group_num=trunc ((logtime-to_date (date, ' Yyyy-mm-dd ')) *1440/5)

Figure out the number of online users per group: count (DISTINCT uuid)

In addition, we will set a timer, starting at 0 points and refreshing the page every 5 minutes.

__1__2__3_

Assuming that we refresh the data at point 1, the time point 1 is recorded on the page, and the next time the query is grouped, it will be queried by point 1 (lastdate) as the query criteria, and the corresponding grouping is found. However, the intermediate table we get is the group number. Then we have to convert the corresponding time according to the group number:

Logtime=group_num*5/1440+to_date (date, ' Yyyy-mm-dd ')

So:

SELECT (To_date (DATE, ' yyyy-mm-dd hh24:mi ') + Group_num * 5/(each)) LogTime
So we can query the condition by the point of time 1.

The specific SQL statements are as follows:

SELECTNum asNownum from(SELECT(To_date (DATE,'YYYY-MM-DD Hh24:mi')+Group_num* 5 /( - *  -)) Logtime,num from(SELECTTRUNC ((logtime-To_date (DATE,'YYYY-MM-DD'))* 1440 / 5) Group_num,Count(DISTINCTUUID) NUM fromOnline_countWHERELogTimebetweenTo_date (DATE,'YYYY-MM-DD Hh24:mi:ss') andSysdateGROUP  byTRUNC ((logtime-To_date (DATE,'YYYY-MM-DD'))* 1440 / 5)ORDER  byGroup_num) T)WHERELogTime=To_date (Lastdate,'YYYY-MM-DD Hh24:mi:ss')

Real-time online query sql