An interesting SQL query (query 7 consecutive days to log in)

An interesting SQL query

A friend has such a SQL query requirement:
There is a login table (Tmp_test) that contains the user ID (UID) and logon time (Login_time). The table structure is as follows:

*************************** 1. Row***************************Field:uidtype:int(Ten) unsignedNull: NOKey: MULDefault:NULLExtra:*************************** 2. Row***************************Field:login_timetype:timestampNull: NOKey: MULDefault:0000-xx-xx xx:xx:xxExtra:

Ask how to check out all users who have logged in for a period of time (for example: 2012-1-1 to 2012-1-17) for 7 consecutive days.

When I write this SQL, I find some interesting things, maybe it's helpful for everyone to write SQL, so record it.

-Basic Idea Loop Join
The first thought is a method similar to loop join:
A. Remove every record from 2012-1-1 to 2012-1-11 .
B. For each record taken, check the table for the next 6 days of the user's records.
If the total is 6 records, the condition for 7 consecutive days is met

-Range Join
The idea of Loop join can be achieved by a join statement. Call it a range Join. When joins are usually used, the
Equivalent join. If the value of the Join column is unique, then one record of the left table corresponds to the right table. and Range Join
, the row of data in the left table corresponds to all records in one range of the right table.

The SQL statement is:

SELECT DISTINCTT.uid fromTmp_test asTJOINTmp_test asT1 onDate (T.login_time)+ 1 <=Date (T1.login_time) andDate (t.login_time)+ 7 >Date (T1.login_time) andT.uid=T1.uidWHERET.login_timebetween’ --1-1 xx:xx:xx′ and’ --1- One  at: -: -′ andT1.login_time>=’ --1-2′ andT.login_time<’ --1- -(Can be removed)

-COUNT (DISTINCT)
"Calculated for 7 consecutive days" can be done through group by grouping and Count (). Because a user may have multiple logons within 1 days,
This needs to be used (COUNT DISTINCT). The SQL statement is:

GROUP  by T.login_time, T.uid  having COUNT (DISTINCT Date (t1.login_time)) = 6

-Bit_or
Considering that the distinct operation requires caching data, it is possible to use bit logic (which may be more efficient). Because for seven consecutive days,
The difference from the first day is, 1,2,3,4,5,6,7. can be represented by the 1-7bit bit respectively. According to this feature, it is possible to group
For each row or (|) Operation. If the last value equals B ' 1111110′ (6 1). That's 7 days in a row. This method can
Avoid distinc operations. I had no idea that MySQL really had a bit-action aggregation function. Bit_or is what we're going to use.

Http://www.oschina.net/question/28_41179?sort=default&p=1

An interesting SQL query (query 7 consecutive days to log in)