大数据学习（121）-sql重点问题

🍋🍋大数据学习🍋🍋

🔥系列专栏： 👑哲学语录: 用力所能及，改变世界。
💖如果觉得博主的文章还不错的话，请点赞👍+收藏⭐️+留言📝支持一下博主哦🤞

窗口函数使用技巧

窗口函数是 SQL 中处理复杂数据分析的强大工具，常用于排名、累计计算、同比环比等场景。以下从基础语法、高频函数、性能优化、典型场景四个维度总结核心技巧，并附示例代码和避坑指南。

一、基础语法与核心组件

1. 完整语法结构

sql

<窗口函数> OVER (
    [PARTITION BY 字段1, 字段2...]  -- 分组依据
    [ORDER BY 字段1 [ASC/DESC], 字段2...]  -- 排序规则
    [窗口子句]  -- 可选，定义窗口范围
)

• 窗口函数分类：

  • 排名函数：ROW_NUMBER()、RANK()、DENSE_RANK()
  • 聚合函数：SUM()、AVG()、COUNT()、MAX()、MIN()
  • 偏移函数：LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE()

• 执行顺序：

  1. FROM → WHERE → GROUP BY → HAVING（生成基础结果集）
  2. 窗口函数计算（基于基础结果集）
  3. SELECT → ORDER BY → LIMIT（过滤和排序最终结果）

二、高频窗口函数与典型场景

1. 排名函数：处理排序与去重

典型场景：

• Top N 筛选：查询每个部门工资最高的 3 人。

  WITH ranked_employees AS (
      SELECT 
          *,
          ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
      FROM employees
  )
  SELECT * FROM ranked_employees WHERE rn <= 3;
  

• 去重：删除表中重复记录，保留主键最小的行。

  WITH duplicates AS (
      SELECT 
          *,
          ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
      FROM users
  )
  DELETE FROM duplicates WHERE rn > 1;
  

2. 聚合函数：动态计算分组统计值

典型场景：

• 累计计算：计算每月累计销售额。

  SELECT 
      month,
      sales_amount,
      SUM(sales_amount) OVER (ORDER BY month) AS cumulative_sales
  FROM monthly_sales;
  

• 移动平均：计算近 3 天的平均订单量。

  SELECT 
      order_date,
      order_count,
      AVG(order_count) OVER (
          ORDER BY order_date 
          RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
      ) AS 3_day_avg
  FROM daily_orders;
  

3. 偏移函数：获取前后行数据

典型场景：

• 环比计算：

  SELECT 
      month,
      sales,
      LAG(sales, 1) OVER (ORDER BY month) AS prev_month_sales,
      ROUND((sales - LAG(sales, 1) OVER (ORDER BY month)) / LAG(sales, 1) OVER (ORDER BY month) * 100, 2) AS growth_rate
  FROM monthly_sales;
  

• 异常检测：找出销售额突然变化的月份。

  SELECT 
      month,
      sales,
      ABS(sales - LAG(sales, 1) OVER (ORDER BY month)) AS sales_change
  FROM monthly_sales
  WHERE ABS(sales - LAG(sales, 1) OVER (ORDER BY month)) > 1000;  -- 变化超过 1000
  

三、窗口子句（Window Frame）高级技巧

1. 窗口范围定义

-- 语法
<窗口函数> OVER (
    PARTITION BY ...
    ORDER BY ...
    [ROWS/RANGE BETWEEN <起始> AND <结束>]
)

• ROWS vs RANGE：
  • ROWS：按物理行计数（无论值是否相同）。
  • RANGE：按逻辑值范围（相同值视为同一行）。

示例：计算当前行及前两行的累计值。

