如何在SQL中自动计算列名？基于日期生成CW并均分销售额

可以实现这个需求，核心是先推导每个物料覆盖的所有日历周，计算单周分配金额后，通过动态行转列生成目标格式的宽表。

原始数据 #

期望输出 #

（CW = 日历周）

实现方案（以MySQL为例） #

1. 生成日历周与单周销售额的中间结果

先用递归CTE生成每个物料覆盖的所有日历周，再计算单周分配金额：

WITH RECURSIVE date_range AS (
    SELECT 
        Material,
        STR_TO_DATE(`From`, '%d.%m.%Y') AS start_date,
        STR_TO_DATE(`To`, '%d.%m.%Y') AS end_date,
        Sales,
        WEEK(STR_TO_DATE(`From`, '%d.%m.%Y'), 1) AS cw  -- 1表示周一为周起始，按需调整
    FROM your_table
    UNION ALL
    SELECT 
        Material,
        start_date,
        end_date,
        Sales,
        cw + 1
    FROM date_range
    WHERE WEEK(end_date, 1) >= cw + 1
),
cw_count AS (
    SELECT 
        Material,
        COUNT(DISTINCT cw) AS total_weeks,
        Sales
    FROM date_range
    GROUP BY Material, Sales
)
SELECT 
    dr.Material,
    CONCAT('CW', dr.cw) AS cw_col,
    ROUND(dr.Sales / cc.total_weeks, 2) AS weekly_sales
FROM date_range dr
JOIN cw_count cc ON dr.Material = cc.Material;

2. 动态行转列生成目标宽表

由于日历周列是动态的，需要用动态SQL自动生成列名：

-- 收集所有需要的日历周列名
SET @cols = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN cw_col = ''', cw_col, ''' THEN weekly_sales END) AS ', cw_col)) INTO @cols
FROM (
    WITH RECURSIVE date_range AS (
        SELECT 
            Material,
            WEEK(STR_TO_DATE(`From`, '%d.%m.%Y'), 1) AS cw
        FROM your_table
        UNION ALL
        SELECT 
            Material,
            cw + 1
        FROM date_range
        WHERE WEEK(STR_TO_DATE((SELECT `To` FROM your_table WHERE Material = date_range.Material), '%d.%m.%Y'), 1) >= cw + 1
    )
    SELECT CONCAT('CW', cw) AS cw_col FROM date_range
) AS cw_list;

-- 生成并执行动态SQL
SET @sql = CONCAT('
SELECT 
    Material, ', @cols, '
FROM (
    WITH RECURSIVE date_range AS (
        SELECT 
            Material,
            STR_TO_DATE(`From`, ''%d.%m.%Y'') AS start_date,
            STR_TO_DATE(`To`, ''%d.%m.%Y'') AS end_date,
            Sales,
            WEEK(STR_TO_DATE(`From`, ''%d.%m.%Y''), 1) AS cw
        FROM your_table
        UNION ALL
        SELECT 
            Material,
            start_date,
            end_date,
            Sales,
            cw + 1
        FROM date_range
        WHERE WEEK(end_date, 1) >= cw + 1
    ),
    cw_count AS (
        SELECT 
            Material,
            COUNT(DISTINCT cw) AS total_weeks,
            Sales
        FROM date_range
        GROUP BY Material, Sales
    )
    SELECT 
        dr.Material,
        CONCAT(''CW'', dr.cw) AS cw_col,
        ROUND(dr.Sales / cc.total_weeks, 2) AS weekly_sales
    FROM date_range dr
    JOIN cw_count cc ON dr.Material = cc.Material
) AS temp
GROUP BY Material;
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关键说明 #

• WEEK() 函数的第二个参数控制周起始日，可根据业务需求调整（0=周日起始，1=周一起始）。
• 如果使用SQL Server，需替换为DATEPART(ISO_WEEK, date)获取ISO周，动态SQL用STRING_AGG拼接列名，执行用EXEC sp_executesql。
• 递归CTE确保覆盖起始到结束日期之间的所有日历周，保证分配逻辑准确。

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