一、什么是 Gap 和 Island 问题？
在数据分析中，我们经常遇到一类被称为 Gap 和 Island 的问题——识别数据中的连续区间。这类问题在业务场景中非常普遍：
- 用户留存分析：计算用户的连续登录天数
- 金融行情：找出股价连续上涨/下跌的区间
- 设备监控：检测服务器在线/离线的连续时段
- 订单分析：识别用户连续下单的活跃周期
如果你尝试用普通的 GROUP BY 来解决，会发现根本做不到——因为连续日期在数据库中并不是连续存储的，它们散落在每一行记录中。
经典的解法是利用窗口函数 + 差值法，这个方法优雅、高效，而且在任何支持窗口函数的数据库中都能用。DuckDB 作为分析型数据库，天然擅长这类操作。
二、核心原理：差值法
差值法的核心思想非常简单：
连续日期 - 连续行号 = 相同值
假设你有一组连续日期 2024-01-01, 2024-01-02, 2024-01-03，对应的行号是 1, 2, 3。那么 日期 - 行号 的结果始终是同一个值（2023-12-31）。一旦遇到断档（比如 2024-01-05 缺失了 01-04），行号继续递增但日期跳过了，差值就会改变，从而形成一个新的「岛」。
这就是为什么 连续日期减去连续行号，结果恒定——它是识别连续区间的数学基础。
三、实战案例：用户连续登录天数
3.1 数据准备
假设我们有一张用户登录日志表 login_log：
CREATE TABLE login_log (
user_id INTEGER,
login_date DATE
);
INSERT INTO login_log VALUES
(1, '2024-01-01'),
(1, '2024-01-02'),
(1, '2024-01-03'),
(1, '2024-01-05'),
(1, '2024-01-06'),
(2, '2024-01-01'),
(2, '2024-01-03'),
(2, '2024-01-04'),
(2, '2024-01-05');
用户 1 在 1 月 1-3 日连续登录，然后断了 1 天，又在 5-6 日连续登录。用户 2 在 1 日单独登录，然后在 3-5 日连续登录。
3.2 差值法实现
WITH numbered AS (
-- 第一步：给每个用户的登录日期排序并编号
SELECT user_id, login_date,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY login_date
) AS rn
FROM login_log
),
grouped AS (
-- 第二步：计算日期 - 行号，得到分组标识
SELECT user_id, login_date,
login_date - INTERVAL (rn) DAY AS grp
FROM numbered
)
-- 第三步：按 (user_id, grp) 聚合，得到连续区间
SELECT user_id,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS consecutive_days
FROM grouped
GROUP BY user_id, grp
ORDER BY user_id, start_date;
3.3 执行结果
user_id | start_date | end_date | consecutive_days
---------+------------+------------+------------------
1 | 2024-01-01 | 2024-01-03 | 3
1 | 2024-01-05 | 2024-01-06 | 2
2 | 2024-01-01 | 2024-01-01 | 1
2 | 2024-01-03 | 2024-01-05 | 3
结果完美展示了每个用户的连续登录区间。
3.4 逐步拆解
| CTE | 作用 | 关键操作 |
|---|---|---|
numbered | 编号 | ROW_NUMBER() 按用户分区排序 |
grouped | 分组 | login_date - rn DAYS 生成组标识 |
| 最终查询 | 聚合 | GROUP BY user_id, grp 汇总区间 |
理解这一步是关键：当日期连续时，login_date - rn 的值保持不变；一旦断档，这个值就会跳变，自然形成新的分组。
四、进阶场景
4.1 价格涨跌区间检测
在金融分析中，识别连续上涨/下跌区间非常有价值：
WITH price_changes AS (
SELECT date, price,
LAG(price) OVER (ORDER BY date) AS prev_price,
CASE
WHEN price > LAG(price) OVER (ORDER BY date) THEN 'UP'
WHEN price < LAG(price) OVER (ORDER BY date) THEN 'DOWN'
ELSE 'SAME'
END AS trend
FROM stock_prices
),
numbered AS (
SELECT date, price, trend,
ROW_NUMBER() OVER (PARTITION BY trend ORDER BY date) AS rn
FROM price_changes
WHERE trend != 'SAME'
)
SELECT trend,
MIN(date) AS start_date,
MAX(date) AS end_date,
COUNT(*) AS days,
FIRST_VALUE(price) OVER (PARTITION BY trend, date - INTERVAL rn DAY ORDER BY date) AS start_price,
LAST_VALUE(price) OVER (PARTITION BY trend, date - INTERVAL rn DAY ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS end_price
FROM numbered
GROUP BY trend, date - INTERVAL rn DAY
ORDER BY start_date;
4.2 设备在线状态分析
WITH numbered AS (
SELECT device_id, status_time,
ROW_NUMBER() OVER (PARTITION BY device_id, status ORDER BY status_time) AS rn
FROM device_events
)
SELECT device_id,
status,
MIN(status_time) AS start_time,
MAX(status_time) AS end_time,
COUNT(*) AS duration_minutes
FROM numbered
GROUP BY device_id, status, status_time - INTERVAL rn MINUTE
ORDER BY start_time;
4.3 使用 DuckDB 的 QUALIFY 简化
DuckDB 支持 QUALIFY 子句，可以进一步简化查询：
WITH numbered AS (
SELECT user_id, login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
FROM login_log
)
SELECT user_id,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS consecutive_days
FROM numbered
GROUP BY user_id, login_date - INTERVAL rn DAY
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY MIN(login_date)) <= 5;
五、DuckDB vs 传统工具对比
| 特性 | DuckDB | PostgreSQL | MySQL 8.0 | pandas | Spark |
|---|---|---|---|---|---|
| 差值法支持 | ✅ 原生 | ✅ 原生 | ✅ 8.0+ | ✅ Python | ✅ Scala/Py |
| 安装复杂度 | 零配置 | 需部署 | 需部署 | pip install | 集群部署 |
| 内存效率 | 列式+向量化 | 行式 | 行式 | 内存全部加载 | 分布式 |
| 查询性能 | 极快 | 快 | 中等 | 中等 | 慢（大集群） |
| SQL 表达力 | 完整窗口函数 | 完整 | 部分支持 | 无 SQL | 有限 |
| 适合场景 | 单机分析 | 事务+分析 | 事务为主 | 数据科学 | 大数据 |
结论：对于 Gap/Island 这类分析型查询，DuckDB 在开发效率和运行性能上都是最佳选择。
六、性能优化建议
6.1 索引策略
-- 为登录日志表创建复合索引
CREATE INDEX idx_login_user_date ON login_log (user_id, login_date);
6.2 分区表
对于超大数据量，可以按日期分区：
-- DuckDB 支持外部表分区
SELECT * FROM read_csv_auto('login_logs/*.csv');
6.3 并行处理
DuckDB 自动利用多核并行执行窗口函数，对于百万级数据通常只需秒级响应。
七、变现建议
掌握 Gap 和 Island 问题的解法，可以衍生出多个商业应用方向：
7.1 用户留存 SaaS 工具
开发一个面向中小电商的用户行为分析 SaaS，核心功能就是连续登录天数、连续购买周期分析。按月订阅定价 ¥99-499/月。
7.2 金融数据产品
搭建股票连续涨跌区间监控系统，为量化投资者提供信号触发服务。可以做成 API 服务，按调用量收费。
7.3 设备监控服务
为 IoT 设备厂商提供在线状态分析服务，帮助识别设备故障模式和用户活跃度。按设备数量收费。
7.4 技术顾问服务
将这套技术包装成数据分析培训课程，面向数据分析师和后端工程师，单节课收费 ¥199-999。
7.5 DuckDB 咨询服务
作为 DuckDB 专家，为企业提供实时数据分析架构设计和性能优化服务，按项目收费 ¥5000-50000。
这篇文章基于 DuckDB 频道「DuckDB 掘金实战」的每日推送整理。更多实战技巧请访问 duckdblab.org。