使用另一列分組時按月滾動季度計算

我正在嘗試計算季度時間段的滾動百分比。通過 sql 的示例資料如下：

create table #demo (Month char(7), Type varchar(10), Mileage int);
--low
insert into #demo values ('2021-05', 'low', 5000);
insert into #demo values ('2021-06', 'low', 15000);
insert into #demo values ('2021-07', 'low', 3000);
insert into #demo values ('2021-08', 'low', 3500);

-- med
insert into #demo values ('2021-05', 'med', 6789);
insert into #demo values ('2021-06', 'med', 12876);
insert into #demo values ('2021-07', 'med', 1578);
insert into #demo values ('2021-08', 'med', 3500);


--high
insert into #demo values ('2021-05', 'high', 5000);
insert into #demo values ('2021-06', 'high', 1500);
insert into #demo values ('2021-07', 'high', 2700);
insert into #demo values ('2021-08', 'high', 2968);

Month   Type    Mileage
2021-05 high    5000
2021-05 low     5000
2021-05 med     6789
2021-06 high    1500
2021-06 low     15000
2021-06 med     12876
2021-07 high    2700
2021-07 low     3000
2021-07 med     1578
2021-08 high    2968
2021-08 low     3500
2021-08 med     3500

我的目標是獲取這些資料并分別計算每個類別的滾動 3 個月百分比。

示例：對于 2021 年的前 3 個月，我希望獲得 2021-05、2021-06、2021-07 的總和（低里程）、總和（中里程）、總和（高里程），然后將每個總和除以這三個月的總和（所有類別）。我想 PIVOT 解決方案在這里是理想的。我只是不知道該怎么做。

所以我希望看到滾動總數，例如：

Month    ThreeMonth_TotalMileage ThreeMonth_Low  ThreeMonth_Med ThreeMonth_High
2021-05  NULL                    NULL            NULL           NULL
2021-06  NULL                    NULL            NULL           NULL
2021-07  53443                   23000           21243          9200
2021-08  46622                   18800           17954          7168

例如 ThreeMonth_TotalMileage 是 2021-05、2021-06、2021-07 所有里程的總和。這只會顯示為 2021-07 年的運行總計，因為我們有 3 個月的歷史可以計算總和，否則我希望它為空。

3Month_Low 是 2021 年 5 月、2021 年 6 月、2021 年 7 月所有低里程的總和，基本上包括當月加上每個計算的前 2 個月。

關于如何嘗試使用 SQL Server 完成此任務的任何想法？謝謝！

uj5u.com熱心網友回復：

您可以使用Ordered Analytical Function來實作。這個想法是找到累積和。

對于將資料從行旋轉到列，您可以使用CASE和MAX。

select
Month
,Max(case when Type='low' then Mileage end)  as Month_Low
,Max(case when Type='med' then Mileage end)as Month_Med
,Max(case when Type='high' then Mileage end)  as Month_High
,case when ROW_NUMBER() over(order by Month asc)>=3 Then 1 end as is_3month_data_present
from #demo
group by month

下面的 SQL 應該給出所需的最終結果。

select
Month
,case when is_3month_data_present=1 then Sum(Month_Low Month_Med Month_High) over(order by Month ROWS BETWEEN 2 PRECEDING and CURRENT ROW )  end as ThreeMonth_TotalMileage   
,case when is_3month_data_present=1 then Sum(Month_Low) over(order by Month ROWS BETWEEN 2 PRECEDING and CURRENT ROW )  end as ThreeMonth_Low  
,case when is_3month_data_present=1 then Sum(Month_Med ) over(order by Month ROWS BETWEEN 2 PRECEDING and CURRENT ROW ) end as ThreeMonth_Med 
,case when is_3month_data_present=1 then Sum(Month_High) over(order by Month ROWS BETWEEN 2 PRECEDING and CURRENT ROW ) end  as ThreeMonth_High
from
(select
Month
,Max(case when Type='low' then Mileage end)  as Month_Low
,Max(case when Type='med' then Mileage end)as Month_Med
,Max(case when Type='high' then Mileage end)  as Month_High
,case when ROW_NUMBER() over(order by Month asc)>=3 Then 1 end as is_3month_data_present
from #demo
group by Month
)stg

輸出：

注意：根據給定的資料集，問題中提供的預期輸出是錯誤的。ThreeMonth_low 是錯誤的，其余都是正確的。對于 2021-07，實際值應23000為20300.

DB Fiddle：在這里試試

標籤：sql sql服务器

上一篇：計算沒有間隙的島嶼的大小

下一篇：通過在C#中使用帶有引數的for回圈來更新表