如何用 SQL 进行数据库挖掘：以用户行为分析为例

用户行为数据通常以一条条事件的形式记录：某位用户在什么时间触发了什么行为，以及当时的页面、设备或来源等上下文。SQL 数据挖掘的核心，不是把查询写得越复杂越好，而是把业务问题转成可验证的口径，再用筛选、聚合、连接和窗口函数从数据中找出模式。

本文以 PostgreSQL 为例，搭建一张简化的事件表，并用 SQL 分析转化漏斗、用户留存、行为分群和会话。示例围绕常见的产品分析问题展开，同时说明查询结果能回答什么、不能回答什么，以及如何避免数据和安全方面的常见陷阱。其他数据库的日期函数、JSON 语法和参数占位符可能不同，迁移时需要按所用数据库调整。

一、SQL 数据挖掘适合做什么

SQL 适合在结构化数据上快速提出问题并检验假设，尤其是数据已经存放在关系型数据库或数据仓库中的场景，例如：

• 描述用户做了什么：统计访问、搜索、收藏、下单等事件的数量和变化趋势。
• 检查流程在哪里流失：比较用户到达不同转化步骤的数量与比例。
• 观察不同用户群的差异：按注册批次、来源渠道、设备或行为特征分组。
• 评估产品变化：对比发布前后指标，但必须结合实验设计、时间因素和其他可能影响。
• 发现值得进一步调查的现象：例如某类用户更常完成购买，或特定页面之后流失增加。

SQL 擅长计算与汇总，但相关性不等于因果关系。即使发现“使用某功能的用户留存更高”，也不能直接断言该功能导致留存提升：更活跃的用户可能本来就更容易使用该功能。要判断因果，通常还需要随机实验、可靠的准实验设计或其他分析方法。

二、准备一份可运行的事件数据

先建立一张最小化事件表。user_id 允许为空，是因为匿名访问或追踪失败时可能无法识别用户；实际业务中也可能有访客 ID、账号 ID 等多种身份标识。

CREATE TABLE user_events (
    event_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    BIGINT,
    event_time TIMESTAMPTZ NOT NULL,
    event_name TEXT NOT NULL,
    properties JSONB NOT NULL DEFAULT '{}'::jsonb
);

INSERT INTO user_events (user_id, event_time, event_name, properties) VALUES
(101, '2025-01-01 09:00:00+00', 'signup',   '{"source":"search"}'),
(101, '2025-01-01 09:05:00+00', 'view_item','{"item_id":"A1"}'),
(101, '2025-01-01 09:10:00+00', 'add_cart', '{"item_id":"A1"}'),
(101, '2025-01-01 09:20:00+00', 'purchase', '{"order_id":"O101"}'),
(102, '2025-01-01 10:00:00+00', 'signup',   '{"source":"ad"}'),
(102, '2025-01-01 10:03:00+00', 'view_item','{"item_id":"B2"}'),
(102, '2025-01-02 12:00:00+00', 'view_item','{"item_id":"C3"}'),
(103, '2025-01-01 11:00:00+00', 'signup',   '{"source":"search"}'),
(103, '2025-01-01 11:08:00+00', 'view_item','{"item_id":"A1"}'),
(103, '2025-01-01 11:12:00+00', 'add_cart', '{"item_id":"A1"}'),
(104, '2025-01-02 08:00:00+00', 'signup',   '{"source":"ad"}'),
(104, '2025-01-02 08:30:00+00', 'view_item','{"item_id":"D4"}'),
(104, '2025-01-03 09:00:00+00', 'purchase', '{"order_id":"O104"}');

这里把事件时间存为 TIMESTAMPTZ，并在样例中统一使用 UTC 偏移。实际生产数据应明确时区策略：数据库保存的时刻、业务使用的自然日，以及报表展示时区必须一致。若按用户所在地的本地日期统计，单纯按 UTC 日期分组可能把事件归入不同的一天。

properties 使用 JSONB 方便存放变化较多的事件属性，但不代表任何字段都应该塞进 JSON。经常用于筛选、连接或分组的字段，通常应考虑提取为明确列，便于约束数据类型、建立索引和维护口径。表结构也没有自动保证“同一业务事件只写入一次”；若上游可能重试，需用业务事件 ID 或幂等机制识别重复记录。

三、先把分析口径说清楚

开始写查询前，先回答几个容易被忽略的问题：

