Mengelompokkan menurut opsi untuk kumpulan SQL khusus di Azure Synapse Analytics

Dalam artikel ini, Anda akan menemukan tips untuk menerapkan grup menurut opsi di kumpulan SQL khusus.

Apa yang dilakukan GROUP BY?

Klausa GROUP BY T-SQL menggabungkan data ke kumpulan ringkasan baris. 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 melakukan 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.

Menggunakan contoh sebelumnya, langkah pertama adalah menentukan 'kubus' yang mendefinisikan semua tingkat agregasi yang ingin kita buat.

Perhatikan CROSS JOIN dari dua tabel turunan karena ini menghasilkan semua tingkat yang kita butuhkan. 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 CTAS:

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 pada kubus berbentuk kolom untuk pelaksanaan agregasi. Kueri akan dijalankan sekali untuk setiap baris dalam tabel sementara bernama #Cube. Hasilnya 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.