目录

SQL一定要会

这篇文章不会有详细的教程，你需要跟大模型一起成长

目标

环境准备

安装VSCode + DuckDB，就可以在本地用SQL操作csv文件了

https://marketplace.visualstudio.com/items?itemName=RandomFractalsInc.duckdb-sql-tools

user_id,username,phone
1,张三,13800138001
2,李四,13900139002
3,王五,13700137003

order_id,user_id,amount,status
1001,1,250.00,已支付
1002,1,88.50,待付款
1003,2,1200.00,已发货

用DuckDB导入这两个文件就可以查询了

SQL基础语法

SELECT username, phone
FROM users
WHERE username = '张三'

SELECT * FROM orders ORDER BY amount DESC;

因为用户信息和订单信息在不同的文件/表里，你需要把它们“拼”起来, 比如下面通过user_id拼起来

SELECT orders.order_id, users.username, orders.amount
FROM orders
LEFT JOIN users ON orders.user_id = users.user_id;

求和

SELECT SUM(amount) FROM orders;

分组统计

SELECT user_id, SUM(amount) 
FROM orders 
GROUP BY user_id;

COUNT(): 统计行数（例如：总共有多少个订单）。

SUM(): 求和（例如：本月总销售额）。

AVG(): 平均值（例如：客单价）。

MAX() / MIN(): 最大/最小值（例如：最高的一笔消费）

你可以把它想象成“套娃”：先通过内层的查询算出一个结果，再把这个结果交给外层的查询去使用。

SELECT * FROM users 
WHERE user_id IN (SELECT DISTINCT user_id FROM orders);

简单来说，CTE 就是把一个复杂的子查询“拎出来”定义成一个临时结果集，并给它起个名字。它就像是 SQL 脚本里的临时变量，让你的代码读起来像是在讲故事，而不是在玩套娃。而且可以复用，不用重复写多次

这里我们没用子查询，而是定义了一个临时表order_summary，就不用在select语句写复杂的代码了

WITH order_summary AS (
    SELECT DISTINCT user_id 
    FROM orders
)
SELECT u.* FROM users u
LEFT JOIN order_summary s ON u.user_id = s.user_id;
操作符含义比喻
UNION并集A + B (去重)
INTERSECT交集A 和 B 共同有的部分
EXCEPT差集A 有但 B 没有的部分

找出那些注册了但从来没买过东西的用户

SELECT user_id FROM users
EXCEPT
SELECT user_id FROM orders;

练习题

根据前面的 users / orders 两表，动手写一写下面的题，写完可以交给大模型帮你批改。

基础查询练习

  1. 查询所有"已支付"状态的订单
  2. 查询金额大于 100 的订单，并按金额降序排列
  3. 查出用户"张三"的电话号码
  4. 用 JOIN 查出每一笔订单对应的用户名和金额
  5. 找出注册了但从未下过单的用户

参考解答：

-- 1
SELECT * FROM orders WHERE status = '已支付';

-- 2
SELECT * FROM orders WHERE amount > 100 ORDER BY amount DESC;

-- 3
SELECT phone FROM users WHERE username = '张三';

-- 4
SELECT orders.order_id, users.username, orders.amount
FROM orders
JOIN users ON orders.user_id = users.user_id;

-- 5
SELECT user_id FROM users
EXCEPT
SELECT user_id FROM orders;

聚合统计练习

  1. 统计每个用户的下单总金额
  2. 统计每个订单状态各有多少笔订单
  3. 找出订单总金额超过 1000 的用户（提示：用 HAVING）
  4. 统计总共有多少笔订单、平均金额是多少

参考解答：

-- 1
SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id;

-- 2
SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status;

-- 3
SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;

-- 4
SELECT COUNT(*) AS order_count, AVG(amount) AS avg_amount
FROM orders;

进阶内容：SQL 面试题

SQL 面试题通常分为语法基础与概念辨析、经典高频手写题以及性能优化与底层原理三大类。

核心语法与概念

-- users(2行) CROSS JOIN orders(3行) → 6行
SELECT users.username, orders.order_id
FROM users
CROSS JOIN orders;

张三|1001
张三|1002
张三|1003
李四|1001
李四|1002
李四|1003

实际业务中 CROSS JOIN 很少单独使用（数据量会爆炸式增长），常用来生成排列组合，比如商品 × 尺码。

高频手写题型

排名与分组 Top N

考查窗口函数（ROW_NUMBER() 连续唯一排序、RANK() 跳跃排序、DENSE_RANK() 连续同分排序）。

-- 查询每个部门薪资前 3 名的员工
SELECT department_id, employee_id, salary
FROM (
    SELECT department_id, employee_id, salary,
           DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rk
    FROM employees
) t
WHERE rk <= 3;

连续活跃/连续登录问题

经典“日期差值法”，利用 登录日期 - ROW_NUMBER() 日期 得到固定基准日期来分组。比如找出连续登录 ≥ 3 天的用户：

-- 登录表 login_log(user_id, login_date)，先按 (user_id, login_date) 去重（一天可能登录多次）
WITH t AS (
    SELECT DISTINCT user_id, login_date
    FROM login_log
),
base AS (
    SELECT user_id, login_date,
           login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS base_date
    FROM t
)
SELECT user_id, COUNT(*) AS consecutive_days
FROM base
GROUP BY user_id, base_date
HAVING COUNT(*) >= 3;

