系列文章目录
第一章 HQL表创建
第二章 HQL初级练习1
第三章 HQL初级练习2
第四章 HQL中级练习1
前言
这里HQL的中级练习,用于复习巩固sql常用的函数等使用方法,还有个人分析问题的思路(仅供参考)
一、习题练习
9. 查询所有用户的连续登录两天及以上的日期区间
- 分析表: 用户登录,时间, 用户登录明细表(user_login_detail)
- 题意分析: 使用等差数列的方式,求连续登录时间,然后找到这个时间的最大值和最小值.
- 表结构展示:
用户id-----------------------用户登录ip----------登录时间-----------------------登出时间
- 函数介绍:
- to_date( ): 格式化时间为年-月-日.
- date_sub(A, B): 对A时间减去B天.
- rank()over(): 排序
代码展示
--第一步: 先去重然后, 对用户使用rank()over()排序,
select t1.user_id,
t1.login_day,
rank() over (partition by user_id order by t1.login_day) rk
from (
select user_id,
to_date( login_ts ) login_day
from user_login_detail
group by user_id, to_date( login_ts )
)t1;
--第二步:等差数列: 就是将login_ts-rk -> 如果连续登录-排名->得出的时间将会是相同的.
select t2.user_id,
t2.login_day,
date_sub(t2.login_day,t2.rk) ds
from (
select t1.user_id,
t1.login_day,
rank( ) over (partition by user_id order by t1.login_day) rk
from (
select user_id,
to_date( login_ts ) login_day
from user_login_detail
group by user_id, to_date( login_ts )
) t1
) t2;
--第四步:对上述的数据使用窗口函数计算出,相同ds的数量>=2,过滤
--set hive.vectorized.execution.enabled=false;
--如果使用的是hive+spark的方式,这里需要将上述的设置关闭一下.
--Error while processing statement: FAILED: Execution Error, return code 3 from org.apache.hadoop.hive.ql.exec.spark.SparkTask.
-- Spark job failed during runtime. Please check stacktrace for the root cause.
select t4.user_id,
t4.login_day
from (
select t3.user_id,
t3.login_day,
count( * ) over (partition by t3.user_id,t3.ds) count_ds
from (
select t2.user_id,
t2.login_day,
date_sub( t2.login_day , t2.rk ) ds
from (
select t1.user_id,
t1.login_day,
rank( ) over (partition by user_id order by t1.login_day) rk
from (
select user_id,
to_date( login_ts ) login_day
from user_login_detail
group by user_id, to_date( login_ts )
) t1
) t2
) t3
)t4 where t4.count_ds>=2;
--第五步:将上述找出最大最小值
select t5.user_id,
min(t5.login_day) min_time,
max(t5.login_day) max_time
from (
select t4.user_id,
t4.login_day
from (
select t3.user_id,
t3.login_day,
count( * ) over (partition by t3.user_id,t3.ds) count_ds
from (
select t2.user_id,
t2.login_day,
date_sub( t2.login_day , t2.rk ) ds
from (
select t1.user_id,
t1.login_day,
rank( ) over (partition by user_id order by t1.login_day) rk
from (
select user_id,
to_date( login_ts ) login_day
from user_login_detail
group by user_id, to_date( login_ts )
) t1
) t2
) t3
) t4
where t4.count_ds >= 2
)t5 group by t5.user_id;
结果展示
10. 查询出每个用户的最近三笔订单
- 分析表: 用户订单,最近三笔(时间), 订单表(order_info)
- 分析题意: 使用窗口函数,按照用户进行分组,时间倒序排序,进行排序处理,然后找到前三名.
- 表结构:
订单id-----------------------用户id-----------------------下单时间---------------------------下单总金额
代码展示
--第一步: 使用窗口函数,(由于这里时间只精确到天,所以有可能出现同一天下单多次的情况,所以使用row_number进行排序)
select user_id,
order_id,
create_date,
row_number() over (partition by user_id order by create_date desc ) row_time
from order_info;
--第二步: 然后过滤出前三名
select t1.user_id,
t1.order_id,
t1.create_date
from (
select user_id,
order_id,
create_date,
row_number( ) over (partition by user_id order by create_date desc ) row_time
from order_info
)t1 where t1.row_time<=3;
结果展示
11. 查询每个用户登录日期的最大空档期
- 需求: 最大空档期: 就是用户两次登录相差时间最大的那两次登录,找到对应的时间(天).用户最后一次登录至今的空档也要考虑在内.
- 分析表: 用户,登录, 空档时间(user_login_detail).
- 分析题意: 这里以天为单位,所以先格式化时间为(年-月-日), 先定义一个最后的时间(由于数据是以前的,所以如果以今天为最后登录时间,所有的结果就是最后一次登录最大,这里定义为2021-10-10) , 然后使用窗口函数lead()找到当前时间的下一个时间, 然后计算时间差,找到最大的那个.
- 表结构:
用户id-----------------------ip地址----------------------登录时间-------------------------登出时间
- 函数介绍:
- to_date(): 格式化时间为:年-月-日
- lead()over(): 窗口函数,用于找到当前行的下面的数据.
- datediff(): 计算日期的差值.
代码展示
--第一步: 使用lead()over()找出当前值的后一个时间(用户进行分组,登录时间排序,如果为最后一个则使用指定日期填充).
select user_id,
to_date( login_ts ),
lead( to_date( login_ts ) , 1 , '2021-10-10' )
over (partition by user_id order by to_date( login_ts )) lead_login
from user_login_detail
where to_date( login_ts ) < '2021-10-10';
--如果不限制那个时间,以真实的为准,可以不用写where后的条件,将2021-10-10这个时间替换为你的时间.
--第二步: 计算出时间差
select t1.user_id,
datediff(t1.lead_login,t1.login) df
from (
select user_id,
to_date( login_ts ) login,
lead( to_date( login_ts ) , 1 , '2021-10-10' )
over (partition by user_id order by to_date( login_ts )) lead_login
from user_login_detail where to_date(login_ts) < '2021-10-10'
)t1;
--第三步:找出每个用户最大的时间差
select t2.user_id,
max(t2.df) max_login_time
from (
select t1.user_id,
datediff( t1.lead_login , t1.login ) df
from (
select user_id,
to_date( login_ts ) login,
lead( to_date( login_ts ) , 1 , '2021-10-10')
over (partition by user_id order by to_date( login_ts )) lead_login
from user_login_detail where to_date(login_ts) < '2021-10-10'
) t1
)t2 group by t2.user_id;
结果展示
12. 查询相同时刻多地登陆的用户
- 需求: 用户在登录的时间内(就是登录了还没有登出这个时间内),又在其他地方登录了.
- 分析表: 用户多地登录,ip地址,登录时间使用(user_loagin_deatil)
- 分析题意: (一个前提: 对于一个设备,正常情况下只能登录一个账号,要想在登录只有把原账号登出,才能再次登录,所以这里不用考虑ip的情况,只要出现有交叉的登录时间,就说明这个账号异地登录了)
(这里有个逻辑的问题),用户每一次登录都有登录的时间,所以这里可以对登录的时间进行排序,然后使用窗口函数lead找到当前行的下一行登录的数据,只要判断下一行登录的时间在上一次登录登出时间之内,且此时的ip地址不同,就说明这个用户多地登录了.
代码展示
---注意表里有个101这个数据,当时(个人认为ip地址是唯一的,每一个登录就都是唯一的,所以当时以为这个数据出现了错误,但是在网上查了一些关于ip方面的知识,了解到有可能会出现不同的设备相同的ip,这里就解释了101的有的设备ip地址一样,但是他还是异地登录.)
--第一步:
select user_id,
ip_address,
login_ts,
logout_ts,
lead(login_ts,1) over (partition by user_id order by login_ts) lead_in
from user_login_detail;
--第二步:有个数字逻辑,就是一个设备登录的账号一般情况下就一个,所以这里就不用再考虑是否ip相同, 再同一个设备如果有账号,需要先退出后才能再次登录.
select t1.user_id,
t1.ip_address,
t1.login_ts,
t1.logout_ts
from (
select user_id,
ip_address,
login_ts,
logout_ts,
lead( login_ts , 1 ) over (partition by user_id order by login_ts) lead_in
from user_login_detail
)t1 where t1.lead_in between t1.login_ts and t1.logout_ts;
---上面这种方式,如果同一时间,多地登录,就有可能出现一些问题,所以这里进行改进
--使用最大的登出时间判断
select user_id,
ip_address,
login_ts,
max(logout_ts)over (partition by user_id order by login_ts rows between unbounded preceding and 1 preceding) max_logout
from user_login_detail;
--再进行判断当max_logout>login_ts时就说明这个是异地登录
select t1.user_id,
t1.ip_address,
t1.login_ts
from (
select user_id,
ip_address,
login_ts,
max( logout_ts )
over (partition by user_id order by login_ts rows between unbounded preceding and 1 preceding) max_logout
from user_login_detail
)t1 where t1.max_logout>t1.login_ts;
结果展示
13. 各品类销量前三的
所有商品
- 分析表: 各个品类, 商品 , 销量,需要订单详情表(order_detail),商品表(sku_info)
- 分析题意: 如果该品类小于三个商品，则输出所有的商品, 将表中相同的商品进行分组,求出总的销售数量, 然后和商品表进行关联, 按照品类进行排序,找出前三的商品.
- 表结构: (sku_info)
商品id-------------- 商品名称-------------商品所属种类id -----------上架时间 ----------------上架时的价格
代码展示
--第一步: 按照商品进行分组,统计出每个商品的销售总量
select sku_id, sum( sku_num ) sum_suk
from order_detail
group by sku_id;
--第二步: 将上述查询结果,和商品表进行关联,以获得商品的分类
select sku.sku_id,
t1.sum_suk,
sku.category_id,
row_number( ) over (partition by sku.category_id order by t1.sum_suk desc ) rn
from sku_info sku
left join (
select sku_id, sum( sku_num ) sum_suk
from order_detail
group by sku_id
) t1 on sku.sku_id = t1.sku_id;
--第三步: 使用窗口函数,将倒序排序后的结果找到前三
select t2.sku_id,
t2.sum_suk,
t2.category_id
from (
select sku.sku_id,
t1.sum_suk,
sku.category_id,
row_number( ) over (partition by sku.category_id order by t1.sum_suk desc ) rn
from sku_info sku
left join (
select sku_id, sum( sku_num ) sum_suk
from order_detail
group by sku_id
) t1 on sku.sku_id = t1.sku_id
) t2
where t2.rn <= 3;
结果展示
14. 各品类中商品价格的中位数
- 需求: 统计每个分类中商品价格的中位数，如果某分类中商品个数为偶数，则输出中间两个价格的平均值，如果是奇数，则输出中间价格即可。
- 分析表: 品类,商品,价格 (sku_info)
- 分析题意:
(1).计算每个分类的商品数量,%2来判断是偶数还是奇数.
(2).将商品按照品类分组的窗口函数进行排序row_number.
(3).使用条件判断的方式,过滤出中位数.
代码展示
select category_id,
cast( avg( price ) as decimal(16 , 2) ) medprice
from (select category_id,
price,
row_number( ) over (partition by category_id order by price) rk,
count( * ) over (partition by category_id) cn
from sku_info) t1
where (cn % 2 = 0 and (rk = cn / 2 or rk = cn / 2 + 1)) ---偶数,判断是偶数后,也将中位数进行限定
or (cn % 2 = 1 and rk = (cn + 1) / 2) --奇数
group by category_id;
----------------------------------------
-----下面个人其他方式(有点过于繁琐,仅供参考)
----------------------------------------
--第一步:计算每个分类的商品数量
select category_id,
count(*) count_sku
from sku_info
group by category_id;
--商品排序
select sku_id,
name,
price,
category_id,
row_number() over (partition by category_id order by price) mind_price
from sku_info;
--根据排序的最后一个值,%2操作判断是奇数还是偶数, 如果是奇数:则这个值/2,向上取整(这个数就是中位数),如果是偶数/2得到中间考前的那个数,将这连续的两个数求和求平均值
--这里是将表和上述求的不同商品品类商品的数量join 下表
select t1.sku_id,
t1.name,
t1.price,
t1.mind_price,
t1.category_id,
t2.count_sku
from (
select sku_id,
name,
price,
category_id,
row_number( ) over (partition by category_id order by price) mind_price
from sku_info
) t1
join (select category_id,
count( * ) count_sku
from sku_info
group by category_id)t2 on t1.category_id=t2.category_id;
--第三步:根据count_sku进行判断是奇数还是偶数
select t3.sku_id,
t3.price,
t3.mind_price,
t3.category_id,
`if`(t3.count_sku%2=0,t3.count_sku/2, null),
`if`(t3.count_sku%2 != 0, ceil(t3.count_sku/2),null)
from (
select t1.sku_id,
t1.price,
t1.mind_price,
t1.category_id,
t2.count_sku
from (
select sku_id,
name,
price,
category_id,
row_number( ) over (partition by category_id order by price) mind_price
from sku_info
) t1
join (select category_id,
count( * ) count_sku
from sku_info
group by category_id) t2 on t1.category_id = t2.category_id
)t3;
-----第四步:使用if的方式,判断是奇数还是偶数
select t4.category_id,
avg(t4.price)
from (
select t3.sku_id,
t3.price,
t3.mind_price,
t3.category_id,
`if`( t3.count_sku % 2 = 0 , t3.count_sku / 2 , null ) c1,
`if`( t3.count_sku % 2 != 0 , ceil( t3.count_sku / 2 ) , null ) c2
from (
select t1.sku_id,
t1.price,
t1.mind_price,
t1.category_id,
t2.count_sku
from (
select sku_id,
name,
price,
category_id,
row_number( ) over (partition by category_id order by price) mind_price
from sku_info
) t1
join (select category_id,
count( * ) count_sku
from sku_info
group by category_id) t2 on t1.category_id = t2.category_id
) t3
----这里按照不同的方式进行过滤
)t4 where ( t4.mind_price=t4.c1 or t4.mind_price=t4.c1+1 ) or (t4.mind_price=t4.c2)
group by t4.category_id;
结果展示
总结
- 分析问题的时候可以将需求,进行相应的拆分,最后在进行组合得出最终的答案.
- 考虑实际的逻辑,(异地登录的问题),同一个设备正常情况要退出后才能在次登录,所以如果第二次登录的时间在第一次登出时间内,就说明这个账号异地登录了.
1570
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
