高德地图能在香港用么_哈尔滨城市规划建设网_上海seo优化公司 kinglink_深圳百度百科

一、背景说明

假设我们是一家大型移动运营商，为了更好地理解用户的使用模式和网络需求，我们需要分析每日的用户活跃度。每个记录包含了日期、用户活跃度（即当天使用的用户数量）以及一个唯一的序号。我们的目标是找出那些连续三天或更多天用户活跃度达到或超过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);

四、实现思路分解

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

五、SQL代码实现

WITH high_activity AS (SELECT id,             -- 唯一序号activity_date,  -- 活动日期active_users,   -- 活跃用户数ROW_NUMBER() OVER (ORDER BY activity_date) AS rn  -- 根据活动日期为每一天生成一个行号 rnFROM user_activityWHERE 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 (SELECTid,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_idFROM date_diff
),consecutive_counts AS (SELECTid,activity_date,active_users,-- 计算每个连续序列中的记录数，使用窗口函数实现COUNT(*) OVER (PARTITION BY group_id) AS consecutive_countFROM 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