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

The wife company has a need like this:

Query the user return information of a game, when the user logs in for two consecutive days, it is determined to be 2 days, if the interval is one day, it is determined to be 3 days, and if the interval is 5 days, it is determined to be 7 days. The interval between user data is 14 days (fixed).

Prepare data

The database syntax is the same as mysql.

Create a table:

Create table user_login (u_id int,login_date timestamp default current_timestamp)

Insert data:

DELIMITER / / create procedure loop_insert () begindeclare days int;declare usrs int;declare mx int;declare I int;set days = 14 * set usrs = 30 * set mx = 500 * set I = 1 * floor ((RAND () * usrs)) values (RAND () * usrs)), subdate (sysdate (), (RAND () * (days+1); set I = I + 1 * *) = mxend repeat;end//call loop_insert ()

The first thing I thought of was to use group_concat,sql. It goes like this:

SELECT u_id, group_concat (distinct DATE_FORMAT (login_date,'% Y% m% d') order by DATE_FORMAT (login_date,'% Y% m% d') desc separator'-') AS yyyymmddFROM user_loginGROUP BY u_id

Later, thinking about the conversion of dates to INTEGER subtraction is not accurate (for example, across the moon), and this display does not solve the requirements.

All right, consider row swapping.

Row to column

Row transfer requires case when enumeration. Fortunately, the date is only 14 days, which can be done:

SELECT u_id, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180413' THEN login_date ELSE''END AS d20180413, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180414' THEN login_date ELSE''END AS d20180414 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180415' THEN login_date ELSE''END AS d20180415, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180416' THEN login_date ELSE''END AS d20180416 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180417' THEN login_date ELSE''END AS d20180417, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180418' THEN login_date ELSE''END AS d20180418 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180419' THEN login_date ELSE' 'END AS d20180419, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180420' THEN login_date ELSE''END AS d20180420 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180421' THEN login_date ELSE' 'END AS d20180421, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180422' THEN login_date ELSE' 'END AS d20180422 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180423' THEN login_date ELSE''END AS d20180423, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180424' THEN login_date ELSE' 'END AS d20180424 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180425' THEN login_date ELSE''END AS d20180425, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180426' THEN login_date ELSE''END AS d20180426 FROM user_login

Query results:

Merge by user

According to the user's merge date, remove the duplicates, and use max to guarantee a single piece of data. If some data is set to 1, countless data is set to 0:

SELECT u_id, CASE WHEN MAX (d20180413) =''THEN' 0' ELSE'1' END AS isZ20180413, CASE WHEN MAX (d20180414) =''THEN' 0' ELSE'1' END AS isZ20180414 CASE WHEN MAX (d20180415) =''THEN' 0' ELSE'1' END AS isZ20180415, CASE WHEN MAX (d20180416) =''THEN' 0' ELSE'1' END AS isZ20180416 CASE WHEN MAX (d20180417) =''THEN' 0' ELSE'1' END AS isZ20180417, CASE WHEN MAX (d20180418) =''THEN' 0' ELSE'1' END AS isZ20180418 CASE WHEN MAX (d20180419) =''THEN' 0' ELSE'1' END AS isZ20180419, CASE WHEN MAX (d20180420) =''THEN' 0' ELSE'1' END AS isZ20180420 CASE WHEN MAX (d20180421) =''THEN' 0' ELSE'1' END AS isZ20180421, CASE WHEN MAX (d20180422) =''THEN' 0' ELSE'1' END AS isZ20180422 CASE WHEN MAX (d20180423) =''THEN' 0' ELSE'1' END AS isZ20180423, CASE WHEN MAX (d20180424) =''THEN' 0' ELSE'1' END AS isZ20180424 CASE WHEN MAX (d20180425) = 'THEN' 0' ELSE'1' END AS isZ20180425, CASE WHEN MAX (d20180426) = 'THEN' 0' ELSE'1' END AS isZ20180426 FROM (SELECT u_id, CASE DATE_FORMAT (login_date) '% Y% m% d') WHEN' 20180413 'THEN login_date ELSE' 'END AS d20180413, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180414' THEN login_date ELSE''END AS d20180414, CASE DATE_FORMAT (login_date) '% Y% m% d') WHEN' 20180415 'THEN login_date ELSE' 'END AS d20180415, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180416' THEN login_date ELSE''END AS d20180416, CASE DATE_FORMAT (login_date) '% Y% m% d') WHEN' 20180417 'THEN login_date ELSE' 'END AS d20180417, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180418' THEN login_date ELSE''END AS d20180418, CASE DATE_FORMAT (login_date) '% Y% m% d') WHEN' 20180419 'THEN login_date ELSE' 'END AS d20180419, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180420' THEN login_date ELSE''END AS d20180420, CASE DATE_FORMAT (login_date) '% Y% m% d') WHEN' 20180421' THEN login_date ELSE''END AS d20180421, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180422' THEN login_date ELSE' 'END AS d20180422, CASE DATE_FORMAT (login_date) '% Y% m% d') WHEN' 20180423 'THEN login_date ELSE' 'END AS d20180423, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180424' THEN login_date ELSE' 'END AS d20180424, CASE DATE_FORMAT (login_date) '% Y% m% d') WHEN' 20180425 'THEN login_date ELSE' 'END AS d20180425, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180426' THEN login_date ELSE''END AS d20180426 FROM user_login) t0 GROUP BY u_id

