GROUP BY beállítások a Synapse SQL-ben
A Synapse SQL különböző GROUP BY-lehetőségek implementálásával lehetővé teszi a megoldások fejlesztését.
Mit csinál a GROUP BY?
A GROUP BY T-SQL záradék összegző sorhalmazba összesíti az adatokat.
A kiszolgáló nélküli SQL-készlet nem támogatja a GROUP BY beállításokat. A dedikált SQL-készlet korlátozott számú GROUP BY beállítást támogat.
A dedikált SQL-készletben támogatott GROUP BY beállítások
A GROUP BY olyan lehetőségekkel rendelkezik, amelyeket a dedikált SQL-készlet nem támogat. Ezek a lehetőségek áthidaló megoldásokkal rendelkeznek, amelyek a következők:
- CSOPORTOSÍTÁS ÖSSZEGZŐVEL
- CSOPORTOSÍTÁSI CSOPORTOK
- CSOPORTOSÍTÁS KOCKÁVAL
Kumulatív és csoportosítási csoportok beállításai
A legegyszerűbb megoldás itt az, hogy az UNION ALL használatával hajtja végre a kumulatív elemet ahelyett, hogy az explicit szintaxisra támaszkodik. Az eredmény pontosan ugyanaz
Az alábbi példa a GROUP BY utasítást használja a ROLLUP beállítással:
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]
)
;
A ROLLUP használatával az előző példa a következő összesítéseket kéri le:
- Ország és régió
- Ország
- Végösszeg
A ROLLUP helyére és ugyanazon eredmények visszaadásához használhatja az UNION ALL függvényt, és explicit módon megadhatja a szükséges összesítéseket:
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;
A CSOPORTOSÍTÁSI KÉSZLETEK lecseréléséhez a minta elve érvényes. Csak a látni kívánt összesítési szintekhez kell létrehoznia az UNION ALL szakaszokat.
Kockabeállítások
A GROUP BY WITH CUBE (CSOPORTOSÍTÁS KOCKÁVAL) csoportot az UNION ALL megközelítéssel lehet létrehozni. A probléma az, hogy a kód gyorsan nehézkessé és kényelmetlenné válhat. A probléma megoldásához használhatja ezt a fejlettebb módszert.
Az első lépés a "kocka" meghatározása, amely meghatározza a létrehozni kívánt összesítési szinteket. Jegyezze fel a két származtatott tábla CROSS JOIN -ját, mivel az az összes szintet létrehozza. A kód többi része formázásra szolgál.
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;
Az alábbi képen a CREATE TABLE AS SELECT (TÁBLÁZAT LÉTREHOZÁSA KIVÁLASZTÁS) eredménye látható:
A második lépés egy céltábla megadása az időközi eredmények tárolásához:
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
)
;
A harmadik lépés az összesítést végrehajtó oszlopok kockájának hurkolása. A lekérdezés az ideiglenes tábla #Cube minden sorában egyszer fog futni. Az eredmények a #Results temp táblában vannak tárolva:
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
Végül az eredményeket a #Results ideiglenes táblából való olvasással is visszaadhatja:
SELECT *
FROM #Results
ORDER BY 1,2,3
;
Ha szakaszokra bontja a kódot, és létrehoz egy hurokszerkezetet, a kód kezelhetőbbé és karbantarthatóbbá válik.
Következő lépések
További fejlesztési tippekért tekintse meg a fejlesztés áttekintését ismertető cikket.
Visszajelzés
https://aka.ms/ContentUserFeedback.
Hamarosan elérhető: 2024-ben fokozatosan kivezetjük a GitHub-problémákat a tartalom visszajelzési mechanizmusaként, és lecseréljük egy új visszajelzési rendszerre. További információ:Visszajelzés küldése és megtekintése a következőhöz: