如何在日期区间内按周生成数据行？大表预计算分析需求

嘿，这个需求我之前在项目里处理过好多次，本质就是把单个时间区间拆成每周对应的独立记录，属于典型的行拆分场景。下面分几种常用数据库给你具体的实现方案，都是经过验证的高效写法：

PostgreSQL自带的generate_series函数天生适合做这种序列生成的事，结合日期计算就能快速生成目标表：

CREATE TABLE active_in_week AS
WITH user_time_ranges AS (
    SELECT
        user_id,
        started_at,
        ends_at,
        -- 计算总周数：用天数差除以7向上取整，避免跨年出错
        CEIL(EXTRACT(DAY FROM ends_at - started_at) / 7)::INT AS total_weeks
    FROM your_large_table
)
SELECT
    ROW_NUMBER() OVER (ORDER BY utr.user_id, s.week_num) AS id,
    utr.user_id,
    s.week_num AS active_week
FROM user_time_ranges utr
-- 给每个用户生成恰好对应周数的序列
CROSS JOIN LATERAL generate_series(1, utr.total_weeks) AS s(week_num);

小解释： #

• user_time_ranges先算出每个用户的时间跨度对应的总周数，用CEIL(天数/7)能完美处理跨年、跨月的情况
• CROSS JOIN LATERAL会为每个用户单独生成序列，不会生成多余数据，性能比全局序列过滤好很多
• ROW_NUMBER()用来生成自增的id字段，保证唯一

MySQL 8.0及以上支持递归CTE，我们可以用它生成周数序列，再和原表关联：

CREATE TABLE active_in_week AS
WITH RECURSIVE week_series AS (
    -- 起始周数
    SELECT 1 AS week_num
    UNION ALL
    -- 递归生成后续周数，直到覆盖所有用户的最大周数
    SELECT week_num + 1 FROM week_series 
    WHERE week_num < (
        SELECT MAX(CEIL(DATEDIFF(ends_at, started_at)/7)) FROM your_large_table
    )
),
user_time_ranges AS (
    SELECT
        user_id,
        started_at,
        ends_at,
        CEIL(DATEDIFF(ends_at, started_at)/7) AS total_weeks
    FROM your_large_table
)
SELECT
    ROW_NUMBER() OVER (ORDER BY utr.user_id, ws.week_num) AS id,
    utr.user_id,
    ws.week_num AS active_week
FROM user_time_ranges utr
-- 只保留每个用户对应的周数
JOIN week_series ws ON ws.week_num <= utr.total_weeks;

如果是MySQL 5.x版本（不支持递归），可以先建一个数字辅助表：

-- 先建一个存1到100的数字表（如果有更大的周数需求可以扩展）
CREATE TABLE numbers (num INT PRIMARY KEY);
INSERT INTO numbers VALUES (1),(2),(3),...,(100); -- 批量插入即可

-- 然后生成目标表
CREATE TABLE active_in_week AS
SELECT
    ROW_NUMBER() OVER (ORDER BY t.user_id, n.num) AS id,
    t.user_id,
    n.num AS active_week
FROM your_large_table t
JOIN numbers n ON n.num <= CEIL(DATEDIFF(t.ends_at, t.started_at)/7);

SQL Server 2022+支持GENERATE_SERIES，写法和PostgreSQL类似：

CREATE TABLE active_in_week AS
WITH user_time_ranges AS (
    SELECT
        user_id,
        started_at,
        ends_at,
        CEILING(DATEDIFF(DAY, started_at, ends_at)/7.0) AS total_weeks
    FROM your_large_table
)
SELECT
    ROW_NUMBER() OVER (ORDER BY utr.user_id, s.value) AS id,
    utr.user_id,
    s.value AS active_week
FROM user_time_ranges utr
CROSS APPLY GENERATE_SERIES(1, utr.total_weeks) AS s;

旧版本SQL Server可以用递归CTE，和MySQL 8.0的写法差不多：

CREATE TABLE active_in_week AS
WITH RECURSIVE week_series AS (
    SELECT 1 AS week_num
    UNION ALL
    SELECT week_num + 1 FROM week_series 
    WHERE week_num < (
        SELECT MAX(CEILING(DATEDIFF(DAY, started_at, ends_at)/7.0)) FROM your_large_table
    )
),
user_time_ranges AS (
    SELECT
        user_id,
        started_at,
        ends_at,
        CEILING(DATEDIFF(DAY, started_at, ends_at)/7.0) AS total_weeks
    FROM your_large_table
)
SELECT
    ROW_NUMBER() OVER (ORDER BY utr.user_id, ws.week_num) AS id,
    utr.user_id,
    ws.week_num AS active_week
FROM user_time_ranges utr
JOIN week_series ws ON ws.week_num <= utr.total_weeks;

几个关键注意点 #

• 周数准确性：千万别直接用周数相减（比如EXTRACT(WEEK)），跨年的时候会出大问题！用天数差除以7向上取整是最稳妥的方式。
• 性能优化：如果你的表是超大规模（千万级以上），建议给started_at和ends_at加索引，并且考虑分批插入，避免一次性生成过多数据导致内存溢出。
• id字段可选方案：如果需要持久化的自增id，也可以在创建表时指定自增列（比如PostgreSQL的SERIAL、MySQL的AUTO_INCREMENT），比用ROW_NUMBER()更可靠。

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