SQL 进阶指南：掌握这些高阶技巧，让你的数据分析快人一步！

睡前大数据

发布于 2025-03-25 14:31:30

31300

代码可运行

运行总次数：0

代码可运行

你以为 SQL 只是简单的 SELECT * FROM table？那你可就大错特错了！在这个数据驱动的时代，真正的高手早已抛弃低效的查询方式，玩转窗口函数、递归 CTE、动态透视表，甚至用近似计算让百亿级数据秒出结果！ 还在为 SQL 运行慢、分析难、报表卡死而苦恼？ 别担心，这篇文章带你深入 SQL 世界，从业务思维出发，手把手拆解 用户增长、行为分析、订单转化、留存率 等核心场景，让你的数据分析能力直接起飞！

1、窗口函数：跨行计算 & 业务指标构建

场景：计算用户的滚动留存率

在用户分析中，我们希望知道用户注册后，在接下来的几天是否仍然活跃，即计算滚动留存率。

WITH user_activity AS (
    SELECT user_id, event_date, 
           LEAD(event_date, 1) OVER (PARTITION BY user_id ORDER BY event_date) AS next_active_date
    FROM user_events
)
SELECT event_date, 
       COUNT(DISTINCT user_id) AS active_users,
       COUNT(DISTINCT CASE WHEN next_active_date IS NOT NULL THEN user_id END) * 1.0 / 
       COUNT(DISTINCT user_id) AS retention_rate
FROM user_activity
GROUP BY event_date
ORDER BY event_date;

思维方式：

先用 LEAD() 计算每个用户的下一次活跃时间
再用 CASE WHEN 统计次日仍然活跃的用户数量

💡 业务价值：

这种方式适用于计算滚动留存、客户活跃度、连续消费等业务场景。

2. 条件聚合（条件 CASE WHEN 计数）

场景：用户行为转化率计算

在电商或 APP 运营中，我们经常需要计算从“浏览商品”到“加入购物车”再到“支付订单”的转化率。

SELECT COUNT(DISTINCT user_id) AS total_users,
       COUNT(DISTINCT CASE WHEN event_type = 'view_product' THEN user_id END) AS view_users,
       COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart' THEN user_id END) AS cart_users,
       COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) AS purchase_users,
       COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) * 1.0 / 
       COUNT(DISTINCT CASE WHEN event_type = 'view_product' THEN user_id END) AS view_to_purchase_rate
FROM user_events
WHERE event_date BETWEEN '2024-03-01' AND '2024-03-07';

思维方式：

通过 CASE WHEN 进行条件计数，计算各环节的转化率
直接通过 COUNT(DISTINCT user_id) 避免 GROUP BY

💡 业务价值：

适用于转化率分析、A/B 测试效果评估、销售漏斗分析等

3. LAG/LEAD 计算时间间隔

场景：用户的平均访问间隔

计算用户每次访问之间的时间差，用于分析用户的访问频次，判断他们的粘性。

WITH visit_times AS (
    SELECT user_id, event_time, 
           LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event_time
    FROM user_events
)
SELECT user_id, 
       AVG(EXTRACT(EPOCH FROM (event_time - prev_event_time))) AS avg_visit_interval
FROM visit_times
WHERE prev_event_time IS NOT NULL
GROUP BY user_id;

思维方式：

用 LAG() 获取用户上一次访问时间
计算时间差后，再求平均值

💡 业务价值：

适用于衡量用户粘性，分析不同用户群体的活跃特征

4. GROUPING SETS / CUBE / ROLLUP

场景：多维度数据聚合

在分析用户订单时，我们可能需要按 天、用户、产品 维度进行不同粒度的汇总。

SELECT user_id, product_id, order_date, SUM(order_amount) AS total_amount
FROM orders
GROUP BY GROUPING SETS (
    (user_id, product_id, order_date),  -- 详细数据
    (user_id, order_date),             -- 按用户和日期
    (product_id, order_date),          -- 按产品和日期
    (order_date)                       -- 按日期
);

思维方式：

GROUPING SETS 允许我们在一条 SQL 语句中进行多维度的聚合计算
比 UNION ALL 拼接多个 GROUP BY 高效

💡 业务价值：

适用于报表系统的多维分析，减少 SQL 复杂度，提高性能

5. 递归 CTE 进行层级数据计算

场景：用户推荐关系

电商、社交、会员体系中，往往有“用户 A 推荐用户 B，B 又推荐了 C”的层级关系，我们希望统计出某个用户的所有下级推荐用户。

WITH RECURSIVE user_tree AS (
    SELECT user_id, referrer_id, 1 AS level
    FROM users
    WHERE referrer_id = 'user_001'  -- 起点用户

    UNION ALL

    SELECT u.user_id, u.referrer_id, ut.level + 1
    FROM users u
    INNER JOIN user_tree ut ON u.referrer_id = ut.user_id
)
SELECT * FROM user_tree;

思维方式：

递归 CTE (WITH RECURSIVE) 可以处理层级关系
适用于树状结构的数据查询（例如推荐关系、组织架构）

💡 业务价值：

在裂变增长、分销、社交网络等场景中非常重要

6. 使用 APPROX_DISTINCT 加速去重统计

场景：大规模数据的 UV 计算

在数仓或者大数据计算场景下，计算去重用户数（UV）非常耗时，很多数据库（如 Trino、Presto、ClickHouse）提供了 APPROX_DISTINCT 进行近似去重计算。

SELECT event_date, APPROX_DISTINCT(user_id) AS approx_uv
FROM user_events
GROUP BY event_date;

思维方式：

使用 APPROX_DISTINCT() 代替 COUNT(DISTINCT user_id)，提高计算性能
适用于 PV、UV 统计，能大幅提升查询速度

💡 业务价值：

在大数据环境下，可以显著优化统计效率，适用于 BI 报表

7. 行列转换（Pivot & Unpivot）

场景：将某些维度数据转为列形式

在某些 BI 场景中，我们希望将日期数据转为列名，例如：

user_id	2024-03-01	2024-03-02	2024-03-03

A	10	15	12

B	8	9	11

SELECT * 
FROM (
    SELECT user_id, event_date, spend_amount
    FROM user_spend
) PIVOT (
    SUM(spend_amount) FOR event_date IN ('2024-03-01', '2024-03-02', '2024-03-03')
);

思维方式：