HQL刷題 50道

尚硅谷HQL刷題網站

在這里插入圖片描述

答案

1.查詢累積銷量排名第二的商品

select sku_id
from (select sku_id, dense_rank() over (order by total desc) rnfrom (select sku_id, sum(sku_num) totalfrom order_detailgroup by sku_id) t1) t2
where rn = 2;

2.查詢至少連續三天下單的用戶

select user_id
from (select user_id,create_date,sum(if(diff > 1, 1, 0)) over (partition by user_id order by create_date) groupsfrom (select user_id,create_date,yestoday,datediff(create_date, yestoday) diff from (select user_id,create_date,lead (create_date, 1, '1970-01-01') over (partition by user_id order by create_date) as yestoday from(select user_id, create_datefrom order_infogroup by user_id, create_date)t1) t2) t3) t4
group by user_id, groups
having count (*)>=3;

3.查詢各品類銷售商品的種類數及銷量最高的商品

select t3.category_id,cate.category_name,t3.sku_id,t3.name,t3.order_num,t3.sku_cnt
from (select category_id,sku_id,name,order_num,sku_cntfrom (select category_id,sku_id,name,order_num,count(distinct sku_id) over (partition by category_id)               sku_cnt,row_number() over (partition by category_id order by order_num desc) rnfrom (select sku.category_id, sku.sku_id, sku.name, sum(od.sku_num) order_numfrom order_detail odjoin sku_info sku on od.sku_id = sku.sku_idgroup by sku.category_id, sku.sku_id, sku.name) t1) t2where rn = 1) t3join category_info cate on t3.category_id = cate.category_id;

4 查詢用戶的累計消費金額及VIP等級

select user_id,create_date,sum_so_far,casewhen sum_so_far >= 0 and sum_so_far < 10000 then '普通會員'when sum_so_far >= 10000 and sum_so_far < 30000 then '青銅會員'when sum_so_far >= 30000 and sum_so_far < 50000 then '白銀會員'when sum_so_far >= 50000 and sum_so_far < 80000 then '黃金會員'when sum_so_far >= 80000 and sum_so_far < 100000 then '白金會員'when sum_so_far >= 100000 then '鉆石會員' end vip_level
from (select user_id,create_date,sum(total) over (partition by user_id order by create_date) sum_so_farfrom (select user_id, create_date, sum(total_amount) total from order_info group by user_id, create_date) t1order by user_id, create_date) t2;

5 查詢首次下單后第二天連續下單的用戶比率

select concat(round(users * 1.0 / total * 100, 1), '%') as percentage
from (select count(distinct user_id) users, totalfrom (select user_id,create_date,first_value(create_date) over (partition by user_id order by create_date) first_day,count(distinct user_id) over ()                                           totalfrom order_info) t1where datediff(create_date, first_day) = 1group by total) t2;

6 每個商品銷售首年的年份、銷售數量和銷售金額

select sku_id, year, order_num, order_amount
from (select sku_id, year, order_num, order_amount, row_number() over (partition by sku_id order by year) rnfrom (select sku_id, year(create_date) year, sum(sku_num) order_num, sum(sku_num * price) order_amountfrom order_detailgroup by sku_id, year(create_date)) t1) t2
where rn = 1;

7 篩選去年總銷量小于100的商品

select sku_id, name, order_num
from (select sku.sku_id, sku.name, sum(sku_num) order_numfrom order_detail odjoin sku_info sku on sku.sku_id = od.sku_idwhere year(od.create_date) = '2021'and datediff(od.create_date, sku.from_date) >= 30group by sku.sku_id, sku.name) t1
where order_num < 100;

8 查詢每日新用戶數

select login_date_first, count(distinct user_id) user_count
from (select user_id,to_date(login_ts)                                          login_date_first,row_number() over (partition by user_id order by login_ts) rnfrom user_login_detail) t1
where rn = 1
group by login_date_first;

9 統計每個商品的銷量最高的日期

