如何在ClickHouse中展开日期范围生成多行数据？

你可以利用ClickHouse的数组操作+ARRAY JOIN语法来高效实现日期范围展开，这比递归CTE更适配ClickHouse的列式存储特性，性能也更优。以下是具体实现方案：

基础实现（处理非空valid_until） #

针对你的原始表结构，直接通过计算日期差生成序列，再展开为多行：

SELECT
    valid_from,
    valid_until,
    dateAdd('day', number, valid_from) AS expanded_dates
FROM my_table
ARRAY JOIN range(0, dateDiff('day', valid_from, valid_until) + 1) AS number

逻辑说明： #

1. dateDiff('day', valid_from, valid_until)：计算两个日期之间的天数差（比如2020-01-01到2020-01-03的差是2）
2. range(0, 差+1)：生成从0到天数差的整数序列（比如0,1,2）
3. dateAdd('day', number, valid_from)：给valid_from依次加上序列中的天数，得到区间内的所有日期
4. ARRAY JOIN：将数组形式的日期序列展开为单独的行

兼容valid_until为NULL的场景 #

如果需要处理valid_until为空的情况（替换为当前日期），可以用COALESCE函数做兼容：

SELECT
    valid_from,
    COALESCE(valid_until, today()) AS valid_until,
    dateAdd('day', number, valid_from) AS expanded_dates
FROM my_table
ARRAY JOIN range(0, dateDiff('day', valid_from, COALESCE(valid_until, today())) + 1) AS number

替代方案：使用generateSeries函数（ClickHouse 21.8+） #

如果你的ClickHouse版本在21.8及以上，可以直接用generateSeries生成日期序列，代码更简洁：

SELECT
    valid_from,
    COALESCE(valid_until, today()) AS valid_until,
    expanded_dates
FROM my_table
ARRAY JOIN generateSeries(valid_from, COALESCE(valid_until, today()), INTERVAL 1 DAY) AS expanded_dates

为什么不用递归CTE？ #

ClickHouse对递归CTE的支持有限，且性能远不如数组展开的方式——递归CTE是行式逻辑，而数组操作是ClickHouse原生优化的列式操作，处理大数据量时差异尤为明显。

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