MS SQL Server サンプルデータの WideWorldImportersDW スキーマを使って T-SQL を覚えるメモ。

Fact.Sale と Dimension.Date を使ってから月間受注の昨対比を抽出するクエリ

DECLARE @end_date date;
SELECT
    @end_date = EOMONTH('2014-12-01');

with t1 as (
    SELECT
        s.[Invoice Date Key] as sale_date,
        d.[Calendar Month Number] as sale_month,
        d.[Calendar Year] as sale_year,
        sum(s.[Total Including Tax]) as total_sale
    FROM
        [WideWorldImportersDW].[Fact].[Sale] as s
        inner join [Dimension].[Date] as d on s.[Invoice Date Key] = d.[Date]
    where
        s.[Invoice Date Key] between '2013-01-01' and @end_date
    group by
        s.[Invoice Date Key],
        d.[Calendar Month Number],
        d.[Calendar Year]
)
select
    t1.[sale_month],
    sum(
        case t1.[sale_year] when 2013 then t1.[total_sale] end
    ) as sale_2013,
    sum(
        case t1.[sale_year] when 2014 then t1.[total_sale] end
    ) as sale_2014,
    100.0 
    * sum(
        case t1.[sale_year] when 2014 then t1.[total_sale] end
    )
    / sum(
        case t1.[sale_year] when 2013 then t1.[total_sale] end
    ) as yoy
from
    t1
group by
    t1.[sale_month]
order by
    t1.[sale_month];

 
結果はこうなる。
yoy は Year-over-Year の事。つまり昨年対比。

月別の昨対比

 
PowerBI で表示するとこうなる。

PowerBI で昨対比を表示

参考図書

ビッグデータ分析・活用のためのSQLレシピ
SQL Server 2016の教科書 開発編