select sku_id,create_date,sum_num
from (select sku_id,create_date,sum_num,row_number() over (partition by sku_id order by sum_num desc,create_date) rnfrom (select sku_id, create_date, sum(sku_num) sum_numfrom order_detailgroup by sku_id, create_date) t1) t2
where rn = 1or create_date = current_date();

10 查詢銷售件數高于品類平均數的商品

select sku_id,name,sum_num,floor(avg_num) cate_avg_num
from (select sku_id,name,sum_num,avg(sum_num) over (partition by category_id) avg_numfrom (select sku.sku_id,sku.name,sku.category_id,sum(od.sku_num) sum_numfrom order_detail odjoin sku_info skuon od.sku_id = sku.sku_idgroup by sku.sku_id, sku.name, sku.category_id) t1) t2
where sum_num > floor(avg_num);

11 用戶注冊、登錄、下單綜合統計

select t1.user_id,to_date(t1.register_time) register_date,t1.total_login_count,t1.login_count_2021,count(*)                  order_count_2021,sum(od.total_amount)      order_amount_2021
from (select distinct user_id,first_value(login_ts) over (partition by user_id order by login_ts) register_time,count(*) over (partition by user_id)                                total_login_count,sum(if(year(login_ts) = '2021', 1, 0)) over (partition by user_id)  login_count_2021from user_login_detail) t1join order_info od on od.user_id = t1.user_id
where year(od.create_date) = '2021'
group by t1.user_id, to_date(t1.register_time),t1.total_login_count,t1.login_count_2021;

12 查詢指定日期的全部商品價格

select sku_id,price
from (select sku_id,cast(tmp_price as decimal(16, 2))                        price,row_number() over (partition by sku_id order by dt desc) rnfrom (select t1.sku_id, nvl(t2.change_date, t1.from_date) dt, nvl(t2.new_price, t1.price) tmp_pricefrom (select sku_id, price, from_datefrom sku_infowhere from_date <= '2021-10-01') t1left join(select sku_id, new_price, change_datefrom sku_price_modify_detailwhere change_date <= '2021-10-01') t2 on t1.sku_id = t2.sku_id) t3) t4
where rn = 1;

13 即時訂單比例

select cast(plan / total as decimal(16, 2)) percentage
from (select count(*) total, sum(if(order_date = custom_date, 1, 0)) planfrom (select user_id,order_date,custom_date,row_number() over (partition by user_id order by order_date) rnfrom delivery_info) t1where rn = 1) t2;

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

select distinct ship.user1_id user_id, f1.sku_id
from friendship_info shipjoin favor_info f1 on ship.user2_id = f1.user_idleft join favor_info f2 on f2.user_id = ship.user1_id and f2.sku_id = f1.sku_id
where f2.sku_id is null;select user_id, sku_id
from (select distinct ship.user1_id user_id, f1.sku_idfrom friendship_info shipjoin favor_info f1 on ship.user2_id = f1.user_idunion allselect user_id, sku_idfrom favor_info) t1
group by user_id, sku_id
having count(*) < 2;

15 查詢所有用戶的連續登錄兩天及以上的日期區間

select user_id, min(dt) start_date, max(dt) end_date
from (select user_id, dt, sum(if(diff > 1, 1, 0)) over (partition by user_id order by dt) numsfrom (select user_id, dt, datediff(dt, yestoday) difffrom (select user_id, dt, lag(dt, 1, '1970-01-01') over (partition by user_id order by dt) yestodayfrom (select user_id, to_date(login_ts) dtfrom user_login_detailgroup by user_id, to_date(login_ts)) t1) t2) t3) t4
group by user_id, nums
having count(*) > 1;

16 男性和女性每日的購物總金額統計

select od.create_date,sum(if(u.gender = '男', od.total_amount, 0)) total_amount_male,sum(if(u.gender = '女', od.total_amount, 0)) total_amount_female
from order_info odjoin user_info u on od.user_id = u.user_id
group by od.create_date;

