drop table if EXISTS Traffic;
create table Traffic(
user_id int,
activity varchar(50),
activity_date date
);
insert into Traffic values(1, 'login', '2019-05-01');
insert into Traffic values(1, 'homepage', '2019-05-01');
insert into Traffic values(1, 'logout', '2019-05-01');
insert into Traffic values(2, 'login', '2019-06-21');
insert into Traffic values(2, 'logout', '2019-06-21');
insert into Traffic values(3, 'login', '2019-01-01');
insert into Traffic values(3, 'jobs', '2019-01-01');
insert into Traffic values(3, 'logout', '2019-01-01');
insert into Traffic values(4, 'login', '2019-06-21');
insert into Traffic values(4, 'groups', '2019-06-21');
insert into Traffic values(4, 'logout', '2019-06-21');
insert into Traffic values(5, 'login', '2019-03-01');
insert into Traffic values(5, 'logout', '2019-03-01');
insert into Traffic values(5, 'login', '2019-06-21');
insert into Traffic values(5, 'logout', '2019-06-21');
- 先找出90天內每個用戶最開始登錄的時間
- 再分組統計各日期的用戶數
select login_date, count(1) user_count
from
(
select min(activity_date) as login_date, user_id
from Traffic
where activity = 'login'
GROUP BY user_id
HAVING DATEDIFF('2019-06-30',login_date) <= 90
) t
GROUP BY login_date
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/205693.html
標籤：java