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

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

Co dělá GROUP BY?

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

• SKUPINA PODLE s ROLLUP
• SADY SESKUPENÍ
• GROUP BY with CUBE

Možnosti souhrnných a seskupovacích sad

Nejjednodušší možností je použít funkci UNION ALL k provedení souhrnu místo toho, aby se spoléhala na explicitní syntaxi. Výsledek je úplně stejný.

Následující příklad použití 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 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;

Aby bylo možné nahradit SADY SESKUPOVÁNÍ, uplatňuje 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.

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 vygenerují všechny úrovně pro nás. 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í 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 datovou krychlí sloupců při provádění agregace. Dotaz se spustí jednou pro každý řádek v dočasné tabulce #Cube. Výsledky jsou uloženy 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 výsledky vrátit čtením z dočasné tabulky #Results:

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.