Clickhouse中基于区间表计算每日订阅数的最优方法

问题背景 #

现有如下结构的subscriptions表及测试数据：

create table subscriptions 
(
start_date Date,
end_date Date,
customer_id Int64
)
engine = MergeTree()
order by (customer_id, start_date, end_date);

insert into subscriptions values ('2023-01-01', '2023-01-04', 1);
insert into subscriptions values ('2023-01-02', '2023-02-01', 2);
insert into subscriptions values ('2023-01-04', '2023-01-10', 3);

需求是计算每日的订阅用户数，预期结果示例如下：

| date       | subscribers |
| ---------- | ----------- |
| 2023-01-01 | 1           |
| 2023-01-02 | 2           |
| 2023-01-03 | 2           |
| 2023-01-04 | 3           |
| 2023-01-05 | 2           |
...

希望利用ClickHouse的特殊函数实现最优计算方式。

最优实现方法 #

方法1：sequence+arrayJoin展开日期（简洁直观） #

ClickHouse的sequence函数可生成起始到结束的日期序列，配合arrayJoin将每个订阅区间拆分为单日记录，再统计每日去重用户数：

SELECT
    date,
    uniq(customer_id) AS subscribers
FROM (
    SELECT
        arrayJoin(sequence(start_date, end_date)) AS date,
        customer_id
    FROM subscriptions
)
GROUP BY date
ORDER BY date;

注：uniq是ClickHouse原生的高效去重计数函数，性能优于count(distinct)，数据量小时两者差异可忽略。

方法2：区间增量聚合（大表高性能首选） #

对于超大规模数据集，展开日期会产生海量中间数据，此时用事件增量法更高效：将订阅开始日期标记为+1，结束日期的次日标记为-1，再做累加计算：

WITH
    (SELECT (start_date, 1) FROM subscriptions)
    UNION ALL
    (SELECT (end_date + INTERVAL 1 DAY, -1) FROM subscriptions)
    AS events
SELECT
    date,
    sum(change) OVER (ORDER BY date) AS subscribers
FROM (
    SELECT
        date,
        sum(change) AS change
    FROM events
    GROUP BY date
)
ORDER BY date;

这种方法仅生成原表2倍的中间数据，内存与IO消耗远低于日期展开法，适合亿级以上数据量的场景。

方法3：range+ARRAY JOIN兼容旧版 #

如果使用的ClickHouse版本低于21.8（不支持sequence函数），可改用range生成日期序列：

SELECT
    date,
    uniq(customer_id) AS subscribers
FROM (
    SELECT
        start_date + number AS date,
        customer_id
    FROM subscriptions
    ARRAY JOIN range(toUInt64(end_date - start_date) + 1) AS number
)
GROUP BY date
ORDER BY date;

效果与方法1一致，仅为兼容旧版本的替代方案。

场景适配建议 #

• 小表/测试场景：方法1最简洁，开发成本低；
• 大表/生产场景：方法2性能最优，资源占用最少；
• 旧版本环境：方法3作为兼容方案使用。

内容的提问来源于stack exchange，提问作者George