SQL题：使用hive查询各类型专利top 10申请人，以及对应的专利申请数

一、题目以及介绍

题目介绍：

1、表名：t_patent_detail （专利明细表）

2、表字段：专利号(patent_id)、专利名称(patent_name)、专利类型(patent_type)、申请时间(aplly_date)、授权时间(authorize_date)、申请人(apply_users)

3、说明：同一个专利，可以有1到多个申请人，多人之间按分号隔开。本表记录数约1万条。例如：

问题：请写出hive查询语句，各类型专利top 10申请人，以及对应的专利申请数

二、解答 

1、创建表

create table t_patent_detail(patent_id string,patent_name string,patent_type string,aplly_date string,authorize_date string,apply_users string
);
INSERT INTO t_patent_detail (patent_id, patent_name, patent_type, aplly_date, authorize_date, apply_users)
VALUES
('CN201821413799.7', '一种用于合金板棒材的往复式感应加热设备', '发明创造', '2018-08-30', '2019-08-09', '朱春野'),
('CN201911153500.8', '转化DNA回收率的检测方法及引物', '发明创造', '2019-11-22', '2020-01-10', '陶启长；韦东'),
('CN202011144174.7', '一种经修饰荧光探针及其应用', '发明创造', '2020-10-23', '2020-11-20', '陶启长；韦东；余明伟'),
('CN201920792416.X', '适用于中型桌面仪器的隔离装置', '实用新型', '2019-05-29', '2020-04-17', '许亦琳；余明伟；杨华'),
('CN201920973176.3', '一种高性价离心管冻存盒', '实用新型', '2019-06-26', '2020-04-17', '许亦琳；余明伟；邬剑星；王敏生'),
('CN202110256789.4', '一种新型环保材料的制备方法', '发明创造', '2021-03-09', '2021-12-15', '李明'),
('CN202220345678.X', '便捷式电子设备支架', '实用新型', '2022-02-18', '2022-08-22', '张华'),
('CN202211123456.7', '智能物流配送系统及方法', '发明创造', '2022-09-16', '2023-02-28', '王强；赵晓'),
('CN202320456789.2', '多功能办公文具收纳盒', '实用新型', '2023-03-20', '2023-10-18', '孙悦；刘敏'),
('CN202310567890.1', '高效能源转换装置', '发明创造', '2023-04-12', '2023-11-09', '吴涛；周琳'),
('CN202410123456.8', '人工智能辅助教学方法', '发明创造', '2024-02-05', '2024-07-12', '郑宇；林悦'),
('CN202420234567.9', '可折叠户外遮阳伞', '实用新型', '2024-01-18', '2024-06-20', '陈晨；杨阳'),
('CN202120678901.2', '新型保温杯结构', '实用新型', '2021-04-06', '2021-10-25', '刘辉；张峰'),
('CN202210789012.3', '大数据分析处理平台', '发明创造', '2022-07-05', '2022-12-30', '马丽；王鹏'),
('CN202320890123.4', '创意灯具设计', '实用新型', '2023-06-15', '2023-12-08', '赵丹；李华'),
('CN202110345678.5', '一种新型太阳能电池技术', '发明创造', '2021-03-30', '2021-11-18', '王力'),
('CN202220456789.X', '便携式空气净化器', '实用新型', '2022-03-15', '2022-09-25', '陈雪'),
('CN202211234567.8', '智能医疗诊断系统', '发明创造', '2022-10-12', '2023-03-20', '刘阳；张辉'),
('CN202320567890.3', '可调节电脑桌', '实用新型', '2023-04-05', '2023-11-12', '李丽；王浩'),
('CN202310678901.4', '高效农业灌溉方法', '发明创造', '2023-05-10', '2023-12-15', '赵刚；孙强'),
('CN202410234567.9', '虚拟现实交互技术', '发明创造', '2024-03-08', '2024-08-20', '周明；吴俊'),
('CN202420345678.0', '防水运动手表', '实用新型', '2024-02-12', '2024-07-25', '林晓；郑凯'),
('CN202120789012.5', '新型雨伞设计', '实用新型', '2021-04-28', '2021-11-05', '杨波；刘悦'),
('CN202210890123.6', '区块链数据安全技术', '发明创造', '2022-07-25', '2022-12-12', '马宁；赵亮'),
('CN202320901234.7', '创意家居装饰品', '实用新型', '2023-07-02', '2023-12-20', '孙琳；李阳'),
('CN202411167890.1', '新型合金材料制备工艺', '发明创造', '2024-10-05', '2025-01-10', '朱春野；陶启长'),
('CN202420278901.X', '便捷式电子秤设计', '实用新型', '2024-02-20', '2024-08-05', '许亦琳；余明伟'),
('CN202411289012.2', '智能数据分析方法', '发明创造', '2024-11-12', '2025-02-25', '韦东；余明伟'),
('CN202420390123.X', '创意手机支架', '实用新型', '2024-03-18', '2024-09-20', '杨华；王敏生'),
('CN202411390124.3', '高效能源转化技术', '发明创造', '2024-11-25', '2025-03-15', '邬剑星；陶启长'),
('CN202420490125.4', '可折叠收纳袋', '实用新型', '2024-04-12', '2024-10-25', '刘敏；许亦琳'),
('CN202411490126.5', '智能物流管理系统', '发明创造', '2024-12-02', '2025-04-10', '王强；韦东'),
('CN202420590127.6', '多功能钥匙扣设计', '实用新型', '2024-05-08', '2024-11-15', '赵晓；余明伟');

2、具体步骤及代码

①先将申请人字段炸裂

②计算不同类型各个申请人的申请专利数

③对不同类型各个申请人按照申请专利数进行排序

④筛选各类型专利top 10申请人，以及对应的专利申请数

with t as (-- 先将申请人字段炸裂select patent_id,patent_name,patent_type,namefrom t_patent_detail lateral view explode(split(apply_users,'；')) mydb as name
) ,t1 as (-- 计算不同类型各个申请人的申请专利数select distinct patent_type,name,count(*) over(partition by patent_type,name) num from t
) ,t2 as (-- 对不同类型各个申请人按照申请专利数进行排序select patent_type,name,num,dense_rank() over (partition by patent_type order by num desc ) sx from t1
) -- 筛选各类型专利top 10申请人，以及对应的专利申请数
select patent_type `专利类型`,name `申请人`,num `专利数`,sx `专利排名` from t2 where sx<=10;