Hive专题：数据开发面试高频题（TopN、留存、连续登录等）

Hive 专题：数据开发面试高频题（TopN、留存、连续登录等）

本文精选 Hive SQL 面试中最高频的 8 类题型，每道题提供 业务场景、核心思路、完整 SQL 示例 及 关键点解析。所有代码均基于 Hive 窗口函数、日期函数、条件聚合等特性编写，可直接运行。

📑 目录

1. 分组 TopN 问题

1.1 每个部门薪资最高的 3 名员工

业务场景：查询每个部门中薪资排名前三的员工，若薪资相同则按工号排序。

核心思路：使用窗口函数 ROW_NUMBER()（无并列）、RANK()（并列跳跃）或 DENSE_RANK()（并列不跳跃）。

SQL 示例：

— 示例表结构
CREATE TABLE emp (
emp_id INT,
name STRING,
dept STRING,
salary DECIMAL(10,2)
);

— 查询每个部门薪资最高的3人（无并列，若薪资相同按emp_id升序）
SELECT dept, name, salary, rn
FROM (
SELECT dept, name, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, emp_id) AS rn
FROM emp
) t
WHERE rn <= 3;

关键点解析：

• PARTITION BY dept：按部门分组。
• ORDER BY salary DESC, emp_id：薪资降序，相同薪资则按工号升序，保证唯一顺序。
• 若要求并列处理（如两个第一，则跳过第二名），使用 RANK()；若并列不跳过，使用 DENSE_RANK()。

1.2 每个品类销量前 2 的商品（并列处理）

业务场景：电商平台，每个商品品类下按销量排序，销量相同时均入选（可能出现超过 2 条）。

SQL 示例：

SELECT category, product_name, sales
FROM (
SELECT category, product_name, sales,
DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS dr
FROM product_sales
) t
WHERE dr <= 2;

关键点解析：DENSE_RANK() 在并列时不产生名次跳跃，保证销量相同的所有商品都能进入前 2。

2. 用户留存率计算

2.1 次日 / 7 日 / 30 日留存率

业务场景：计算某日新增用户在次日、第 7 日、第 30 日仍活跃的比例。

核心思路：

• 定义“新增用户”：首次登录日期（或指定注册日期）。
• 定义“活跃”：当天有登录行为（记录在 user_active 表中，每用户每天一条）。
• 通过左连接计算留存。

表结构假设：user_active(user_id, dt)，已去重。

SQL 示例（计算 2026-04-01 新增用户的后续留存）：

WITH new_users AS (
SELECT user_id, MIN(dt) AS first_dt
FROM user_active
GROUP BY user_id
HAVING MIN(dt) = '2026-04-01'
),
retention AS (
SELECT
n.first_dt,
COUNT(DISTINCT n.user_id) AS new_cnt,
COUNT(DISTINCT CASE WHEN a1.dt = DATE_ADD(n.first_dt, 1) THEN n.user_id END) AS day1_ret,
COUNT(DISTINCT CASE WHEN a7.dt = DATE_ADD(n.first_dt, 7) THEN n.user_id END) AS day7_ret,
COUNT(DISTINCT CASE WHEN a30.dt = DATE_ADD(n.first_dt, 30) THEN n.user_id END) AS day30_ret
FROM new_users n
LEFT JOIN user_active a1 ON n.user_id = a1.user_id AND a1.dt = DATE_ADD(n.first_dt, 1)
LEFT JOIN user_active a7 ON n.user_id = a7.user_id AND a7.dt = DATE_ADD(n.first_dt, 7)
LEFT JOIN user_active a30 ON n.user_id = a30.user_id AND a30.dt = DATE_ADD(n.first_dt, 30)
GROUP BY n.first_dt
)
SELECT first_dt, new_cnt,
day1_ret / new_cnt AS day1_retention_rate,
day7_ret / new_cnt AS day7_retention_rate,
day30_ret / new_cnt AS day30_retention_rate
FROM retention;

关键点解析：

• DATE_ADD(n.first_dt, 1) 计算次日日期。
• 使用 LEFT JOIN 避免因次日无活跃而丢失新增用户基数。
• COUNT(DISTINCT CASE WHEN … THEN user_id END) 是 Hive 中常用的条件计数写法。

2.2 多日连续留存（留存矩阵）

