GROUP BY lehetőségek a Synapse SQL-ben

Tip

Microsoft Fabric Data Warehouse egy nagyvállalati szintű relációs raktár egy Data Lake-alaprendszeren, jövőre kész architektúrával, beépített AI-vel és új funkciókkal. Ha még nem ismerkedik adattárházzal, kezdje a Fabric Data Warehouse. A meglévő dedikált SQL-készlet számítási feladatai frissíthetők Fabric az adatelemzés, a valós idejű elemzés és a jelentéskészítés új képességeinek eléréséhez.

• A Fabric ingyenes próbaverzió indítása.
• Migration Assistant Fabric Data Warehouse.

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 tesz 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 a következő megoldásokkal rendelkeznek:

• CSOPORTOSÍTÁS ROLLUP-pal
• CSOPORTOSÍTÁSI HALMAZOK
• CSOPORTOSÍTÁS CUBE-VAL

Összesítés és csoportosítókészletek beállításai

A legegyszerűbb megoldás itt az, hogy az UNION ALL használatával hajtsuk végre az összesítőt az explicit szintaxis használata helyett. 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:

• Ország és régió
• Ország
• Végösszeg

A ROLLUP cseréjére, és hogy ugyanazokat az eredményeket érje el, használhatja az UNION ALL-t, és explicit módon meghatározhatja 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 GROUPING SETS helyettesítésére a mintaelv alkalmazandó. Csak a megjeleníteni kívánt összesítési szintekhez kell létrehoznia az UNION ALL szakaszokat.

Kocka opciók

A GROUP BY WITH CUBE az UNION ALL megközelítéssel hozható létre. A probléma az, hogy a kód gyorsan nehézkessé és kezelhetetlenné válhat. A probléma megoldásához használhatja ezt a fejlettebb megközelítést.

Az első lépés a létrehozni kívánt összesítési szinteket meghatározó kocka meghatározása. Vegye figyelembe a két származtatott tábla CROSS JOIN-ját, mivel ez minden szintet létrehoz. 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 eredménye látható:

A második lépés egy céltábla megadása köztes eredmények tárolására:

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 oszlopok kockája fölötti hurkolás az összesítés végrehajtásához. A lekérdezés az #Cube ideiglenes tábla 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 az #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.