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

ファクトテーブルの dbo.FactInternetSales と関連ディメンションテーブルを結合して製品別の累計売上構成比を抽出するクエリ

with t1 as (
    SELECT
        s.[ProductKey],
        s.[CustomerKey],
        d.[FullDateAlternateKey],
        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.[subcategory],
        sum(t1.[revenue]) as revenue
    from
        t1
    group by
        t1.[subcategory]
)
select
    t2.[subcategory],
    t2.[revenue],
    100.0 * t2.[revenue] / sum(t2.[revenue]) over() as sales_ratio,
    100.0 * sum(t2.[revenue]) over(order by t2.[revenue] desc) 
    / sum(t2.[revenue]) over() as stacked_sales_ratio
from
    t2;

 
結果はこうなる。

商品の売上構成比

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

PowerBI で累計売上構成比を可視化

参考図書

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