业务场景：批量计算某段时间内每天新增用户的次日、3 日、7 日留存，生成留存矩阵报表。

SQL 示例：

WITH new_users AS (
SELECT user_id, MIN(dt) AS first_dt
FROM user_active
GROUP BY user_id
HAVING first_dt BETWEEN '2026-04-01' AND '2026-04-07'
),
active_ret AS (
SELECT
n.first_dt,
DATEDIFF(a.dt, n.first_dt) AS day_gap,
COUNT(DISTINCT n.user_id) AS ret_cnt
FROM new_users n
JOIN user_active a ON n.user_id = a.user_id
WHERE a.dt BETWEEN n.first_dt AND DATE_ADD(n.first_dt, 30)
GROUP BY n.first_dt, DATEDIFF(a.dt, n.first_dt)
)
SELECT first_dt,
MAX(CASE WHEN day_gap=1 THEN ret_cnt ELSE 0 END) / MAX(CASE WHEN day_gap=0 THEN ret_cnt ELSE 0 END) AS day1_rate,
MAX(CASE WHEN day_gap=3 THEN ret_cnt ELSE 0 END) / MAX(CASE WHEN day_gap=0 THEN ret_cnt ELSE 0 END) AS day3_rate,
MAX(CASE WHEN day_gap=7 THEN ret_cnt ELSE 0 END) / MAX(CASE WHEN day_gap=0 THEN ret_cnt ELSE 0 END) AS day7_rate
FROM active_ret
GROUP BY first_dt
ORDER BY first_dt;

关键点解析：DATEDIFF(a.dt, n.first_dt) 得到距离首次登录的天数，通过 CASE WHEN 将不同间隔的留存人数转为列。

3. 连续登录 / 活跃天数问题

3.1 用户最大连续登录天数

业务场景：计算每个用户历史上最长的连续登录天数。

核心思路（日期减排名技巧）：

• 对每个用户按日期排序并编号 rn。
• 若日期连续，则 dt – rn 为常量。
• 按用户和 dt – rn 分组，统计个数即为连续天数。

SQL 示例：

WITH user_dates AS (
SELECT DISTINCT user_id, dt FROM user_active
),
ranked AS (
SELECT user_id, dt,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY dt) AS rn
FROM user_dates
)
SELECT user_id, MAX(continuous_days) AS max_continuous_days
FROM (
SELECT user_id, COUNT(*) AS continuous_days
FROM ranked
GROUP BY user_id, DATE_SUB(dt, rn)
) t
GROUP BY user_id;

关键点解析：

• DATE_SUB(dt, rn)：将日期减去行号，连续登录的这组值相同。
• 先按 (user_id, dt) 去重，避免同一天多条记录。

3.2 连续 3 天登录的用户

业务场景：找出至少连续 3 天登录的用户。

SQL 示例（基于同一技巧）：

WITH user_dates AS (
SELECT DISTINCT user_id, dt FROM user_active
),
ranked AS (
SELECT user_id, dt,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY dt) AS rn
FROM user_dates
)
SELECT DISTINCT user_id
FROM (
SELECT user_id, DATE_SUB(dt, rn) AS group_flag
FROM ranked
) t
GROUP BY user_id, group_flag
HAVING COUNT(*) >= 3;

3.3 连续登录超过 N 天的用户及起止日期

业务场景：不仅找出用户，还要显示每次连续登录的起始和结束日期。

SQL 示例：

WITH user_dates AS (
SELECT DISTINCT user_id, dt FROM user_active
),
ranked AS (
SELECT user_id, dt,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY dt) AS rn
FROM user_dates
),
grouped AS (
SELECT user_id, DATE_SUB(dt, rn) AS group_flag,
MIN(dt) AS start_date, MAX(dt) AS end_date, COUNT(*) AS days
FROM ranked
GROUP BY user_id, DATE_SUB(dt, rn)
)
SELECT user_id, start_date, end_date, days
FROM grouped
WHERE days >= 3
ORDER BY user_id, start_date;

4. 用户行为序列与漏斗分析

4.1 页面访问路径（Clickstream）

业务场景：统计用户从首页到商品详情页的路径，计算各步骤转化。

SQL 示例（获取每个用户的页面访问序列）：

WITH user_actions AS (
SELECT user_id, page, action_time,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY action_time) AS seq
FROM clickstream
)
SELECT user_id,
COLLECT_LIST(page) AS page_path
FROM user_actions
GROUP BY user_id;

