HQL 55 题【已完结】（2）https://developer.aliyun.com/article/1532398
3-22
1、查询至少连续三天下单的用户
SELECT DISTINCT user_id FROM ( SELECT user_id,datediff(ld,create_date) diff FROM ( SELECT user_id, create_date, lead(create_date,2,'9999-12-31') OVER (PARTITION BY user_id ORDER BY create_date) ld FROM ( -- 查询用户的所有下单日期 SELECT DISTINCT user_id,create_date FROM order_info )t1 )t2 )t3 WHERE diff=2;
知识点：借助窗口函数 lead 向后两行进行取值，并通过计算每个下单日期和后两行的差值来过滤出连续下单超3天的用户。
2024-4-10：这个思路是存在问题的：如果有人连续5天10天下单那他必然是连续3天下单的，但是diff>2，但是这里如果条件修改为 diff>=2 ，那么即使不连续，它的 diff 也会大于2；
标准答案
SELECT user_id FROM ( SELECT user_id, create_date, row_number() OVER(PARTITION BY user_id ORDER BY create_date) rk FROM ( SELECT DISTINCT user_id,create_date FROM order_info -- 防止用户一天多次下单 )t0 )t1 GROUP BY user_id,date_sub(create_date,rk) HAVING count(*)>2;
2、查询各品类销售商品的种类数及销量最高的商品
SELECT t3.category_id, t3.category_name, t3.sku_cnt, t3.name, t3.sku_id, t3.order_num from ( SELECT t1.*, t2.order_num, row_number() over ( PARTITION BY t1.category_id ORDER BY t2.order_num desc ) rk FROM ( -- 商品的分类名 SELECT ci.category_id, category_name, sku_id, name, sku_cnt FROM category_info ci JOIN sku_info si ON ci.category_id = si.category_id LEFT JOIN ( SELECT category_id, count(sku_id) sku_cnt FROM sku_info GROUP BY category_id ) t ON t.category_id = si.category_id ) t1 LEFT JOIN ( SELECT sku_id, sum(sku_num) order_num FROM order_detail GROUP BY sku_id ) t2 ON t1.sku_id = t2.sku_id ) t3 WHERE rk = 1;
3、查询用户的累计消费金额及VIP等级
SELECT t1.*, CASE WHEN 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(sum_amount) OVER(PARTITION BY user_id ORDER BY create_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_so_far FROM ( SELECT user_id, create_date, sum(total_amount) sum_amount FROM order_info GROUP BY user_id,create_date )t )t1;
4、查询首次下单后第二天连续下单的用户比率
SELECT concat(round(count(DISTINCT user_id)/10*100,1),'%') percentage FROM ( SELECT t1.user_id, datediff(ld,create_date) diff, t2.cnt from ( SELECT user_id, create_date, lead(create_date,1,'9999-12-31') OVER(PARTITION BY user_id ORDER BY create_date) ld FROM order_info GROUP BY user_id,create_date )t1 JOIN (SELECT count(DISTINCT user_id) cnt FROM order_info)t2 )t3 WHERE diff=1;
3-24
1、筛选去年总销量小于100的商品
需求：从订单明细表(order_detail)中筛选出去年总销量小于100的商品及其销量，假设今天的日期是2022-01-10，不考虑上架时间小于一个月的商品
SELECT t1.sku_id,t2.name,t1.order_num FROM ( SELECT sku_id, sum(sku_num) order_num FROM order_detail od WHERE date_format(create_date,'yyyy')='2021' GROUP BY sku_id )t1 RIGHT JOIN ( SELECT sku_id, name FROM sku_info WHERE datediff('2022-01-10',from_date)>30 )t2 ON t1.sku_id=t2.sku_id WHERE t1.order_num<100;
3-25、
1、向用户推荐朋友收藏的商品
SELECT DISTINCT t1.user1_id user_id,t2.sku_id from friendship_info t1 JOIN favor_info t2 on t1.user2_id=t2.user_id where concat(t1.user1_id,t2.sku_id) not in ( SELECT concat(user_id,sku_id) from favor_info );
3-26
1、统计每个商品的销量最高的日期
SELECT sku_id,create_date,sum_num FROM ( SELECT t1.*, row_number() over(PARTITION BY sku_id ORDER BY sum_num DESC) rk FROM ( SELECT sku_id, create_date, sum(sku_num) sum_num FROM order_detail GROUP BY sku_id,create_date )t1 )t2 WHERE rk=1;
2、查询每日新用户数
SELECT login_date_first,count(*) user_count FROM ( SELECT user_id, min(date_format(login_ts,'yyyy-MM-dd')) login_date_first FROM user_login_detail GROUP BY user_id )t1 GROUP BY login_date_first;
3、查询销售件数高于品类平均数的商品
SELECT t2.sku_id,t2.name,t2.sum_num,t4.cate_avg_num FROM ( SELECT od.sku_id,name,category_id,sum(sku_num) sum_num FROM order_detail od JOIN sku_info si ON od.sku_id=si.sku_id GROUP BY category_id,od.sku_id,name )t2 LEFT JOIN ( -- 各品类的总销量 SELECT category_id,CAST (sum(sku_num)/count(DISTINCT sku_id) AS int) cate_avg_num FROM ( SELECT od.*,si.category_id FROM order_detail od JOIN sku_info si ON od.sku_id=si.sku_id )t3 GROUP BY category_id )t4 ON t2.category_id=t4.category_id WHERE t2.sum_num>t4.cate_avg_num;
注意：这里犯了个错，join 操作的时候直接在外层指定投影字段，没必要里边再查询子表的部分字段，比如上面的子表 t2 原本是这样的：
SELECT od.sku_id,name,category_id,sum(sku_num) sum_num FROM order_detail od JOIN ( SELECT si.sku_id,si.category_id FROM sku_info si ON od.sku_id=si.sku_id )t1 GROUP BY category_id,od.sku_id,name
完全可以这样：
SELECT od.sku_id,name,category_id,sum(sku_num) sum_num FROM order_detail od JOIN sku_info si ON od.sku_id=si.sku_id GROUP BY category_id,od.sku_id,name
其实效果是差不多的，但是上面的让人读的时候思路更加清晰，本来嵌套查询就够乱的了。
4、用户注册、登录、下单综合统计
SELECT DISTINCT t1.user_id,t1.register_date,t2.total_login_count,t3.login_count_2021,t4.order_count_2021,t5.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 LEFT JOIN ( -- 查询用户总登录次数 SELECT user_id,count(*) total_login_count FROM user_login_detail GROUP BY user_id )t2 ON t1.user_id=t2.user_id LEFT JOIN ( -- 查询用户2021年的登录次数 SELECT user_id,count(*) login_count_2021 FROM user_login_detail WHERE year(login_ts)='2021' GROUP BY user_id )t3 ON t2.user_id=t3.user_id LEFT JOIN ( -- 2021年下单次数 SELECT user_id,count(*) order_count_2021 FROM order_info WHERE year(create_date)='2021' GROUP BY user_id )t4 ON t3.user_id=t4.user_id LEFT JOIN ( -- 2021年下单金额 SELECT user_id,sum(total_amount) order_amount_2021 FROM order_info WHERE year(create_date)='2021' GROUP BY user_id )t5 ON t4.user_id=t5.user_id WHERE t4.order_count_2021 IS NOT NULL;
5、查询指定日期的全部商品价格
需求：查询所有商品（sku_info表）截至到2021年10月01号的最新商品价格（需要结合价格修改表进行分析）
SELECT si.sku_id,CAST(coalesce(t2.new_price,price) AS DECIMAL(16,2)) price FROM sku_info si LEFT JOIN ( -- 查询截止2021-10-01修改的商品id SELECT sku_id,new_price FROM ( SELECT sku_id, new_price, row_number() over(PARTITION BY sku_id ORDER BY change_date DESC) rk FROM sku_price_modify_detail WHERE datediff('2021-10-01',change_date)>=0 )t1 WHERE rk=1 )t2 ON si.sku_id=t2.sku_id;
6、及时订单比例
SELECT CAST(sum(IF(t2.first_order_date=t2.custom_date,1,0))/count(*) AS DECIMAL(16,2)) percentage FROM ( -- 查询所有用户的首单日期和期望配送日期 SELECT DISTINCT t1.user_id,t1.first_order_date,di.custom_date FROM ( SELECT user_id, min(order_date) first_order_date FROM delivery_info GROUP BY user_id )t1 LEFT JOIN delivery_info di ON di.user_id=t1.user_id AND t1.first_order_date=di.order_date )t2;
HQL 55 题【已完结】（4）https://developer.aliyun.com/article/1532400
