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 satır özet kümesine toplar. GROUP BY,ayrılmış SQL havuzunun desteklemediği bazı seçeneklere sahiptir. Bu seçeneklerin geçici çözümleri şunlardır:
- ROLLUP ile GROUP BY
- GRUPLANDıRMA KÜMELERI
- KÜP ile GROUP BY
Toplama ve gruplandırma kümeleri seçenekleri
Buradaki en basit seçenek, belirtik söz dizimine güvenmek yerine toplamayı gerçekleştirmek için UNION ALL kullanmaktır. 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]
)
;
ROLLUP kullanılarak, önceki örnek aşağıdaki toplamaları istemektedir:
- Ülke ve Bölge
- Ülke
- Genel Toplam
ROLLUP'ı 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.
Küp seçenekleri
UNION ALL yaklaşımını kullanarak BIR GROUP BY WITH CUBE oluşturmak mümkündür. Sorun, kodun hızlı bir şekilde hantal ve hantal hale gelmesidir. 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.
Türetilmiş iki tablonun CROSS JOIN'ini not alın çünkü bu bizim için tüm düzeyleri oluşturur. 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'nin 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
)
;
Üçüncü adım, toplamayı gerçekleştiren sütun küpümüzü döngüye almaktır. Sorgu, #Cube geçici tablosundaki 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, geçici #Results 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ış.