目录
一、需求背景
二、实现思路
三、Hive SQL 实现
语句解析
注意事项
四、结果展示
五、总结
在实际的数仓项目中，经常需要对数据进行统计分析，比如在专利管理系统中，需要对不同类型的专利进行申请人排名分析。本文将介绍如何在 Hive 中通过 SQL 查询实现各类型专利的 Top 10 申请人统计。
一、需求背景
假设有一张专利明细表 t_patent_detail，其中记录了专利号、专利名称、专利类型、申请时间、授权时间和申请人等字段。具体字段如下：
patent_id：专利号patent_name：专利名称patent_type：专利类型（包括发明创造、实用新型等）aplly_date：申请时间authorize_date：授权时间apply_users：申请人（多个申请人用分号;隔开）
表 t_patent_detail 数据样例如下图所示：
目标是查询出各类型专利中申请次数最多的 Top 10 申请人及对应的专利申请数量。
二、实现思路
- 申请人字段拆分：
apply_users字段包含多个申请人，用分号;分隔。需要先使用LATERAL VIEW EXPLODE函数将申请人字段拆分成多行，每行一个申请人。 - 分组统计：对每个专利类型中的申请人进行统计，计算每位申请人的专利申请次数。
- 排名：使用
RANK()函数对每个专利类型中的申请人申请次数进行排名，并筛选出前 10 名。
三、Hive SQL 实现
以下是实现该需求的 Hive SQL 查询语句：
WITH temp AS (-- 将申请人字段拆分成单独的记录SELECT d.patent_type, t1.coll AS apply_nameFROM t_patent_detail dLATERAL VIEW EXPLODE(SPLIT(d.apply_users, ';')) t1 AS coll
)SELECT apply_name AS `申请人`, COUNT(*) AS `专利申请数`, RANK() OVER(PARTITION BY patent_type ORDER BY COUNT(*) DESC) AS `专利数排名`
FROM temp
GROUP BY apply_name, patent_type
HAVING RANK() OVER(PARTITION BY patent_type ORDER BY COUNT(*) DESC) <= 10;语句解析
-
LATERAL VIEW EXPLODE(SPLIT(d.apply_users, ';')) t1 AS coll：将
apply_users字段中的申请人用分号;分割开来，生成多行，每行包含一个申请人名字。 -
COUNT(*)：对每个申请人的专利数量进行计数，统计申请次数。
-
RANK() OVER(PARTITION BY patent_type ORDER BY COUNT(*) DESC)：通过
RANK()函数对每个专利类型中的申请人按申请次数进行排名。 -
HAVING 子句：筛选出每种专利类型中申请次数最多的前 10 名。
注意事项
- 在使用
RANK()时，确保对PARTITION BY和ORDER BY的理解。PARTITION BY patent_type表示对不同的专利类型分别统计排名，ORDER BY COUNT(*) DESC表示按照申请次数降序排列。 - 使用
LATERAL VIEW EXPLODE处理多值字段时要小心，可能会导致数据量增加，应确保 Hive 集群的性能可以承受。
四、结果展示
执行上述 SQL 查询语句后，将会得到如下的结果：
| 申请人 | 专利申请数 | 专利数排名 |
|---|---|---|
| 申请人A | 15 | 1 |
| 申请人B | 12 | 2 |
| … | … | … |
每种专利类型下的申请人按照申请次数降序排列，展示出 Top 10 的申请人及其申请次数。
五、总结
通过本文，我们学习了如何使用 Hive 的 SQL 来拆分多值字段并进行分组统计和排名。该方法适用于类似包含多值字段的分析需求，能够帮助我们快速得到各类型专利的 Top 10 申请人，为数据分析和决策提供支持。
