🗄️ 07 · SQL 进阶与数仓取数¶
数据分析师 60% 以上的时间在写 SQL。本节覆盖窗口函数、组内 top-N、连续登录、留存 SQL、行列转换、同比环比六大高频场景,全部用 sqlite3 真实执行。
📋 本节要解决的问题¶
| # | 问题 | SQL 知识点 |
|---|---|---|
| 1 | 三种排名有什么区别? | ROW_NUMBER / RANK / DENSE_RANK |
| 2 | 每个品类销售额 top-2 怎么取? | 窗口函数 + PARTITION BY |
| 3 | 连续登录天数怎么算? | 日期 - 行号 = 分组键 |
| 4 | 次日留存率怎么写? | 自连接 + 口径核算 |
| 5 | 行列转换怎么做? | CASE WHEN 聚合 |
| 6 | 同比环比怎么算? | LAG 窗口函数 |
Copied!
import sqlite3
import pandas as pd
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE orders (order_id INT, user_id INT, city TEXT, category TEXT, amount REAL, dt TEXT)')
cur.execute('CREATE TABLE active_log (user_id INT, dt TEXT)')
rows = [
(1, 101, '北京', '数码', 120.5, '2025-01-05'), (2, 102, '上海', '服饰', 88.0, '2025-01-06'),
(3, 103, '北京', '数码', 210.5, '2025-01-06'), (4, 101, '北京', '食品', 99.0, '2025-01-07'),
(5, 104, '深圳', '服饰', 155.0, '2025-01-07'), (6, 102, '上海', '家居', 300.0, '2025-01-08'),
(7, 105, '杭州', '食品', 45.0, '2025-01-08'), (8, 103, '北京', '数码', 133.5, '2025-01-09'),
(9, 104, '深圳', '食品', 66.0, '2025-01-10'), (10, 101, '北京', '服饰', 188.0, '2025-01-11'),
]
cur.executemany('INSERT INTO orders VALUES (?,?,?,?,?,?)', rows)
# 活跃日志:用户 + 活跃日期
act = [
(101, '2025-01-05'), (101, '2025-01-06'), (101, '2025-01-07'),
(102, '2025-01-06'), (102, '2025-01-07'),
(103, '2025-01-06'), (103, '2025-01-07'), (103, '2025-01-08'),
(104, '2025-01-07'), (104, '2025-01-08'), (104, '2025-01-09'),
(105, '2025-01-08'),
]
cur.executemany('INSERT INTO active_log VALUES (?,?)', act)
conn.commit()
print('SQLite 环境就绪:', cur.execute('SELECT COUNT(*) FROM orders').fetchone()[0], '条订单')
import sqlite3
import pandas as pd
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE orders (order_id INT, user_id INT, city TEXT, category TEXT, amount REAL, dt TEXT)')
cur.execute('CREATE TABLE active_log (user_id INT, dt TEXT)')
rows = [
(1, 101, '北京', '数码', 120.5, '2025-01-05'), (2, 102, '上海', '服饰', 88.0, '2025-01-06'),
(3, 103, '北京', '数码', 210.5, '2025-01-06'), (4, 101, '北京', '食品', 99.0, '2025-01-07'),
(5, 104, '深圳', '服饰', 155.0, '2025-01-07'), (6, 102, '上海', '家居', 300.0, '2025-01-08'),
(7, 105, '杭州', '食品', 45.0, '2025-01-08'), (8, 103, '北京', '数码', 133.5, '2025-01-09'),
(9, 104, '深圳', '食品', 66.0, '2025-01-10'), (10, 101, '北京', '服饰', 188.0, '2025-01-11'),
]
cur.executemany('INSERT INTO orders VALUES (?,?,?,?,?,?)', rows)
# 活跃日志:用户 + 活跃日期
act = [
(101, '2025-01-05'), (101, '2025-01-06'), (101, '2025-01-07'),
(102, '2025-01-06'), (102, '2025-01-07'),
(103, '2025-01-06'), (103, '2025-01-07'), (103, '2025-01-08'),
(104, '2025-01-07'), (104, '2025-01-08'), (104, '2025-01-09'),
(105, '2025-01-08'),
]
cur.executemany('INSERT INTO active_log VALUES (?,?)', act)
conn.commit()
print('SQLite 环境就绪:', cur.execute('SELECT COUNT(*) FROM orders').fetchone()[0], '条订单')
SQLite 环境就绪: 10 条订单
1️⃣ 三种排名:ROW_NUMBER / RANK / DENSE_RANK¶
✍️ §1.1 手算三种排名:分数 [100,100,90,80] 各排第几¶
ROW_NUMBER 逐个编号、RANK 并列跳号、DENSE_RANK 并列不跳号——4 个分数手算,一眼看清区别。
设定:分数 [100, 100, 90, 80]。
| 分数 | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 100 | 1 | 1 | 1 |
| 100 | 2 | 1 | 1 |
| 90 | 3 | 3 | 2 |
| 80 | 4 | 4 | 3 |
手算收获:
- 三兄弟各管一档:ROW_NUMBER = 物理编号(永远 1,2,3,4);RANK = 名次(并列占位,90 变第 3);DENSE_RANK = 紧凑名次(90 变第 2)
- "并列占位 vs 紧凑"一句话速答:RANK 有跳号(100,100,90 → 1,1,3),DENSE_RANK 无跳号(→ 1,1,2)——取"前 N 名"用 DENSE_RANK,取"精确行号"用 ROW_NUMBER
- 与 GROUP BY 区别(§2):窗口函数不折叠行、保留每条明细——组内 top-N 就是 ROW_NUMBER 按组分区(§2 代码)
- 应用链:连续登录(§3)用"日期-行号"分组、留存(§4)用自连接、环比(§6)用 LAG——都是窗口思想
下面 numpy 复算。
Copied!
# ---- §1.1 验证:numpy 复算三种排名 ----
scores = [100, 100, 90, 80]
rn = list(range(1, 5))
# 手算: RANK 并列跳号、DENSE_RANK 紧凑
rank = []
for s in scores:
rank.append(1 + sum(1 for t in scores if t > s))
dense = []
seen = {}
for i, s in enumerate(sorted(set(scores), reverse=True)):
seen[s] = i + 1
dense = [seen[s] for s in scores]
print(f'ROW_NUMBER = {rn}')
print(f'RANK = {rank}')
print(f'DENSE_RANK = {dense}')
assert rn == [1, 2, 3, 4] and rank == [1, 1, 3, 4] and dense == [1, 1, 2, 3]
print('\n✓ 与手算一致')
# ---- §1.1 验证:numpy 复算三种排名 ----
scores = [100, 100, 90, 80]
rn = list(range(1, 5))
# 手算: RANK 并列跳号、DENSE_RANK 紧凑
rank = []
for s in scores:
rank.append(1 + sum(1 for t in scores if t > s))
dense = []
seen = {}
for i, s in enumerate(sorted(set(scores), reverse=True)):
seen[s] = i + 1
dense = [seen[s] for s in scores]
print(f'ROW_NUMBER = {rn}')
print(f'RANK = {rank}')
print(f'DENSE_RANK = {dense}')
assert rn == [1, 2, 3, 4] and rank == [1, 1, 3, 4] and dense == [1, 1, 2, 3]
print('\n✓ 与手算一致')
ROW_NUMBER = [1, 2, 3, 4] RANK = [1, 1, 3, 4] DENSE_RANK = [1, 1, 2, 3] ✓ 与手算一致
Copied!
q = '''
SELECT city, amount,
ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn,
RANK() OVER (PARTITION BY city ORDER BY amount DESC) AS rk,
DENSE_RANK() OVER (PARTITION BY city ORDER BY amount DESC) AS drk
FROM orders ORDER BY city, amount DESC'''
df = pd.DataFrame(cur.execute(q).fetchall(), columns=['city', 'amount', 'ROW_NUMBER', 'RANK', 'DENSE_RANK'])
print(df.to_string(index=False))
# RANK 与 DENSE_RANK 在并列时不同:RANK 跳号、DENSE_RANK 不跳
print('\n➡️ 北京金额并列 120.5 的两行:RANK 都=3(跳号),DENSE_RANK 都=2(不跳)')
q = '''
SELECT city, amount,
ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn,
RANK() OVER (PARTITION BY city ORDER BY amount DESC) AS rk,
DENSE_RANK() OVER (PARTITION BY city ORDER BY amount DESC) AS drk
FROM orders ORDER BY city, amount DESC'''
df = pd.DataFrame(cur.execute(q).fetchall(), columns=['city', 'amount', 'ROW_NUMBER', 'RANK', 'DENSE_RANK'])
print(df.to_string(index=False))
# RANK 与 DENSE_RANK 在并列时不同:RANK 跳号、DENSE_RANK 不跳
print('\n➡️ 北京金额并列 120.5 的两行:RANK 都=3(跳号),DENSE_RANK 都=2(不跳)')
city amount ROW_NUMBER RANK DENSE_RANK 上海 300.0 1 1 1 上海 88.0 2 2 2 北京 210.5 1 1 1 北京 188.0 2 2 2 北京 133.5 3 3 3 北京 120.5 4 4 4 北京 99.0 5 5 5 杭州 45.0 1 1 1 深圳 155.0 1 1 1 深圳 66.0 2 2 2 ➡️ 北京金额并列 120.5 的两行:RANK 都=3(跳号),DENSE_RANK 都=2(不跳)
2️⃣ 组内 top-N:每品类销售额前 2¶
Copied!
q = '''
SELECT category, city, total, rn FROM (
SELECT category, city, SUM(amount) AS total,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(amount) DESC) AS rn
FROM orders GROUP BY category, city
) WHERE rn <= 2 ORDER BY category, rn'''
res = pd.DataFrame(cur.execute(q).fetchall(), columns=['category', 'city', 'total', 'rn'])
print(res.to_string(index=False))
assert len(res) == 6 # 4 品类中有些品类不足 2 个城市
print('\n✅ 组内 top-N 完成')
q = '''
SELECT category, city, total, rn FROM (
SELECT category, city, SUM(amount) AS total,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(amount) DESC) AS rn
FROM orders GROUP BY category, city
) WHERE rn <= 2 ORDER BY category, rn'''
res = pd.DataFrame(cur.execute(q).fetchall(), columns=['category', 'city', 'total', 'rn'])
print(res.to_string(index=False))
assert len(res) == 6 # 4 品类中有些品类不足 2 个城市
print('\n✅ 组内 top-N 完成')
category city total rn
家居 上海 300.0 1
数码 北京 464.5 1
服饰 北京 188.0 1
服饰 深圳 155.0 2
食品 北京 99.0 1
食品 深圳 66.0 2
✅ 组内 top-N 完成
3️⃣ 连续登录天数(经典题)¶
Copied!
# 思路:活跃日期 - 按用户排序后的行号 = 分组键,连续的日子落在同一组
q = '''
SELECT user_id, MIN(dt) AS start_dt, MAX(dt) AS end_dt, COUNT(*) AS days FROM (
SELECT user_id, dt,
julianday(dt) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY dt) AS grp
FROM active_log
) GROUP BY user_id, grp ORDER BY user_id, start_dt'''
res = cur.execute(q).fetchall()
for r in res:
print(f'用户 {r[0]}: {r[1]} → {r[2]},连续 {r[3]} 天')
assert max(r[3] for r in res) == 3
print('\n✅ 最长连续登录 3 天(用户 103)')
# 思路:活跃日期 - 按用户排序后的行号 = 分组键,连续的日子落在同一组
q = '''
SELECT user_id, MIN(dt) AS start_dt, MAX(dt) AS end_dt, COUNT(*) AS days FROM (
SELECT user_id, dt,
julianday(dt) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY dt) AS grp
FROM active_log
) GROUP BY user_id, grp ORDER BY user_id, start_dt'''
res = cur.execute(q).fetchall()
for r in res:
print(f'用户 {r[0]}: {r[1]} → {r[2]},连续 {r[3]} 天')
assert max(r[3] for r in res) == 3
print('\n✅ 最长连续登录 3 天(用户 103)')
用户 101: 2025-01-05 → 2025-01-07,连续 3 天 用户 102: 2025-01-06 → 2025-01-07,连续 2 天 用户 103: 2025-01-06 → 2025-01-08,连续 3 天 用户 104: 2025-01-07 → 2025-01-09,连续 3 天 用户 105: 2025-01-08 → 2025-01-08,连续 1 天 ✅ 最长连续登录 3 天(用户 103)
4️⃣ 次日留存率(自连接 + 口径)¶
Copied!
# 分母:每个日期的活跃用户数;分子:次日仍活跃的用户数
q = '''
SELECT a.dt AS day,
COUNT(DISTINCT a.user_id) AS active_users,
COUNT(DISTINCT b.user_id) AS retained_next,
ROUND(1.0*COUNT(DISTINCT b.user_id)/COUNT(DISTINCT a.user_id), 3) AS retention
FROM active_log a
LEFT JOIN active_log b ON a.user_id = b.user_id
AND b.dt = date(a.dt, '+1 day')
GROUP BY a.dt ORDER BY a.dt'''
res = cur.execute(q).fetchall()
for r in res:
print(f'{r[0]}: 活跃 {r[1]},次日留存 {r[2]},留存率 {r[3]}')
print('\n➡️ 口径提醒:分母是「当天活跃用户」,不是「当天新增用户」')
# 分母:每个日期的活跃用户数;分子:次日仍活跃的用户数
q = '''
SELECT a.dt AS day,
COUNT(DISTINCT a.user_id) AS active_users,
COUNT(DISTINCT b.user_id) AS retained_next,
ROUND(1.0*COUNT(DISTINCT b.user_id)/COUNT(DISTINCT a.user_id), 3) AS retention
FROM active_log a
LEFT JOIN active_log b ON a.user_id = b.user_id
AND b.dt = date(a.dt, '+1 day')
GROUP BY a.dt ORDER BY a.dt'''
res = cur.execute(q).fetchall()
for r in res:
print(f'{r[0]}: 活跃 {r[1]},次日留存 {r[2]},留存率 {r[3]}')
print('\n➡️ 口径提醒:分母是「当天活跃用户」,不是「当天新增用户」')
2025-01-05: 活跃 1,次日留存 1,留存率 1.0 2025-01-06: 活跃 3,次日留存 3,留存率 1.0 2025-01-07: 活跃 4,次日留存 2,留存率 0.5 2025-01-08: 活跃 3,次日留存 1,留存率 0.333 2025-01-09: 活跃 1,次日留存 0,留存率 0.0 ➡️ 口径提醒:分母是「当天活跃用户」,不是「当天新增用户」
5️⃣ 行列转换(行转列)¶
Copied!
# 将「城市×品类订单数」转成透视样式(用 CASE WHEN + 聚合模拟)
q = '''
SELECT city,
SUM(CASE WHEN category='数码' THEN 1 ELSE 0 END) AS digi,
SUM(CASE WHEN category='服饰' THEN 1 ELSE 0 END) AS cloth,
SUM(CASE WHEN category='食品' THEN 1 ELSE 0 END) AS food,
SUM(CASE WHEN category='家居' THEN 1 ELSE 0 END) AS home
FROM orders GROUP BY city ORDER BY city'''
res = pd.DataFrame(cur.execute(q).fetchall(), columns=['city', '数码', '服饰', '食品', '家居'])
print(res.to_string(index=False))
assert res.loc[res['city'] == '北京', '数码'].iloc[0] == 3
print('\n✅ 行转列完成(等价于 pandas pivot_table)')
# 将「城市×品类订单数」转成透视样式(用 CASE WHEN + 聚合模拟)
q = '''
SELECT city,
SUM(CASE WHEN category='数码' THEN 1 ELSE 0 END) AS digi,
SUM(CASE WHEN category='服饰' THEN 1 ELSE 0 END) AS cloth,
SUM(CASE WHEN category='食品' THEN 1 ELSE 0 END) AS food,
SUM(CASE WHEN category='家居' THEN 1 ELSE 0 END) AS home
FROM orders GROUP BY city ORDER BY city'''
res = pd.DataFrame(cur.execute(q).fetchall(), columns=['city', '数码', '服饰', '食品', '家居'])
print(res.to_string(index=False))
assert res.loc[res['city'] == '北京', '数码'].iloc[0] == 3
print('\n✅ 行转列完成(等价于 pandas pivot_table)')
city 数码 服饰 食品 家居 上海 0 1 0 1 北京 3 1 1 0 杭州 0 0 1 0 深圳 0 1 1 0 ✅ 行转列完成(等价于 pandas pivot_table)
6️⃣ 同比 / 环比(LAG 窗口函数)¶
Copied!
# 构造月度销售数据
cur.execute('DROP TABLE IF EXISTS monthly')
cur.execute('CREATE TABLE monthly (month TEXT, sales REAL)')
mrows = [('2025-01', 100), ('2025-02', 108), ('2025-03', 95), ('2025-04', 120)]
cur.executemany('INSERT INTO monthly VALUES (?,?)', mrows)
conn.commit()
q = '''
SELECT month, sales,
LAG(sales) OVER (ORDER BY month) AS prev_month,
ROUND(100.0*(sales - LAG(sales) OVER (ORDER BY month))/LAG(sales) OVER (ORDER BY month), 1) AS mom_pct
FROM monthly ORDER BY month'''
res = pd.DataFrame(cur.execute(q).fetchall(), columns=['month', 'sales', '上月', '环比%'])
print(res.to_string(index=False))
print('\n➡️ 同比需与上年同月比较(需保留上年数据);环比与上一期比较')
# 构造月度销售数据
cur.execute('DROP TABLE IF EXISTS monthly')
cur.execute('CREATE TABLE monthly (month TEXT, sales REAL)')
mrows = [('2025-01', 100), ('2025-02', 108), ('2025-03', 95), ('2025-04', 120)]
cur.executemany('INSERT INTO monthly VALUES (?,?)', mrows)
conn.commit()
q = '''
SELECT month, sales,
LAG(sales) OVER (ORDER BY month) AS prev_month,
ROUND(100.0*(sales - LAG(sales) OVER (ORDER BY month))/LAG(sales) OVER (ORDER BY month), 1) AS mom_pct
FROM monthly ORDER BY month'''
res = pd.DataFrame(cur.execute(q).fetchall(), columns=['month', 'sales', '上月', '环比%'])
print(res.to_string(index=False))
print('\n➡️ 同比需与上年同月比较(需保留上年数据);环比与上一期比较')
month sales 上月 环比% 2025-01 100.0 NaN NaN 2025-02 108.0 100.0 8.0 2025-03 95.0 108.0 -12.0 2025-04 120.0 95.0 26.3 ➡️ 同比需与上年同月比较(需保留上年数据);环比与上一期比较
7️⃣ 取数规范与数仓思维(必背)¶
- 口径先行:先确认指标定义(分母/分子、去重键、时间范围)再写 SQL;
- 避免全表扫描:加时间分区过滤、只 SELECT 所需列、用好索引;
- 去重要明确:同一用户多单是否算多次?用 DISTINCT 还是 GROUP BY?
- NULL 处理:COUNT(col) 忽略 NULL,COUNT(*) 不忽略;LEFT JOIN 后用
IS NULL找未匹配; - 结果校验:抽样核对、与业务系统交叉验证、断言行数/总额;
- 写完自查:数据量级是否异常?有无重复?时间边界是否闭合?
📝 自测清单¶
- 能讲清 ROW_NUMBER / RANK / DENSE_RANK 区别并手写
- 能手写组内 top-N(窗口函数 row_number + 子查询)
- 能手写连续登录(日期-行号分组)并解释原理
- 能写出次日留存率 SQL,并说清分母口径
- 能用 CASE WHEN 做行转列
- 能用 LAG 算环比/同比