如何在SQL中按Product ID分区扩展日期并转换格式？

现有如下结构的表（简化示例）：

Start_Date | End_Date   | product ID | etc.
2020-01-01 | 2020-03-02 | 1233       
2021-11-05 | 2021-12-30 | 532        

需要生成一张按product ID分组、包含连续月度起始日期（格式为yyyy-mm-01）的表，同时保留其他列的常量值，目标表示例如下：

Date_       | product ID | etc.
2020-01-01  | 1233       
2020-02-01  | 1233
2020-03-01  | 1233
2021-11-01  | 532
2021-12-01  | 532        

核心问题：在存在数千个product ID的场景下，如何高效实现该需求？

针对不同主流SQL数据库，推荐以下适配大规模数据的高效实现方式：

1. PostgreSQL #

利用generate_series生成对应产品的月份序列，结合LATERAL连接避免全局笛卡尔积：

SELECT
    date_trunc('month', s.month)::date AS Date_,
    t."product ID",
    t.etc
FROM
    your_table t
JOIN LATERAL (
    SELECT generate_series(
        date_trunc('month', t.Start_Date),
        date_trunc('month', t.End_Date),
        INTERVAL '1 month'
    ) AS month
) s ON true
ORDER BY t."product ID", Date_;

LATERAL会为每个产品单独生成匹配的月份序列，性能优于全局序列关联。

2. MySQL 8.0+ / MariaDB 10.2+ #

通过递归CTE生成覆盖全数据范围的月份序列，再与原表做范围关联：

WITH RECURSIVE month_series AS (
    SELECT
        MIN(date_format(Start_Date, '%Y-%m-01')) AS month_date
    FROM your_table
    UNION ALL
    SELECT
        date_add(month_date, INTERVAL 1 MONTH)
    FROM month_series
    WHERE month_date <= (SELECT MAX(date_format(End_Date, '%Y-%m-01')) FROM your_table)
)
SELECT
    ms.month_date AS Date_,
    t."product ID",
    t.etc
FROM your_table t
JOIN month_series ms
    ON ms.month_date BETWEEN date_format(t.Start_Date, '%Y-%m-01') AND date_format(t.End_Date, '%Y-%m-01')
ORDER BY t."product ID", ms.month_date;

适合数据时间跨度不大的场景，全局序列仅生成一次，关联成本低。

3. SQL Server #

方式一：递归CTE #

WITH month_series AS (
    SELECT
        DATEFROMPARTS(YEAR(MIN(Start_Date)), MONTH(MIN(Start_Date)), 1) AS month_date
    FROM your_table
    UNION ALL
    SELECT
        DATEADD(MONTH, 1, month_date)
    FROM month_series
    WHERE month_date <= DATEFROMPARTS(YEAR(MAX(End_Date)), MONTH(MAX(End_Date)), 1)
)
SELECT
    ms.month_date AS Date_,
    t.[product ID],
    t.etc
FROM your_table t
JOIN month_series ms
    ON ms.month_date BETWEEN DATEFROMPARTS(YEAR(t.Start_Date), MONTH(t.Start_Date), 1) 
                        AND DATEFROMPARTS(YEAR(t.End_Date), MONTH(t.End_Date), 1)
ORDER BY t.[product ID], ms.month_date
OPTION (MAXRECURSION 0); -- 月份跨度超100时需启用

方式二：利用系统表（性能更优） #

SELECT
    DATEADD(MONTH, n.number, DATEFROMPARTS(YEAR(t.Start_Date), MONTH(t.Start_Date), 1)) AS Date_,
    t.[product ID],
    t.etc
FROM your_table t
JOIN master..spt_values n
    ON n.type = 'P'
    AND DATEADD(MONTH, n.number, DATEFROMPARTS(YEAR(t.Start_Date), MONTH(t.Start_Date), 1)) 
        <= DATEFROMPARTS(YEAR(t.End_Date), MONTH(t.End_Date), 1)
ORDER BY t.[product ID], Date_;

通用优化建议 #

• 为Start_Date、End_Date、product ID建立联合索引，大幅降低关联查询的耗时
• 提前将原表的Start_Date和End_Date转换为月度起始日期（用计算列或预处理表存储），避免在关联条件中重复调用日期函数
• 数据量极大时，可分批次处理，或开启数据库并行查询特性加速

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