数据仓库与数据分析面试题及答案

一、基础概念题

1. 数据仓库基础概念

问：什么是数据仓库？它与数据库的主要区别是什么？
答：

数据仓库定义：数据仓库是一个面向主题的、集成的、相对稳定的、反映历史变化的数据集合，用于支持管理决策。
与数据库的区别：
- 数据库：面向事务处理（OLTP），操作频繁，数据实时性强
- 数据仓库：面向分析处理（OLAP），查询为主，数据批量更新
- 数据库：细节数据，数据量相对较小
- 数据仓库：汇总数据，数据量大，历史数据

问：数据仓库的分层架构（ODS、DWD、DWS、ADS）分别代表什么？
答：

ODS层（原始数据层）：存储原始数据，保持数据原貌
DWD层（明细数据层）：对ODS层数据进行清洗、整合
DWS层（汇总层）：对DWD层数据进行聚合，生成指标
ADS层（应用层）：面向具体业务需求的数据

问：星型模型和雪花模型的区别是什么？
答：

星型模型：事实表直接连接多个维度表，结构简单
雪花模型：维度表可以进一步连接其他维度表，结构复杂但节省存储空间

2. Hive相关概念

问：Hive内部表和外部表的区别是什么？使用场景有什么不同？
答：

内部表：Hive管理表数据，删除表时数据也被删除
外部表：Hive不管理表数据，删除表时数据保留
使用场景：内部表适合临时数据处理，外部表适合多工具共享数据

问：Hive分区表和分桶表的概念及使用场景
答：

分区表：按字段值将数据划分为不同目录，提高查询效率
分桶表：按哈希算法将数据分散到不同文件，适合抽样和join优化

问：Hive的存储格式有哪些？（TextFile、ORC、Parquet等）
答：

TextFile：默认格式，可读性好但压缩率低
ORC：列式存储，压缩率高，查询性能好
Parquet：列式存储，适合复杂数据类型

3. SQL基础语法

问：INSERT INTO 和 INSERT OVERWRITE 的区别是什么？
答：

INSERT INTO：向表中追加数据
INSERT OVERWRITE：覆盖表中原有数据

问：WHERE 、HAVING 、GROUP BY 的执行顺序和作用
答：

执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
作用：
- WHERE：过滤行数据
- GROUP BY：分组聚合
- HAVING：过滤分组后的结果

问：内连接、左连接、右连接、全连接的区别
答：

内连接：只返回匹配的行
左连接：返回左表所有行，右表不匹配用NULL填充
右连接：返回右表所有行，左表不匹配用NULL填充
全连接：返回两表所有行，不匹配用NULL填充

二、数据分析函数

1. 聚合函数

问：COUNT() 、SUM() 、AVG() 、MAX() 、MIN() 的基本用法
答：

COUNT()：计数
SUM()：求和
AVG()：平均值
MAX()：最大值
MIN()：最小值

问：COUNT(DISTINCT) 和 COUNT(*) 的区别
答：

COUNT(*)：计算所有行数
COUNT(DISTINCT)：计算去重后的行数

问：SUM(CASE WHEN) 的使用场景
答：
条件求和，例如：

SUM(CASE WHEN status = 'success' THEN amount ELSE 0 END) AS success_amount

2. 开窗函数

问：开窗函数的基本语法：OVER(PARTITION BY ... ORDER BY ...)
答：
开窗函数用于在查询结果集的行上执行计算，基本语法为：

函数名() OVER(PARTITION BY 分区字段 ORDER BY 排序字段)

问：ROW_NUMBER() 、RANK() 、DENSE_RANK() 的区别
答：

ROW_NUMBER()：连续排名，不重复
RANK()：排名相同则跳号
DENSE_RANK()：排名相同不跳号

问：LAG() 、LEAD() 函数的作用和用法
答：

LAG()：获取前N行的值
LEAD()：获取后N行的值
用法：LAG(字段名, N)、LEAD(字段名, N)

问：FIRST_VALUE() 、LAST_VALUE() 的使用场景
答：

FIRST_VALUE()：获取分组内第一个值
LAST_VALUE()：获取分组内最后一个值

3. 字符串函数

问：CONCAT() 、SUBSTRING() 、SPLIT() 的用法
答：

