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

原创已于 2024-11-20 08:57:26 修改 · 1k 阅读

18 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#hive #hadoop #数据仓库

于 2024-11-07 19:57:50 首次发布

Hive 专栏收录该内容

6 篇文章

订阅专栏

在实际的数仓项目中，经常需要对数据进行统计分析，比如在专利管理系统中，需要对不同类型的专利进行申请人排名分析。本文将介绍如何在 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
),
ranked_applications AS (
    -- 计算每个申请人在每种专利类型下的申请数和排名
    SELECT apply_name,
           patent_type,
           COUNT(*) AS application_count,
           RANK() OVER (PARTITION BY patent_type ORDER BY COUNT(*) DESC) AS rank_within_type
    FROM temp
    GROUP BY apply_name, patent_type
)
-- 选择每种专利类型下排名在前10的申请人
SELECT apply_name AS `申请人`,
       patent_type AS `专利类型`,
       application_count AS `专利申请数`,
       rank_within_type AS `专利数排名`
FROM ranked_applications
WHERE rank_within_type <= 10
ORDER BY patent_type, rank_within_type; -- 可选：按专利类型和排名排序输出

语句解析

LATERAL VIEW EXPLODE(SPLIT(d.apply_users, ';')) t1 AS coll：将 apply_users 字段中的申请人用分号 ; 分割开来，生成多行，每行包含一个申请人名字。
COUNT(*)：对每个申请人的专利数量进行计数，统计申请次数。
RANK() OVER(PARTITION BY patent_type ORDER BY COUNT(*) DESC)：通过 RANK() 函数对每个专利类型中的申请人按申请次数进行排名。

注意事项

在使用 RANK() 时，确保对 PARTITION BY 和 ORDER BY 的理解。PARTITION BY patent_type 表示对不同的专利类型分别统计排名，ORDER BY COUNT(*) DESC 表示按照申请次数降序排列。
使用 LATERAL VIEW EXPLODE 处理多值字段时要小心，可能会导致数据量增加，应确保 Hive 集群的性能可以承受。