Hive sql 进阶题 03

用户注册、登录、下单综合统计

从用户登录明细表（user_login_detail）和订单信息表（order_info）中查询每个用户的注册日期（首次登录日期）、总登录次数以及其在2021年的登录次数、订单数和订单总额。

select t1.user_id, register_date, cnt, cnt2021, order_count_2021, order_amount_2021 from (select user_id, min(date_format(login_ts, 'yyyy-MM-dd')) register_date, count(*) cnt, count(`if`(year(login_ts) = 2021, 1, null)) cnt2021 from user_login_detail group by user_id) t1 left join (select user_id, count(`if`(year(create_date) = 2021, 1, null)) order_count_2021, sum(`if`(year(create_date) = 2021, total_amount, 0)) order_amount_2021 from order_info group by user_id) t2 on t1.user_id = t2.user_id;

查询指定日期的全部商品价格

从商品价格修改明细表（sku_price_modify_detail）中查询2021-10-01的全部商品的价格，假设所有商品初始价格默认都是99。

select sku_info.sku_id, nvl(new_price, 99) price from sku_info left join ( select sku_id, new_price from ( select sku_id, new_price, change_date, row_number() over (partition by sku_id order by change_date desc) rn from sku_price_modify_detail where change_date <= '2021-10-01' ) t1 where rn = 1 ) t2 on sku_info.sku_id = t2.sku_id;

即时订单比例

订单配送中，如果期望配送日期和下单日期相同，称为即时订单，如果期望配送日期和下单日期不同，称为计划订单。

请从配送信息表（delivery_info）中求出每个用户的首单（用户的第一个订单）中即时订单的比例，保留两位小数，以小数形式显示。

select round(count(`if`(order_date = custom_date, 1, null)) / count(*), 2) percentage from (select user_id, order_date, custom_date, row_number() over (partition by user_id order by order_date) rn from delivery_info) t1 where rn = 1;

向用户推荐朋友收藏的商品

现需要请向所有用户推荐其朋友收藏但是用户自己未收藏的商品，请从好友关系表（friendship_info）和收藏表（favor_info）中查询出应向哪位用户推荐哪些商品。

select distinct t1.user_id, friend_favor.sku_id from ( select user1_id user_id, user2_id friend_id from friendship_info union select user2_id, user1_id from friendship_info ) t1 left join favor_info friend_favor on t1.friend_id = friend_favor.user_id left join favor_info user_favor on t1.user_id = user_favor.user_id and friend_favor.sku_id = user_favor.sku_id where user_favor.sku_id is null;

查询所有用户的连续登录两天及以上的日期区间

登录明细表（user_login_detail）中查询出，所有用户的连续登录两天及以上的日期区间，以登录时间（login_ts）为准。

select user_id, min(login_date) start_date, max(login_date) end_date from ( select user_id, login_date, date_sub(login_date, rn) flag from ( select user_id, login_date, row_number() over (partition by user_id order by login_date) rn from ( select user_id, date_format(login_ts, 'yyyy-MM-dd') login_date from user_login_detail group by user_id, date_format(login_ts, 'yyyy-MM-dd') ) t1 ) t2 ) t3 group by user_id, flag having count(*) >= 2;