Možnosti SESKUPIT PODLE v Synapse SQL

Článek
06/01/2023

Synapse SQL umožňuje vyvíjet řešení díky implementaci různých možností GROUP BY.

Co funkce GROUP BY dělá

Klauzule GROUP BY T-SQL agreguje data do souhrnné sady řádků.

Bezserverový fond SQL nepodporuje možnosti GROUP BY. Vyhrazený fond SQL podporuje omezený počet možností GROUP BY.

Podporované možnosti GROUP BY ve vyhrazeném fondu SQL

GROUP BY má některé možnosti, které vyhrazený fond SQL nepodporuje. Tyto možnosti obsahují alternativní řešení, která jsou následující:

SESKUPOVAT PODLE S KUMULATIVNÍ AKTUALIZACÍ
SESKUPOVACÍ SADY
SESKUPOVAT PODLE POMOCÍ DATOVÉ KRYCHLE

Možnosti sad souhrnů a seskupení

Nejjednodušší možností je použít k provedení souhrnu funkci UNION ALL, a nespoléhat se na explicitní syntaxi. Výsledek je přesně stejný.

Následující příklad používá příkaz GROUP BY s možností 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]
                )
;

Pomocí funkce ROLLUP si předchozí příklad vyžádá následující agregace:

Země a oblast
Země
Celkový součet

Pokud chcete funkci ROLLUP nahradit a vrátit stejné výsledky, můžete použít funkci UNION ALL a explicitně zadat požadované agregace:

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;

Pokud chcete nahradit SESKUPOVACÍ SADY, použije se princip výběru. Stačí vytvořit jenom oddíly UNION ALL pro požadované úrovně agregace.

Možnosti datové krychle

Pomocí přístupu UNION ALL je možné vytvořit funkci GROUP BY WITH CUBE. Problém je v tom, že kód se může rychle stát těžkopádným a nepraktickém. Pokud chcete tento problém zmírnit, můžete použít tento pokročilejší přístup.

Prvním krokem je definování datové krychle, která definuje všechny úrovně agregace, které chceme vytvořit. Všimněte si křížového spojení dvou odvozených tabulek, protože generuje všechny úrovně. Zbytek kódu je k dispozici pro formátování.

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;

Následující obrázek znázorňuje výsledky příkazu CREATE TABLE AS SELECT:

Seskupovat podle datové krychle

Druhým krokem je určení cílové tabulky pro ukládání průběžných výsledků:

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

Třetím krokem je smyčka přes krychli sloupců provádějících agregaci. Dotaz se spustí jednou pro každý řádek v #Cube dočasné tabulce. Výsledky jsou uložené v #Results dočasné tabulce:

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

Nakonec můžete vrátit výsledky čtením z #Results dočasné tabulky:

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

Rozdělením kódu do oddílů a vygenerováním konstruktoru smyčky se kód stává lépe spravovatelným a udržovatelným.

Další kroky

Další tipy pro vývoj najdete v přehledu vývoje.

Sdílet prostřednictvím