Synapse SQL では、さまざまな GROUP BY オプションを実装してソリューションを開発できます。
GROUP BY の機能
GROUP BY T-SQL 句は、行の概要セットにデータを集計します。
サーバーレス SQL プールでは、GROUP BY オプションはサポートされていません。 専用 SQL プールでは、限られた数の GROUP BY オプションがサポートされています。
専用 SQL プールでサポートされる GROUP BY オプション
GROUP BY には、専用 SQL プールでサポートされていないオプションがいくつかあります。 これらのオプションには、次のような回避策があります。
- ROLLUP を使用したグループ化
- グルーピング集合
- 「GROUP BY」と「CUBE」を使用したグループ化
ロールアップおよびグループ化セットのオプション
ここで最も簡単なオプションは、明示的な構文に依存するのではなく、UNION ALL を使用してロールアップを実行することです。 結果はまったく同じです
次の例では、ROLLUP オプションと共に GROUP BY ステートメントを使用します。
SELECT [SalesTerritoryCountry]
, [SalesTerritoryRegion]
, SUM(SalesAmount) AS TotalSalesAmount
FROM dbo.factInternetSales s
JOIN dbo.DimSalesTerritory t ON s.SalesTerritoryKey = t.SalesTerritoryKey
GROUP BY ROLLUP (
[SalesTerritoryCountry]
, [SalesTerritoryRegion]
)
;
ROLLUP を使用すると、前の例では次の集計が要求されます。
- 国と地域
- 国
- 総計
ROLLUP を置き換えて同じ結果を返すには、UNION ALL を使用し、必要な集計を明示的に指定します。
SELECT [SalesTerritoryCountry]
, [SalesTerritoryRegion]
, SUM(SalesAmount) AS TotalSalesAmount
FROM dbo.factInternetSales s
JOIN dbo.DimSalesTerritory t ON s.SalesTerritoryKey = t.SalesTerritoryKey
GROUP BY
[SalesTerritoryCountry]
, [SalesTerritoryRegion]
UNION ALL
SELECT [SalesTerritoryCountry]
, NULL
, SUM(SalesAmount) AS TotalSalesAmount
FROM dbo.factInternetSales s
JOIN dbo.DimSalesTerritory t ON s.SalesTerritoryKey = t.SalesTerritoryKey
GROUP BY
[SalesTerritoryCountry]
UNION ALL
SELECT NULL
, NULL
, SUM(SalesAmount) AS TotalSalesAmount
FROM dbo.factInternetSales s
JOIN dbo.DimSalesTerritory t ON s.SalesTerritoryKey = t.SalesTerritoryKey;
GROUPING SETS を置き換えるには、サンプル原則が適用されます。 表示する集計レベルの UNION ALL セクションのみを作成する必要があります。
キューブ オプション
UNION ALL アプローチを使用して GROUP BY WITH CUBE を作成できます。 問題は、コードがすぐに面倒で扱いにくくなる可能性があるということです。 この問題を軽減するには、このより高度なアプローチを使用できます。
最初の手順では、作成するすべての集計レベルを定義する 'キューブ' を定義します。 2つの派生テーブルの CROSS JOIN に注意してください。それはすべてのレベルを生成します。 残りのコードは書式設定用です。
CREATE TABLE #Cube
WITH
( DISTRIBUTION = ROUND_ROBIN
, LOCATION = USER_DB
)
AS
WITH GrpCube AS
(SELECT CAST(ISNULL(Country,'NULL')+','+ISNULL(Region,'NULL') AS NVARCHAR(50)) as 'Cols'
, CAST(ISNULL(Country+',','')+ISNULL(Region,'') AS NVARCHAR(50)) as 'GroupBy'
, ROW_NUMBER() OVER (ORDER BY Country) as 'Seq'
FROM ( SELECT 'SalesTerritoryCountry' as Country
UNION ALL
SELECT NULL
) c
CROSS JOIN ( SELECT 'SalesTerritoryRegion' as Region
UNION ALL
SELECT NULL
) r
)
SELECT Cols
, CASE WHEN SUBSTRING(GroupBy,LEN(GroupBy),1) = ','
THEN SUBSTRING(GroupBy,1,LEN(GroupBy)-1)
ELSE GroupBy
END AS GroupBy --Remove Trailing Comma
,Seq
FROM GrpCube;
次の図は、 CREATE TABLE AS SELECT の結果を示しています。
2 番目の手順では、中間結果を格納するためのターゲット テーブルを指定します。
DECLARE
@SQL NVARCHAR(4000)
,@Columns NVARCHAR(4000)
,@GroupBy NVARCHAR(4000)
,@i INT = 1
,@nbr INT = 0
;
CREATE TABLE #Results
(
[SalesTerritoryCountry] NVARCHAR(50)
,[SalesTerritoryRegion] NVARCHAR(50)
,[TotalSalesAmount] MONEY
)
WITH
( DISTRIBUTION = ROUND_ROBIN
, LOCATION = USER_DB
)
;
3 番目の手順では、集計を実行する列のキューブをループ処理します。 クエリは、#Cube 一時テーブルのすべての行に対して 1 回実行されます。 結果は、#Results 一時テーブルに格納されます。
SET @nbr =(SELECT MAX(Seq) FROM #Cube);
WHILE @i<=@nbr
BEGIN
SET @Columns = (SELECT Cols FROM #Cube where seq = @i);
SET @GroupBy = (SELECT GroupBy FROM #Cube where seq = @i);
SET @SQL ='INSERT INTO #Results
SELECT '+@Columns+'
, SUM(SalesAmount) AS TotalSalesAmount
FROM dbo.factInternetSales s
JOIN dbo.DimSalesTerritory t
ON s.SalesTerritoryKey = t.SalesTerritoryKey
'+CASE WHEN @GroupBy <>''
THEN 'GROUP BY '+@GroupBy ELSE '' END
EXEC sp_executesql @SQL;
SET @i +=1;
END
最後に、#Results 一時テーブルから読み取ることで結果を返すことができます。
SELECT *
FROM #Results
ORDER BY 1,2,3
;
コードをセクションに分割し、ループコンストラクトを生成することで、コードはより管理しやすく保守しやすくなります。
次のステップ
開発についてのその他のヒントは、開発の概要に関するページをご覧ください。