Možnosti GROUP BY v Synapse SQL

Synapse SQL umožňuje vývoj řešení implementací různých možností GROUP BY.

Co group BY dělá

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

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

Možnosti GROUP BY podporované ve vyhrazeném fondu SQL

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

• Použití klauzule GROUP BY s operátorem ROLLUP
• SADY SESKUPENÍ
• GROUP BY with CUBE

Možnosti seskupovacích a souhrnných sad

Nejjednodušší možností je použít funkci UNION ALL k provedení kumulativní aktualizace místo toho, aby se spoléhala na explicitní syntaxi. Výsledek je úplně 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 vyžaduje předchozí příklad následující agregace:

• Země a oblast
• Země
• Celkový součet

Chcete-li nahradit funkci ROLLUP 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 SADY SESKUPOVÁNÍ, použije se princip vzorku. Pro úrovně agregace, které chcete zobrazit, stačí vytvořit jenom oddíly UNION ALL.

Možnosti krychle

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

Prvním krokem je definování datové krychle, která definuje všechny úrovně agregace, kterou chceme vytvořit. Poznamenejte si křížové 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 ukazuje výsledky CREATE TABLE AS SELECT:

Druhým krokem je určení cílové tabulky pro ukládání dočasný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 procházet kostku sloupců a provádět agregaci. Dotaz se spustí jednou pro každý řádek v dočasné tabulce #Cube. Výsledky se ukládají v dočasné #Results 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 výsledky vrátit čtením z #Results dočasné tabulky:

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

Rozdělením kódu do oddílů a generová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.