梧桐数据库之识别用户活跃度分享

一、背景说明

假设我们是一家大型移动运营商，为了更好地理解用户的使用模式和网络需求，我们需要分析每日的用户活跃度。每个记录包含了日期、用户活跃度（即当天使用的用户数量）以及一个唯一的序号。我们的目标是找出那些连续三天或更多天用户活跃度达到或超过100的日期。这样的分析可以帮助我们识别出高需求时段，并据此优化网络资源分配和服务质量。

二、表结构说明

-- 用户活跃度表建表语句
CREATE TABLE user_activity (
  id INT PRIMARY KEY COMMENT '唯一序号',
  activity_date DATE NOT NULL UNIQUE COMMENT '活动日期',
  active_users INT NOT NULL COMMENT '活跃用户数'
);

三、表数据插入

INSERT INTO user_activity (id, activity_date, active_users) VALUES
(1, '2023-10-01', 120),
(2, '2023-10-02', 150),
(3, '2023-10-03', 90),
(4, '2023-10-04', 110),
(5, '2023-10-05', 130),
(6, '2023-10-06', 140),
(7, '2023-10-07', 80),
(8, '2023-10-08', 100),
(9, '2023-10-09', 120),
(10, '2023-10-10', 110);

四、实现思路分解

1. 筛选条件：首先找出所有活跃用户数大于或等于100的记录。
2. 连续性检查：然后，通过自连接或其他方法来检查这些记录在id上是否连续。
3. 分组与过滤：将连续的记录分组，并确保每组至少包含三条记录。
4. 结果排序：最后，按照activity_date升序排列结果集。

五、SQL代码实现

WITH high_activity AS (
  SELECT 
    id,             -- 唯一序号
    activity_date,  -- 活动日期
    active_users,   -- 活跃用户数
    ROW_NUMBER() OVER (ORDER BY activity_date) AS rn  -- 根据活动日期为每一天生成一个行号 rn
  FROM 
    user_activity
  WHERE 
    active_users >= 100  -- 过滤条件为active_users 大于或等于 100 
),

date_diff AS (
  SELECT 
    id,             -- 唯一序号
    activity_date,  -- 活动日期
    active_users,   -- 活跃用户数
    rn,             -- 行号
    activity_date - LAG(activity_date, 1, activity_date) OVER (ORDER BY activity_date) AS date_diff -- 计算每一天与前一天的日期差
  FROM 
    high_activity
),

consecutive_groups AS (
  SELECT
    id,
    activity_date,  -- 活动日期
    active_users,   -- 活跃用户数
    -- 通过累积日期差来标识连续的序列 group_id，如果 date_diff 为 1，则认为是连续的，使用窗口函数实现
    SUM(CASE WHEN date_diff = 1 THEN 0 ELSE 1 END) OVER (ORDER BY activity_date) AS group_id
  FROM 
    date_diff
),

consecutive_counts AS (
  SELECT
    id,
    activity_date,
    active_users,
    -- 计算每个连续序列中的记录数，使用窗口函数实现
    COUNT(*) OVER (PARTITION BY group_id) AS consecutive_count
  FROM 
    consecutive_groups
)

-- 过滤出连续天数大于或等于3天的记录
SELECT 
  id, 
  activity_date, 
  active_users
FROM 
  consecutive_counts
WHERE 
  consecutive_count >= 3 -- 过滤出连续天数大于或等于 3 天的记录
ORDER BY 
  activity_date ASC;  -- 按 activity_date 升序排列

这样，我们就可以得到所有连续三天或更多天用户活跃度达到或超过100的日期。这个查询可以帮助移动运营商更好地理解和管理网络资源。

执行结果如下：

id	activity_date	active_users
4	2023-10-04	110
5	2023-10-05	130
6	2023-10-06	140
8	2023-10-08	100
9	2023-10-09	120
10	2023-10-10	110