Azure Synapse Analytics'te ayrılmış SQL havuzları için gruplandırma seçenekleri

Bu makalede, ayrılmış SQL havuzlarında gruplandırma ölçütü seçeneklerini uygulamaya yönelik ipuçları bulacaksınız.

GROUP BY ne yapar?

GROUP BY T-SQL yan tümcesi, verileri bir özet satır kümesine toplar. GROUP BY,ayrılmış SQL havuzunun desteklemediği bazı seçeneklere sahiptir. Bu seçeneklerin geçici çözümleri şunlardır:

• GROUP BY with ROLLUP
• GROUPING SETS
• CUBE ile GROUP BY işlemi

Rollup and grouping sets options

The simplest option here is to use UNION ALL to perform the rollup rather than relying on the explicit syntax. Sonuç tamamen aynıdır.

ROLLUP seçeneğiyle GROUP BY deyimini kullanan aşağıdaki örnek:

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

ÖNCEKI örnek ROLLUP kullanarak aşağıdaki toplamaları istemektedir:

• Ülke ve Bölge
• Ülke
• Genel Toplam

ROLLUP değerini değiştirmek ve aynı sonuçları döndürmek için UNION ALL kullanabilir ve gerekli toplamaları açıkça belirtebilirsiniz:

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;

GRUPLANDıRMA KÜMELERİ'ni değiştirmek için örnek ilkesi geçerlidir. Yalnızca görmek istediğiniz toplama düzeyleri için UNION ALL bölümleri oluşturmanız gerekir.

Cube options

UNION ALL yaklaşımını kullanarak KÜPLE GROUP BY oluşturmak mümkündür. Sorun, kodun hızlı bir şekilde hantal ve kontrol edilmesi zor hale gelebiliyor olmasıdır. Bu sorunu azaltmak için bu daha gelişmiş yaklaşımı kullanabilirsiniz.

Önceki örneği kullanarak ilk adım, oluşturmak istediğimiz tüm toplama düzeylerini tanımlayan 'küp' tanımlamaktır.

Take note of the CROSS JOIN of the two derived tables since this generates all the levels for us. Kodun geri kalanı biçimlendirme için oradadır:

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;

Aşağıdaki görüntüde CTAS sonuçları gösterilmektedir:

İkinci adım, ara sonuçları depolamak için bir hedef tablo belirtmektir:

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

The third step is to loop over our cube of columns performing the aggregation. Sorgu, geçici #Cube tablodaki her satır için bir kez çalıştırılır. Sonuçlar #Results geçici tablosunda depolanır:

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

Son olarak, #Results geçici tablosundan okuyarak sonuçları döndürebilirsiniz:

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

Kodu bölümlere ayırıp döngü oluşturma yapısı oluşturarak kod daha yönetilebilir ve sürdürülebilir hale gelir.

Sonraki adımlar

Daha fazla geliştirme ipucu için bkz . geliştirmeye genel bakış.