17 訂單金額趨勢分析

select create_date,round(sum(total_amount) over (order by ts range between 172800 preceding and current row), 2) total_3d,round(avg(total_amount) over (order by ts range between 172800 preceding and current row), 2) avg_3d
from (select create_date,unix_timestamp(create_date, 'yyyy-MM-dd') ts,sum(total_amount)                         total_amountfrom order_infogroup by create_date) t1;

18.購買過商品1和商品2但是沒有購買商品3的顧客

select user_id
from (select distinct order_info.user_id, order_detail.sku_idfrom order_infojoin order_detail on order_info.order_id = order_detail.order_idwhere order_detail.sku_id in (1, 2, 3)) t1
group by user_id
having sum(if(sku_id = 3, 3, 1)) = 2;

19 統計每日商品1和商品2銷量的差值

select create_date,sum(if(sku_id = 1, sku_num, 0)) - sum(if(sku_id = 2, sku_num, 0)) diff
from order_detail
where sku_id in (1, 2)
group by create_date;

20 查詢出每個用戶的最近三筆訂單

select user_id,order_id,create_date
from (select user_id,order_id,create_date,dense_rank() over (partition by user_id order by create_date desc) rnfrom order_info) t1
where rn < 4;

21 查詢每個用戶登錄日期的最大空檔期

select user_id, max(datediff(future, dt)) max_diff
from (select user_id,dt,lead(dt, 1, '2021-10-10') over (partition by user_id order by dt) futurefrom (select distinct user_id, to_date(login_ts) dt from user_login_detail) t1) t2
group by user_id;

22 查詢相同時刻多地登陸的用戶

select user_id
from (select u1.user_id,if(u1.login_ts <= u2.login_ts, if(u1.logout_ts >= u2.login_ts, if(u1.ip_address = u2.ip_address, 0, 1), 0),0) numfrom user_login_detail u1join user_login_detail u2 on u1.user_id = u2.user_id and u1.login_ts != u2.login_ts) t2
group by user_id
having sum(num) > 0;

23 銷售額完成任務指標的商品

select distinct sku_id
from (select sku_id, sum(if(diff > 1, 1, 0)) over (partition by sku_id order by dt) numfrom (select sku_id, dt, (year(dt) - year(pass)) * 12 + month(dt) - month(pass) difffrom (select sku_id, dt, lag(dt, 1, '1970-01-01') over (partition by sku_id order by dt) passfrom (select sku_id, dtfrom (select sku_id, trunc(create_date, 'MM') dt, sum(price * sku_num) totalfrom order_detailgroup by sku_id, trunc(create_date, 'MM')) t1
--   按題目的過濾條件 where not ((sku_id = 1 and total < 21000) or (sku_id = 2 and total < 10000))where (sku_id = 1 and total >= 21000)or (sku_id = 2 and total >= 10000)) t2) t3) t4) t5
group by sku_id, num
having count(*) > 1;

24 根據商品銷售情況進行商品分類

select category, count(*) cn
from (select sku_id,casewhen total <= 5000 then '冷門商品'when total <= 19999 then '一般商品'else '熱門商品' end categoryfrom (select sku_id, sum(sku_num) total from order_detail group by sku_id) t1) t2
group by category;

25 各品類銷量前三的所有商品題目意思不明確 dense_rank() row_number()

select sku_id,category_id
from (select sku_id,category_id,dense_rank() over (partition by category_id order by total desc) rnfrom (select od.sku_id, sku.category_id, sum(sku_num) totalfrom order_detail odjoin sku_info sku on od.sku_id = sku.sku_idgroup by od.sku_id, sku.category_id) t1) t2
where rn < 4;

26 各品類中商品價格的中位數

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) rn,count(*) over (partition by category_id)                    cnfrom sku_info) t1
where rn in (ceil((cn + 1) * 0.5), floor((cn + 1) * 0.5))
group by category_id;

27 找出銷售額連續3天超過100的商品

