Mengelompokkan menurut opsi untuk kumpulan SQL khusus di Azure Synapse Analytics

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

Apa yang dilakukan GROUP BY?

Klausa T-SQL GROUP BY mengagregasi data ke set ringkasan baris. 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 sets

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 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.

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

Perhatikan CROSS JOIN dari dua tabel turunan karena ini menghasilkan semua level untuk kita. 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 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 mengulangi kubus kolom kita yang melakukan agregasi. Kueri akan berjalan sekali untuk setiap baris dalam tabel sementara #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 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.