4.2 转化漏斗（浏览→加购→支付）

业务场景：统计某日所有用户中，完成浏览商品、加入购物车、支付三个步骤的人数及转化率。

SQL 示例：

WITH user_funnel AS (
SELECT user_id,
MAX(CASE WHEN action = 'view' THEN 1 ELSE 0 END) AS has_view,
MAX(CASE WHEN action = 'cart' THEN 1 ELSE 0 END) AS has_cart,
MAX(CASE WHEN action = 'pay' THEN 1 ELSE 0 END) AS has_pay
FROM user_behavior
WHERE dt = '2026-04-01'
GROUP BY user_id
)
SELECT
COUNT(user_id) AS total_users,
SUM(has_view) AS view_cnt,
SUM(has_cart) AS cart_cnt,
SUM(has_pay) AS pay_cnt,
SUM(has_cart) / SUM(has_view) AS view_to_cart_rate,
SUM(has_pay) / SUM(has_cart) AS cart_to_pay_rate
FROM user_funnel;

关键点解析：MAX(CASE WHEN … THEN 1 ELSE 0 END) 可判断用户是否发生过该行为。

5. 行列转换

5.1 行转列（多行合并为一行）

业务场景：将每个用户的多个标签合并为一个逗号分隔的字符串。

SQL 示例：

SELECT user_id,
CONCAT_WS(',', COLLECT_SET(tag)) AS tags_str
FROM user_tags
GROUP BY user_id;

关键点解析：COLLECT_SET 去重，COLLECT_LIST 保留重复；CONCAT_WS 指定分隔符拼接。

5.2 列转行（一行拆成多行）

业务场景：将逗号分隔的标签字符串拆分为多行。

SQL 示例：

SELECT user_id, tag
FROM (
SELECT user_id, SPLIT(tags_str, ',') AS tags_array
FROM user_tags_table
) t
LATERAL VIEW EXPLODE(tags_array) tmp AS tag;

6. 拉链表设计与回滚查询

6.1 拉链表的每日增量更新

业务场景：用户维度表（如会员等级）缓慢变化，需要保留历史状态。

拉链表结构：

CREATE TABLE dim_user (
user_id BIGINT,
level STRING,
start_date DATE,
end_date DATE,
is_current STRING — 'Y' 或 'N'
) STORED AS ORC;

每日增量更新 SQL（合并变化数据）：

— 假设 ods_user 为今日全量用户数据
INSERT OVERWRITE TABLE dim_user
SELECT * FROM dim_user WHERE is_current = 'N' — 保留已关闭的历史
UNION ALL
— 新增/变化的用户（取今日最新数据作为当前有效）
SELECT
user_id, level,
CURRENT_DATE AS start_date,
'9999-12-31' AS end_date,
'Y' AS is_current
FROM ods_user
UNION ALL
— 关闭那些等级发生变化的旧记录
SELECT
old.user_id, old.level, old.start_date,
DATE_SUB(CURRENT_DATE, 1) AS end_date,
'N' AS is_current
FROM dim_user old
JOIN ods_user new ON old.user_id = new.user_id
WHERE old.is_current = 'Y' AND old.level != new.level;

6.2 查询指定时间点的维度值

业务场景：查询 2026-03-15 当天的用户等级。

SQL 示例：

SELECT user_id, level
FROM dim_user
WHERE start_date <= '2026-03-15' AND end_date >= '2026-03-15';

7. UV、PV、新增等常见指标

7.1 每日新增用户数

业务场景：统计每天首次登录的用户数。

SQL 示例：

WITH first_login AS (
SELECT user_id, MIN(dt) AS first_dt
FROM user_active
GROUP BY user_id
)
SELECT first_dt, COUNT(user_id) AS new_users
FROM first_login
GROUP BY first_dt
ORDER BY first_dt;

7.2 每日活跃用户数（DAU）及周同比

SQL 示例：

SELECT dt, COUNT(DISTINCT user_id) AS dau
FROM user_active
WHERE dt >= DATE_SUB(CURRENT_DATE, 30)
GROUP BY dt
ORDER BY dt;

以上题目覆盖了 Hive SQL 面试中 80% 的高频考点。建议读者在理解思路后，结合实际数据表进行练习，并熟练使用 EXPLAIN 优化执行计划。