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

hive面试题，超详细解析。各类型专利top 10申请人，以及对应的专利申请数

news 2024/11/7 20:44:21

1. 需求概述

根据给定的专利明细表 t_patent_detail，需要查询出每种专利类型下申请数最多的前 10 位申请人，以及他们对应的专利申请数量。由于每个专利可以有一个或多个申请人，而申请人之间通过分号 ； 隔开，因此需要进行拆分和统计。

2. 表结构说明

表 t_patent_detail 存储了专利的详细信息，主要字段如下：

patent_id：专利号
patent_name：专利名称
patent_type：专利类型
apply_date：申请时间
authorize_date：授权时间
apply_users：申请人（可能有多个，使用分号 ； 隔开）

表 t_patent_detail 中的记录数大约为 10,000 条。示例数据如下：

patent_id	patent_name	patent_type	apply_date	authorize_date	apply_users
P123456	专利A	发明专利	2023-01-01	2023-06-01	张三；李四；王五
P789012	专利B	外观设计专利	2023-02-01	2023-07-01	张三；赵六
P345678	专利C	实用新型专利	2023-03-01	2023-08-01	王五；李四

3. 查询目的

通过查询，输出每种专利类型下，专利申请数量最多的前 10 位申请人及其申请数量，按申请数量降序排列。

4. 查询步骤与逻辑

查询的主要步骤如下：

拆分申请人列表：使用 LATERAL VIEW 和 explode 函数将每个专利中的 apply_users（申请人）字段按分号拆分成多个申请人。
统计每个申请人在每种专利类型下的申请数：按专利类型和申请人进行分组，统计每个申请人在每个专利类型下的专利申请数。
为每个专利类型中的申请人排名：使用 DENSE_RANK() 函数按每个申请人在该专利类型下的申请数量进行排名。
筛选每个专利类型中的前 10 名申请人：通过 WHERE 子句，选择每种专利类型下排名前 10 的申请人。

5. Hive 查询语句

WITH t AS (-- 1. 拆分每个专利的多个申请人并统计每个申请人在每种专利类型中的专利数量SELECTusers,            -- 申请人patent_type,      -- 专利类型COUNT(1) AS gs    -- 每个申请人在该专利类型下的专利数量FROM t_patent_detailLATERAL VIEW explode(split(apply_users, '；')) temTab AS users -- 拆分申请人字段GROUP BY users, patent_type   -- 按申请人和专利类型分组
),
t2 AS (-- 2. 对每种专利类型的申请人进行排名SELECT *, DENSE_RANK() OVER (PARTITION BY patent_type ORDER BY gs DESC) AS paiming -- 按专利数量排名FROM t
)
-- 3. 获取每种专利类型的 Top 10 申请人
SELECT *
FROM t2
WHERE paiming <= 10;  -- 只保留排名前10的申请人

6. 查询解析

WITH t AS (...) 子查询：
- 在 WITH 子句中定义了一个临时表 t，该表的作用是拆分每个专利的 apply_users 字段，将其按分号 ； 拆分成多个申请人，并统计每个申请人在每种专利类型下的申请数量。
- LATERAL VIEW explode(split(apply_users, '；')) temTab AS users：首先使用 split() 函数将 apply_users 按照分号 ； 拆分成多个申请人，然后通过 explode 函数将每个拆分后的申请人展成单独的行。
- COUNT(1) AS gs：统计每个申请人在每个专利类型下的专利数量。
WITH t2 AS (...) 子查询：
- 使用 DENSE_RANK() 函数对每个专利类型中的申请人按专利数量 gs 进行降序排名。DENSE_RANK() 会为相同数量的专利赋相同的排名，并且排名不跳跃。
- PARTITION BY patent_type 确保排名是按每种专利类型独立进行的。
SELECT * FROM t2 WHERE paiming <= 10：
- 最后，从子查询 t2 中选择排名前 10 的申请人数据。
- paiming <= 10：只保留排名前 10 的申请人数据，确保每种专利类型只返回前 10 名。

7. 结果示例

users	patent_type	gs	paiming
张三	发明专利	5	1
李四	发明专利	4	2
王五	发明专利	3	3
张三	外观设计专利	6	1
赵六	外观设计专利	4	2
王五	实用新型专利	5	1
李四	实用新型专利	3	2