• 分析对象是什么？是账号、设备、访客，还是某种合并后的用户身份？身份合并规则会改变人数和留存率。
• 事件如何定义？一次点击、一次成功请求，还是一次有效业务状态变化？应优先使用可靠的服务端事件，或校验客户端事件是否会重复、漏报。
• 统计窗口是什么？从首次访问起算、按自然日统计，还是按滚动的 24 小时统计？它们不是同一口径。
• 重复事件如何处理？按行计数会把重复上报也算进去。要先确认是否存在稳定的去重键及可信的去重规则。
• 数据是否完整？最新日期可能尚未写入完毕，跨系统数据也可能有延迟。未成熟的数据窗口不宜与完整窗口直接比较。

下面的查询都把 user_id 作为用户身份，并排除空值。这个约定只是示例，并不能替代实际项目中的身份设计。

四、分析转化漏斗

一个常见问题是：注册用户中有多少人查看商品、加入购物车并完成购买？下面的查询按用户统计各步骤是否发生，并要求后一步的事件时间严格晚于前一步。它计算的是整个查询时间范围内的用户级漏斗，不是限定在首次注册后的某个小时或某天内的漏斗。

WITH per_user AS (
    SELECT
        user_id,
        MIN(event_time) FILTER (WHERE event_name = 'signup')   AS signup_time,
        MIN(event_time) FILTER (WHERE event_name = 'view_item') AS view_time,
        MIN(event_time) FILTER (WHERE event_name = 'add_cart')  AS cart_time,
        MIN(event_time) FILTER (WHERE event_name = 'purchase')  AS purchase_time
    FROM user_events
    WHERE user_id IS NOT NULL
    GROUP BY user_id
),
steps AS (
    SELECT
        user_id,
        signup_time IS NOT NULL AS did_signup,
        signup_time IS NOT NULL
            AND view_time > signup_time AS did_view_after_signup,
        signup_time IS NOT NULL
            AND view_time > signup_time
            AND cart_time > view_time AS did_cart_after_view,
        signup_time IS NOT NULL
            AND view_time > signup_time
            AND cart_time > view_time
            AND purchase_time > cart_time AS did_purchase_after_cart
    FROM per_user
)
SELECT
    COUNT(*) FILTER (WHERE did_signup) AS signup_users,
    COUNT(*) FILTER (WHERE did_view_after_signup) AS viewed_users,
    COUNT(*) FILTER (WHERE did_cart_after_view) AS cart_users,
    COUNT(*) FILTER (WHERE did_purchase_after_cart) AS purchased_users
FROM steps;

这段写法易读，但有一个重要限制：每一步先取该用户在全表中的最早事件，再检查事件顺序。如果用户的较早行为发生在注册前，之后又完成了正确顺序的行为，查询可能无法识别那条有效路径。对于严格的“从注册开始，在限定窗口内按顺序完成”漏斗，应先选定注册事件，再只分析注册之后、窗口之内的事件；如果同一时间戳内事件顺序也重要，还要有可靠的顺序字段，不能只依赖时间戳。

漏斗转化率可以在上述人数结果上计算，例如“加入购物车人数除以查看商品人数”。分母必须在业务口径中明确：是全部注册用户，还是进入上一步的人？当分母为零时要处理除零情况，可使用 NULLIF(分母, 0)。此外，用户级漏斗与事件级漏斗回答的问题不同，不能把事件条数直接当成用户数。

五、计算用户留存

留存分析要先定义“回访”。下面把用户首次出现的日期作为注册批次，并将任意后续事件视为活跃。它计算的是第 0 天和第 1 天的自然日留存：第 1 天指首次活动日期之后的下一个日期，不代表首次活动时刻起的第 24 至 48 小时。

WITH first_seen AS (
    SELECT
        user_id,
        MIN((event_time AT TIME ZONE 'UTC')::date) AS cohort_date
    FROM user_events
    WHERE user_id IS NOT NULL
    GROUP BY user_id
),
user_activity AS (
    SELECT DISTINCT
        user_id,
        (event_time AT TIME ZONE 'UTC')::date AS activity_date
    FROM user_events
    WHERE user_id IS NOT NULL
),
retention AS (
    SELECT
        f.cohort_date,
        a.activity_date - f.cohort_date AS day_number,
        COUNT(DISTINCT f.user_id) AS active_users
    FROM first_seen f
    JOIN user_activity a ON a.user_id = f.user_id
    WHERE a.activity_date >= f.cohort_date
      AND a.activity_date <= f.cohort_date + 7
    GROUP BY f.cohort_date, a.activity_date - f.cohort_date
),
cohort_size AS (
    SELECT cohort_date, COUNT(*) AS users
    FROM first_seen
    GROUP BY cohort_date
)
SELECT
    r.cohort_date,
    r.day_number,
    c.users AS cohort_users,
    r.active_users,
    ROUND(r.active_users::numeric / NULLIF(c.users, 0), 4) AS retention_rate
