HQL 55 题【已完结】（3）https://developer.aliyun.com/article/1532399
7、男性和女性每日的购物总金额统计
需求：从订单信息表（order_info）和用户信息表（user_info）中，分别统计每天男性和女性用户的订单总金额，如果当天男性或者女性没有购物，则统计结果为0。
SELECT create_date, sum(IF(gender='男',total_amount,0)) total_amount_male, sum(IF(gender='女',total_amount,0)) total_amount_female FROM ( SELECT oi.user_id,gender,total_amount,create_date FROM order_info oi LEFT JOIN user_info ui ON oi.user_id=ui.user_id )t1 GROUP BY create_date;
或者
SELECT create_date, SUM(CASE WHEN gender = '男' THEN total_amount ELSE 0 END) AS total_amount_male, SUM(CASE WHEN gender = '女' THEN total_amount ELSE 0 END) AS total_amount_female FROM ( SELECT oi.user_id, ui.gender, oi.total_amount, oi.create_date FROM order_info oi LEFT JOIN user_info ui ON oi.user_id = ui.user_id ) t1 GROUP BY create_date;
3-27
1、购买过商品1和商品2但是没有购买商品3的顾客
SELECT user_id FROM ( SELECT user_id, sum(IF(sku_id IN (1,2),1,0)) sum_num, MAX(CASE WHEN sku_id = 3 THEN 1 ELSE 0 END) AS has_sku_3 FROM ( SELECT DISTINCT od.sku_id,oi.user_id FROM order_detail od JOIN order_info oi ON od.order_id=oi.order_id )t1 GROUP BY user_id )t2 WHERE has_sku_3=0 AND sum_num=2;
判断是否包含 sku_id = 3 的记录不能使用功能 if，这里用的是 MAX(CASE WHEN) 新的思路，需要好好记住！
2、订单金额趋势分析
SELECT create_date, cast(sum(total_amount) over(partition by ds rows between 2 preceding and current ROW) as decimal(16,2)) total_3d, cast(avg(total_amount) over(partition by ds rows between 2 preceding and current row) as decimal(16,2)) avg_3d FROM ( SELECT t1.create_date, total_amount, date_sub(create_date,row_number() over(order by create_date)) ds FROM ( SELECT create_date, sum(total_amount) total_amount FROM order_info group by create_date )t1 )t2;
3-29
1、统计每日商品1和商品2销量的差值
SELECT coalesce(t1.create_date,t2.create_date) create_date, IF(t1.sku_num is null,0,t1.sku_num)-IF(t2.sku_num is null,0,t2.sku_num) diff FROM ( SELECT create_date, sum(sku_num) sku_num FROM order_detail WHERE sku_id='1' GROUP BY create_date )t1 FULL OUTER JOIN ( SELECT create_date, sum(sku_num) sku_num FROM order_detail WHERE sku_id='2' GROUP BY create_date )t2 ON t1.create_date=t2.create_date;
2、查询出每个用户的最近三笔订单
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) rk FROM order_info )t1 WHERE rk<=3;
3、查询每个用户登录日期的最大空档期
SELECT user_id, max(diff) max_diff FROM ( SELECT user_id, datediff(date_format(lead(login_ts,1,'2021-10-10') OVER(PARTITION BY user_id ORDER BY login_ts),'yyyy-MM-dd'),date_format(login_ts,'yyyy-MM-dd')) diff FROM user_login_detail )t1 GROUP BY user_id;
4、查询统一时刻多地登录的用户
SELECT DISTINCT user_id FROM ( SELECT user_id, if(ip != next_ip AND login_ts <= next_login_ts,1,0) status FROM ( SELECT user_id, ip_address ip, login_ts, lead(ip_address,1) OVER(PARTITION BY user_id ORDER BY login_ts) next_ip, lead(login_ts,1) OVER(PARTITION BY user_id ORDER BY login_ts) next_login_ts FROM user_login_detail )t1 )t2 WHERE t2.status=1;
5、各品类销量前三的所有商品
SELECT sku_id,category_id FROM ( SELECT t1.sku_id, sum_num, si.category_id, row_number() OVER(PARTITION BY si.category_id ORDER BY sum_num DESC) rk FROM ( SELECT sku_id,sum(sku_num) sum_num FROM order_detail GROUP BY sku_id )t1 JOIN sku_info si ON si.sku_id=t1.sku_id )t2 WHERE rk<=3;
4-9
网站崩了几天，所以没更。
1、各品类中的商品价格中位数
需求：从商品信息表（sku_info）求出各分类商品价格的中位数，如果一个分类下的商品个数为偶数则输出中间两个值的平均值，如果是奇数，则输出中间数即可。
思路1
SELECT category_id, CAST(sum(price)/count(*) AS DECIMAL(16,2)) medprice FROM ( SELECT category_id, price, rk, max(rk) OVER(PARTITION BY category_id) max_rk FROM ( SELECT category_id, price, row_number() OVER(PARTITION BY category_id ORDER BY price) rk FROM sku_info )t1 )t2 WHERE rk IN (ceil((max_rk+1)/2),floor((max_rk+1)/2)) GROUP BY category_id;
思路2
select category_id, cast(avg(price) as DECIMAL(10,2)) medprice from ( select *, ceil(count / 2) mid, `if`(count % 2 = 0, 1, 0) flag from ( select sku_id, category_id, price, row_number() over (partition by category_id order by price) rn, count(1) over (partition by category_id) count from sku_info )t )t1 where rn = mid or rn = mid + flag group by category_id;
4-10
1、找出销售额连续3天超过100的商品
需求：从订单详情表（order_detail）中找出销售额连续3天超过100的商品。
SELECT DISTINCT sku_id FROM ( SELECT sku_id, create_date, row_number() OVER(PARTITION BY sku_id ORDER BY create_date) rk FROM GROUP BY sku_id,create_date HAVING sum(price*sku_num)>100 )t1 GROUP BY sku_id,date_sub(create_date,rk) HAVING count(*)>2;
子表 t1 中的 GROUP BY sku_id,create_date 的作用是对一天多个订单进行去重。
2、查询所有用户的连续登录两天及以上的日期区间
需求：从登录明细表（user_login_detail）中查询出，所有用户的连续登录两天及以上的日期区间，以登录时间（login_ts）为准。
SELECT user_id, min(login_date) start_date, max(login_date) end_date FROM ( SELECT user_id, login_date, row_number() OVER(PARTITION BY user_id ORDER BY login_date) rk FROM ( SELECT DISTINCT user_id,date_format(login_ts,'yyyy-MM-dd') login_date FROM user_login_detail )t1 )t2 GROUP BY user_id,date_sub(login_date,rk) HAVING count(*)>1;
3、求出商品连续售卖的时间区间
需求：从订单详情表（order_detail）中，求出商品连续售卖的时间区间（1天也算连续）
SELECT sku_id, min(create_date) start_date, max(create_date) end_date FROM ( SELECT sku_id, create_date, row_number() OVER(PARTITION BY sku_id ORDER BY create_date) rk FROM ( SELECT DISTINCT sku_id,create_date FROM order_detail )t1 )t2 GROUP BY sku_id,date_sub(create_date,rk) HAVING count(*)>0;
HQL 55 题【已完结】（5）https://developer.aliyun.com/article/1532401
