10.数据库练习
bt.user_visit_log | ||
visit_time(访问时间) | user_id(账号ID) | page_url(访问页量) |
2017-03-16 00:59:43 | 23564 | /ad/test1 |
2017-03-16 01:01:03 | 94535 | /ad/test2 |
bt.user_register_info | ||
user_id(账号ID) | user_name(账号名称) | reg_time(注册时间) |
23564 | 张三 | 2014-03-16 21:29:56 |
94535 | 李四 | 2014-12-12 21:45:56 |
(1)统计该网站每天新注册的用户数量
(2)统计账户名称为“王五”的账户在2017-03-16当天访问最多的页面
(3)统计出2017-03-01到2017-03-16每天访问量最多的ID号
(4)请随机抽取100个2017年新注册的账户
(5)定义指标网站的当月留存率=当月和上月均访问过网站用户数量/上月访问过网站的用户数量;统计2017年3月的留存率。
(1)Select substr(reg_time,1,10) as curr_date,
count(1) as daily_add_user_count
from bt_user_register_info
group by substr(reg_time,1,10);
(2)
select page_url
from
(select
page_url,
vsit_count,
rank() over(order by Visit_count) as rk
from
(select
a.page_url,
count(1) as visit_count
from bt_user_visit_log a
left join bt_user_register_info b on a.user_id=b.user_id
where b.user_name=’王五’
and substr(a.visit_time,1,10)=’2017-03-16’
group by a.page_url)c
)d where rk=1
(3)
select user_id
from
(Select
user_id,
visit_count,
rank() over(order by Visit_count) as rk
from
(select
a.user_id,
count(1) as visit_count
from bt_user_visit_log a
left join bt_user_register_info b on a.user_id=b.user_id
where substr(a.visit_time,1,10)>=’2017-03-01’
and substr(a.visit_time,1,10)<=’2017-03-16’
group by a.user_id)c
)d where rk=1
(4) select * from bt_user_register_info where subtr(reg_time,1,4)='2017'order by rand() limit 100;
(5) select count(1) as two_mon_usercount
from
(select
substr(a.visit_time,1,7) as month,
user_id
from bt_user_visit_log
where substr(a.visit_time,1,7)='2017-02'
group by substr(a.visit_time,1,7),user_id)a
join
(select
substr(a.visit_time,1,7) as month,
user_id
from bt_user_visit_log
where substr(a.visit_time,1,7)='2017-03'
group by substr(a.visit_time,1,7),user_id)b on (a.user_id=b.user_id)
select count(distinct user_id) as last_mon_user_count
from bt_user_visit_log
where substr(a.visit_time,1,7)='2017-02'