查询销售件数高于品类平均数的商品
从订单明细表（order_detail）中查询累积销售件数高于其所属品类平均数的商品，期望结果如下：
sku_id |
name |
sum_num |
cate_avg_num |
2 |
手机壳 |
302 |
110.5 |
5 |
破壁机 |
242 |
194.75 |
7 |
热水壶 |
252 |
194.75 |
8 |
微波炉 |
253 |
194.75 |
10 |
帐篷 |
299 |
290.5 |
11 |
烧烤架 |
320 |
290.5 |
12 |
遮阳伞 |
349 |
290.5 |
select sku_id, name, sum_num, cate_avg_num from (select t1.sku_id,category_id,name,sum_num, avg(sum_num) over (partition by category_id ) cate_avg_num from (select sku_id,sum(sku_num) sum_num from order_detail1 group by sku_id)t1 left join( select sku_id,name,category_id from sku_info1 )sku on t1.sku_id=sku.sku_id)t2 where sum_num>cate_avg_num
用户注册、登录、下单综合统计
从用户登录明细表（user_login_detail）和订单信息表（order_info）中查询每个用户的注册日期（首次登录日期）、总登录次数以及其在2021年的登录次数、订单数和订单总额。期望结果如下：
user_id (用户id) |
register_date (注册日期) |
total_login_count (累积登录次数) |
login_count_2021 (2021年登录次数) |
order_count_2021 (2021年下单次数) |
order_amount_2021 (2021年订单金额) |
101 |
2021-09-21 |
5 |
5 |
4 |
143660.00 |
102 |
2021-09-22 |
4 |
4 |
4 |
177850.00 |
103 |
2021-09-23 |
2 |
2 |
4 |
75890.00 |
104 |
2021-09-24 |
4 |
4 |
4 |
89880.00 |
105 |
2021-10-04 |
1 |
1 |
4 |
120100.00 |
106 |
2021-10-04 |
2 |
2 |
4 |
119150.00 |
107 |
2021-09-25 |
4 |
4 |
4 |
124150.00 |
108 |
2021-10-06 |
2 |
2 |
4 |
155770.00 |
109 |
2021-09-26 |
3 |
3 |
4 |
153500.00 |
1010 |
2021-09-27 |
2 |
2 |
4 |
51950.00 |
select login.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, count(1) total_login_count, count(if(year(login_ts) = '2021', 1, null)) login_count_2021 from user_login_detail group by user_id ) login join ( select user_id, count(distinct(order_id)) order_count_2021, sum(total_amount) order_amount_2021 from order_info where year(create_date) = '2021' group by user_id ) oi on login.user_id = oi.user_id;
查询指定日期的全部商品价格
查询所有商品（sku_info表）截至到2021年10月01号的最新商品价格（需要结合价格修改表进行分析）
关键点：在10月1号之前改价不可能只有一次，所以要取得最新的一次价格
select sku_info1.sku_id, nvl(new_price, 99) price from sku_info1 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_detail1 where change_date <= '2021-10-01' ) t1 where rn = 1 ) t2 on sku_info1.sku_id = t2.sku_id;