Query results:

Column wrapping

Use group_concat:

SELECT u_id, CONCAT (isZ20180413, isZ20180414, isZ20180415, isZ20180416, isZ20180417, isZ20180418, isZ20180419, isZ20180420, isZ20180421, isZ20180422, isZ20180423, isZ20180424, isZ20180425, isZ20180426) AS sumIsZ FROM (SELECT u_id, CASE WHEN MAX (d20180413) = 'THEN' 0' ELSE'1' END AS isZ20180413 CASE WHEN MAX (d20180414) =''THEN' 0' ELSE'1' END AS isZ20180414, CASE WHEN MAX (d20180415) =''THEN' 0' ELSE'1' END AS isZ20180415 CASE WHEN MAX (d20180416) =''THEN' 0' ELSE'1' END AS isZ20180416, CASE WHEN MAX (d20180417) =''THEN' 0' ELSE'1' END AS isZ20180417 CASE WHEN MAX (d20180418) =''THEN' 0' ELSE'1' END AS isZ20180418, CASE WHEN MAX (d20180419) =''THEN' 0' ELSE'1' END AS isZ20180419 CASE WHEN MAX (d20180420) =''THEN' 0' ELSE'1' END AS isZ20180420, CASE WHEN MAX (d20180421) =''THEN' 0' ELSE'1' END AS isZ20180421 CASE WHEN MAX (d20180422) = 'THEN' 0' ELSE'1' END AS isZ20180422, CASE WHEN MAX (d20180423) =''THEN' 0' ELSE'1' END AS isZ20180423 CASE WHEN MAX (d20180424) =''THEN' 0' ELSE'1' END AS isZ20180424, CASE WHEN MAX (d20180425) =''THEN' 0' ELSE'1' END AS isZ20180425 CASE WHEN MAX (d20180426) =''THEN' 0' ELSE'1' END AS isZ20180426 FROM (SELECT u_id, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180413' THEN login_date ELSE''END AS d20180413 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180414' THEN login_date ELSE''END AS d20180414, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180415' THEN login_date ELSE''END AS d20180415 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180416' THEN login_date ELSE''END AS d20180416, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180417' THEN login_date ELSE''END AS d20180417 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180418' THEN login_date ELSE''END AS d20180418, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180419' THEN login_date ELSE' 'END AS d20180419 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180420' THEN login_date ELSE''END AS d20180420, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180421' THEN login_date ELSE' 'END AS d20180421 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180422' THEN login_date ELSE''END AS d20180422, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180423' THEN login_date ELSE''END AS d20180423 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180424' THEN login_date ELSE''END AS d20180424, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180425' THEN login_date ELSE' 'END AS d20180425 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180426' THEN login_date ELSE''END AS d20180426 FROM user_login) t0 GROUP BY u_id) T1