FROM retention r
JOIN cohort_size c USING (cohort_date)
ORDER BY r.cohort_date, r.day_number;

查询结果每个批次、每个实际出现的活跃日输出一行。如果某天没有活跃用户，该组合不会自动出现。需要完整展示第 0 至第 7 天时，应生成日序列，再将统计结果左连接上去，把缺失人数按零处理；不过对尚未经过完整观察期的新批次，不应把未来日期当作零留存。

示例用 UTC 日期。如果产品以某个固定业务时区计算自然日，应先将事件时间转换到该时区再取日期。首次出现也不必然等于注册：若要分析注册批次，应使用可信的注册事件或账号表中的注册时间，而不是把任何最早事件当成注册。

六、用行为序列识别会话

当没有可信的会话 ID 时，可以依据相邻事件之间的时间间隔划分会话。常见规则是间隔超过 30 分钟就开启新会话，但这是分析约定，不是用户真实离开的证明。下面按用户和事件时间排序，并在时间间隔超过 30 分钟时分配新会话编号。

WITH ordered AS (
    SELECT
        user_id,
        event_id,
        event_time,
        event_name,
        LAG(event_time) OVER (
            PARTITION BY user_id
            ORDER BY event_time, event_id
        ) AS previous_time
    FROM user_events
    WHERE user_id IS NOT NULL
),
marked AS (
    SELECT *,
        CASE
            WHEN previous_time IS NULL
              OR event_time - previous_time > INTERVAL '30 minutes'
            THEN 1
            ELSE 0
        END AS starts_session
    FROM ordered
),
numbered AS (
    SELECT *,
        SUM(starts_session) OVER (
            PARTITION BY user_id
            ORDER BY event_time, event_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS session_number
    FROM marked
)
SELECT
    user_id,
    session_number,
    MIN(event_time) AS session_start,
    MAX(event_time) AS session_end,
    COUNT(*) AS event_count
FROM numbered
GROUP BY user_id, session_number
ORDER BY user_id, session_number;

event_id 在时间相同时提供稳定排序，但它只代表数据库插入顺序，不一定等于真实发生顺序。若业务要求严格的因果顺序，应采集可信的事件序号或服务端时间。高延迟、离线补传、跨设备行为以及机器人流量也会影响会话划分，因此 30 分钟规则应按产品场景验证，而不是当作通用事实。

七、按行为分群并比较指标

分群可以从简单且可解释的规则开始。例如，将在指定日期前完成购买的用户标为购买用户，再比较两组的人数。分群规则、观察期和指标窗口需要分开定义，否则容易把未来信息泄漏到分组条件中。

WITH user_flags AS (
    SELECT
        user_id,
        BOOL_OR(event_name = 'purchase'
                AND event_time < TIMESTAMPTZ '2025-01-08 00:00:00+00')
            AS purchased_in_window
    FROM user_events
    WHERE user_id IS NOT NULL
      AND event_time >= TIMESTAMPTZ '2025-01-01 00:00:00+00'
      AND event_time <  TIMESTAMPTZ '2025-01-08 00:00:00+00'
    GROUP BY user_id
)
SELECT
    CASE WHEN purchased_in_window
         THEN 'purchased' ELSE 'not_purchased' END AS segment,
    COUNT(*) AS users
FROM user_flags
GROUP BY 1
ORDER BY 1;

这里的“购买”只用于描述分组，不足以说明购买用户与未购买用户的差异由购买本身造成。更有用的后续分析可能是：按注册渠道比较七日购买率，或观察首次购买之前的行为。不过应确保每组有足够样本，指标定义一致，并避免不断尝试很多切分后只报告最显著的结果。分组探索能生成假设，验证假设仍需独立数据或合适的实验。

八、生产查询中的参数与安全

业务查询通常需要传入用户 ID、时间范围或事件名称。应用程序应使用数据库驱动提供的绑定参数，而不是把用户输入直接拼接进 SQL。PostgreSQL 的参数占位符在不同驱动中形式不同：例如部分驱动使用 $1，另一些驱动使用命名参数或问号占位符，应查阅对应驱动的文档。

SELECT user_id, event_time, event_name
FROM user_events
WHERE user_id = $1
  AND event_time >= $2
  AND event_time <  $3
ORDER BY event_time
LIMIT $4;

上面是参数化 SQL 的形状示例；$1 至 $4 不是要在普通 SQL 编辑器中原样执行的通用语法。应用需通过驱动将相应值作为参数传入。参数通常用于绑定数据值，不用于安全地替换表名、列名、关键字或排序方向。如果需要动态选择这些标识符，应使用应用内白名单映射到固定 SQL 片段，不能把任意输入拼接为标识符。

参数化查询能降低把输入误当作 SQL 代码执行的风险，但不是完整的安全体系。仍应遵循最小权限原则，为分析账号限制写入和管理权限；保护凭据，避免在日志中记录敏感参数；对访问用户数据设置授权、审计、保留期限和脱敏规则。SQL 不是绕过访问控制或获取未授权数据的工具。使用真实用户数据进行分析，应遵守适用的法律、组织政策与数据授权范围。

此外，数据库和驱动对参数数量、SQL 语句大小、数据类型及查询执行时间可能有各自限制；限制会因产品、版本、驱动和配置而异，不存在可以不加区分套用的单一数字。大型的用户 ID 列表不宜无限制地拼进一条语句。可考虑临时表、批次处理或数据库支持的数组参数，并检查执行计划与资源消耗。对外部输入还应验证业务范围，例如起止时间是否合理、时间范围是否过大。

九、常见错误与排查方法

• 把行数当成用户数：一个用户可能触发很多次事件。按人数统计时使用合适的用户标识和 COUNT(DISTINCT user_id)，同时理解去重成本与身份口径。
• 重复事件未处理：客户端重试或消息重复投递会抬高事件量。先确认事件是否有稳定唯一 ID，再按明确规则去重；不要随意按时间和事件名删记录，可能误删真实重复行为。
• 连接导致人数膨胀：一对多表连接后，一条用户记录可能变成多行。连接前检查两侧的键是否唯一，必要时先聚合或使用半连接逻辑。
• 过滤条件放错位置：在外连接后的 WHERE 中筛选右表字段，可能把左连接效果变成内连接。要根据意图区分连接条件和结果筛选条件。
• 忽略空值：COUNT(列) 不统计该列的 NULL，而 COUNT(*) 统计行数。明确空值代表“未知”“不适用”还是追踪缺失，不要把它们随意当作零。
• 时间范围边界不清：建议采用半开区间，即大于等于起始时间、小于结束时间。相邻区间这样划分不会在边界重复计数，也避免随意假设时间精度。
• 分母定义含糊：转化率应说明分子、分母、窗口和用户范围。分母为零时应返回空值或按业务规则处理，而不是发生除零错误。
• 只看平均数：少数极端用户可能明显拉高均值。可同时查看中位数、分位数、分布及分组结果；统计口径应与业务问题匹配。
• 忽略数据成熟度：当天数据可能未完整到达。对转化和留存设置观察窗口，标识数据更新时间，并避免把未成熟批次与完整批次直接比较。
• 只优化 SQL 语法：长时间查询可能源于扫描过多数据、缺少合适索引或连接膨胀。先限定时间范围，查看执行计划，再根据真实查询模式评估索引；不宜盲目给所有字段建索引。

十、让分析结果更可靠

一条能运行的查询，不等于一个可靠的结论。实际工作中，可以把分析流程固定为：明确问题与指标定义；核对事件埋点及身份口径；限定时间和用户范围；先检查原始样本、空值、重复率与时间分布；编写查询并对照小样本人工验证；最后说明结论的限制和数据更新时间。

对于可复用的指标，应把业务定义写进数据字典或指标层，避免不同团队各自计算出名称相同、口径不同的“转化率”。对关键报表可增加数据质量检查，例如事件量突降、必填属性缺失或事件时间晚于入库时间过多时发出提醒。查询也应尽量显式列出字段，避免使用 SELECT * 把无关或敏感列带进分析结果。

结语

SQL 用户行为挖掘可以从几个可检验的问题开始：用户是否按预期顺序完成关键步骤，某个注册批次在之后的自然日还有多少用户活跃，不同群体的行为分布是否存在差异。窗口函数、条件聚合和日期计算足以解决许多常见分析任务，但结果始终受身份识别、事件质量、时间定义与观察窗口约束。

把查询当作分析方法的一部分，而不是结论本身：先讲清口径，再验证数据，最后谨慎解释观察到的差异。这样写出的 SQL 才不仅能运行，也更容易被复查、复用和用于实际决策。