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

ファクトテーブルの dbo.FactInternetSales と関連ディメンションテーブルを結合して、製品別の売上推移をファンチャートで表示するためのデータを抽出する。

with t1 as (
    SELECT
        s.[ProductKey],
        s.[CustomerKey],
        d.[FullDateAlternateKey],
        convert(varchar(7), format(d.[FullDateAlternateKey], 'yyyy-MM')) as year_month,
        s.[SalesOrderNumber],
        s.[SalesOrderLineNumber],
        s.[OrderQuantity] * s.[UnitPrice] as revenue,
        pc.[EnglishProductCategoryName] as category,
        psc.[EnglishProductSubcategoryName] as subcategory,
        p.[EnglishProductName] as productname
    FROM
        [dbo].[FactInternetSales] as s
        inner join [dbo].[DimDate] as d on s.[OrderDateKey] = d.[DateKey]
        inner join [dbo].[DimProduct] as p on s.[ProductKey] = p.[ProductKey]
        left join [dbo].[DimProductSubcategory] as psc on p.[ProductSubcategoryKey] = psc.[ProductSubcategoryKey]
        left join [dbo].[DimProductCategory] as pc on psc.[ProductCategoryKey] = pc.[ProductCategoryKey]
    where
        1 = 1
        and s.[OrderDate] between '2013-01-01' and '2013-12-31'
),
t2 as (
    select
        t1.[year_month],
        t1.[category],
        sum(t1.[revenue]) as revenue
    from
        t1
    group by
        t1.[year_month],
        t1.[category]
)
select
    t2.[year_month],
    t2.[category],
    t2.[revenue],
    first_value(t2.[revenue]) over(partition by t2.[category] order by t2.[year_month]) as base,
    100.0 * t2.[revenue] / first_value(t2.[revenue]) over(partition by t2.[category] order by t2.[year_month]) as rate
from
    t2
order by
    t2.[year_month];

 
結果はこうなる。

T-SQL で売上データの推移を抽出

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

PowerBI の線グラフでファンチャート表示

参考図書

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