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

This article mainly shows you "SQL how to query continuously logged-in users", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "SQL how to query continuously logged-in users" this article.

Take 3 consecutive days as an example, use the tool: MySQL.

1. Create the SQL table:

Create table if not exists orde (id varchar (10), date datetime,orders varchar (10)); insert into orde values ('1x, '2019); insert into orde values (' 1, '2019); insert into orde values (' 1, '20119); insert into orde values (' 1, '20119) Insert into orde values ('1hammer,' 20119); insert into orde values ('1, '20119); insert into orde values (' 4', '20119); insert into orde values (' 2mm, '20119) Insert into orde values ('3hammer,' 20119); insert into orde values ('4yen,' 20119); insert into orde values ('5yen,' 20119); insert into orde values ('6here,' 20119); insert into orde values ('7march,' 20119); insert into orde values ('858,' 20119) Insert into orde values ('999,' 20119); insert into orde values ('9, '20119); insert into orde values (' 9, '20119) Insert into orde values ('9', '2019); insert into orde values (' 9', '2019)

View the data sheet:

two。 Use the row_number () over () sort function to calculate the ranking of each id, SQL as follows:

Select *, row_number () over (partition by id order by date) 'rank'from ordewhere orders is not NULL

View the data sheet:

3. Subtract the date date field from the rank ranking field, SQL as follows:

Select *, DATE_SUB (a.dategery interval a.rank day) 'date_sub'from (select *, row_number () over (partition by id order by date)' rank'from ordewhere orders is not NULL) a

View the data:

4. According to id and date grouping and calculating the number of grouped (count), the earliest login time and the latest login time, the SQL is as follows:

Select b.idjimmin (date) 'start_time',max (date)' end_time',count (*) 'date_count'from (select *, DATE_SUB (a.datejure interval a.rank day)' date_sub'from (select *, row_number () over (partition by id order by date) 'rank'from ordewhere orders is not NULL) a) bgroup by b.datekeeper subjournal count (*) > = 3

View the data:

The above is all the contents of the article "how to query continuously logged-in users in SQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.