系列文章目录
第一章 HQL表创建
第二章 HQL初级练习1
第三章 HQL初级练习2
第四章 HQL中级练习1
第五章 HQL中级练习2
前言
这里HQL的中级练习,用于复习巩固sql常用的函数等使用方法,还有个人分析问题的思路(仅供参考)
一、习题练习
15. 找出销售额连续3天超过100的商品
- 分析表: 商品,销售额(商品销售数量*销售的价格), 时间 . order_detail.
- 分析题意: 拆解问题,销售额大于100的,连续三天时间(考虑使用等差数列的方式判断)
- 表结构(order_detail):
订单明细id-----------------------订单id ------------商品id---------下单日期---------------下单价格--------下单数量
代码演示
--第一步: 过滤商品销售额>100的,要进行分组按照商品和时间(原因就是一个商品再同一天可能多次卖出)
select sku_id, create_date, sum(sku_num*price)
from order_detail
group by sku_id, create_date having sum(sku_num*price)>100;
--第二步: 窗口函数排序,然后使用等差方法,
select t1.sku_id,
t1.create_date,
rank() over (partition by t1.sku_id order by t1.create_date) rk,
date_sub(t1.create_date,rank() over (partition by t1.sku_id order by t1.create_date)) ds,
t1.sum_price
from (
select sku_id, create_date, sum( sku_num * price ) sum_price
from order_detail
group by sku_id, create_date
having sum( sku_num * price ) > 100
)t1;
---第三步:等差数列的作用,如果ds再同一个id下相同的数量>=3则说明连续三天.
--这个有可能会出现重复的数据,可以嵌套一个子查询去重(出现重复的情况是这个商品有两个连续的三天销售额>100)
select sku_id
from (select t1.sku_id,
t1.create_date,
date_sub( t1.create_date , rank( ) over (partition by t1.sku_id order by t1.create_date) ) ds,
from (
select sku_id, create_date, sum( sku_num * price ) sum_price
from order_detail
group by sku_id, create_date
having sum( sku_num * price ) > 100
) t1)
t2 group by t2.sku_id,t2.ds ---注意这里的分组,id和等差时间相同,这两个条件满足,才能保证count(t2.ds)>=3有作用,
--如果只对sku_id分组,这里统计的是销售天数大于3的情况而不是连续的三天.
having count(t2.ds)>=3;
结果展示
16. 求出商品连续售卖的时间区间
- 需求:
连续售卖解释,例子:
2023年9月5号 卖出商品 6号也卖出了 7号也卖出了 -> 时间区间为9月5 ~ 9月7
2023年9月10号 卖出商品 11号没有卖出了 ->时间区间为9月10 ~ 9月10 - 分析表: 商品,销售时间,(order_detail)
- 分析题意: 和上述解题方式类似,使用等差数列的方式判断是否是连续的时间,找到这个连续时间的最大时间和最小时间.
代码演示
--第一步:窗口函数,排序,date_sub():产生等差数据,判断是否是连续数据 (注意一定要先进行去重操作,可能在一天会售卖多次的情况)
select t1.sku_id,
t1.create_date,
--如果去过重,可以不使用这个
row_number( ) over (partition by t1.sku_id order by t1.create_date) rn,
date_sub(t1.create_date,row_number( ) over (partition by t1.sku_id order by t1.create_date)) ds_date
from (
select sku_id,create_date from order_detail group by sku_id, create_date
)t1;
--第二步: 将上述表按照 sku_id, ds_date进行分组,然后那ds_date+min(rn)就是最开始的时间,ds_date+max(rn)最后时间
select t3.sku_id,
date_add( t3.ds_date , min( t3.rn ) ) min_date,
date_add( t3.ds_date , max( t3.rn ) ) max_date
from (
select t1.sku_id,
row_number( ) over (partition by t1.sku_id order by t1.create_date) rn,
date_sub( t1.create_date , row_number( ) over (partition by t1.sku_id order by t1.create_date) ) ds_date
from (
select sku_id, create_date
from order_detail
group by sku_id, create_date
) t1
) t3
group by t3.sku_id, t3.ds_date
order by t3.sku_id;
------------------方法二
select sku_id,
min(create_date) start_date,
max(create_date) end_date
from (select sku_id,
create_date,
row_number() over (partition by sku_id order by create_date) rk
from (select sku_id,
create_date
from order_detail
group by sku_id,
create_date) t1) t2
group by sku_id,
date_add(create_date, -rk) order by sku_id;
结果展示
17. 查看每件商品的售价涨幅情况
- 需求: 得到最近一次价格的涨幅情况，并按照涨幅升序排序.
- 分析表: 商品价格变更明细表（sku_price_modify_detail), 商品表sku_info
- 分析题意: 最近一次价格变动情况,可能存在有的商品价格没有发生变化这里就需要和商品上架时的价格进行关联, 对商品价格变动按照时间进行倒序排序,注意(三种情况,有多次修改,有1次修改,没有修改),这三种情况,要根据不同的情况进行分析,(1)有多次修改的只需要在价格变更表里进行处理,(2)修改1次的情况,就要和商品上架的价格进行关联,进而获得对应的价格变动,(3)没有修改商品价格变动=0,这里要找到对应的商品.
- 表结构:(sku_price_modify_detail)
商品id--------------------------------修改后的价格--------------------修改价格的时间
sku_info
商品id ------------------商品名称 ---------商品所属种类id --------------上架时间------------------上架时的价格
代码演示
--第一步: 使用窗口函数,获得最新时间的价格情况,会有两种情况,如果有多次变动数据都是正常的,如果只存在一次变动,那么使用lead得不到下一行的数据,这里会返回null,
--由于只要最新的变动价格,所以排序只要=1的即可.
select t1.sku_id,
t1.new_price,
t1.last_price
from (
select sku_id,
change_date,
new_price,
lead(new_price,1,null)over(partition by sku_id order by change_date desc) last_price,
row_number() over (partition by sku_id order by change_date desc ) rn --按照商品分区,时间倒序排序
from sku_price_modify_detail
) t1 where t1.rn=1;
--第二步: 左关联商品表,
select si.sku_id,
---如果这里的sku_id=null则表示,则没有修改价格,此时价格变动=0
if( t2.sku_id is null , 0 ,
if( t2.last_price is null , t2.new_price - si.price , t2.new_price - t2.last_price ) ) price_change
---第二个if,如果修改了一次,这里价格的值为null, 就拿修改一次的价格-上架时的价格, 如果不是null,表示修改了多次,这里就拿最新的价格-上一次变动的价格.
from sku_info si
left join --原因商品表有所有商品的价格
(
select t1.sku_id,
t1.new_price,
t1.last_price
from (
select sku_id,
new_price,
lead( new_price , 1 , null ) over (partition by sku_id order by change_date desc) last_price,
row_number( ) over (partition by sku_id order by change_date desc) rk
from sku_price_modify_detail
) t1
where t1.rk = 1
) t2
on
si.sku_id = t2.sku_id
order by price_change;
结果展示:
18. 统计活跃间隔对用户分级结果
- 需求: 活跃的定义: 登录过就说明活跃
忠实用户：近7天活跃且非新用户 -> 最后一次登录的时间 和 today时间 差值 <=7 最开始登录的时间>=7
新晋用户：近7天新增 ->最后一次登录的时间 到 最开始登录的时间<=7
沉睡用户：近7天未活跃但是在7天前活跃 ->最后一次登录的时间 和 today时间 差值 >7 and <30
流失用户：近30天未活跃但是在30天前活跃 -> 最后一次登录的时间 和 today时间 差值 >30
–假设这个表中的最大登录时间是数据中所有日期的最大值记作: today. - 分析表: 登录时间,用户,登录明细表 user_login_detail.
- 分析题意: 需要先知道用户的分级是如何限制的(如上已经做了解释), 知道了分级的关系就很容易解决,找到每个用户最开始登录的时间和最后登录的时间,然后只需要判断最晚登录的时间和最早的登录时间和假设的那个时间进行比较,在按照上述分类进行相应的判断.最后统计数量.
- 表结构:(user_login_detail)
用户id ----------------------登录ip ----------------------登录时间 -------------------------登出时间
代码演示
--第一步: 登录表的最大时间
select to_date(max(login_ts)) today
from user_login_detail;
--第二步:找到每个用户最早登录时间和最后登录时间min, max
select user_id,
min(to_date(login_ts)) first_login,
max(to_date(login_ts)) last_login
from user_login_detail
group by user_id;
--第三步: 由于today只有一个数据,这里使用笛卡尔连接,然后做标签, 注意t2.today - t1.last_login是日期,要转化为天,最后按照类型分组,求出相应的数量
select t3.type,
count( t3.user_id ) cn
from (
select t1.user_id,
case when datediff(t2.today , t1.last_login) <= 7 and datediff(t2.today , t1.first_login) > 7 then '忠实用户'
when datediff(t2.today , t1.last_login) > 7 and datediff(t2.today , t1.last_login) <= 30 then '沉睡用户'
when datediff(t2.today , t1.last_login) > 30 then '流失用户'
when datediff(t2.today , t1.first_login) <= 7 then '新晋用户'
end type
from (
select user_id,
min( to_date( login_ts ) ) first_login,
max( to_date( login_ts ) ) last_login
from user_login_detail
group by user_id
) t1
--这里使用了笛卡尔集,关联数据
join (select to_date( max( login_ts ) ) today
from user_login_detail) t2
) t3
group by t3.type;
结果展示:
19. 连续签到领金币数
- 需求: 用户每天签到可以领1金币，并可以累计签到天数，连续签到的第3、7天分别可以额外领2和6金币。每连续签到7天重新累积签到天数。~求出每个用户金币总数，并按照金币总数倒序排序.
- 分析表: 登录,用户,连续(时间): user_login_detail 登录明细表
- 分析题意: (1) 由于可能会出现在同一天登录多次的情况,所以,这里先进行去重, (2) 每个星期都会进行重置,所以可以直接计算一个星期获取金币的总数量
连续签到的天数: 1 , 2 , 3 , 4 , 5 , 6 , 7
获取的金币数量: 1 , 2 , 5 , 6 , 7 , 8 , 15
(3)第二步设置了一个标签,然后统计每个用户的总金币数量
代码演示
--第一步: 按照用户和用户登录时间(登录时间先格式化为天为单位),进行分组,原因用于去除在同一天重复登录的情况
select distinct user_id,
to_date( login_ts ) login_time
from user_login_detail;
--第二步: 判断用户是几次连续登录的情况, 等差数据方式
select t1.user_id,
t1.login_time,
rank( ) over (partition by t1.user_id order by t1.login_time) rk,
date_sub( t1.login_time , rank( ) over (partition by t1.user_id order by t1.login_time) ) ds_login
from (
select distinct user_id,
to_date( login_ts ) login_time
from user_login_detail
) t1;
--第三步: 然后按照用户进行分组,计算出ds_login的数量,根据不同的数据,标上不同的数据(做标签)
select t2.user_id,
t2.ds_login,
case when count( * ) % 7 = 1 then 1
when count( * ) % 7 = 2 then 2
when count( * ) % 7 = 3 then 5
when count( * ) % 7 = 4 then 6
when count( * ) % 7 = 5 then 7
when count( * ) % 7 = 6 then 8
when count( * ) % 7 = 0 then 15
end count_num
from (
select t1.user_id,
t1.login_time,
rank( ) over (partition by t1.user_id order by t1.login_time) rk,
date_sub( t1.login_time , rank( ) over (partition by t1.user_id order by t1.login_time) ) ds_login
from (
select distinct user_id,
to_date( login_ts ) login_time
from user_login_detail
) t1
) t2
group by t2.user_id, t2.ds_login;
--第四步: 按照用户进行分组,统计count_num数量
select t3.user_id,
sum( t3.count_num ) count_nums
from (
select t2.user_id,
case when count( * ) % 7 = 1 then 1
when count( * ) % 7 = 2 then 2
when count( * ) % 7 = 3 then 5
when count( * ) % 7 = 4 then 6
when count( * ) % 7 = 5 then 7
when count( * ) % 7 = 6 then 8
when count( * ) % 7 = 0 then 15
end count_num
from (
select t1.user_id,
t1.login_time,
rank( ) over (partition by t1.user_id order by t1.login_time) rk,
date_sub( t1.login_time ,
rank( ) over (partition by t1.user_id order by t1.login_time) ) ds_login
from (
select distinct user_id,
to_date( login_ts ) login_time
from user_login_detail
) t1
) t2
group by t2.user_id, t2.ds_login
) t3
group by t3.user_id
order by count_nums desc;
------------------
--方式二:
------------------
select t3.user_id,
sum( t3.coin_cn ) sum_coin_cn
from (
select t2.user_id,
max( t2.counti_cn ) + sum( if( t2.counti_cn % 3 = 0 , 2 , 0 ) ) +
sum( if( t2.counti_cn % 7 = 0 , 6 , 0 ) ) coin_cn
from (
select t1.user_id,
t1.login_date,
date_sub( t1.login_date , t1.rk ) login_date_rk,
count( * )
over (partition by t1.user_id, date_sub( t1.login_date , t1.rk ) order by t1.login_date) counti_cn
from (
select user_id,
date_format( login_ts , 'yyyy-MM-dd' ) login_date,
rank( ) over (partition by user_id order by date_format( login_ts , 'yyyy-MM-dd' )) rk
from user_login_detail
group by user_id, date_format( login_ts , 'yyyy-MM-dd' )
) t1
) t2
group by t2.user_id, t2.login_date_rk
) t3
group by t3.user_id
order by sum_coin_cn desc;
结果展示
总结
- 中级题目相对较多,这里需要分析的题,要考虑的更加全面.
- 注意还有标签的使用,例如19题
- 对于题目考虑问题要全面,例如17题,当时就没有考虑到会有三种情况.
- 使用等差数列的方式,可以更好的判断是否连续.
678
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
