Seskupení podle možností pro vyhrazené fondy SQL ve službě Azure Synapse Analytics

V tomto článku najdete tipy pro implementaci možností seskupování podle ve vyhrazených fondech SQL.

Co funkce GROUP BY dělá?

Klauzule T-SQL GROUP BY agreguje data do souhrnné sady řádků. Group BY nabízí některé možnosti, které vyhrazený fond SQL nepodporuje. Tyto možnosti mají následující alternativní řešení:

• GROUP BY with ROLLUP
• SESKUPOVACÍ SADY
• GROUP BY with CUBE

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 naprosto stejný.

Následující příklad s použitím příkazu 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 ukázkový princip. Oddíly UNION ALL stačí vytvořit jenom pro požadované úrovně agregace.

Možnosti datové krychle

Je možné vytvořit GROUP BY WITH CUBE pomocí přístupu UNION ALL. Problémem je, ž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.

V předchozím příkladu je prvním krokem 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 tím se pro nás vygenerují 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 CTAS:

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 naši datová krychle 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 stane lépe spravovatelným a udržovatelným.

Další kroky

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