货到付款网站制作全国各城市感染高峰进度查询
文章目录
- 1. SQL的执行顺序是什么?on和join谁先执行,为什么?on和where的区别?
- 2. 已知表user,字段id, date,求新用户的次日留存率
- 3. 已知表user,字段id,date,求每个日期新用户的次日留存率
- 4. 已知表a,字段id,log_date,求连续登录天数大于等于2的用户id
- 5. 已知表table,字段id,date,求连续三天或三天以上未登录的用户id
- 6. 订单表orders,字段有order_id,user_id,date。求22年1月每个用户单天订单最高次数和每个用户单天订单最高次数的日期。
- 7. 已知:商品上下架时间表log,商品id,上下架类型type(online:上架,offline:下架),时间log_time,输出每个商品id,上架时间online_time,下架时间offline_time。
- 8. 已知用户行为日志表tb_user_log,uid用户id,artical_id文章id,in_time观看开始时间,out_time观看结束时间。统计每篇文章同一时刻最大在看人数,如果同一时刻有进入也有离开时,先记录用户数增加再记录减少。结果按最大人数降序。
- 9. 有一张销量信息表table1,字段有类目cate,店铺id,销售额sales。求出每个类目销售额排名前40%的店铺信息。(输出字段cate,id,sales)
1. SQL的执行顺序是什么?on和join谁先执行,为什么?on和where的区别?
- 执行顺序:
from→on→join→where→group by→having→select→distinct→order by→limit
为什么on
在join
前面执行?因为要先经过筛选,才能通过连接把外部行加进来。
on
和where
的区别:on
筛选之后,可以通过外连接把移除的行再次添加进来,而where
是最终过滤的作用。
举个例子 有一个学生表:班级、姓名;一个成绩表:姓名、成绩。我们需要返回X
班的所有学生的成绩,但是班上有人缺考,也就是成绩表里没有姓名,我们先用on
得到的就是有考试成绩的名字,通过外连接,我们就可以得到全班人的名字以及成绩。
2. 已知表user,字段id, date,求新用户的次日留存率
select a.date, count(distinct b.id)/count(distinct a.id) rate
from (select id,min(date) date from user group by id) a #求出用户的第一次登录时间
left join user b on a.id=b.id
and datediff(b.date,a.date)=1
group by a.date
解析:首先需要找到每一个新用户的登录时间,与第二天进行对比,distinct
是为了排除用户同一天多次登录的情况。(本题未考虑新增用户为0
的情况,实际需不需要考虑由具体情况定)
3. 已知表user,字段id,date,求每个日期新用户的次日留存率
select c.date,ifnull(d.rate,0) rate from
(select distinct(date) date from user) c
left join
(select a.date,count(distinct b.id)/count(distinct a.id) rate
from (select id,min(date) date from user group by id) a
# 求出用户的第一次登录时间
left join user b on a.id=b.id and datediff(b.date,a.date)=1
group by a.date) d
on c.date=d.date
d表内容和上一题一样,但我们需要考虑某天新增用户为0的情况,并输出新增用户的次日留存率0以及日期。通过c表进行左连接之后,日期为连接条件,那么我们就获得了每个日期以及对应的新用户次日留存率。因为d表中不含新增用户为0的留存率情况,因此左连接之后,这种情况就会变成null
值,所以通过ifnull
函数转化成0,这样就输出了每个日期新用户的次日留存率情况了。
4. 已知表a,字段id,log_date,求连续登录天数大于等于2的用户id
SELECT distinct id
FROM (select *,dense_rank() over (PARTITION by id ORDER BY log_date)
dk from a) t
GROUP BY id,DATE_SUB(log_date,INTERVAL dk day)
HAVING count(DISTINCT(log_date))>=2
解析:首先需要给用户的登录日期排序,只能用dense_rank
是因为用户同一天可能会登录多次。如果一个用户连续登录或者同一天多次登录,那么他的登录日期与序号的差值是相同的,所以可以根据id
,差值分组,便可以求得用户的连续登录和同一天多次登录的累计天数。 having
里面的distinct
是为了排除用户同一天多次登录的情况,select
里面的distinct
是为了排除一个用户多次连续登录,比如:连续登录两天,第三天未登录,接着又连续登录两天。
5. 已知表table,字段id,date,求连续三天或三天以上未登录的用户id
select distinct id
from (select *,lag(date) over(partition by id order by date) date1 from table)a
where datediff(date,date1)>=4
lag
函数是为了获得用户上一次登录的时间,利用where
与本次登录时间进行比较。如果一个用户要连续三天没登录,因此他下一次登录就应该在第四天,所以是大于等于4。
6. 订单表orders,字段有order_id,user_id,date。求22年1月每个用户单天订单最高次数和每个用户单天订单最高次数的日期。
select user_id,date,cnt
from
(select user_id,date,cnt,rank() over(partition by user_id order by cnt desc) rk
from (select user_id,date,count(order_id) cnt from orders where date_format(date,'%Y%m')=202201 group by user_id,date)a
)b
where rk=1
解析:每个用户每天可能下单多次,因此需要先统计每个用户每天的订单量也就是a
表。然后利用窗口函数对用户id
分组对订单量倒序排序,筛选出排名为1
的数据就可以了。如果题目不要求求出每个用户单天订单最高次数的日期,那么可以直接对a
表用户id
分组,使用max
函数再得出结果了。
7. 已知:商品上下架时间表log,商品id,上下架类型type(online:上架,offline:下架),时间log_time,输出每个商品id,上架时间online_time,下架时间offline_time。
select a.id,a.log_time online_time,b.log_time offline_time from (select id,log_time,row_number()over(partition by id order by log_time) rn1 from log where type='online')a
left join (select id,log_time,row_number()over(partition by id order by log_time) rn2 from log where type='offline')b
on a.id=b.id and a.rn1=b.rn2
解析:同一个商品有多个上下架时间,因此不能通过case when
进行行转列。首先需要对同一个商品所有的上下架时间进行排序,根据序号让每一个上架时间匹配对应的下架时间。用left join
是因为会存在商品有上架时间,没有下架时间这种情况。
8. 已知用户行为日志表tb_user_log,uid用户id,artical_id文章id,in_time观看开始时间,out_time观看结束时间。统计每篇文章同一时刻最大在看人数,如果同一时刻有进入也有离开时,先记录用户数增加再记录减少。结果按最大人数降序。
select artical_id,max(uv) max_uv from
(select artical_id,sum(num) over(partition by artical_id order by dt,num desc) uv from (select artical_id,in_time dt,1 num from tb_user_logunion all select artical_id,out_time dt,-1 num from tb_user_log) a
) b
group by artical_id
order by max_uv desc
首先需要想到的是,做这种类似某一时刻最大在线人数的题目,都可以转化成当用户开始观看时用户数+1
,当用户结束观看时用户数-1
,然后通过sum
窗口函数累计求和。如果能想到这一点,这题就很简单了。所以刚开始需要将用户观看开始、观看结束的时间表取出来,再合并变成表a
。通过sum
窗口函数对每个文章进行分组根据时间顺序排序、num
倒序排序累计求和变成表b
,用sum
函数是因为需要求出每个时刻的累计在线人数。num
倒序排序是因为同一时刻有进入也有离开时,先记录增加,再记录减少。最后对b
表通过文章id
分组,max
函数就可以求到最大在看人数了,结果按降序排序,加个order by max_uv desc
就行了。
9. 有一张销量信息表table1,字段有类目cate,店铺id,销售额sales。求出每个类目销售额排名前40%的店铺信息。(输出字段cate,id,sales)
select cate,id,sales from (
select cate,id,sales,row_number()
over(partition by cate order by sales desc) rn,
count(id) over(partition by cate) ct from table1
) a
where rn/ct<=0.4
解析:首先先对每个类目的销售额进行倒序排序,也就是row_number()
窗口函数。count(id) over(partition by cate)
是求出每个类目中的店铺个数,然后从a
表中查询设置where
条件前40%
就可以了。