怎么用SQL分析连续登录流失_SQL分析连续登录流失模式

陆砚码

2026-10-17 • 👁 0人浏览过

通过SQL分析用户连续登录流失，首先计算相邻登录日期差值，识别连续登录序列的起始与结束，进而确定用户流失时间点。实际应用于游戏、电商、在线教育等领域，结合登录频率、会话时长、行为完成率等指标可更全面评估用户活跃度与流失风险。 SQL分析连续登录流失，本质上就是找出哪些用户在连续登录一段时间后停止了登录，并识别出他们流失的具体时间点。关键在于如何利用SQL处理时间序列数据，找到连续登录的起始和中断。解决方案：要分析连续登录流失，需要一张包含用户登录信息的表，假设表名为

user_login

，包含

user_id

（用户ID）和

login_date

（登录日期）两列。计算每个用户的登录日期差：首先，需要计算每个用户相邻两次登录日期之间的差值。这可以通过窗口函数实现。

WITH LoginDiff AS (
  SELECT
    user_id,
    login_date,
    login_date - LAG(login_date, 1, login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS date_diff
  FROM
    user_login
)
SELECT * FROM LoginDiff;

这里，

LAG()

函数用于获取每个用户上一次的登录日期，然后计算当前登录日期与上一次登录日期的差值。如果这是用户的第一次登录，

LAG()

函数会返回

login_date

本身，

date_diff

将为0。识别连续登录的起始点：接下来，我们需要识别出每个用户连续登录的起始点。如果

date_diff

大于1，则表示该登录日期是一个新的连续登录序列的开始。

WITH LoginDiff AS (
  SELECT
    user_id,
    login_date,
    login_date - LAG(login_date, 1, login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS date_diff
  FROM
    user_login
),
LoginGroups AS (
  SELECT
    user_id,
    login_date,
    CASE WHEN date_diff > 1 THEN 1 ELSE 0 END AS new_group,
    SUM(CASE WHEN date_diff > 1 THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY login_date) AS group_id
  FROM
    LoginDiff
)
SELECT * FROM LoginGroups;

new_group

列标记了每个新的连续登录序列的开始，

group_id

列则为每个连续登录序列分配了一个唯一的ID。计算每个连续登录序列的开始和结束日期：现在，我们可以计算每个连续登录序列的开始日期和结束日期。

WITH LoginDiff AS (
  SELECT
    user_id,
    login_date,
    login_date - LAG(login_date, 1, login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS date_diff
  FROM
    user_login
),
LoginGroups AS (
  SELECT
    user_id,
    login_date,
    CASE WHEN date_diff > 1 THEN 1 ELSE 0 END AS new_group,
    SUM(CASE WHEN date_diff > 1 THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY login_date) AS group_id
  FROM
    LoginDiff
),
LoginRanges AS (
  SELECT
    user_id,
    group_id,
    MIN(login_date) AS start_date,
    MAX(login_date) AS end_date,
    COUNT(*) AS consecutive_days
  FROM
    LoginGroups
  GROUP BY
    user_id,
    group_id
)
SELECT * FROM LoginRanges;

这里，我们使用

MIN()

和

MAX()

函数计算每个连续登录序列的开始日期和结束日期，并使用

COUNT(*)

计算连续登录的天数。 MySQL（Linux） MySQL 9.6.0是面向Linux平台的2026年创新版本，核心架构迎来重大革新。其将外键约束与级联操作从InnoDB引擎层上移至SQL层，确保所有数据变更均被完整记录至Binlog，彻底解决了CDC（变更数据捕获）与主从复制中的数据不一致难题。此外，该版本引入container_aware启动选项以原生适配容器环境，并对审计日志进行了组件化重构，为追求极致数据一致性与云原生体验的开发者提供了全新选择。下载识别流失用户：最后，我们可以识别出流失用户，即那些在某个连续登录序列结束后没有再次登录的用户。这需要与一个“活跃用户”的概念结合起来，例如，如果用户在过去30天内没有登录，则认为该用户已流失。

WITH LoginDiff AS (
  SELECT
    user_id,
    login_date,
    login_date - LAG(login_date, 1, login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS date_diff
  FROM
    user_login
),
LoginGroups AS (
  SELECT
    user_id,
    login_date,
    CASE WHEN date_diff > 1 THEN 1 ELSE 0 END AS new_group,
    SUM(CASE WHEN date_diff > 1 THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY login_date) AS group_id
  FROM
    LoginDiff
),
LoginRanges AS (
  SELECT
    user_id,
    group_id,
    MIN(login_date) AS start_date,
    MAX(login_date) AS end_date,
    COUNT(*) AS consecutive_days
  FROM
    LoginGroups
  GROUP BY
    user_id,
    group_id
),
LastLogin AS (
  SELECT
    user_id,
    MAX(login_date) AS last_login_date
  FROM
    user_login
  GROUP BY
    user_id
)
SELECT
  lr.user_id,
  lr.end_date AS last_consecutive_login,
  ll.last_login_date,
  CASE WHEN ll.last_login_date < CURRENT_DATE - INTERVAL '30 days' THEN 'Churned' ELSE 'Active' END AS status
FROM
  LoginRanges lr
JOIN
  LastLogin ll ON lr.user_id = ll.user_id
WHERE lr.end_date = (SELECT MAX(end_date) FROM LoginRanges lrx WHERE lrx.user_id = lr.user_id);

这里，我们首先计算每个用户的最后一次登录日期，然后将该日期与当前日期进行比较，如果超过30天，则认为该用户已流失。连续登录流失分析有哪些实际应用场景？连续登录流失分析的应用场景非常广泛，尤其是在依赖用户活跃度的产品和服务中。比如，游戏公司可以用它来监控玩家的流失情况，及时发现并解决可能导致玩家离开的问题，例如游戏难度过高、活动吸引力不足等。电商平台可以利用它来分析用户购物习惯的变化，了解用户是否因为商品质量、价格或服务等原因不再活跃，从而优化商品推荐和客户服务。对于在线教育平台，它可以帮助识别学生学习积极性的变化，及时提供辅导和支持，提高课程完成率。金融科技公司则可以利用它来监控用户对金融产品的兴趣，了解用户是否因为风险承受能力、收益预期或其他原因停止使用产品，以便调整产品策略和风险控制。如何优化SQL查询以提高连续登录流失分析的效率？优化SQL查询效率的关键在于减少数据扫描和计算量。首先，对

user_login

表的

user_id

和

login_date

列建立索引，可以显著提高查询速度。其次，尽量避免在

WHERE

子句中使用函数，因为这会导致索引失效。例如，可以将

WHERE DATE(login_date) = '2023-01-01'

改为

WHERE login_date >= '2023-01-01 00:00:00' AND login_date < '2023-01-02 00:00:00'

。此外，可以使用临时表或物化视图来存储中间结果，避免重复计算。例如，可以将

LoginDiff

表存储为临时表，然后在后续查询中直接使用。最后，根据数据库的特性进行优化，例如，MySQL可以使用

EXPLAIN

命令来分析查询计划，找出潜在的性能瓶颈。除了连续登录天数，还有哪些指标可以用于更全面地分析用户流失？除了连续登录天数，还有很多指标可以帮助我们更全面地分析用户流失。例如，登录频率可以反映用户的使用习惯，登录频率下降可能预示着用户活跃度降低。会话时长可以反映用户对产品的参与程度，会话时长缩短可能意味着用户兴趣减退。关键行为完成率可以反映用户是否成功体验了产品的核心功能，例如，电商用户的购买转化率、社交用户的互动率等。用户活跃天数可以反映用户在一段时间内的活跃程度，例如，过去7天、30天或90天的活跃天数。用户价值可以通过用户的消费金额、贡献内容等指标来衡量，高价值用户的流失需要特别关注。此外，还可以结合用户画像数据，例如，年龄、性别、地域、兴趣等，分析不同用户群体的流失原因。

相关文章