select distinct sku_id
from order_detail
where create_date in(select distinct create_datefrom (select create_date, count(*) over (order by nums) cntfrom (select create_date,sum(if(diff > 1, 1, 0)) over (order by create_date) numsfrom (select create_date,datediff(create_date,lag(create_date, 1, '1970-01-01') over (order by create_date)) difffrom (select create_datefrom order_detailgroup by create_datehaving sum(sku_num) > 100) t1) t2) t3) t4where cnt > 2);

28 查詢有新注冊用戶的當天的新用戶數量、新用戶的第一天留存率

select first_login,sum(if(diff = 0, 1, 0))                                                       register,cast((sum(if(diff = 1, 1.0, 0)) / sum(if(diff = 0, 1, 0))) as decimal(16, 2)) retention
from (select distinct user_id,to_date(min(login_ts) over (partition by user_id))                              first_login,datediff(to_date(login_ts), to_date(min(login_ts) over (partition by user_id))) difffrom user_login_detail) t1
group by first_login;

29 求出商品連續售賣的時間區間

select sku_id,min(create_date) start_date,max(create_date) end_date
from (select sku_id,create_date,sum(if(diff > 1, 1, 0)) over (partition by sku_id order by create_date) numsfrom (select sku_id,create_date,datediff(create_date,lag(create_date, 1, '1970-01-01') over (partition by sku_id order by create_date)) difffrom (select distinct sku_id, create_datefrom order_detail) t1) t2) t3
group by sku_id, nums;

30 登錄次數及交易次數統計

select t1.user_id, t1.login_date, t1.login_count, nvl(t2.order_count, 0) order_count
from (select user_id, to_date(login_ts) login_date, count(*) login_countfrom user_login_detailgroup by user_id, to_date(login_ts)) t1left join(select user_id, order_date, count(*) order_countfrom delivery_infogroup by user_id, order_date) t2 on t1.user_id = t2.user_id and t1.login_date = t2.order_date;

31 按年度列出每個商品銷售總額

select sku_id,year(create_date)                            year_date,cast(sum(sku_num * price) as decimal(16, 2)) sku_sum
from order_detail
group by sku_id, year(create_date);

32 某周內每件商品每天銷售情況

select sku_id,sum(if(dayofweek(create_date) = 2, sku_num, 0)) monday,sum(if(dayofweek(create_date) = 3, sku_num, 0)) tuesday,sum(if(dayofweek(create_date) = 4, sku_num, 0)) wednesday,sum(if(dayofweek(create_date) = 5, sku_num, 0)) thursday,sum(if(dayofweek(create_date) = 6, sku_num, 0)) friday,sum(if(dayofweek(create_date) = 7, sku_num, 0)) saturday,sum(if(dayofweek(create_date) = 1, sku_num, 0)) sunday
from order_detail
where create_date >= '2021-09-27'and create_date <= '2021-10-03'
group by sku_id;

33 查看每件商品的售價漲幅情況(排除只有1次漲幅的)

select sku_id, price_change
from (select sku_id,row_number() over (partition by sku_id order by change_date desc)                        rn,count(*) over (partition by sku_id)                                                      cn,new_price - (lead(new_price, 1, 0) over (partition by sku_id order by change_date desc)) price_changefrom sku_price_modify_detail) t1
where rn = 1and cn > 1;

34 銷售訂單首購和次購分析

-- 題目實際意思
select user_id, min(create_date) first_date, max(create_date) last_date, cn
from (select user_id,create_date,cn,row_number() over (partition by user_id order by create_date) rnfrom (select o.user_id, o.create_date, count(*) over (partition by o.user_id) cnfrom sku_info skujoin order_detail od on sku.sku_id = od.sku_idjoin order_info o on o.order_id = od.order_idwhere sku.name in ('xiaomi 10', 'apple 12', 'xiaomi 13')) t1where cn > 1) t2
where rn < 3
group by user_id, cn;
-- 實際結果
select user_id, min(create_date) first_date, max(create_date) last_date, cn
from (select user_id, create_date, cnfrom (select o.user_id, o.create_date, count(*) over (partition by o.user_id) cnfrom sku_info skujoin order_detail od on sku.sku_id = od.sku_idjoin order_info o on o.order_id = od.order_idwhere sku.name in ('xiaomi 10', 'apple 12', 'xiaomi 13')) t1where cn > 1) t2
group by user_id, cn;

