当前位置：首页 > article >正文

Hive 查询各类型专利 Top 10 申请人及对应的专利申请数

article 2024/11/9 3:04:48

一、需求背景

二、实现思路

三、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 集群的性能可以承受。