如何用SQL将用户分组的起止日期数组展开为连续日期范围

问题说明 #

现有一张包含用户和日期范围数组的表，date_agg列存储了每个用户的起始和结束日期，需要将该列批量展开为每个用户对应的连续每日记录，要求使用纯SQL实现，不依赖Python UDF。

原始表结构 #

+----------------------------------+-----+
|DATE_AGG                          |USER |
+----------------------------------+-----+
|[                                 |Julia|
|"2010-01-01",                     |     |
|"2022-08-23"                      |     |
|]                                 |     |
|[                                 |Jon  |
|"2010-01-01",                     |     |
|"2022-08-23"                      |     |
|]                                 |     |
|[                                 |Amina|
|"2010-01-01",                     |     |
|"2022-08-23"                      |     |
|]                                 |     |
+----------------------------------+-----+

生成原始表的SQL #

SELECT ARRAY_CONSTRUCT(dt_from, dt_to) as date_agg, user
FROM (
  VALUES
    ('2010-01-01', '2022-08-23', 'Julia'),
    ('2010-01-01', '2022-08-23', 'Jon'),
    ('2010-01-01', '2022-08-23', 'Amina')
       ) t(dt_from, dt_to, user)

解决方案 #

通过CTE拆分日期范围、生成日期偏移量，再关联实现批量展开：

WITH user_date_ranges AS (
    -- 从数组提取起始/结束日期，转换为DATE类型
    SELECT 
        user,
        date_agg[0]::DATE AS dt_from,
        date_agg[1]::DATE AS dt_to
    FROM (
        -- 替换成你的实际表名/子查询
        SELECT ARRAY_CONSTRUCT(dt_from, dt_to) as date_agg, user
        FROM (
            VALUES
                ('2010-01-01', '2022-08-23', 'Julia'),
                ('2010-01-01', '2022-08-23', 'Jon'),
                ('2010-01-01', '2022-08-23', 'Amina')
        ) t(dt_from, dt_to, user)
    )
),
date_offset_generator AS (
    -- 生成连续的日期偏移量，ROWCOUNT设为足够覆盖最长日期范围的数值
    SELECT ROW_NUMBER() OVER (ORDER BY NULL) - 1 AS day_offset
    FROM TABLE(GENERATOR(ROWCOUNT => 10000))
)
-- 关联生成每个用户的连续日期
SELECT 
    DATEADD(DAY, day_offset, dt_from) AS date,
    user
FROM user_date_ranges
JOIN date_offset_generator 
    ON day_offset <= DATEDIFF(DAY, dt_from, dt_to)
ORDER BY user, date;

代码说明 #

1. user_date_ranges：解析date_agg数组，提取每个用户的起始日期（dt_from）和结束日期（dt_to），确保格式为DATE类型。
2. date_offset_generator：生成从0开始的连续整数偏移量，用于计算起始日期之后的每一天。ROWCOUNT需要设置为大于等于你的数据中最长日期范围的天数。
3. 关联查询：通过偏移量不超过日期范围天数的条件，为每个用户生成从dt_from到dt_to的所有连续日期。

期望输出 #

date   user
2010-01-01  Amina
2010-01-02  Amina
2010-01-03  Amina
...         ...    ...
2022-08-21  Julia
2022-08-22  Julia
2022-08-23  Julia

内容的提问来源于stack exchange，提问作者Umar.H