数仓拉链表分区实现详细方案

<think>好的，我现在需要帮用户整理一份数仓工程师面试中常见的SQL考题及答案。首先，我得回忆一下数仓工程师面试通常考察哪些SQL知识点。数仓工程师可能更关注复杂查询、性能优化、数据建模、窗口函数、数据清洗等方面的问题。 首先，用户需要的是常见的考题，所以应该覆盖基础到中高级的内容。比如，连接操作、聚合函数、子查询、窗口函数、数据去重、分页查询、日期处理、行列转换等。这些都是数仓工作中常见的任务。 接下来，我需要考虑每个知识点对应的典型题目。比如，连接操作可能会涉及到内连接、左连接等；窗口函数可能包括排名、分组聚合等。此外，数仓中的优化问题，比如索引使用、查询性能优化，也是常见的面试题。 然后，每个题目需要附带答案，答案要准确且符合SQL标准。需要注意的是，不同的数据库系统可能有不同的语法，比如Hive SQL和传统的关系型数据库（如MySQL、PostgreSQL）在部分函数上存在差异。用户提到的是数仓工程师，可能更多涉及Hive或者Spark SQL，所以需要适当考虑这些方言，但通用SQL可能更稳妥。 此外，题目需要由浅入深，结构清晰。可能需要分为基础篇、进阶篇和应用篇，覆盖不同的难度层次，这样用户看起来更有条理。 现在，开始列举具体的问题： 基础部分： 1. 多表连接：比如，查询每个部门的员工数量，需要连接部门和员工表。 2. 聚合函数：计算每个部门的平均工资。 3. 去重：找出有销售记录的客户。 4. 条件过滤：查找工资高于平均工资的员工。 5. 分组过滤：找出销售额超过10000的部门。 进阶部分： 1. 窗口函数：计算每个部门工资排名前三的员工。 2. 日期函数：查询近30天的订单。 3. 行转列：将月份作为列显示销售额。 4. 递归查询：处理层级数据，如员工上下级关系。 5. 性能优化：分析查询慢的原因，如何优化。 应用场景： 1. 数据清洗：处理重复数据。 2. 留存率计算：次日、7日留存。 3. 连续登录用户：连续3天登录的用户。 4. 拉链表应用：如何查询某历史时间点的状态。 5. 缓慢变化维处理：Type II的处理方法。 接下来，每个题目需要写出具体的SQL示例，并确保语法正确。比如，窗口函数中使用ROW_NUMBER()，日期函数中使用CURRENT_DATE进行日期计算，行转列可能需要使用CASE WHEN或者PIVOT（但不同数据库支持不同，这里可能需要用标准方法）。 同时，答案要解释关键点，比如在性能优化中，提到索引、避免全表扫描等。 需要注意的潜在问题：用户可能需要答案简洁明了，直接给出SQL语句和解释。但有些问题可能有多种解法，需要选择最常见或最优的方式。比如，去重可以使用DISTINCT或GROUP BY，但这里可能需要说明两者的区别。 另外，数仓中常用的比如Hive的分区、分桶，或者使用EXPLAIN查看执行计划，也可能被提到，但可能属于更高级的问题。在整理时，可能需要适当涵盖这些点。 最后，检查每个SQL的语法是否正确，特别是关于别名、子查询的引用、窗口函数的正确使用。例如，在窗口函数中，PARTITION BY和ORDER BY是否正确，别名是否正确引用。 总结下来，需要整理大约15-20个问题，覆盖基础、进阶和应用场景，确保每个问题有答案和解析，帮助用户准备面试。</think>以下为数据仓库工程师面试常见的SQL考题及参考答案，按难度和场景分类整理： --- ### **一、基础篇** #### 1. 多表关联查询 **题目**：查询每个部门的员工数量（表`department`有字段`dept_id, dept_name`，表`employee`有字段`emp_id, emp_name, dept_id`）。 ```sql SELECT d.dept_name, COUNT(e.emp_id) AS emp_count FROM department d LEFT JOIN employee e ON d.dept_id = e.dept_id GROUP BY d.dept_name; ``` **关键点**：使用`LEFT JOIN`保证部门无员工时显示0。 --- #### 2. 聚合函数与筛选 **题目**：统计每个部门的平均工资（表`salary`含`emp_id, salary, month`），且仅显示平均工资>5000的部门。 ```sql SELECT dept_id, AVG(salary) AS avg_salary FROM employee e JOIN salary s ON e.emp_id = s.emp_id GROUP BY dept_id HAVING AVG(salary) > 5000; ``` **关键点**：`HAVING`用于分组后筛选，不可用`WHERE`替代。 --- #### 3. 去重与子查询 **题目**：查询有销售记录的客户ID（表`sales`含重复的`customer_id`）。 ```sql SELECT DISTINCT customer_id FROM sales; -- 或 SELECT customer_id FROM sales GROUP BY customer_id; ``` **区别**：`DISTINCT`去重，`GROUP BY`可结合聚合函数扩展。 --- ### **二、进阶篇** #### 4. 窗口函数（排名场景） **题目**：查询每个部门工资排名前三的员工。 ```sql SELECT * FROM ( SELECT emp_name, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) t WHERE rn <= 3; ``` **关键点**：`ROW_NUMBER()`生成唯一排名，`RANK()`允许并列。 --- #### 5. 日期处理 **题目**：查询最近30天内的订单（表`orders`含`order_id, order_date`）。 ```sql SELECT order_id FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'; ``` **注意**：Hive中需用`date_sub(CURRENT_DATE(), 30)`。 --- #### 6. 行列转换（行转列） **题目**：将月度销售额从行格式转为列格式（原始表`sales`含`year, month, amount`）。 ```sql SELECT year, MAX(CASE WHEN month=1 THEN amount END) AS Jan, MAX(CASE WHEN month=2 THEN amount END) AS Feb FROM sales GROUP BY year; ``` **扩展**：列转行可用`UNION ALL`或`LATERAL VIEW`（Hive）。 --- ### **三、应用场景篇** #### 7. 数据清洗（去重保留最新记录） **题目**：表`user_log`有重复的`user_id`，需保留`log_time`最新的一条。 ```sql DELETE FROM user_log WHERE (user_id, log_time) NOT IN ( SELECT user_id, MAX(log_time) FROM user_log GROUP BY user_id ); ``` **替代方案**：使用窗口函数`ROW_NUMBER()`标记后删除。 --- #### 8. 留存率计算 **题目**：计算次日留存率（表`login`含`user_id, login_date`）。 ```sql SELECT COUNT(DISTINCT d1.user_id) AS total_users, COUNT(DISTINCT d2.user_id) AS retained_users, retained_users / total_users AS retention_rate FROM login d1 LEFT JOIN login d2 ON d1.user_id = d2.user_id AND d2.login_date = DATE_ADD(d1.login_date, 1); ``` --- #### 9. 拉链表查询历史状态 **题目**：查询用户2023-01-01的等级状态（表`user_level`含`user_id, level, start_date, end_date`）。 ```sql SELECT user_id, level FROM user_level WHERE start_date <= '2023-01-01' AND end_date > '2023-01-01'; ``` **核心逻辑**：时间区间覆盖查询日期。 --- ### **四、性能优化篇** #### 10. 慢查询分析 **题目**：某查询`SELECT * FROM logs WHERE create_time > '2023-01-01'`执行慢，如何优化？ **答案**： - 对`create_time`建索引。 - 避免`SELECT *`，改为指定字段。 - 若表为Hive分区表，按`create_time`分区。 --- ### **附：高频考点总结** | **类型** | **考点** | |----------------|--------------------------| | 基础语法 | JOIN、GROUP BY、HAVING | | 高级函数 | 窗口函数、日期函数、CASE WHEN | | 场景设计 | 留存率、TopN、去重逻辑 | | 数仓特性 | 拉链表、缓慢变化维、分区 | 建议结合具体业务场景练习，并理解执行计划（`EXPLAIN`命令）的使用。