Opsi GROUP BY di Synapse SQL

Synapse SQL memungkinkan pengembangan solusi dengan menerapkan berbagai opsi GROUP BY.

Apa yang dilakukan GROUP BY

Klausa GROUP BY T-SQL menggabungkan data ke kumpulan ringkasan baris.

Kumpulan SQL tanpa server tidak mendukung opsi GROUP BY. Kumpulan SQL khusus mendukung sejumlah opsi GROUP BY yang terbatas.

Opsi GROUP BY yang didukung di kumpulan SQL khusus

GROUP BY memiliki beberapa opsi yang tidak didukung kumpulan SQL khusus. Opsi ini memiliki solusinya, yaitu sebagai berikut:

• GROUP BY dengan ROLLUP
• Kelompok Set
• GROUP BY dengan CUBE

Opsi kumpulan rollup dan pengelompokan

Opsi paling sederhana di sini adalah menggunakan UNION ALL untuk menjalankan rollup daripada mengandalkan sintaks eksplisit. Hasilnya sama persis

Contoh berikut menggunakan pernyataan GROUP BY dengan opsi ROLLUP:

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]
                )
;

Dengan menggunakan ROLLUP, contoh sebelumnya meminta agregasi berikut:

• Negara dan Wilayah
• Negara
• Total Keseluruhan

Untuk mengganti ROLLUP dan mengembalikan hasil yang sama, Anda dapat menggunakan UNION ALL dan secara eksplisit menentukan agregasi yang diperlukan:

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;

Untuk mengganti GROUPING SETS, prinsip sampel berlaku. Anda hanya perlu membuat bagian UNION ALL untuk tingkat agregasi yang ingin Anda lihat.

Opsi kubus

Anda dapat membuat GROUP BY WITH CUBE menggunakan pendekatan UNION ALL. Masalahnya adalah bahwa kode dapat dengan cepat menjadi rumit dan sulit diatur. Untuk mengurangi masalah ini, Anda dapat menggunakan pendekatan yang lebih canggih ini.

Langkah pertama adalah menentukan 'kubus' yang mendefinisikan semua tingkat agregasi yang ingin kita buat. Perhatikan penggunaan CROSS JOIN dari dua tabel turunan karena ini menghasilkan semua tingkatan. Kode lainnya ada untuk pemformatan.

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;

Gambar berikut menunjukkan hasil CREATE TABLE AS SELECT:

Langkah kedua adalah menentukan tabel target untuk menyimpan hasil sementara:

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
)
;

Langkah ketiga adalah melakukan iterasi melalui matriks kolom sambil melakukan agregasi. Kueri akan dijalankan sekali untuk setiap baris dalam tabel sementara bernama #Cube. Hasil disimpan dalam tabel sementara #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

Terakhir, Anda dapat mengembalikan hasilnya dengan membaca dari tabel sementara #Results:

SELECT *
FROM #Results
ORDER BY 1,2,3
;

Dengan memecah kode menjadi beberapa bagian dan membuat konstruksi perulangan, kode menjadi lebih mudah dikelola dan dipertahankan.

Langkah berikutnya

Untuk tips pengembangan selengkapnya, lihat ringkasan pengembangan.