Subquery子查询在用户行为分析中的应用

下面从概念 → 典型用户行为分析场景 → SQL 示例 → 实践建议几个层面，系统性说明 Subquery（子查询）在用户行为分析中的应用。

一、什么是 Subquery（子查询）

子查询是指嵌套在 SELECT / FROM / WHERE / HAVING 中的查询语句，用于：

• 先进行一次“中间计算”
• 再基于中间结果做进一步分析

在用户行为分析中，常用于：

• 行为筛选
• 行为聚合
• 行为前后对比
• 漏斗、留存、分层分析

二、用户行为分析常见场景与子查询应用

1️⃣ 行为筛选：筛选“发生过某行为的用户”

场景
找出“最近 7 天浏览过商品但未下单的用户”

SELECT user_id
FROM users
WHERE user_id IN (
    SELECT user_id
    FROM user_behavior
    WHERE behavior_type = 'view'
      AND event_date >= CURRENT_DATE - 7
)
AND user_id NOT IN (
    SELECT user_id
    FROM user_behavior
    WHERE behavior_type = 'order'
      AND event_date >= CURRENT_DATE - 7
);

✅ 子查询用于：

• 找到“浏览用户集合”
• 排除“下单用户集合”

2️⃣ 行为聚合：按用户聚合后再分析

场景
找出“单次会话中点击次数 > 10 的用户”

SELECT user_id, click_count
FROM (
    SELECT user_id,
           COUNT(*) AS click_count
    FROM user_behavior
    WHERE behavior_type = 'click'
    GROUP BY user_id, session_id
) t
WHERE click_count > 10;

✅ 子查询用于：

• 先按 session 聚合
• 再对聚合结果过滤

3️⃣ 留存 / 复购分析（最常见）

场景
首次下单用户在 7 天后是否再次下单（复购）

SELECT COUNT(DISTINCT t1.user_id) AS first_buy_users,
       COUNT(DISTINCT t2.user_id) AS repurchase_users
FROM (
    SELECT user_id, MIN(event_date) AS first_buy_date
    FROM user_behavior
    WHERE behavior_type = 'order'
    GROUP BY user_id
) t1
LEFT JOIN user_behavior t2
  ON t1.user_id = t2.user_id
 AND t2.behavior_type = 'order'
 AND t2.event_date = t1.first_buy_date + 7;

✅ 子查询用于：

• 计算“首次行为时间”
• 作为后续行为的锚点

4️⃣ 行为路径 / 漏斗分析

场景
分析“浏览 → 加购 → 下单”转化率

SELECT
    (SELECT COUNT(DISTINCT user_id)
     FROM user_behavior
     WHERE behavior_type = 'view') AS view_users,

    (SELECT COUNT(DISTINCT user_id)
     FROM user_behavior
     WHERE behavior_type = 'cart') AS cart_users,

    (SELECT COUNT(DISTINCT user_id)
     FROM user_behavior
     WHERE behavior_type = 'order') AS order_users;

✅ 子查询用于：

• 分别计算漏斗每一层人数
• 便于计算转化率

5️⃣ 用户分层（RFM / 活跃度）

场景
按最近一次下单时间给用户分“活跃 / 沉睡用户”

SELECT user_id,
       CASE
           WHEN days_since_last_order <= 7 THEN '活跃'
           WHEN days_since_last_order <= 30 THEN '一般'
           ELSE '沉睡'
       END AS user_level
FROM (
    SELECT user_id,
           CURRENT_DATE - MAX(event_date) AS days_since_last_order
    FROM user_behavior
    WHERE behavior_type = 'order'
    GROUP BY user_id
) t;

✅ 子查询用于：

• 计算“最近一次行为时间”
• 后续做业务规则分层

6️⃣ 行为前后对比（Before / After）

场景
某运营活动前后，用户下单量变化

SELECT
    (SELECT COUNT(*)
     FROM user_behavior
     WHERE behavior_type = 'order'
       AND event_date BETWEEN '2024-06-01' AND '2024-06-07') AS before_cnt,
    (SELECT COUNT(*)
     FROM user_behavior
     WHERE behavior_type = 'order'
       AND event_date BETWEEN '2024-06-08' AND '2024-06-14') AS after_cnt;

✅ 子查询用于：

• 快速进行时间段对比
• 常用于 AB 测试、活动评估

三、子查询 vs JOIN vs CTE（使用建议）

✅ 现代 SQL 推荐：
当子查询层级过多时，使用 CTE 更易维护。

WITH first_order AS (
    SELECT user_id, MIN(event_date) AS first_date
    FROM user_behavior
    WHERE behavior_type = 'order'
    GROUP BY user_id
)
SELECT *
FROM first_order;

四、用户行为分析中子查询的核心价值总结

✅ 先聚合、后筛选
✅ 先定位用户、再分析行为
✅ 快速计算指标（留存、转化、复购）
✅ 减少重复扫描数据，提高可维护性

如果你愿意，我可以：

• ✅ 用 真实业务数据表结构 帮你改写 SQL
• ✅ 讲 Subquery 在漏斗 / 留存 / RFM 中的最佳实践
• ✅ 对比 Hive / Spark SQL / MySQL / PostgreSQL 的写法差异

只要告诉我你的数据库类型和业务背景即可。