SUM(sales) OVER (
    ORDER BY month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

2. 常见窗口范围写法

四、性能优化与避坑指南

1. 索引优化

• 为 PARTITION BY 和 ORDER BY 字段创建复合索引：

  CREATE INDEX idx_dept_salary ON employees (dept_id, salary);
  

   
  • 适用场景：频繁按部门分组并按工资排序的查询。

• 覆盖索引：若查询仅需窗口函数结果，可创建覆盖索引避免回表。

  CREATE INDEX idx_month_sales ON monthly_sales (month, sales);
  

2. 避免重复计算

• 用 CTE 缓存中间结果：

  WITH ranked_data AS (
      SELECT 
          *,
          ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
      FROM employees
  )
  SELECT * FROM ranked_data WHERE rn <= 3;  -- 避免重复计算排名
  

3. 窗口函数 vs 自连接

• 窗口函数更高效：

  -- 低效：自连接计算部门平均工资
  SELECT 
      e.emp_id,
      e.salary,
      d.avg_salary
  FROM employees e
  JOIN (
      SELECT dept_id, AVG(salary) AS avg_salary 
      FROM employees 
      GROUP BY dept_id
  ) d ON e.dept_id = d.dept_id;
  
  -- 高效：窗口函数直接计算
  SELECT 
      emp_id,
      dept_id,
      salary,
      AVG(salary) OVER (PARTITION BY dept_id) AS avg_salary
  FROM employees;
  

五、高频面试题

问题 1：如何用窗口函数实现用户留存分析？

答案示例：

WITH first_login AS (
    SELECT 
        user_id,
        MIN(login_date) AS first_date
    FROM user_login
    GROUP BY user_id
),
login_days AS (
    SELECT 
        fl.user_id,
        fl.first_date,
        ul.login_date,
        DATEDIFF(ul.login_date, fl.first_date) AS days_since_first
    FROM first_login fl
    JOIN user_login ul ON fl.user_id = ul.user_id
)
SELECT 
    first_date,
    COUNT(DISTINCT user_id) AS new_users,
    SUM(CASE WHEN days_since_first = 1 THEN 1 ELSE 0 END) AS day1_retained,
    ROUND(SUM(CASE WHEN days_since_first = 1 THEN 1 ELSE 0 END) / COUNT(DISTINCT user_id) * 100, 2) AS day1_retention_rate
FROM login_days
GROUP BY first_date
ORDER BY first_date;

问题 2：窗口函数中 ORDER BY 的作用是什么？

答案要点：

• 对聚合函数：ORDER BY 定义窗口范围的排序规则（如累计值按时间递增）。
• 对排名函数：ORDER BY 决定按哪个字段排名（如按工资降序排名）。
• 若省略 ORDER BY：窗口范围默认为整个分区（所有行）。

问题 3：LAG() 和 LEAD() 的区别是什么？

答案要点：

• LAG()：获取当前行之前的第 N 行数据（用于环比、历史对比）。
• LEAD()：获取当前行之后的第 N 行数据（用于预测、提前预警）。

六、实战建议

1. 复杂查询分步骤编写：

   -- 步骤 1：计算基础数据
   WITH base_data AS (
       SELECT 
           user_id,
           order_date,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS order_rank
       FROM orders
   )
   -- 步骤 2：筛选首次订单
   SELECT * FROM base_data WHERE order_rank = 1;
   

2. 结合业务场景选择窗口函数：

   • 排名需求：用 ROW_NUMBER()/RANK()。
   • 时间序列分析：用 LAG()/LEAD()。
   • 动态聚合：用 SUM() OVER()/AVG() OVER()。

3. 注意窗口范围边界：

   -- 错误：未指定 ORDER BY，默认窗口为整个分区
   SUM(sales) OVER (PARTITION BY month)  -- 返回每月总和，而非累计值
   
   -- 正确：明确 ORDER BY，计算累计值
   SUM(sales) OVER (PARTITION BY month ORDER BY day)
   

总结：窗口函数使用策略

通过灵活组合窗口函数、窗口子句和索引优化，可高效解决各类复杂数据分析需求，提升 SQL 查询的表达能力和执行效率。

用简单的话直接说明partition by 与group by 的主要区别

核心区别：

1. 分组后结果行数：

   • GROUP BY：合并分组，结果行数减少（每组1行）。
   • PARTITION BY：保留原始行，结果行数不变（每组多行）。

2. 功能用途：

   • GROUP BY：用于聚合统计（如求和、计数）。
   • PARTITION BY：用于窗口计算（如排名、累计值）。

一句话总结：

• GROUP BY 是“合并同类项”，生成新的汇总行；
• PARTITION BY 是“分组加特效”，在原数据上叠加计算结果。

在出一些相关的sql上的细节问题

以下是补充的 SQL 细节问题清单，聚焦高频考点中的易错点和细节差异，覆盖 JOIN、聚合函数、NULL 处理、索引优化 等维度，附答案要点和示例代码：

一、JOIN 细节问题

1. LEFT JOIN 与 INNER JOIN 的结果差异

问题：
两张表 A (id: 1,2,3) 和 B (id: 2,3,4)，执行 A LEFT JOIN B ON A.id = B.id 和 A INNER JOIN B ON A.id = B.id 的结果分别是什么？

答案要点：

• LEFT JOIN：返回 A 的所有行（1,2,3），匹配不上的 B 字段填 NULL。

  SELECT A.id, B.id FROM A LEFT JOIN B ON A.id = B.id;
  -- 结果：(1,NULL), (2,2), (3,3)
  

• INNER JOIN：仅返回匹配的行（2,3）。

  SELECT A.id, B.id FROM A INNER JOIN B ON A.id = B.id;
  -- 结果：(2,2), (3,3)
  

2. ON 与 WHERE 条件的执行顺序

问题：
LEFT JOIN 中，ON A.x = B.x AND B.y = 'value' 和 WHERE B.y = 'value' 的区别是什么？

答案要点：

• ON 条件：在连接时过滤右表（B），不影响左表（A）的保留。

  SELECT A.id, B.y FROM A LEFT JOIN B ON A.id = B.id AND B.y = 'active';
  -- 结果：A 的所有行，B 中不满足 y='active' 的行填 NULL
  

• WHERE 条件：在连接后过滤整个结果集，可能导致左表行被剔除。

  SELECT A.id, B.y FROM A LEFT JOIN B ON A.id = B.id WHERE B.y = 'active';
  -- 结果：仅保留 B.y='active' 的行，等价于 INNER JOIN
  

二、聚合函数细节

3. COUNT 的不同用法

问题：
COUNT(*)、COUNT(column)、COUNT(DISTINCT column) 的区别是什么？

答案要点：

• COUNT(*)：统计所有行，包括 NULL 值。
• COUNT(column)：统计 column 非 NULL 的行。
• COUNT(DISTINCT column)：统计 column 非重复且非 NULL 的值。

示例：

CREATE TABLE test (id INT, name VARCHAR(10));
INSERT INTO test VALUES (1, NULL), (2, 'Alice'), (3, 'Alice');

SELECT 
    COUNT(*) AS count_all,          -- 结果：3
    COUNT(name) AS count_name,      -- 结果：2（排除 NULL）
    COUNT(DISTINCT name) AS count_distinct;  -- 结果：1（去重后 'Alice'）

4. 聚合函数与 NULL 的关系

问题：
SUM(column)、AVG(column) 遇到 NULL 值如何处理？

答案要点：

• 聚合函数忽略 NULL：

  CREATE TABLE sales (amount INT);
  INSERT INTO sales VALUES (100), (NULL), (200);
  
  SELECT 
    SUM(amount) AS total,     -- 结果：300（忽略 NULL）
    AVG(amount) AS average;   -- 结果：150（计算时排除 NULL）
  

• 若需包含 NULL：用 COALESCE 转换为 0。

  SELECT 
    SUM(COALESCE(amount, 0)) AS total,  -- 结果：300 + 0 = 300
    AVG(COALESCE(amount, 0)) AS average;  -- 结果：(100+0+200)/3 = 100
  

三、NULL 处理细节

5. NULL 的比较规则

问题：
WHERE column = NULL 和 WHERE column IS NULL 的区别是什么？

答案要点：

• = 无法判断 NULL：

  SELECT * FROM users WHERE email = NULL;  -- 永远返回空结果
  

• 必须用 IS NULL 或 IS NOT NULL：

  SELECT * FROM users WHERE email IS NULL;  -- 正确
  

6. COALESCE 与 IFNULL 的区别

问题：
COALESCE(value1, value2) 和 IFNULL(value1, value2) 的区别是什么？

答案要点：

• COALESCE：支持多个参数，返回第一个非 NULL 值（标准 SQL）。

  COALESCE(NULL, 1, 2)  -- 结果：1
  

• IFNULL：仅支持两个参数（MySQL 特有）。

  IFNULL(NULL, 1)  -- 结果：1
  

四、索引优化细节

7. 复合索引的最左匹配原则

问题：
索引 (a, b, c) 能加速哪些查询？

答案要点：

• 可加速：

  WHERE a = 1              -- 匹配 a
  WHERE a = 1 AND b = 2    -- 匹配 a,b
  WHERE a = 1 AND b = 2 AND c = 3  -- 匹配 a,b,c
  

• 无法加速：

  WHERE b = 2              -- 未从 a 开始
  WHERE a = 1 AND c = 3    -- 跳过 b
  

8. 索引失效的常见场景

问题：
哪些操作会导致索引失效？

答案要点：

• 对索引字段使用函数：

  WHERE YEAR(create_time) = 2023  -- 索引失效
  

• 隐式类型转换：

  WHERE id = '123'  -- 若 id 为 INT，触发类型转换
  

• 范围查询后的字段：

  WHERE a > 10 AND b = 20  -- 索引 (a, b) 仅 a 生效
  

五、SQL 执行顺序细节

9. SQL 子句的执行顺序

问题：
写出 SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT 的执行顺序。

答案要点：

1. FROM → 2. WHERE → 3. GROUP BY → 4. HAVING → 5. SELECT → 6. ORDER BY → 7. LIMIT

10. 子查询与 JOIN 的性能差异

问题：
何时使用子查询，何时使用 JOIN？

答案要点：

• 子查询：适用于单行 / 单列的结果（如标量子查询）。

  SELECT name FROM users WHERE id = (SELECT user_id FROM orders WHERE order_id = 1);
  

• JOIN：适用于多表关联，性能通常更优。

  SELECT u.name FROM users u JOIN orders o ON u.id = o.user_id WHERE o.order_id = 1;
  

六、日期时间处理细节

11. 日期比较的常见陷阱

问题：
如何查询 2023-01-01 到 2023-01-31 之间的数据？

答案要点：

• 正确写法：

  WHERE date_column >= '2023-01-01' AND date_column < '2023-02-01';
  

• 错误写法：

  WHERE date_column BETWEEN '2023-01-01' AND '2023-01-31';  -- 可能漏掉 23:59:59 的记录
  

12. 不同数据库的日期函数差异

问题：
如何获取当前日期？

答案要点：

七、字符串处理细节

13. 字符串拼接的语法差异

问题：
如何拼接字符串 'Hello' 和 'World'？

答案要点：

14. 字符串截取函数

问题：
如何截取字符串 'abcdef' 的前 3 个字符？

答案要点：

八、业务场景细节

15. 如何实现分页查询？

问题：
查询第 10 页（每页 10 条）的数据。

答案要点：

• MySQL/PostgreSQL：

  SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 90;  -- 第 10 页
  

• 优化大分页：

  SELECT * FROM users WHERE id > 90 LIMIT 10;  -- 通过主键定位，避免全表扫描
  

16. 如何设计自增主键？

问题：
自增主键 vs UUID 的适用场景？

答案要点：

• 自增主键：性能优，适合频繁插入（如订单表）。

  CREATE TABLE orders (id BIGINT AUTO_INCREMENT PRIMARY KEY, ...);
  

• UUID：分布式场景，避免主键冲突（如用户表）。

  CREATE TABLE users (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), ...);
  

九、面试应答技巧

1. 分步骤解释复杂问题：
   例如解释窗口函数时：

   " 第一步，PARTITION BY 将数据分组；第二步，ORDER BY 确定每组内的排序；第三步，窗口函数对每组数据进行计算。"

2. 主动对比不同方案：
   例如优化慢查询时：

   "我会先分析执行计划，若发现全表扫描，优先添加索引；若索引已存在但仍慢，考虑是否需要拆分大查询或使用覆盖索引。"

3. 结合业务场景举例：
   例如解释反范式化时：

   "在电商订单表中，我们冗余存储了用户姓名，因为订单查询时 90% 的场景需要展示用户信息，这样能减少 JOIN 操作，提升查询速度。"

总结

SQL 面试的细节陷阱主要集中在：

1. 逻辑差异：LEFT JOIN vs INNER JOIN、ON vs WHERE、GROUP BY vs PARTITION BY。
2. NULL 处理：NULL 不能用 = 比较，聚合函数默认忽略 NULL。
3. 索引优化：复合索引的最左匹配、索引失效场景。
4. 函数差异：不同数据库的日期 / 字符串函数语法。