成都房地产,seo案例分析方案,沈阳网站制作的公司,前端开发是什么专业文章目录 1. SQL的执行顺序是什么#xff1f;on和join谁先执行#xff0c;为什么#xff1f;on和where的区别#xff1f;2. 已知表user,字段id, date#xff0c;求新用户的次日留存率3. 已知表user#xff0c;字段id#xff0c;date#xff0c;求每个日期新用户的次日留… 文章目录 1. SQL的执行顺序是什么on和join谁先执行为什么on和where的区别2. 已知表user,字段id, date求新用户的次日留存率3. 已知表user字段iddate求每个日期新用户的次日留存率4. 已知表a字段idlog_date求连续登录天数大于等于2的用户id5. 已知表table字段iddate求连续三天或三天以上未登录的用户id6. 订单表orders字段有order_iduser_iddate。求22年1月每个用户单天订单最高次数和每个用户单天订单最高次数的日期。7. 已知商品上下架时间表log商品id上下架类型typeonline上架offline下架时间log_time输出每个商品id上架时间online_time下架时间offline_time。8. 已知用户行为日志表tb_user_loguid用户idartical_id文章idin_time观看开始时间out_time观看结束时间。统计每篇文章同一时刻最大在看人数如果同一时刻有进入也有离开时先记录用户数增加再记录减少。结果按最大人数降序。9. 有一张销量信息表table1字段有类目cate店铺id销售额sales。求出每个类目销售额排名前40%的店铺信息。输出字段cateidsales 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.idb.id
and datediff(b.date,a.date)1
group by a.date解析首先需要找到每一个新用户的登录时间与第二天进行对比distinct是为了排除用户同一天多次登录的情况。本题未考虑新增用户为0的情况实际需不需要考虑由具体情况定
3. 已知表user字段iddate求每个日期新用户的次日留存率
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.idb.id and datediff(b.date,a.date)1
group by a.date) d
on c.dated.dated表内容和上一题一样但我们需要考虑某天新增用户为0的情况并输出新增用户的次日留存率0以及日期。通过c表进行左连接之后日期为连接条件那么我们就获得了每个日期以及对应的新用户次日留存率。因为d表中不含新增用户为0的留存率情况因此左连接之后这种情况就会变成null值所以通过ifnull函数转化成0这样就输出了每个日期新用户的次日留存率情况了。
4. 已知表a字段idlog_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字段iddate求连续三天或三天以上未登录的用户id
select distinct id
from (select *,lag(date) over(partition by id order by date) date1 from table)a
where datediff(date,date1)4lag函数是为了获得用户上一次登录的时间利用where与本次登录时间进行比较。如果一个用户要连续三天没登录因此他下一次登录就应该在第四天所以是大于等于4。
6. 订单表orders字段有order_iduser_iddate。求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 rk1解析每个用户每天可能下单多次因此需要先统计每个用户每天的订单量也就是a表。然后利用窗口函数对用户id分组对订单量倒序排序筛选出排名为1的数据就可以了。如果题目不要求求出每个用户单天订单最高次数的日期那么可以直接对a表用户id分组,使用max函数再得出结果了。
7. 已知商品上下架时间表log商品id上下架类型typeonline上架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 typeonline)a
left join (select id,log_time,row_number()over(partition by id order by log_time) rn2 from log where typeoffline)b
on a.idb.id and a.rn1b.rn2解析同一个商品有多个上下架时间,因此不能通过case when进行行转列。首先需要对同一个商品所有的上下架时间进行排序根据序号让每一个上架时间匹配对应的下架时间。用left join是因为会存在商品有上架时间没有下架时间这种情况。
8. 已知用户行为日志表tb_user_loguid用户idartical_id文章idin_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%的店铺信息。输出字段cateidsales
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/ct0.4解析首先先对每个类目的销售额进行倒序排序也就是row_number()窗口函数。count(id) overpartition by cate是求出每个类目中的店铺个数然后从a表中查询设置where条件前40%就可以了。