CONCAT()：连接字符串
SUBSTRING()：截取字符串
SPLIT()：分割字符串

问：TRIM() 、LTRIM() 、RTRIM() 的区别
答：

TRIM()：去除首尾空格
LTRIM()：去除左侧空格
RTRIM()：去除右侧空格

问：UPPER() 、LOWER() 的作用
答：

UPPER()：转换为大写
LOWER()：转换为小写

三、实际应用场景

1. 运营数据分析场景

问：如何计算用户留存率？
答：

SELECT 
  date_sub(first_login_date, 1) AS login_date,
  COUNT(DISTINCT user_id) AS new_users,
  COUNT(DISTINCT CASE WHEN datediff(login_date, first_login_date) = 1 THEN user_id END) AS retained_users,
  COUNT(DISTINCT CASE WHEN datediff(login_date, first_login_date) = 1 THEN user_id END) / COUNT(DISTINCT user_id) AS retention_rate
FROM user_login_log
GROUP BY date_sub(first_login_date, 1)

问：如何分析用户行为路径？
答：
可以通过事件流分析，记录用户在不同页面或功能间的跳转路径，使用序列分析方法。

问：如何统计每日活跃用户数（DAU）？
答：

SELECT date, COUNT(DISTINCT user_id) AS dau FROM user_activity GROUP BY date

问：如何计算转化率漏斗？
答：

SELECT 
  step,
  COUNT(DISTINCT user_id) AS user_count,
  LAG(COUNT(DISTINCT user_id)) OVER(ORDER BY step) AS prev_user_count,
  COUNT(DISTINCT user_id) / LAG(COUNT(DISTINCT user_id)) OVER(ORDER BY step) AS conversion_rate
FROM user_funnel
GROUP BY step

2. 数据质量处理

问：如何处理NULL值？
答：
使用IS NULL、IS NOT NULL判断，COALESCE()、NVL()函数处理

问：如何去重？
答：
使用DISTINCT或GROUP BY

问：如何处理异常值？
答：
使用WHERE条件过滤，或使用统计方法（如3σ原则）

3. 性能优化

问：如何优化Hive SQL查询性能？
答：

合理分区和分桶
使用列式存储格式
减少数据扫描范围
使用LIMIT限制返回行数

问：大表关联小表的优化策略
答：
大表关联小表时，小表放在右边，利用Map端Join优化

问：分区裁剪和列裁剪的作用
答：

分区裁剪：减少扫描的分区数量
列裁剪：只读取需要的列，减少数据传输量

四、实操题目

1. 基础SQL练习

问：计算每个用户的总消费金额
答：

SELECT user_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY user_id
ORDER BY total_amount DESC;

问：找出连续3天活跃的用户
答：

SELECT user_id
FROM (
    SELECT 
        user_id,
        activity_date,
        LEAD(activity_date, 1) OVER(PARTITION BY user_id ORDER BY activity_date) AS next_day,
        LEAD(activity_date, 2) OVER(PARTITION BY user_id ORDER BY activity_date) AS next_next_day
    FROM user_activity
) t
WHERE 
    DATEDIFF(next_day, activity_date) = 1
    AND DATEDIFF(next_next_day, next_day) = 1
GROUP BY user_id;

2. 开窗函数练习

问：计算每个用户的订单金额排名
答：

SELECT 
    user_id, 
    order_id, 
    order_amount,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_amount DESC) AS rn,
    RANK() OVER(PARTITION BY user_id ORDER BY order_amount DESC) AS rk,
    DENSE_RANK() OVER(PARTITION BY user_id ORDER BY order_amount DESC) AS dense_rk
FROM orders;

问：计算用户消费的环比增长
答：

SELECT 
    user_id,
    month,
    monthly_amount,
    LAG(monthly_amount) OVER(PARTITION BY user_id ORDER BY month) AS last_month_amount,
    CASE 
        WHEN LAG(monthly_amount) OVER(PARTITION BY user_id ORDER BY month) IS NULL THEN 0
        ELSE (monthly_amount - LAG(monthly_amount) OVER(PARTITION BY user_id ORDER BY month)) / 
             LAG(monthly_amount) OVER(PARTITION BY user_id ORDER BY month) * 100
    END AS growth_rate
FROM user_monthly_consumption;

一点一滴