다음을 통해 공유

Synapse SQL의 GROUP BY 옵션

Synapse SQL을 사용하면 다양한 GROUP BY 옵션을 구현하여 솔루션을 개발할 수 있습니다.

GROUP BY의 기능

GROUP BY T-SQL 절은 데이터를 요약 행 집합에 집계합니다.

서버리스 SQL 풀은 GROUP BY 옵션을 지원하지 않습니다. 전용 SQL 풀은 제한된 수의 GROUP BY 옵션을 지원합니다.

전용 SQL 풀에서 지원되는 GROUP BY 옵션

GROUP BY에는 전용 SQL 풀이 지원하지 않는 몇 가지 옵션이 있습니다. 이러한 옵션에는 다음과 같은 해결 방법이 있습니다.

  • GROUP BY with ROLLUP
  • GROUPING SETS
  • GROUP BY with CUBE

롤업 및 그룹셋 옵션

여기서 가장 간단한 옵션은 명시적 구문에 의존하지 않고 UNION ALL을 사용하여 롤업을 실행하는 것입니다. 결과는 정확히 동일합니다.

다음 예제에서는 ROLLUP 옵션과 함께 GROUP BY 문을 사용합니다.

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

앞의 예제에서는 ROLLUP을 사용하여 다음 집계를 요청합니다.

  • 국가 및 지역
  • 국가
  • 총합계

ROLLUP을 바꾸고 동일한 결과를 반환하려면 UNION ALL을 사용하고 필요한 집계를 명시적으로 지정할 수 있습니다.

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;

GROUPING SETS를 대체하기 위해 샘플 원칙이 적용됩니다. 보려는 집계 수준에 대한 UNION ALL 섹션만 만들어야 합니다.

큐브 옵션

UNION ALL 접근 방식을 사용하여 GROUP BY WITH CUBE를 만들 수 있습니다. 문제는 코드가 빠르게 번거롭고 다루기 어려울 수 있다는 것입니다. 이 문제를 완화하려면 이 고급 접근 방식을 사용할 수 있습니다.

첫 번째 단계는 만들려는 모든 집계 수준을 정의하는 '큐브'를 정의하는 것입니다. 두 파생 테이블의 CROSS JOIN에 주목하세요. 이는 모든 수준을 생성합니다. 코드의 나머지 부분에는 서식 지정이 있습니다.

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;

다음 이미지는 CREATE TABLE AS SELECT의 결과를 보여줍니다.

큐브별로 그룹화

두 번째 단계는 중간 결과를 저장하기 위한 대상 테이블을 지정하는 것입니다.

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

세 번째 단계에서는 집계를 처리하는 열의 큐브를 반복적으로 수행합니다. 쿼리는 #Cube 임시 테이블의 모든 행에 대해 한 번 실행됩니다. 결과는 #Results 임시 테이블에 저장됩니다.

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

마지막으로 #Results 임시 테이블에서 읽어 결과를 반환할 수 있습니다.

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

코드를 섹션으로 분리하고 루프 구문을 생성하면 코드 관리가 더 쉬워지고 유지 관리가 쉬워집니다.

다음 단계

더 많은 개발 팁은 개발 개요를 참조하세요.

  • Last updated on