データウェアハウスへのクエリを実行する

10 分

データウェアハウス内のディメンションおよびファクトテーブルにデータが読み込まれている場合は、SQL を使用してテーブルへのクエリを実行し、それに含まれるデータを分析できます。 Synapse 専用 SQL プール内のテーブルに対するクエリに使用される Transact-SQL 構文は、SQL Server または Azure SQL Database で使用される SQL に似ています。

ディメンション属性によるメジャーの集計

データウェアハウスを使用したほとんどのデータ分析では、ファクトテーブルの数値メジャーをディメンションテーブルの属性別に集計する必要があります。スターまたはスノーフレークの各スキーマの実装方法により、この種の集計を実行するクエリは、ファクトテーブルをディメンションテーブルに接続するための JOIN 句と、集計階層を定義するための集計関数と GROUP BY 句の組み合わせを使用します。

たとえば、次の SQL では、架空のデータウェアハウス内の FactSales と DimDate のテーブルに対してクエリを実行し、年と四半期ごとの売上金額を集計します。

SELECT  dates.CalendarYear,
        dates.CalendarQuarter,
        SUM(sales.SalesAmount) AS TotalSales
FROM dbo.FactSales AS sales
JOIN dbo.DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear, dates.CalendarQuarter
ORDER BY dates.CalendarYear, dates.CalendarQuarter;

このクエリの結果は、次の表のようになります。

CalendarYear	CalendarQuarter	TotalSales
2020	1	25980.16
2020	2	27453.87
2020	3	28527.15
2020	4	31083.45
2021	1	34562.96
2021	2	36162.27
...	...	...

必要な数のディメンションテーブルを結合して、必要な集計を計算できます。たとえば、次のコードでは、前の例を拡張して、DimCustomer テーブル内の顧客の住所の詳細に基づいて、市区町村別に四半期の売上合計金額を分類します。

SELECT  dates.CalendarYear,
        dates.CalendarQuarter,
        custs.City,
        SUM(sales.SalesAmount) AS TotalSales
FROM dbo.FactSales AS sales
JOIN dbo.DimDate AS dates ON sales.OrderDateKey = dates.DateKey
JOIN dbo.DimCustomer AS custs ON sales.CustomerKey = custs.CustomerKey
GROUP BY dates.CalendarYear, dates.CalendarQuarter, custs.City
ORDER BY dates.CalendarYear, dates.CalendarQuarter, custs.City;

今回の結果には、各市区町村の四半期ごとの売上合計金額が含まれます。

CalendarYear	CalendarQuarter	City	TotalSales
2020	1	アムステルダム	5982.53
2020	1	ベルリン	2826.98
2020	1	シカゴ	5372.72
...	...	...	..
2020	2	アムステルダム	7163.93
2020	2	ベルリン	8191.12
2020	2	シカゴ	2428.72
...	...	...	..
2020	3	アムステルダム	7261.92
2020	3	ベルリン	4202.65
2020	3	シカゴ	2287.87
...	...	...	..
2020	4	アムステルダム	8262.73
2020	4	ベルリン	5373.61
2020	4	シカゴ	7726.23
...	...	...	..
2021	1	アムステルダム	7261.28
2021	1	ベルリン	3648.28
2021	1	シカゴ	1027.27
...	...	...	..

スノーフレークスキーマでの結合

スノーフレークスキーマを使用する場合、ディメンションを部分的に正規化でき、ファクトテーブルをスノーフレークディメンションに関連付けるために複数の結合が必要になります。たとえば、データウェアハウスに、製品カテゴリが個別の DimCategory テーブルに正規化されている DimProduct ディメンションテーブルが含まれているとします。製品カテゴリ別に販売されたアイテムを集計するクエリは、次の例のようになります。

SELECT  cat.ProductCategory,
        SUM(sales.OrderQuantity) AS ItemsSold
FROM dbo.FactSales AS sales
JOIN dbo.DimProduct AS prod ON sales.ProductKey = prod.ProductKey
JOIN dbo.DimCategory AS cat ON prod.CategoryKey = cat.CategoryKey
GROUP BY cat.ProductCategory
ORDER BY cat.ProductCategory;

このクエリの結果には、製品カテゴリ別に販売されたアイテムの数が含まれています。

ProductCategory	ItemsSold
アクセサリ	28271
Bits and pieces	5368
...	...

Note

クエリによって DimProduct のどのフィールドも返されない場合でも、FactSales と DimProduct、および DimProduct と DimCategory の JOIN 句は両方とも必要になります。

順位付け関数の使用

分析クエリのもう 1 つの一般的な種類は、ディメンション属性に基づいて結果を分割し、各パーティション内で結果を "順位付け" することです。たとえば、店舗をその売上収益によって毎年順位付けしたいとします。この目標を達成するには、Transact SQL の "順位付け" 関数 (ROW_NUMBER、RANK、DENSE_RANK、NTILE など) を使用できます。これらの関数を使用して、データをカテゴリに分類し、それぞれがパーティション内の各行の相対位置を示す特定の値を返すようにすることができます。

