在用户运营的工作中，识别连续登录用户并分析其价值是提升留存和营收的重要环节，连续登录的用户通常对产品的认可度更高，消费和互动的意愿也更强，通过SQL可以快速完成这类用户的筛选和价值量化。
连续登录用户的识别逻辑
要分析连续登录用户，首先需要明确连续登录的定义，通常指用户在连续的N个自然日内都有登录行为，比如连续3天登录、连续7天登录。识别的核心思路是先对用户的登录记录去重，再按用户维度排序，通过日期差值判断登录是否连续。
基础数据准备
假设我们有用户登录记录表user_login_log，表结构如下：
| 字段名 | 类型 | 说明 |
|---|---|---|
| user_id | bigint | 用户ID |
| login_date | date | 登录日期 |
同时还有用户消费表user_order，用于后续价值分析：
| 字段名 | 类型 | 说明 |
|---|---|---|
| user_id | bigint | 用户ID |
| order_amount | decimal | 订单金额 |
| order_date | date | 订单日期 |
计算连续登录天数
我们可以使用窗口函数ROW_NUMBER()给每个用户的登录日期排序，再用登录日期减去排序值，如果结果相同则说明登录是连续的，具体SQL如下：
-- 去重用户每日登录记录，避免同一天多次登录干扰计算
WITH distinct_login AS (
SELECT DISTINCT user_id, login_date
FROM user_login_log
),
-- 计算每个用户的登录排序和日期差值
login_rank AS (
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS diff_date
FROM distinct_login
),
-- 统计每个用户的连续登录分组和连续天数
continuous_group AS (
SELECT
user_id,
diff_date,
COUNT(1) AS continuous_days,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date
FROM login_rank
GROUP BY user_id, diff_date
)
SELECT *
FROM continuous_group
WHERE continuous_days >= 3 -- 筛选连续3天及以上登录的用户
ORDER BY user_id, start_date;
连续登录用户价值分析
识别到连续登录用户后，我们可以结合用户的消费、互动等行为数据，从多个维度分析其价值，常见的分析维度包括消费贡献、留存表现、活跃度等。
消费价值分析
我们可以统计连续登录用户在连续登录周期内的消费总金额、平均客单价，和普通用户做对比，判断其消费贡献能力，SQL示例如下：
-- 关联连续登录用户和消费数据，统计消费价值
WITH distinct_login AS (
SELECT DISTINCT user_id, login_date
FROM user_login_log
),
login_rank AS (
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS diff_date
FROM distinct_login
),
continuous_user AS (
SELECT
user_id,
diff_date,
COUNT(1) AS continuous_days,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date
FROM login_rank
GROUP BY user_id, diff_date
HAVING continuous_days >= 3
)
SELECT
cu.user_id,
cu.continuous_days,
cu.start_date,
cu.end_date,
COALESCE(SUM(uo.order_amount), 0) AS total_order_amount,
COALESCE(AVG(uo.order_amount), 0) AS avg_order_amount,
COUNT(uo.order_amount) AS order_count
FROM continuous_user cu
LEFT JOIN user_order uo
ON cu.user_id = uo.user_id
AND uo.order_date >= cu.start_date
AND uo.order_date <= cu.end_date
GROUP BY cu.user_id, cu.continuous_days, cu.start_date, cu.end_date
ORDER BY total_order_amount DESC;
价值分层建议
根据分析结果，我们可以对连续登录用户做分层运营：
- 连续登录7天以上且消费金额高的用户，可标记为高价值核心用户，提供专属权益和优先服务
- 连续登录3-7天但消费较少的用户，可推送优惠券、专属活动引导消费转化
- 连续登录天数下降的用户，可发送召回提醒，避免用户流失
注意事项
在实际分析中需要注意几个问题，首先是登录日期的去重，避免用户同一天多次登录被重复计算；其次是日期函数的适配，不同数据库（如MySQL、PostgreSQL、Hive）的日期计算函数略有差异，需要根据实际使用的数据库调整语法；最后是分析周期的选择，可根据业务场景选择近30天、近90天等周期做滚动分析，更贴合业务实际。