林甸网站建设,做网站的公司怎么做业务,网页设计学徒培训招生,网站颜色搭配前言 今天开始为期一个多月的 HQL 练习#xff0c;共 55 道 HQL 题#xff0c;大概每天两道#xff0c;从初级函数到中级函数。这次的练习不再是基础的 join 那种通用 SQL 语法了#xff0c;而是引入了更多 Hive 的函数#xff08;单行函数、窗口函数等#xff09;。 我…前言 今天开始为期一个多月的 HQL 练习共 55 道 HQL 题大概每天两道从初级函数到中级函数。这次的练习不再是基础的 join 那种通用 SQL 语法了而是引入了更多 Hive 的函数单行函数、窗口函数等。 我会把 HQL 中函数和语法的一些注意事项写在每一题下面的 知识点 中方便上课复习。同样这博客估计没人看如果谁实在需要建表语句给我留言就行。
3-10
1、查询累积销量排名第二的商品中级
SELECT sku_id from(SELECT sku_id,rank() OVER(ORDER BY order_sum desc) rkfrom(SELECT sku_id,sum(sku_num) order_sumFROM order_detailGROUP BY sku_idORDER BY order_sum descLIMIT 2)as t1)as t2
WHERE rk2;
知识点
SQL 中 distinct 必须跟在 select 之后distinct 不能单独用于选择性地仅对结果集中的某个字段去重而不影响其他字段
select distinct sku_id, sku_num,rk from(...
);
-- 尽管查询结果中 sku_id 字段的值可能重复但是不能通 select distinct 来对单个属性去重
sku_id sku_num rk
1 2 1
1 3 2Hive 的子查询必须要有别名 !
3-12
1、筛选2021年总销量小于100的商品初级
需求从订单明细表order_detail中筛选出2021年总销量小于100的商品及其销量假设今天的日期是2022-01-10不考虑上架时间小于一个月的商品。思路拿 2021 年总销量小于100的商品id和上架时间大于30的商品id进行join
order_detail_idorder_idsku_idcreate_datepricesku_num1112021-09-272000.0022132021-09-275000.0053242021-09-286000.0094252021-09-28500.0033
2.1、查询出2021年总销量小于 100 的商品
-- 1.1 2021年销售总量小于100的商品
select sku_id, sum(sku_num) order_sum
from order_detail
where year(create_date)2021
group by sku_id
having order_sum100;
2.2、查询出上架时间大于30天的商品
-- 1.2 上架时间小于 30 天的商品
select sku_id,name from sku_info
where datediff(2022-01-10,from_date)30;
2.3、join
-- join 两个子表
select t1.sku_id,name from (select sku_id, sum(sku_num) order_sumfrom order_detailwhere year(create_date)2021group by sku_idhaving order_sum100)t1 join (select sku_id,name from sku_infowhere datediff(2022-01-10,from_date)30)t2 on t1.sku_id t2.sku_id;
知识点
datediff(2022-01-10,2021-01-10) 365注意日期1必须大于日期2否则结果是负数
2、查询每日新增用户初级
uer_idip_addresslogin_tslogout_ts101180.149.130.1612021-09-21 08:00:002021-09-27 08:30:00101180.149.130.1612021-09-27 08:00:002021-09-27 08:30:00101180.149.130.1612021-09-28 09:00:002021-09-28 09:10:00101180.149.130.1612021-09-29 13:30:002021-09-29 13:50:00 思路1每天有多少人是首日登录就有多少新增用户。查询出每个用户的首日登录时间然后按照日期分组聚合就得到了每日新增用户。而不是去考虑开窗我是这么想的
思路2开窗也可以实现用 row_numer 对每个用户的登录时间进行排名group by user_id然后根据登录时间进行分区将该天 row_number1 的值说明是首次登录进行聚合。
思路1
2.1、查询用户首日登录日期
-- 查询用户首次登录的日期
select user_id,min(date_format(login_ts,yyyy-MM-dd)) first_login_date
from user_login_detail
group by user_id;
2.2、查询每天有多少用户是首日登录
-- 按照日期分组得到每天的新增用户
select first_login_date,count(*) from(select user_id,min(date_format(login_ts,yyyy-MM-dd)) first_login_datefrom user_login_detailgroup by user_id)t1
group by first_login_date;
注意怎么把 login_ts 格式2021-09-21 08:00:00这种时间字符串指定的字段取出来
我是这么实现的
select concat_ws(-,string(year(date_format(login_ts,yyyy-MM-dd HH:mm:ss))),string(month(date_format(login_ts,yyyy-MM-dd HH:mm:ss))),string(day(date_format(login_ts,yyyy-MM-dd HH:mm:ss)))),标准
select date_format(login_ts,yyyy-MM-dd) from user_login_detail;
思路2
select dt,sum(if(rk1,1,0)) new_user_nums from(select user_id,date_format(login_ts,yyyy-MM-dd) dt,row_number() over (partition by user_id order by login_ts) rkfrom user_login_detail)t1
group by dt
having new_user_nums0;
3、用户注册、登录、下单综合统计初级
需求从用户登录明细表user_login_detail和订单信息表order_info中查询每个用户的注册日期首次登录日期、总登录次数以及2021年的登录次数、订单数和订单总额。
思路无脑 join 没有什么难度
order_info
序号编号日期金额11012021-09-2729000.0021012021-09-2870500.0031012021-09-2943300.0041012021-09-30860.00 user_login_detail
3.1、用户首日登录日期
-- 用户首日登录日期
select user_id,min(date_format(login_ts,yyyy-MM-dd)) register_date
from user_login_detail
group by user_id;
注意能 group by 就 group by 不然 join 之后报错。
3.2、用户累积登录次数
-- 用户累积登录次数
select user_id,size(collect_set(date_format(login_ts,yyyy-MM-dd))) total_login_count
from user_login_detail
group by user_id;
知识点 利用 collect_set() 把登录日期收集到一个集合里正好做了去重就不用担心用户一天登录多次的情况了。
3.3、用户2021年登录次数
-- 用户2021登录次数
select user_id,size(collect_set(date_format(login_ts,yyyy-MM-dd))) login_count_2021
from user_login_detail
where year(date_format(login_ts,yyyy-MM-dd))2021
group by user_id;
3.4、用户2021年下单次数和下单金额
-- 用户2021年下单次数和下单金额
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,year(create_date);
3.5、join起来
select t1.user_id,register_date,total_login_count,login_count_2021,order_count_2021,order_amount_2021 from(select user_id,min(date_format(login_ts,yyyy-MM-dd)) register_date from user_login_detail group by user_id)t1 join (select user_id,size(collect_set(date_format(login_ts,yyyy-MM-dd))) total_login_countfrom user_login_detailgroup by user_id)t2 on t1.user_idt2.user_id
join (select user_id,size(collect_set(date_format(login_ts,yyyy-MM-dd))) login_count_2021
from user_login_detail
where year(date_format(login_ts,yyyy-MM-dd))2021
group by user_id)t3 on t1.user_idt3.user_id
join (select user_id,count(order_id) order_count_2021,sum(total_amount) order_amount_2021from order_infowhere year(create_date)2021group by user_id,year(create_date))t4 on t1.user_idt4.user_id;
3.13
1、向用户推荐朋友收藏的商品
需求请向所有用户推荐其朋友收藏但是自己未收藏的商品从好友关系表friendship_info和收藏表favor_info中查询出应向哪位用户推荐哪些商品。
firendship_info
user1_iduser2_id1011010101108101106101104
favor_info
user_idsku_idcreate_date10132021-09-23101122021-09-2310162021-09-25101102021-09-21
思路
核心就是 left join 因为 left join 可以把保留左表的内容这里我们保留的是好友的商品收藏表我们只要根据用户喜欢的商品id和好友喜欢的商品id进行 left join 得到的字段sku_id如果不为 null 就说明这件商品他俩都收藏了如果为 null 就说明这件商品好友收藏了但是用户没有收藏。
1.1、获取用户所有好友
-- 查询所有用户的好友
select user1_id user_id,user2_id friend_id from friendship_info
union
select user2_id,user1_id from friendship_info;知识点
join 是横向合并会形成宽表而 union 是纵向合并形成长表union 会对结果进行排序去重union all 不会
1.2、得到用户好友的收藏列表
-- join得到用户好友收藏的商品select user1_id user_id,user2_id friend_id from friendship_infounionselect user2_id,user1_id from friendship_infojoin favor_info firend_favoron user2_idfirend_favor.user_id;
1.3、left join 过滤
select distinct t1.user_id,firend_favor.sku_id
from (select user1_id user_id,user2_id friend_id from friendship_infounionselect user2_id,user1_id from friendship_info
)t1join favor_info firend_favoron t1.friend_idfirend_favor.user_idleft join favor_info user_favoron t1.user_iduser_favor.user_id and firend_favor.sku_iduser_favor.sku_idwhere user_favor.sku_id is null;
2、男性和女性每日的购物总金额统计初级
需求从订单信息表order_info和用户信息表user_info中分别统计每天男性和女性用户的订单总金额如果当天男性或者女性没有购物则统计结果为0。
order_info
user_info
编号性别出生日期101男1990-01-01102女1991-02-01103女1992-03-01104男1993-04-01
思路1
1、获取不同性别的消费信息
select t2.gender,t1.create_date,t1.total_amount
from order_info t1
join user_info t2 on t1.user_idt2.user_id 我们没有必要查询用户的 id 信息只需要性别后面我们需要根据性别过滤、创建订单的日期后面我们需要根据日期分组和订单总额我们需要根据不同性别统计每天的订单总额即可。
2、按照日期 join 不同性别的每天销售总额
select coalesce(t3.create_date,t4.create_date),if(t3.total_amount_male is null,0,t3.total_amount_male),if(t4.total_amount_female is null ,0,t4.total_amount_female) from(select create_date,sum(total_amount) total_amount_male from(select t2.gender,t1.create_date,t1.total_amountfrom order_info t1join user_info t2 on t1.user_idt2.user_id)t1where gender男group by create_date)t3 full join (select create_date,sum(total_amount) total_amount_female from(select t2.gender,t1.create_date,t1.total_amountfrom order_info t1join user_info t2 on t1.user_idt2.user_id)t2where gender女group by create_date)t4 on t3.create_datet4.create_date
知识点
显然 t3 和 t4 这两个子表分别是男性和女性的每天购物总额这里我们进行的是 full join 这样会保留两张表的所有数据因为数据中存在某 一天男生购物了但是女生没有或者女士购物了男性没有。对于最后查询结果的日期字段就需要保证这个日期不能为 null但是我们又不能显示 t3 t4 两个日期所以我们使用了 coalesce 字段来获取非 null 的日期字段前后顺序并不影响COALESCE 函数用于返回多个表达式中的第一个非NULL值。
思路2
思路1是我自己实现的一种方式思路2是答案不得不说还是这种写法高级
select create_date,cast(sum(if(gender男,total_amount,0)) as decimal(16,2)) total_amount_male,cast(sum(if(gender女,total_amount,0)) as decimal(16,2)) total_amount_female
from order_info oi
join user_info ui on oi.user_idui.user_id
group by create_date;
知识点 cast(expr as type)将expr的执行结果转换为type类型的数据并返回expr可以是函数可以嵌套、字段或字面值。转换失败返回null对于cast(expr as boolean)对任意的非空字符串expr返回true decimal(精度,标度)比如 decimal(16,2)表示一个十进制数其中16是总的数字数量精度而2是小数点后的数字数量标度
