题目需求
分别从登录明细表(user_login_detail)和配送信息表(delivery_info)中,根据用户登录时间和下单时间统计每个用户在每一天的登录次数和交易次数。
期望结果如下:
user_id (用户id) | login_date (登录时间) | login_count (登陆次数) | order_count (交易次数) |
---|---|---|---|
101 | 2021-09-21 | 1 | 0 |
101 | 2021-09-27 | 1 | 1 |
101 | 2021-09-28 | 1 | 1 |
101 | 2021-09-29 | 1 | 1 |
… | … | … | … |
需要用到的表:
用户登录明细表:user_login_detail
user_id(用户id) | ip_address(ip地址) | login_ts(登录时间) | logout_ts(登出时间) |
---|---|---|---|
101 | 180.149.130.161 | 2021-09-21 08:00:00 | 2021-09-27 08:30:00 |
102 | 120.245.11.2 | 2021-09-22 09:00:00 | 2021-09-27 09:30:00 |
103 | 27.184.97.3 | 2021-09-23 10:00:00 | 2021-09-27 10:30:00 |
配送信息表:delivery_info
delivery_id (运单 id ) | order_id (订单id) | user_id (用户 id ) | order_date (下单日期) | custom_date (期望配送日期) |
---|---|---|---|---|
1 | 1 | 101 | 2021-09-27 | 2021-09-29 |
2 | 2 | 101 | 2021-09-28 | 2021-09-28 |
3 | 3 | 101 | 2021-09-29 | 2021-09-30 |
实现一
select t1.user_id as user_id,
t1.login_date as login_date,
t1.login_count as login_count,
nvl(t2.order_count, 0) as order_count
from (
-- 1) 统计每个 user_id 在 每一天的 登录次数
select user_id,
date_format(login_ts, 'yyyy-MM-dd') as login_date,
count(*) as login_count
from user_login_detail
group by user_id, date_format(login_ts, 'yyyy-MM-dd')
) t1
left join
(
-- 2) 统计每个 user_id 在 每一天的 交易(下单)次数
select user_id,
order_date,
count(*) as order_count
from delivery_info
group by user_id, order_date
) t2
on t1.user_id = t2.user_id and t1.login_date = t2.order_date;
题目来源
http://practice.atguigu.cn/#/question/30/desc?qType=SQL