水善利万物而不争,处众人之所恶,故几于道💦
题目:
从用户登录明细表(user_login_detail)和订单信息表(order_info)中查询每个用户的注册日期(首次登录日期)、总登录次数以及其在2021年的登录次数、订单数和订单总额。
with a as(
select
user_id,
substring(login_ts,1,10) register_date,
row_number() over( partition by user_id order by login_ts) rn
from user_login_detail
), b as(
select user_id,register_date from a where rn=1
), c as(
select
user_id,count(login_ts) total_login_count
from user_login_detail group by user_id
), d as(
select
user_id,count( login_ts) login_count_2021
from user_login_detail where year(login_ts)='2021' group by user_id
), e as(
select
user_id,count( order_id) order_count_2021
from order_info where year( create_date)='2021' group by user_id
), f as(
select
user_id,sum(total_amount) order_amount_2021
from order_info where year(create_date)='2021' group by user_id
)
select
b.user_id,
register_date,
total_login_count,
login_count_2021,
order_count_2021,
order_amount_2021
from b join c on b.user_id=c.user_id
join d on c.user_id=d.user_id
join e on d.user_id=e.user_id
join f on e.user_id=f.user_id
上面这个sql可以简单优化下:
with a as(
select
user_id,
substring(login_ts,1,10) register_date,
row_number() over( partition by user_id order by login_ts) rn
from user_login_detail
), b as(
select user_id,register_date from a where rn=1
), c as(
select
user_id,
count(login_ts) total_login_count,
count( login_ts) login_count_2021
from user_login_detail group by user_id
), d as(
select
user_id,
count( order_id) order_count_2021,
sum(total_amount) order_amount_2021
from order_info where year( create_date)='2021' group by user_id
)
select
b.user_id,
register_date,
total_login_count,
login_count_2021,
order_count_2021,
order_amount_2021
from b join c on b.user_id=c.user_id
join d on c.user_id=d.user_id
总结:
我的思路就是简单的一列一列的查,然后来个连接......
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)