35 同期商品售賣分析表

select sku_id,month(create_date)                            month,sum(if(year(create_date) = 2020, sku_num, 0)) 2020_skusum,sum(if(year(create_date) = 2021, sku_num, 0)) 2021_skusum
from order_detail
-- 按題目意思 where create_date >= '2021-01-01' and create_date < '2023-01-01'
where create_date >= '2020-01-01'and create_date < '2022-01-01'
group by sku_id, month(create_date);

36 國慶期間每個品類的商品的收藏量和購買量

select t1.sku_id, t1.total sku_sum, nvl(t2.uv, 0) favor_cn
from (select sku_id, sum(sku_num) totalfrom order_detailwhere create_date >= '2021-10-01'and create_date <= '2021-10-07'group by sku_id) t1left join(select sku_id, count(distinct user_id) uvfrom favor_infowhere create_date >= '2021-10-01'and create_date <= '2021-10-07'group by sku_id) t2 on t1.sku_id = t2.sku_id;

37 統計活躍間隔對用戶分級結果

select level, count(*) cn
from (select casewhen datediff(today, register) <= 7 then '新增用戶'when datediff(today, login) <= 7 then '忠實用戶'when datediff(today, login) < 30 then '沉睡用戶'else '流失用戶' end levelfrom (select distinct user_id,max(dt) over ()                     today,min(dt) over (partition by user_id) register,max(dt) over (partition by user_id) loginfrom (select distinct user_id, to_date(login_ts) dtfrom user_login_detail) t1) t2) t3
group by level;

38 連續簽到領金幣數

select user_id,sum(if(total % 7 > 2, floor(total / 7) * 15 + (total % 7) + 2, floor(total / 7) * 15 + (total % 7))) sum_coin_cn
from (select user_id, count(*) totalfrom (select user_id,sum(if(nums > 1, 1, 0)) over (partition by user_id order by dt) typefrom (select user_id,dt,datediff(dt, lag(dt, 1, '1970-01-01') over (partition by user_id order by dt)) numsfrom (select distinct user_id, to_date(login_ts) dtfrom user_login_detail) t1) t2) t3group by user_id, type) t4
group by user_id
order by sum_coin_cn desc;

39 國慶期間的7日動銷率和滯銷率

