SQL Unpivot 滚动日期/周计算-解网

问：

这是我原来的桌子。它有一个预测发布日期和 6 列，表示 6 周的预测，然后是原始周日期。

Forecast_Release_Date	Adj_Fcst_1	Adj_Fcst_2	Adj_Fcst_3	Adj_Fcst_4	Adj_Fcst_5	Adj_Fcst_6
03-09-2023	299	312	309	248	282	270

预测周 1 = 10-09-2023

预测第 2 周 = 17-09-2023

预测第 3 周 = 24-09-2023

预测第 4 周 = 01-10-2023

预测第 5 周 = 08-10-2023

预测第 6 周 = 15-10-2023

我已经成功地将列取消透视为行。

Forecast_Release_Date	Forecast_Release_Week	Adjusted_Forecast
03-09-2023	10-09-2023	299
03-09-2023	10-09-2023	312
03-09-2023	10-09-2023	309
03-09-2023	10-09-2023	248
03-09-2023	10-09-2023	282
03-09-2023	10-09-2023	270

这是我的查询：

SELECT Forecast_Release_Date
    ,DATEADD(Week, 1, Forecast_Release_Date) AS Forecast_Week
    ,Adjusted_Forecast
FROM (
    SELECT SourceFileInsertDateTime AS Forecast_Release_Date
        ,Adj_Fcst_1
        ,Adj_Fcst_2
        ,Adj_Fcst_3
        ,Adj_Fcst_4
        ,Adj_Fcst_5
        ,Adj_Fcst_6
    FROM MyTable
    ) p
UNPIVOT(Adjusted_Forecast FOR [Week] IN (
            Adj_Fcst_1
            ,Adj_Fcst_2
            ,Adj_Fcst_3
            ,Adj_Fcst_4
            ,Adj_Fcst_5
            ,Adj_Fcst_6
            )) AS unpvt

但我似乎无法获得Forecast_Release_Week列的滚动每周日期，即第一行应该是 10-09，第二行 = 17-09，依此类推。

注意：这必须是基于发布日期列的动态，而不是硬编码。

我尝试按照 Unpivot 表和计算字段中的代码进行操作，但它给了我相同的结果。

任何帮助都是值得赞赏的。

提前致谢。

P. S. - 我正在使用 Azure Synapse 数据仓库 SQL。

SQL Azure-Synapse 取消 Pivot 交叉应用

SELECT
       SourceFileInsertDateTime AS Forecast_Release_Date
     , CrossApplied.*
FROM MyTable
CROSS APPLY (
    VALUES 
            (Adj_Fcst_1, dateadd(week,1,SourceFileInsertDateTime))
          , (Adj_Fcst_2, dateadd(week,2,SourceFileInsertDateTime))
          , (Adj_Fcst_3, dateadd(week,3,SourceFileInsertDateTime))
          , (Adj_Fcst_4, dateadd(week,4,SourceFileInsertDateTime))
          , (Adj_Fcst_5, dateadd(week,5,SourceFileInsertDateTime))
          , (Adj_Fcst_6, dateadd(week,6,SourceFileInsertDateTime))
        ) AS CrossApplied(Adjusted_Forecast, Forecast_Release_Week)

此处更全面地解释了该技术，请参阅此答案

在 Azure Synapse 数据仓库 SQL 中使用

SELECT
    SourceFileInsertDateTime AS Forecast_Release_Date,
    Adjusted_Forecast,
    Forecast_Release_Week
FROM
    (
        SELECT Adj_Fcst_1 AS Adjusted_Forecast, dateadd(week,1,SourceFileInsertDateTime) AS Forecast_Release_Week FROM MyTable
        UNION ALL
        SELECT Adj_Fcst_2, dateadd(week,2,SourceFileInsertDateTime) FROM MyTable
        UNION ALL
        SELECT Adj_Fcst_3, dateadd(week,3,SourceFileInsertDateTime) FROM MyTable
        UNION ALL
        SELECT Adj_Fcst_4, dateadd(week,4,SourceFileInsertDateTime) FROM MyTable
        UNION ALL
        SELECT Adj_Fcst_5, dateadd(week,5,SourceFileInsertDateTime) FROM MyTable
        UNION ALL
        SELECT Adj_Fcst_6, dateadd(week,6,SourceFileInsertDateTime) FROM MyTable
    ) AS T

SQL Unpivot 滚动日期/周计算

SQL Unpivot Rolling Date/Week Calculation

评论

评论