Mysql, a log table, how to count the users who have only one day record?

there is such a log table , which stores the records of user clicks, and there may be multiple records in one day. Now I want to count out that users who have only one day click records, users who have two days click records, users who have three days click records, , and so on, 4 days, 5 days. How should I write this sql?
related fields in the table:

< table > < tr > < td > id (PK,AI) < / td > < td > user_id < / td > < td > click_time (click time: Y-m-d H:i:s) < / td > < / tr > < / table >
Mysql
Mar.18,2021

Let's talk about the results first. After
SELECT TO_DAYS (MAX (time))-TO_DAYS (MIN (time)), USER FROM test GROUP BY USER
is executed, the result is as follows:
0 Wang
1 Li
2
query is grouped according to the user, and then the difference between the maximum time and the minimum time of the user is calculated. For example, the records of 0 and Wang indicate that all the records of the user Wang are on the same day.

-Segmentation Line-
after the hint of @ Xing Aiming, I realized that I was wrong in the first place.
after rethinking, you only need to write a sentence of code.
create a new table by testing yourself. The structure of the test, table is

sql:

.

only one day is easier to write.
use the maximum click time of the user-the minimum click time of the user. If not more than a day. There's only one day.
other cases
take the minimum time, add it by the number of days, if the maximum time is exceeded. Calculate the number of cycles

come on, logically, first set the click time to a certain range and then re-user_id (distinct), or group group by. This is the first step, and then use the count function to count the total number of entries, that is, how many days there are, and then group them according to this result. There is an one-day, two-day, three-day arrangement. I hope if you don't know the specific sentences, you have to learn them first. I'll write it down below for you. 

select a.datenum,group_concat(a.user_id,',') from (select count(*) as datenum,user_id from table group by to_days(click_time),user_id ) as a group by a.datenum;

ps:, because there is no specific data test, whether it can be used or not, you still have to test it (if you want to write it with me). That's the logic.

the description of the problem is rather vague. @ Mr. Chen er has given a general idea, @ liuxy to see if it is the result you want.

Previous: Processing of websocket in vue

Next: Linux successfully created uwsgi soft connection, but typed uwsgi Times on the command line. What should I do?

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
© 2021 CodesHelper
Advertising Contact us About us
Menu