Hive 查询各类型专利 top10 申请人及专利申请数
一、背景
在专利数据处理中，我们常常需要分析不同类型专利下申请人的活跃度。例如，给定一个专利明细表 t_patent_detail，其中包含专利号、专利名称、专利类型、申请时间、授权时间和申请人等信息，且一个专利可能有多个申请人（以分号隔开）。我们的目标是找出各类型专利 top10 申请人以及他们对应的专利申请数。
二、数据结构
我们的数据存储在名为 t_patent_detail 的表中，其表结构如下：
- 专利号(patent_id)：每个专利的唯一标识符。
- 专利名称(patent_name)：专利的名称，描述专利的内容。
- 专利类型(patent_type)：例如发明创造、实用新型等不同类型的专利分类。
- 申请时间(aplly_date)：专利申请的日期。
- 授权时间(authorize_date)：专利获得授权的日期。
- 申请人(apply_users)：一个或多个申请人的名称，多人之间以分号分隔。
以下是一些示例数据：
| patent_id | patent_name | patent_type | aplly_date | authorize_date | apply_users |
|---|---|---|---|---|---|
| CN201821413799.7 | 一种用于合金板棒材的往复式感应加热设备 | 发明创造 | 2018/8/30 | 2019/8/9 | 朱春野 |
| CN201911153500.8 | 转化 DNA 回收率的检测方法及引物 | 发明创造 | 2019/11/22 | 2020/1/10 | 陶启长;韦东 |
| CN202011144174.7 | 一种经修饰荧光探针及其应用 | 发明创造 | 2020/10/23 | 2020/11/20 | 陶启长;韦东;余明伟 |
| CN201920792416.X | 适用于中型桌面仪器的隔离装置 | 实用新型 | 2019/5/29 | 2020/4/17 | 许亦琳;余明伟;杨华 |
| CN201920973176.3 | 一种高性价离心管冻存盒 | 实用新型 | 2019/6/26 | 2020/4/17 | 许亦琳;余明伟;邬剑星;王敏生 |
三、查询思路
- 第一步：处理申请人字段
由于每个专利可能有多个申请人，我们需要将申请人字段“炸裂”（使用 Hive 的explode函数），将每个申请人拆分成单独的行。这里使用split函数将以分号分隔的申请人字符串拆分成数组，然后使用explode函数将数组中的每个元素（即每个申请人）展开。 - 第二步：分组统计
对处理后的申请人进行分组，并使用count(*)函数统计每个申请人的专利申请数。同时，使用rank()函数按照专利申请数进行排名，以便后续找出 top10 申请人。
四、Hive 查询语句解析
以下是实现上述功能的 Hive 查询语句：
--各类型专利 top10 申请人，以及对应的专利申请数
select t1.apply_name `申请人`,count(*) `专利申请数`,rank() over(order by
count(*) desc) `专利数排名`
from (--先将申请人字段炸裂select d.*, t1.coll `apply_name`from t_patent_detail dlateral view explode(split(apply_users,';')) t1 as coll
) t1
group by t1.apply_name; --按照申请人分组
- 在子查询中：
from t_patent_detail d从专利明细表开始查询。lateral view explode(split(apply_users,';')) t1 as coll这部分是关键。首先，split(apply_users,';')将apply_users字段（申请人字段）按照分号进行拆分，得到一个申请人名称的数组。然后，explode函数将这个数组展开，每个元素（申请人名称）成为单独的一行。t1是一个临时表别名，coll是展开后的申请人名称列的别名。d.*表示选择原始表中的所有其他列。
- 在外部查询中：
group by t1.apply_name按照申请人名称进行分组。count(*)统计每个申请人的专利申请数。rank() over(order by count(*) desc)使用rank函数按照专利申请数降序排名，这里虽然没有筛选 top10 的步骤，但可以根据这个排名在后续进一步处理得到 top10 的结果。