前言

? ? ? ? 今天開始為期一個多月的 HQL 練習(xí)，共 55 道 HQL 題，大概每天兩道，從初級函數(shù)到中級函數(shù)。這次的練習(xí)不再是基礎(chǔ)的 join 那種通用 SQL 語法了，而是引入了更多 Hive 的函數(shù)（單行函數(shù)、窗口函數(shù)等）。??

? ? ? ? 我會把 HQL 中函數(shù)和語法的一些注意事項寫在每一題下面的 "知識點" 中，方便上課復(fù)習(xí)。同樣這博客估計沒人看，如果誰實在需要建表語句給我留言就行。

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 rk=2;

知識點：

• SQL 中 distinct 必須跟在 select 之后
• distinct 不能單獨用于選擇性地僅對結(jié)果集中的某個字段去重，而不影響其他字段

select distinct sku_id, sku_num,rk from(...
);
-- 盡管查詢結(jié)果中 sku_id 字段的值可能重復(fù)，但是不能通 select distinct 來對單個屬性去重
sku_id    sku_num    rk
1            2        1
1            3        2

• Hive 的子查詢必須要有別名 !

3-12

1、篩選2021年總銷量小于100的商品（初級）

• 需求：從訂單明細(xì)表（order_detail）中篩選出2021年總銷量小于100的商品及其銷量，假設(shè)今天的日期是2022-01-10，不考慮上架時間小于一個月的商品。
• 思路：拿 2021 年總銷量小于100的商品id和上架時間大于30的商品id進(jìn)行join

order_detail_id	order_id	sku_id	create_date	price	sku_num
1	1	1	2021-09-27	2000.00	2
2	1	3	2021-09-27	5000.00	5
3	2	4	2021-09-28	6000.00	9
4	2	5	2021-09-28	500.00	33

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_sum<100;

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_sum<100)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否則結(jié)果是負(fù)數(shù)

2、查詢每日新增用戶（初級）

uer_id	ip_address	login_ts	logout_ts
101	180.149.130.161	2021-09-21 08:00:00	2021-09-27 08:30:00
101	180.149.130.161	2021-09-27 08:00:00	2021-09-27 08:30:00
101	180.149.130.161	2021-09-28 09:00:00	2021-09-28 09:10:00
101	180.149.130.161	2021-09-29 13:30:00	2021-09-29 13:50:00

?思路1：每天有多少人是首日登錄就有多少新增用戶。查詢出每個用戶的首日登錄時間，然后按照日期分組聚合就得到了每日新增用戶。而不是去考慮開窗（我是這么想的）

思路2：開窗也可以實現(xiàn)，用 row_numer 對每個用戶的登錄時間進(jìn)行排名（group by user_id），然后根據(jù)登錄時間進(jìn)行分區(qū)將該天 row_number=1 的值（說明是首次登錄）進(jìn)行聚合。

思路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）這種時間字符串指定的字段取出來？

我是這么實現(xiàn)的：

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')))),

標(biāo)準(zhǔn)：

select date_format(login_ts,'yyyy-MM-dd') from user_login_detail;

思路2

select dt,sum(`if`(rk=1,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_nums>0;

3、用戶注冊、登錄、下單綜合統(tǒng)計（初級）

需求：從用戶登錄明細(xì)表（user_login_detail）和訂單信息表（order_info）中查詢每個用戶的注冊日期（首次登錄日期）、總登錄次數(shù)，以及2021年的登錄次數(shù)、訂單數(shù)和訂單總額。

思路：無腦 join 沒有什么難度

order_info：

序號	編號	日期	金額
1	101	2021-09-27	29000.00
2	101	2021-09-28	70500.00
3	101	2021-09-29	43300.00
4	101	2021-09-30	860.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、用戶累積登錄次數(shù)

-- 用戶累積登錄次數(shù)
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() 把登錄日期收集到一個集合里，正好做了去重，就不用擔(dān)心用戶一天登錄多次的情況了。

3.3、用戶2021年登錄次數(shù)

-- 用戶2021登錄次數(shù)
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年下單次數(shù)和下單金額

-- 用戶2021年下單次數(shù)和下單金額
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_id=t2.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_id=t3.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_id=t4.user_id;

3.13

1、向用戶推薦朋友收藏的商品

需求：請向所有用戶推薦其朋友收藏但是自己未收藏的商品，從好友關(guān)系表（friendship_info）和收藏表（favor_info）中查詢出應(yīng)向哪位用戶推薦哪些商品。

firendship_info：

user1_id	user2_id
101	1010
101	108
101	106
101	104

favor_info：

user_id	sku_id	create_date
101	3	2021-09-23
101	12	2021-09-23
101	6	2021-09-25
101	10	2021-09-21

思路：

• 核心就是 left join ，因為 left join 可以把保留左表的內(nèi)容（這里我們保留的是好友的商品收藏表），我們只要根據(jù)用戶喜歡的商品id和好友喜歡的商品id進(jìn)行 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 會對結(jié)果進(jìn)行排序去重，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_id=firend_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_id=firend_favor.user_idleft join favor_info user_favoron t1.user_id=user_favor.user_id and firend_favor.sku_id=user_favor.sku_idwhere user_favor.sku_id is null;

2、男性和女性每日的購物總金額統(tǒng)計（初級）

需求：從訂單信息表（order_info）和用戶信息表（user_info）中，分別統(tǒng)計每天男性和女性用戶的訂單總金額，如果當(dāng)天男性或者女性沒有購物，則統(tǒng)計結(jié)果為0。

order_info：

user_info：

編號	性別	出生日期
101	男	1990-01-01
102	女	1991-02-01
103	女	1992-03-01
104	男	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_id=t2.user_id

?我們沒有必要查詢用戶的 id 信息，只需要性別（后面我們需要根據(jù)性別過濾）、創(chuàng)建訂單的日期（后面我們需要根據(jù)日期分組）和訂單總額（我們需要根據(jù)不同性別統(tǒng)計每天的訂單總額）即可。

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_id=t2.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_id=t2.user_id)t2where gender='女'group by create_date)t4 on t3.create_date=t4.create_date

知識點：

• 顯然 t3 和 t4 這兩個子表分別是男性和女性的每天購物總額，這里我們進(jìn)行的是 full join 這樣會保留兩張表的所有數(shù)據(jù)，因為數(shù)據(jù)中存在某 一天男生購物了但是女生沒有，或者女士購物了男性沒有。
• 對于最后查詢結(jié)果的日期字段就需要保證這個日期不能為 null，但是我們又不能顯示 t3 t4 兩個日期，所以我們使用了 coalesce 字段來獲取非 null 的日期字段（前后順序并不影響）
• COALESCE 函數(shù)用于返回多個表達(dá)式中的第一個非NULL值。

思路2

思路1是我自己實現(xiàn)的一種方式，思路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_id=ui.user_id
group by create_date;

知識點：

• cast(expr as <type>)：將expr的執(zhí)行結(jié)果轉(zhuǎn)換為<type>類型的數(shù)據(jù)并返回，expr可以是函數(shù)（可以嵌套）、字段或字面值。轉(zhuǎn)換失敗返回null，對于cast(expr as boolean)，對任意的非空字符串expr返回true

• decimal(精度,標(biāo)度)：比如?decimal(16,2)表示一個十進(jìn)制數(shù)，其中16是總的數(shù)字?jǐn)?shù)量（精度），而2是小數(shù)點后的數(shù)字?jǐn)?shù)量（標(biāo)度）