Opsi GROUP BY di Synapse SQL

Artikel
06/01/2023

Synapse SQL memungkinkan pengembangan solusi dengan menerapkan opsi GROUP BY yang berbeda.

Apa yang dilakukan GROUP BY

Klausa T-SQL GROUP BY mengagregasi data ke barisan kumpulan ringkasan.

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

Opsi GROUP BY didukung pada kumpulan SQL khusus

GROUP BY memiliki beberapa opsi yang tidak didukung oleh kumpulan SQL khusus. Opsi ini memiliki solusi sebagai berikut:

GROUP BY dengan ROLLUP
GROUPING SETS
GROUP BY dengan CUBE

Opsi rollup dan grouping set

Opsi paling sederhana di sini adalah menggunakan UNION ALL untuk mengeksekusi 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 menampilkan 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

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

Langkah pertama adalah mendefinisikan 'kubus' yang mendefinisikan semua tingkat agregasi yang ingin kita buat. Catatlah CROSS JOIN dari dua tabel yang diturunkan karena menghasilkan semua tingkatan. Sisa kode 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:

Mengelompokkan menurut kubus

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 mengulangi kubus kolom yang melakukan agregasi. Kueri akan berjalan sekali untuk setiap baris dalam tabel sementara #Cube. Hasil disimpan dalam tabel #Results temp:

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 menampilkan hasilnya dengan membaca dari tabel sementara #Results:

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

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

Langkah berikutnya

Untuk tips pengembangan selengkapnya, buka gambaran pengembangan.

Bagikan melalui