Query results:

Statistics: SELECT u_id, sumIsZ, CASE WHEN LOCATE ('11mm, sumIsZ) > 0 THEN 2 ELSE 0 END AS is2Back,/* 2 days reflux * / CASE WHEN LOCATE (' 101 cycles, sumIsZ) > 0 THEN 3 ELSE 0 END AS is3Back,/* 3 days reflux * / CASE WHEN LOCATE ('1001 cycles, sumIsZ) > 0 THEN 4 ELSE 0 END AS is4Back / * 4-day reflux * / CASE WHEN LOCATE ('10001 ELSE, sumIsZ) > 0 THEN 5 ELSE 0 END AS is5Back,/* 5-day reflux * / CASE WHEN LOCATE (' 100001 cycles, sumIsZ) > 0 THEN 6 ELSE 0 END AS is6Back,/* 6-day reflux * / CASE WHEN LOCATE ('1000001 cycle, sumIsZ) > 0 THEN 7 ELSE 0 END AS is7Back/*7 daily reflux * / FROM (SELECT u_id CONCAT (isZ20180413, isZ20180414, isZ20180415, isZ20180416, isZ20180417, isZ20180418, isZ20180419, isZ20180420, isZ20180421, isZ20180422, isZ20180423, isZ20180424, isZ20180425, isZ20180426) AS sumIsZ FROM (SELECT u_id, CASE WHEN MAX (d20180413) = 'THEN' 0' ELSE'1' END AS isZ20180413 CASE WHEN MAX (d20180414) =''THEN' 0' ELSE'1' END AS isZ20180414, CASE WHEN MAX (d20180415) =''THEN' 0' ELSE'1' END AS isZ20180415 CASE WHEN MAX (d20180416) =''THEN' 0' ELSE'1' END AS isZ20180416, CASE WHEN MAX (d20180417) =''THEN' 0' ELSE'1' END AS isZ20180417 CASE WHEN MAX (d20180418) =''THEN' 0' ELSE'1' END AS isZ20180418, CASE WHEN MAX (d20180419) =''THEN' 0' ELSE'1' END AS isZ20180419 CASE WHEN MAX (d20180420) =''THEN' 0' ELSE'1' END AS isZ20180420, CASE WHEN MAX (d20180421) =''THEN' 0' ELSE'1' END AS isZ20180421 CASE WHEN MAX (d20180422) = 'THEN' 0' ELSE'1' END AS isZ20180422, CASE WHEN MAX (d20180423) =''THEN' 0' ELSE'1' END AS isZ20180423 CASE WHEN MAX (d20180424) =''THEN' 0' ELSE'1' END AS isZ20180424, CASE WHEN MAX (d20180425) =''THEN' 0' ELSE'1' END AS isZ20180425 CASE WHEN MAX (d20180426) =''THEN' 0' ELSE'1' END AS isZ20180426 FROM (SELECT u_id, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180413' THEN login_date ELSE''END AS d20180413 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180414' THEN login_date ELSE''END AS d20180414, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180415' THEN login_date ELSE''END AS d20180415 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180416' THEN login_date ELSE''END AS d20180416, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180417' THEN login_date ELSE''END AS d20180417 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180418' THEN login_date ELSE''END AS d20180418, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180419' THEN login_date ELSE' 'END AS d20180419 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180420' THEN login_date ELSE''END AS d20180420, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180421' THEN login_date ELSE' 'END AS d20180421 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180422' THEN login_date ELSE''END AS d20180422, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180423' THEN login_date ELSE''END AS d20180423 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180424' THEN login_date ELSE''END AS d20180424, CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180425' THEN login_date ELSE' 'END AS d20180425 CASE DATE_FORMAT (login_date,'% Y% m% d') WHEN '20180426' THEN login_date ELSE''END AS d20180426 FROM user_login) t0 GROUP BY u_id) T1) T2

Query results:

Conclusion

Honey, just write according to this logic tomorrow, damn it. (23:50 on 26 April 2018)

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.