-- 固定式（要優化）
select category_id,cast(round(first / first_total, 2) as decimal(16, 2))         first_sale_rate,cast(1 - round(first / first_total, 2) as decimal(16, 2))     first_unsale_rate,cast(round(second / second_total, 2) as decimal(16, 2))       second_sale_rate,cast(1 - round(second / second_total, 2) as decimal(16, 2))   second_unsale_rate,cast(round(third / third_total, 2) as decimal(16, 2))         third_sale_rate,cast(1 - round(third / third_total, 2) as decimal(16, 2))     third_unsale_rate,cast(round(fourth / fourth_total, 2) as decimal(16, 2))       fourth_sale_rate,cast(1 - round(fourth / fourth_total, 2) as decimal(16, 2))   fourth_unsale_rate,cast(round(fifth / fifth_total, 2) as decimal(16, 2))         fifth_sale_rate,cast(1 - round(fifth / fifth_total, 2) as decimal(16, 2))     fifth_unsale_rate,cast(round(sixth / sixth_total, 2) as decimal(16, 2))         sixth_sale_rate,cast(1 - round(sixth / sixth_total, 2) as decimal(16, 2))     sixth_unsale_rate,cast(round(seventh / seventh_total, 2) as decimal(16, 2))     seventh_sale_rate,cast(1 - round(seventh / seventh_total, 2) as decimal(16, 2)) seventh_unsale_rate
from (select sku.category_id,count(distinct if(sku.from_date <= '2021-10-01', sku.sku_id, null)) first_total,count(distinct if(od.create_date = '2021-10-01', od.sku_id, null))  first,count(distinct if(sku.from_date <= '2021-10-02', sku.sku_id, null)) second_total,count(distinct if(od.create_date = '2021-10-02', od.sku_id, null))  second,count(distinct if(sku.from_date <= '2021-10-03', sku.sku_id, null)) third_total,count(distinct if(od.create_date = '2021-10-03', od.sku_id, null))  third,count(distinct if(sku.from_date <= '2021-10-04', sku.sku_id, null)) fourth_total,count(distinct if(od.create_date = '2021-10-04', od.sku_id, null))  fourth,count(distinct if(sku.from_date <= '2021-10-05', sku.sku_id, null)) fifth_total,count(distinct if(od.create_date = '2021-10-05', od.sku_id, null))  fifth,count(distinct if(sku.from_date <= '2021-10-06', sku.sku_id, null)) sixth_total,count(distinct if(od.create_date = '2021-10-06', od.sku_id, null))  sixth,count(distinct if(sku.from_date <= '2021-10-07', sku.sku_id, null)) seventh_total,count(distinct if(od.create_date = '2021-10-07', od.sku_id, null))  seventhfrom sku_info skuleft join order_detail od on sku.sku_id = od.sku_idgroup by sku.category_id) t1;

40 出平臺同時在線最多的人數

select max(num) as cn
from (select sum(flag) over (order by dt) numfrom (select login_ts dt, 1 flagfrom user_login_detailunion allselect logout_ts dt, -1 flagfrom user_login_detail) t1) t2;

41 同時在線人數問題

select live_id, max(num) max_user_count
from (select live_id, sum(flag) over (partition by live_id order by dt) numfrom (select user_id, live_id, in_datetime dt, 1 flagfrom live_eventsunion allselect user_id, live_id, out_datetime dt, -1 flagfrom live_events) t1) t2
group by live_id;

42 會話劃分問題

select user_id,page_id,view_timestamp,concat(user_id, '-', sum(if(diff > 60, 1, 0)) over (partition by user_id order by view_timestamp)) session_id
from (select user_id,page_id,view_timestamp,view_timestamp - lag(view_timestamp, 1, 0) over (partition by user_id order by view_timestamp) difffrom page_view_events) t1;

43 間斷連續登錄用戶問題

select user_id, max(num) max_day_count
from (select user_id, datediff(max(dt), min(dt)) + 1 numfrom (select user_id, dt, sum(if(diff > 2, 1, 0)) over (partition by user_id order by dt) typefrom (select user_id,dt,datediff(dt, lag(dt, 1, '1970-01-01') over (partition by user_id order by dt)) difffrom (select distinct user_id, to_date(login_datetime) dtfrom login_events) t1) t2) t3group by user_id, type) t4
group by user_id;

44 日期交叉問題

select brand, sum(if(datediff(end_date, stt) >= 0, datediff(end_date, stt) + 1, 0)) promotion_day_count
from (select brand,if(max_date is null, start_date, if(start_date > max_date, start_date, date_add(max_date, 1))) stt,end_datefrom (select brand,start_date,end_date,max(end_date)over (partition by brand order by start_date rows between UNBOUNDED PRECEDING and 1 PRECEDING) max_datefrom promotion_info) t1) t2
group by brand;

45 復購率問題(注意全是90天內)

select product_id, cast(sum(if(nums > 1, 1, 0)) / count(*) as decimal(16, 2)) as cpr
from (select user_id, product_id, count(*) numsfrom (select user_id, product_id, datediff(max(order_date) over (), order_date) difffrom order_detail) t1where diff <= 90group by user_id, product_id) t2
group by product_id
order by crp desc, product_id;

