关注+星标，干货第一时间送达
前面插录了几篇 Shell 脚本：
Shell 脚本案例【3】 Mysql批量导入、Shell 脚本案例【2】Mysql批量导出、
和 Python：
Python机器学习【1】数据集、Python机器学习【2】字典特征提取
相关的内容。
想的是丰富知识，扩充装备库吧。
其实，作为一名程序员，
各种语言、各种框架、各种代码，
都是要去了解掌握的。
喜欢大家能够有所收获，并且喜欢。
如果觉得小编的分享还不错，
别忘记点赞嗷
今天，做一道简单的SQL题目。
【温馨提示：建表语句及数据导入脚本，已经放在文章末尾】
一、需求场景
题目：
从订单明细表order_detail中，根据商品的销售数量进行分类。
销售量 0 ~ 5000 为冷门商品，
销售量 5001 ~ 19999 为一般商品，
销售量 20000 以上为热门商品。
结果展示为统计不同类别商品的销量。
订单明细表order_detail：
正确结果：
二、解决方案
演示工具：Hive-3.1.3 + DataGrip2022
题意清晰明了，没有弯弯绕绕，
直接开动。
（1）按照商品编号sku_id分组，统计每件商品的销量和
select sku_id,
sum(sku_num) as cnt
from order_detail
group by sku_id
结果集：
（2）根据每个商品销量的大小，进行分类
select
case when cnt > 20000 then '热门'
when cnt > 5000 then '一般'
else '冷门'
end as category
from (
select sku_id,
sum(sku_num) as cnt
from order_detail
group by sku_id
)t1
结果集：
如果这里不使用 case when，
也可以替换为 if 判断，
那么，代码就是这样：
select
sku_id,
`if`(cnt>20000, '热门', `if`(cnt>5000, '一般', '冷门')) as category
from (
select sku_id,
sum(sku_num) as cnt
from order_detail
group by sku_id
)t1
（3）按照分类分组，计算每个分类的销量和
select category,
count(*) as num
from (
select
case when cnt > 20000 then '热门'
when cnt > 5000 then '一般'
else '冷门'
end as category
from (
select sku_id,
sum(sku_num) as cnt
from order_detail
group by sku_id
)t1
)t2
group by category
结果集：
三、源数据
订单明细表order_detail：
create table order_detail
(
order_detail_id string,
order_id string,
sku_id string,
create_date string,
price decimal(16, 2),
sku_num int
)
row format serde 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
with serdeproperties ('field.delim' = '\t') stored as
inputformat 'org.apache.hadoop.mapred.TextInputFormat'
outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
location 'hdfs://mycluster/user/hive/warehouse/hql_test2.db/order_detail'
tblproperties ('bucketing_version' = '2');
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('1', '1', '1', '2021-09-27', 2000.00, 2);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('2', '1', '3', '2021-09-27', 5000.00, 5);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('3', '2', '4', '2021-09-28', 6000.00, 9);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('4', '2', '5', '2021-09-28', 500.00, 33);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('5', '3', '7', '2021-09-29', 100.00, 37);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('6', '3', '8', '2021-09-29', 600.00, 46);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('7', '3', '9', '2021-09-29', 1000.00, 12);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('8', '4', '12', '2021-09-30', 20.00, 43);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('9', '5', '1', '2021-10-01', 2000.00, 8);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('10', '5', '2', '2021-10-01', 10.00, 18);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('11', '5', '3', '2021-10-01', 5000.00, 6);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('12', '6', '4', '2021-10-01', 6000.00, 8);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('13', '6', '6', '2021-10-01', 2000.00, 1);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('14', '7', '7', '2021-10-01', 100.00, 17);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('15', '7', '8', '2021-10-01', 600.00, 48);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('16', '7', '9', '2021-10-01', 1000.00, 45);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('17', '8', '10', '2021-10-02', 100.00, 48);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('18', '8', '11', '2021-10-02', 50.00, 15);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('19', '8', '12', '2021-10-02', 20.00, 31);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('20', '9', '1', '2021-09-30', 2000.00, 9);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('21', '9', '2', '2021-10-02', 10.00, 5800);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('22', '10', '4', '2021-10-02', 6000.00, 1);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('23', '10', '5', '2021-10-02', 500.00, 24);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('24', '10', '6', '2021-10-02', 2000.00, 5);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('25', '11', '8', '2021-10-02', 600.00, 39);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('26', '12', '10', '2021-10-03', 100.00, 47);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('27', '12', '11', '2021-10-03', 50.00, 19);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('28', '12', '12', '2021-10-03', 20.00, 13000);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('29', '13', '1', '2021-10-03', 2000.00, 4);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('30', '13', '3', '2021-10-03', 5000.00, 1);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('31', '14', '4', '2021-10-03', 6000.00, 5);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('32', '14', '5', '2021-10-03', 500.00, 47);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('33', '14', '6', '2021-10-03', 2000.00, 8);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('34', '15', '7', '2021-10-03', 100.00, 20);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('35', '16', '10', '2021-10-03', 100.00, 22);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('36', '16', '11', '2021-10-03', 50.00, 42);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('37', '16', '12', '2021-10-03', 20.00, 7400);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('38', '17', '1', '2021-10-04', 2000.00, 3);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('39', '17', '2', '2021-10-04', 10.00, 21);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('40', '18', '4', '2021-10-04', 6000.00, 8);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('41', '18', '5', '2021-10-04', 500.00, 28);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('42', '18', '6', '2021-10-04', 2000.00, 3);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('43', '19', '7', '2021-10-04', 100.00, 55);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('44', '19', '8', '2021-10-04', 600.00, 11);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('45', '19', '9', '2021-10-04', 1000.00, 31);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('46', '20', '11', '2021-10-04', 50.00, 45);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('47', '20', '12', '2021-10-04', 20.00, 27);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('48', '21', '1', '2021-10-04', 2000.00, 2);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('49', '21', '2', '2021-10-04', 10.00, 39);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('50', '21', '3', '2021-10-04', 5000.00, 1);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('51', '22', '4', '2021-10-05', 6000.00, 8);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('52', '22', '5', '2021-10-05', 500.00, 20);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('53', '23', '7', '2021-10-05', 100.00, 58);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('54', '23', '8', '2021-10-05', 600.00, 18);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('55', '23', '9', '2021-10-05', 1000.00, 30);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('56', '24', '10', '2021-10-05', 100.00, 27);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('57', '24', '11', '2021-10-05', 50.00, 28);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('58', '24', '12', '2021-10-05', 20.00, 53);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('59', '25', '1', '2021-10-05', 2000.00, 5);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('60', '25', '2', '2021-10-05', 10.00, 35);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('61', '25', '3', '2021-10-05', 5000.00, 9);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('62', '26', '4', '2021-10-05', 6000.00, 1);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('63', '26', '5', '2021-10-05', 500.00, 13);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('64', '26', '6', '2021-10-05', 2000.00, 1);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('65', '27', '7', '2021-10-06', 100.00, 30);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('66', '27', '8', '2021-10-06', 600.00, 19);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('67', '27', '9', '2021-10-06', 1000.00, 33);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('68', '28', '10', '2021-10-06', 100.00, 37);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('69', '28', '11', '2021-10-06', 50.00, 46);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('70', '28', '12', '2021-10-06', 20.00, 45);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('71', '29', '1', '2021-10-06', 2000.00, 8);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('72', '29', '2', '2021-10-06', 10.00, 57);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('73', '29', '3', '2021-10-06', 5000.00, 8);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('74', '30', '4', '2021-10-06', 6000.00, 3);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('75', '30', '5', '2021-10-06', 500.00, 33);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('76', '30', '6', '2021-10-06', 2000.00, 5);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('77', '31', '8', '2021-10-07', 600.00, 13);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('78', '31', '9', '2021-10-07', 1000.00, 43);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('79', '32', '10', '2021-10-07', 100.00, 24);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('80', '32', '11', '2021-10-07', 50.00, 30);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('81', '33', '1', '2021-10-07', 2000.00, 8);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('82', '33', '2', '2021-10-07', 10.00, 48);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('83', '33', '3', '2021-10-07', 5000.00, 5);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('84', '34', '4', '2021-10-07', 6000.00, 10);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('85', '34', '5', '2021-10-07', 500.00, 44);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('86', '34', '6', '2021-10-07', 2000.00, 3);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('87', '35', '8', '2020-10-08', 600.00, 25);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('88', '36', '10', '2020-10-08', 100.00, 57);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('89', '36', '11', '2020-10-08', 50.00, 44);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('90', '36', '12', '2020-10-08', 20.00, 56);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('91', '37', '1', '2020-10-08', 2000.00, 2);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('92', '37', '2', '2020-10-08', 10.00, 26);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('93', '37', '3', '2020-10-08', 5000.00, 1);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('94', '38', '6', '2020-10-08', 2000.00, 6);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('95', '39', '7', '2020-10-08', 100.00, 35);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('96', '39', '8', '2020-10-08', 600.00, 34);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('97', '40', '10', '2020-10-08', 100.00, 37);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('98', '40', '11', '2020-10-08', 50.00, 51);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('99', '40', '12', '2020-10-08', 20.00, 27);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('100', '41', '15', '2020-10-08', 300.00, 15);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('101', '42', '13', '2021-01-01', 260.00, 13);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('102', '43', '13', '2021-01-02', 280.00, 14);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('103', '44', '14', '2021-01-03', 420.00, 21);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('104', '45', '14', '2021-01-04', 240.00, 12);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('105', '46', '14', '2021-09-26', 240.00, 12);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('106', '47', '14', '2021-10-24', 240.00, 12);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('107', '48', '14', '2022-09-24', 240.00, 12);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('108', '49', '1', '2022-09-24', 2000.00, 1);
INSERT INTO hql_test2.order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('109', '49', '2', '2022-09-24', 10.00, 1);
OK，这就是本期的内容了，下期再见！
文章转载自皮皮克克，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
