SQL 分析查询

SQL 是 BI 分析师最高频的工具：从数仓取数、聚合 KPI、做 cohort 与排名，都依赖扎实的查询能力。与 /roadmaps/bi-analyst 中 SQL 节点对齐。

学习目标

• 熟练 SELECT、JOIN、GROUP BY、HAVING 分析写法
• 掌握窗口函数做排名、累计、同比
• 避免常见性能与口径错误（笛卡尔积、重复计数）

核心概念

基础分析模式

-- 每门课程的 lesson 数量
SELECT t.slug, t.title, COUNT(l.id) AS lesson_count
FROM cf_track t
LEFT JOIN cf_lesson l ON l.track_id = t.id
GROUP BY t.id, t.slug, t.title
ORDER BY lesson_count DESC;

• LEFT JOIN：保留无 lesson 的 track
• GROUP BY：与 SELECT 非聚合字段一致
• 用 COUNT(DISTINCT user_id) 防重复计数

高级：窗口函数

函数	用途
ROW_NUMBER()	每组 Top N
SUM() OVER (...)	累计 GMV、Running total
LAG/LEAD	环比、前后对比
NTILE	用户分群（十分位）

-- 按 category 内课程 lesson 数排名
SELECT slug, category, lesson_count,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY lesson_count DESC) AS rn
FROM mart_track_stats;

CTE 与可读性

用 WITH 拆步骤：清洗 → 聚合 → 最终 SELECT。复杂报表 一个巨型嵌套子查询不如 3 层 CTE 可维护。

性能与规范

• 过滤条件尽量 先 WHERE 再 JOIN（优化器会改写，但逻辑要清晰）
• 大表加日期分区条件，避免全表扫
• 明细层与汇总层分离：BI 查 mart，少直接扫 ODS 亿级表

练习

• 写出「各 level 课程数量及占比」的 SQL
• 用窗口函数求每个 category 下 lesson 数 Top 3 的 track
• 解释 INNER JOIN 与 LEFT JOIN 在漏斗分析中的选择

小结

SQL 连接业务问题与数据表；JOIN 定口径，窗口函数定视角。 深入见 sql-essentials，下一课补 Excel 与 Python 工具链。