46 出勤率問題

select course_id,cast(sum(if(total is null, 0, if(total > 2400, 1, 0))) / count(*) as decimal(16, 2)) adr
from (select t1.course_id, sum(unix_timestamp(l.login_out) - unix_timestamp(l.login_in)) totalfrom (select course_id, id from course_apply lateral view explode(user_id) user_id as id) t1left join user_login l on t1.course_id = l.course_id and l.user_id = t1.idgroup by t1.course_id, t1.id) t2
group by course_id;

47 打車問題

select period,count(*)                                           get_car_num,cast(avg(nvl(wait, 0)) / 60 as decimal(16, 2))     wait_time,cast(avg(nvl(dispatch, 0)) / 60 as decimal(16, 2)) dispatch_time
from (select casewhen hour(r.event_time) >= 7 and hour(r.event_time) < 9 then '早高峰'when hour(r.event_time) >= 9 and hour(r.event_time) < 17 then '工作時間'when hour(r.event_time) >= 17 and hour(r.event_time) < 20 then '晚高峰'else '休息時間' end                                         period,unix_timestamp(o.order_time) - unix_timestamp(r.event_time) wait,unix_timestamp(o.start_time) - unix_timestamp(o.order_time) dispatchfrom get_car_record rleft join get_car_order o on r.order_id = o.order_id) t1
group by period;

48 排列問題

-- 自連接
select t1.team_name team_name_1, t2.team_name team_name_2
from team t1join team t2 on t1.team_name > t2.team_name;-- 開窗聚合，炸裂函數
select team_name_1, team_name_2
from (select team_name                                                                               team_name_1,collect_list(team_name)over (order by team_name rows between 1 following and unbounded following) team_listfrom team) t2 lateral view explode(team_list) team_list as team_name_2;

49 視頻熱度問題

-- 結果(但是不符合題目意思)
select video_id,cast(ceil((whole / total + up + comment + retweet) / (datediff(today, max_dt) + 1)) as decimal(16, 1)) heat
from (select video_id,today,max(dt)                                 max_dt,count(*)                                total,sum(if(l.ts >= i.duration, 1, 0)) * 100 whole,sum(l.if_like) * 5                      up,count(l.comment_id) * 3                 comment,sum(l.if_retweet) * 2                   retweetfrom (select video_id,unix_timestamp(end_time) - unix_timestamp(start_time) ts,to_date(end_time)                                     dt,to_date(max(end_time) over (partition by video_id))   today,if_like,comment_id,if_retweetfrom user_video_log) ljoin video_info i on i.video_id = l.video_idwhere l.dt <= l.todayand l.dt >= date_sub(l.today, 29)group by l.video_id, today) t1
order by heat
limit 3;-- 題目意思
select video_id,cast(((whole / total + up + comment + retweet) / fresh) as decimal(16, 2)) heat
from (select video_id,30 - count(distinct dt) + 1             fresh,count(*)                                total,sum(if(l.ts >= i.duration, 1, 0)) * 100 whole,sum(l.if_like) * 5                      up,count(l.comment_id) * 3                 comment,sum(l.if_retweet) * 2                   retweetfrom (select video_id,unix_timestamp(end_time) - unix_timestamp(start_time) ts,to_date(end_time)                                     dt,to_date(max(end_time) over ())                        today,if_like,comment_id,if_retweetfrom user_video_log) ljoin video_info i on i.video_id = l.video_idwhere l.dt <= l.todayand l.dt >= date_sub(l.today, 29)group by l.video_id) t1
order by heat
limit 3;

50 員工在職人數問題

select mth,cast(sum(num) as decimal(16,2)) ps from
(select month(dt) mth,id,sum(if((dt >= en_dt and dt <= le_dt)or (dt >= en_dt and le_dt is null),1,0))/count(*) num
from cal joinemp
where dt < '2020-04-01'and dt >= '2020-01-01' group by month(dt),id) t2 group by mth;