Shafizadeh Shafizadeh - 5 months ago 18
SQL Question

How can I grouping an unix time per day?

I have a table like this:

// requests
+----+----------+-------------+
| id | id_user | unix_time |
+----+----------+-------------+
| 1 | 2353 | 1339412843 |
| 2 | 2353 | 1339412864 |
| 3 | 5462 | 1339412894 |
| 4 | 3422 | 1339412899 |
| 5 | 3422 | 1339412906 |
| 6 | 2353 | 1339412906 |
| 7 | 7785 | 1339412951 |
| 8 | 2353 | 1339413640 |
| 9 | 5462 | 1339413621 |
| 10 | 5462 | 1339414490 |
| 11 | 2353 | 1339414923 |
| 12 | 2353 | 1339419901 |
| 13 | 8007 | 1339424860 |
| 14 | 7785 | 1339424822 |
| 15 | 2353 | 1339424902 |
+----+----------+-------------+


I want to grouping
unix_time
column based on separated days. Actually I'm trying to make this for an specific user:

enter image description here

As you see I need tow numbers for an user:


  • the number of all days which there is a foot print of the user into
    requests
    table

  • the number of biggest consecutive days



How can I do that?

Actually I can use
WHERE id_user = :id
to select user's rows. And I can calculate the number of days by
SUM()
. And by using
MAX()
I can calculate the biggest consecutive range. Just I need to grouping those unix times.

Answer

Please give it a try:

SELECT 
t.id_user,
COUNT(*) totalVisits,
MAX(t.max_cons) maxCons
FROM 
(SELECT
        id_user,
        @lastUnixTime AS lastUnixTimeOfuser,
        IF(@uid <> id_user, @currentMax := 1 , @currentMax),
        IF(@uid <> id_user, @lastUnixTime := 0, @lastUnixTime := @lastUnixTimeOfLastRecord),

        IF(@uid = id_user, 
                    IF((@lastUnixTime + 86400) >= utime, @currentMax := @currentMax + 1, @currentMax := 1), @lastUnixTime := 0),
        IF(@currentMax > @max, @max := @currentMax, @max ),
        IF(@uid <> id_user , @max := 1 ,@max),
        @uid := id_user,    
        @lastUnixTimeOfLastRecord := utime,
        @max AS max_cons
    FROM
        (
        SELECT 
            id_user,
            (unix_time DIV 86400) * 86400 AS utime
        FROM requests 
        GROUP BY id_user, utime ) dayWiseRequestTable ,
        (
            SELECT
                @uid := 0,
                @currentMax := 0,
                @max := 0,
                @lastUnixTime := 0,
                @lastUnixTimeOfLastRecord := 0
        ) vars

    ORDER BY id_user, utime) t
GROUP BY t.id_user;

SQL FIDDLE DEMO

Output:

The final output looks like below:

id_user Total_Visits    Maximum_Consecutive_Visits
2353        7                    2
3422        2                    2
5462        3                    2
7785        2                    1
8007        1                    1

EDIT:

In order to get output for a specific user you need to add a WHERE clause in the inner query.

Please check this

SQL FIDDLE

Comments