这里的 base CTE 核心作用是构造一个用于分组的"恒定基准日期"（分组标识），以此把连续的日期归为同一组。

核心原理：等差数列同步增长。本质是利用"日期与连续序号"的差值分组——中间一旦断签，后面的日期相对序号多出来的天数，就会让它自动掉进另一个分组。

直观数据示例——假设用户在 1 号至 3 号连续登录，断签后 6 号、7 号又连续登录：

login_dateROW_NUMBER()差值计算 (login_date - rn 天)归属分组 (base_date)
2026-01-0112026-01-01 - 1 天2025-12-31（第 1 段连续）
2026-01-0222026-01-02 - 2 天2025-12-31（第 1 段连续）
2026-01-0332026-01-03 - 3 天2025-12-31（第 1 段连续）
断签---
2026-01-0642026-01-06 - 4 天2026-01-02（第 2 段连续）
2026-01-0752026-01-07 - 5 天2026-01-02（第 2 段连续）

后续只需执行 GROUP BY user_id, base_date，再统计 COUNT(*)，就能直接算出每一段连续登录的具体天数。

语法提示：不同数据库对日期减整数的语法支持略有不同。在 MySQL 中通常写为 DATE_SUB(login_date, INTERVAL rn DAY)，而在 PostgreSQL / DuckDB 中可直接使用 login_date - rn 或 login_date - INTERVAL rn DAY。

留存率计算

次日留存、7 日留存，通常使用用户首日行为表通过 LEFT JOIN 关联后续日期的行为记录。

先理解两个业务概念：

一句话：留存率 = 第 N 天仍活跃的首日用户数 ÷ 首日新增用户数，用来衡量产品留住用户的能力，是运营和增长分析的核心指标。

从纯粹的指标定义与数据分析视角来看，7 日留存率（Day 7 Retention Rate）业内最标准、最通用的定义就是严格的“第 7 天”。

标准定义：某一天（$D_0$）新增或首次活跃的用户中，恰好在第 7 天（$D_0 + 7$ 天）当天又有活跃行为的用户占比。

公式：

$$\text{7 日留存率} = \frac{\text{在 } D_0 + 7 \text{ 天活跃的 } D_0 \text{ 批次用户数}}{\text{在 } D_0 \text{ 天新增/首次活跃的用户总数}} \times 100\%$$

为什么标准定义一定要卡"第 7 天"：

容易混淆的几个"7 天"指标对比：

指标名称英文术语核心时间窗口衡量目的
7 日留存率（标准）Day 7 Retention仅看第 7 天当天（$D_0 + 7$）衡量产品在完整周周期后的习惯养成与粘性
7 日内留存率7-Day Range Retention第 1 天至第 7 天（$[D_1, D_7]$）任意一天衡量新手期的整体未流失率（漏斗转化）
周留存率Week 1 Retention第 2 周（即 8~14 天）内任意一天适用于中低频产品，放宽单日波动的颗粒度
-- 活跃表 user_active(user_id, active_date)
WITH first_active AS (  -- 每个用户的首日活跃日期
    SELECT user_id, MIN(active_date) AS first_date
    FROM user_active
    GROUP BY user_id
)
SELECT f.first_date,
       COUNT(DISTINCT f.user_id)                                   AS new_users,
       COUNT(DISTINCT a1.user_id) * 1.0 / COUNT(DISTINCT f.user_id) AS day1_retention,
       COUNT(DISTINCT a7.user_id) * 1.0 / COUNT(DISTINCT f.user_id) AS day7_retention
FROM first_active f
LEFT JOIN user_active a1 ON f.user_id = a1.user_id
                        AND a1.active_date = f.first_date + INTERVAL 1 DAY
LEFT JOIN user_active a7 ON f.user_id = a7.user_id
                        AND a7.active_date = f.first_date + INTERVAL 7 DAY
GROUP BY f.first_date;

思路：把首日活跃表当作"基准"，分别去 JOIN 第 1 天、第 7 天的活跃记录，JOIN 上了就说明该用户次日/7 日留存，除以首日新增人数即得留存率。上面的 SQL 采用业界标准的严格口径——a7.active_date = first_date + INTERVAL 7 DAY 精确匹配第 7 天当天。MySQL 中写法为 DATE_ADD(f.first_date, INTERVAL 7 DAY)。

查找并删除重复数据

利用 GROUP BY ... HAVING COUNT(*) > 1 或窗口函数标记 ROW_NUMBER() > 1。

-- 用户表 users_dup(user_id, username, email)，以 email 判断重复

-- 第一步：找出有重复的 email
SELECT email, COUNT(*) AS cnt
FROM users_dup
GROUP BY email
HAVING COUNT(*) > 1;

-- 第二步：删除重复行，每组保留 user_id 最小的一条
DELETE FROM users_dup
WHERE user_id NOT IN (
    SELECT MIN(user_id) FROM users_dup GROUP BY email
);

窗口函数法更适合“先标记看看哪些是重复行”：

SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS rn
FROM users_dup;
-- rn > 1 的就是重复行

-- 配合 CTE 直接删除重复行
WITH ranked AS (
    SELECT user_id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS rn
    FROM users_dup
)
DELETE FROM users_dup
WHERE user_id IN (SELECT user_id FROM ranked WHERE rn > 1);

性能优化与底层原理

D2 Diagram
qtopie.github.io