Geek asks and answers

Find a user who repeats and matches a condition in MySQL

The table has a date, user, user_location, for example:

20120417 userA location1
20120417  userA location2
20120417  userB location3
20120417  userC location2
20120417  userA location1
20120417  userB location2
20120417 userA location3
20120416 userA location1
20120416  userA location2
20120416  userB location3
20120416  userC location2
20120416  userA location1
20120416  userB location2
20120415 userA location3
20120415 userA location1
20120415  userA location2
20120415  userB location3
20120415  userC location2
20120415  userA location1
20120415  userB location2
20120415 userA location3
20120414 ....
....

I tried some stupid methods to find users that match 3 days in a row, every day should be at least 2 different places.

Is it possible to use a SQL query for this, or should I try something like a php script?

+3
source share
1 answer

I think it works. With MSSQL, you do not need to duplicate views.

SELECT l1.user,l1.thedate, COUNT(*) CNT FROM
( /* Derived table1: user,thedate,loc */
SELECT st.user,st.thedate,COUNT(*) locs FROM sotest st
GROUP BY st.user,st.thedate 
HAVING( COUNT(DISTINCT st.location) > 1)
) l1
JOIN
( /* Derived table2: user,thedate,loc */
SELECT st.user,st.thedate,COUNT(*) locs FROM sotest st
GROUP BY st.user,st.thedate 
HAVING( COUNT(DISTINCT st.location) > 1)
) l2 ON l2.user = l1.user and DATEDIFF(l2.thedate , l1.thedate) = 1
JOIN
( /* Derived table3: user,thedate,loc */
SELECT st.user,st.thedate,COUNT(*) locs FROM sotest st
GROUP BY st.user,st.thedate 
HAVING( COUNT(DISTINCT st.location) > 1)
) l3  ON l3.user = l2.user and DATEDIFF(l3.thedate , l2.thedate) = 1
GROUP BY l1.user,l1.thedate

Each of the views defines the days when the user has 2 or more locations.

0
source

More articles:


All Articles