点击关注公众号，干货第一时间送达
不知诸位熟悉Hive 吗？
这题小编用的是HQL编写的，
其实，和SQL区别不大。
建表语句会有点区别，
问题不大。
【温馨提示：建表语句及数据导入脚本，已经放在了文章末尾】
一、需求场景
题目：
请查询累积销量排名第二的商品。
如果存在多个排名第二的商品，则全部返回；
如果不存在排名第二的商品，则返回 null；
需要用的是两张表：
order_detail（订单详情表）
sku_info（商品信息表）
正确的结果是：
二、解决方案
演示工具：Hive-3.1.3 + DataGrip2022
商品的累积销量排名，
其实很好计算，
通过"开窗排序"即可。
但是，当不存在的时候，返回 null，
其实有点意思。
（1）分组计算每个商品的累积销量
select
sku_id,
sum(sku_num) as cnt
from order_detail
group by sku_id
结果集：
（2）按照销量，对商品进行排名
select t1.sku_id,
t1.cnt,
dense_rank() over(order by t1.cnt desc) as rk
from (
select
sku_id,
sum(sku_num) as cnt
from order_detail
group by sku_id
)t1
结果集：
关于：
rank()、dense_rank()、row_number()
这三个 "开窗排序" 的使用，
前面的文章：xxxxx
有详细介绍，感兴趣的小伙伴可以点击翻看一下。
（3）筛选排名第二的商品
select t2.sku_id, sk.name,t2.cnt
from (
select t1.sku_id,
t1.cnt,
dense_rank() over(order by t1.cnt desc) as rk
from (
select
sku_id,
sum(sku_num) as cnt
from order_detail
group by sku_id
)t1
)t2
join sku_info sk
on t2.sku_id = sk.sku_id
where rk=2
结果集：
到此，可能有的小伙伴觉得就结束了。
但是要记得，题目要求的是如果数据不存在，
需要返回 null。
如果不做处理，数据不存在的时候，返回的是空，
例如：
如果我们要查询不存在的数据：
因此，我们可以利用 right join 进行 null 处理：
（4）right join，处理 null 值显示
select t3.sku_id, t3.name, t3.cnt
from (
select t2.sku_id, sk.name,t2.cnt
from (
select t1.sku_id,
t1.cnt,
dense_rank() over(order by t1.cnt desc) as rk
from (
select
sku_id,
sum(sku_num) as cnt
from order_detail
group by sku_id
)t1
)t2
join sku_info sk
on t2.sku_id = sk.sku_id
where rk=2
)t3
right join (select 1) t4
on 1=1;
结果集：
三、源数据
order_info:
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/db/order_detail'
tblproperties ('bucketing_version' = '2');
INSERT INTO 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 order_detail (order_detail_id, order_id, sku_id, create_date, price, sku_num) VALUES ('109', '49', '2', '2022-09-24', 10.00, 1);
sku_info：
create table sku_info
(
sku_id string,
name string,
category_id string,
from_date string,
price double
)
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/db/sku_info'
tblproperties ('bucketing_version' = '2');
INSERT INTO sku_info (sku_id, name, category_id, from_date, price) VALUES ('1', 'xiaomi 10', '1', '2020-01-01', 2000);
INSERT INTO sku_info (sku_id, name, category_id, from_date, price) VALUES ('2', '手机壳', '1', '2020-02-01', 10);
INSERT INTO sku_info (sku_id, name, category_id, from_date, price) VALUES ('3', 'apple 12', '1', '2020-03-01', 5000);
INSERT INTO sku_info (sku_id, name, category_id, from_date, price) VALUES ('4', 'xiaomi 13', '1', '2020-04-01', 6000);
INSERT INTO sku_info (sku_id, name, category_id, from_date, price) VALUES ('5', '破壁机', '2', '2020-01-01', 500);
INSERT INTO sku_info (sku_id, name, category_id, from_date, price) VALUES ('6', '洗碗机', '2', '2020-02-01', 2000);
INSERT INTO sku_info (sku_id, name, category_id, from_date, price) VALUES ('7', '热水壶', '2', '2020-03-01', 100);
INSERT INTO sku_info (sku_id, name, category_id, from_date, price) VALUES ('8', '微波炉', '2', '2020-04-01', 600);
INSERT INTO sku_info (sku_id, name, category_id, from_date, price) VALUES ('9', '自行车', '3', '2020-01-01', 1000);
INSERT INTO sku_info (sku_id, name, category_id, from_date, price) VALUES ('10', '帐篷', '3', '2020-02-01', 100);
INSERT INTO sku_info (sku_id, name, category_id, from_date, price) VALUES ('11', '烧烤架', '3', '2020-02-01', 50);
INSERT INTO sku_info (sku_id, name, category_id, from_date, price) VALUES ('12', '遮阳伞', '3', '2020-03-01', 20);
INSERT INTO sku_info (sku_id, name, category_id, from_date, price) VALUES ('13', '长粒香', '2', '2020-01-01', 20);
INSERT INTO sku_info (sku_id, name, category_id, from_date, price) VALUES ('14', '金龙鱼', '2', '2021-01-01', 20);
INSERT INTO sku_info (sku_id, name, category_id, from_date, price) VALUES ('15', '巧乐兹', '2', '2020-01-01', 20);
INSERT INTO sku_info (sku_id, name, category_id, from_date, price) VALUES ('16', '费列罗', '2', '2022-01-01', 20);
文章转载自皮皮克克，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