ROW_NUMBER は、パーティション内の行の序数位置を返します。たとえば、最初の行の番号は 1、2 番目の行は 2 などです。
RANK は、順序付けされた結果の各行の順位付けされた位置を返します。たとえば、売上高別に並べ替えられた店舗のパーティションでは、売上高が最も多い店舗は 1 に順位付けされます。複数の店舗の売上高が同じ場合は、同じ順位が付けられます。以降の店舗に割り当てられる順位は、売上高が多い店舗の数 (同順位を含む) を反映します。
DENSE_RANK は RANK と同じ方法でパーティション内の行を順位付けしますが、複数の行が同じ順位を持つ場合、以降の行の順位付け位置では、同順位が無視されます。
NTILE は、その行に該当する指定されたパーセンタイルを返します。たとえば、売上高別に並べ替えられた店舗のパーティションでは、NTILE(4) は、店舗の売上高が位置する四分位数を返します。

たとえば、次のクエリについて考えてみます。

SELECT  ProductCategory,
        ProductName,
        ListPrice,
        ROW_NUMBER() OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS RowNumber,
        RANK() OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS Rank,
        DENSE_RANK() OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS DenseRank,
        NTILE(4) OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS Quartile
FROM dbo.DimProduct
ORDER BY ProductCategory;

クエリは、製品をそのカテゴリに基づいてグループに分類します。各カテゴリパーティション内の各製品の相対位置は、その表示価格に基づいて決定されます。このクエリの結果は、次の表のようになります。

ProductCategory	ProductName	ListPrice	RowNumber	順位	DenseRank	Quartile
アクセサリ	ウィジェット	8.99	1	1	1	1
Accessories	Knicknak	8.49	2	2	2	1
Accessories	Sprocket	5.99	3	3	3	2
アクセサリ	Doodah	5.99	4	3	3	2
アクセサリ	Spangle	2.99	5	5	4	3
アクセサリ	Badabing	0.25	6	6	5	4
Bits and pieces	Flimflam	7.49	1	1	1	1
Bits and pieces	Snickity wotsit	6.99	2	2	2	1
Bits and pieces	Flange	4.25	3	3	3	2
...	...	...	...	...	...	...

Note

このサンプルの結果は、RANK と DENSE_RANK の違いを示しています。 "アクセサリ" カテゴリでは、Sprocket と Doodah の製品の表示価格が同じであり、両方とも 3 番目に高い価格の製品として順位付けされていることに注意してください。次に価格の高い製品には、RANK では 5 (それよりも高価な製品が 4 つあります)、DENSE_RANK では 4 (それより高価なものが 3 つあります) の順位が付けられています。

順位付け関数の詳細については、Azure Synapse Analytics ドキュメントの「順位付け関数 (Transact-SQL)」を参照してください。

概算カウントの取得

データウェアハウスの目的は主に、企業の分析データモデルとレポートをサポートすることですが、データアナリストやデータサイエンティストは多くの場合、データの基本的な規模と分布を決定するだけのために、何らかの初期データ探索を実行する必要があります。

たとえば、次のクエリでは、COUNT 関数を使用して、架空のデータウェアハウス内の各年の売上数を取得します。

SELECT dates.CalendarYear AS CalendarYear,
    COUNT(DISTINCT sales.OrderNumber) AS Orders
FROM FactSales AS sales
JOIN DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear
ORDER BY CalendarYear;

このクエリの結果は次の表のようになります。

CalendarYear	Orders
2019	239870
2020	284741
2021	309272
...	...

データウェアハウス内のデータ量によっては、指定された条件を満たすレコードの数をカウントする単純なクエリでも、実行にかなりの時間がかかることを意味する可能性があります。多くの場合、正確なカウントは必要ありません。概算見積もりで十分です。このような場合は、次の例に示すように APPROX_COUNT_DISTINCT 関数を使用できます。

SELECT dates.CalendarYear AS CalendarYear,
    APPROX_COUNT_DISTINCT(sales.OrderNumber) AS ApproxOrders
FROM FactSales AS sales
JOIN DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear
ORDER BY CalendarYear;

APPROX_COUNT_DISTINCT 関数は、HyperLogLog アルゴリズムを使用して概算カウントを取得します。結果は 97% の確率で最大エラー率が 2% であることが保証されるため、以前と同じ架空のデータを使用したこのクエリの結果は、次の表のようになります。

CalendarYear	ApproxOrders
2019	235552
2020	290436
2021	304633
...	...

カウントの精度は低くなりますが、年間売上のおおよその比較には十分です。大量のデータがある場合、APPROX_COUNT_DISTINCT 関数を使用したクエリはより速く完了するため、基本的なデータ探索時に、正確性の低下は許容されるトレードオフになる場合があります。

注意

詳しくは、APPROX_COUNT_DISTINCT 関数の資料を参照してください。

データ ウェアハウスへのクエリを実行する

ディメンション属性によるメジャーの集計

スノーフレーク スキーマでの結合

順位付け関数の使用

概算カウントの取得

フィードバック

データウェアハウスへのクエリを実行する

スノーフレークスキーマでの結合