目录
一、需求背景
二、实现思路
三、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_name
- FROM t_patent_detail d
- LATERAL 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 查询语句后，将会得到如下的结果：
申请人专利申请数专利数排名申请人A151申请人B122………
每种专利类型下的申请人按照申请次数降序排列，展示出 Top 10 的申请人及其申请次数。
五、总结
通过本文，我们学习了如何使用 Hive 的 SQL 来拆分多值字段并进行分组统计和排名。该方法实用于类似包含多值字段的分析需求，可以或许帮助我们快速得到各类型专利的 Top 10 申请人，为数据分析和决议提供支持。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
