MSSQL] PIVOT, UNPIVOT, GROUPING SETS, ROLLUP, CUBE

• 결과 sets을 pivot, unpivot 하는 쿼리를 작성해보자. 
• GROUPING SET, CUBE, ROLLUP과 함께 여러 그룹을 명시하는 쿼리를 작성해보자. 

PIVOT, UNPIVOT

SQL Server에서 피벗을 사용하여 데이터가 표시되는 방식을 행 기반 방향에서 열 기반 방향으로 전환할 수 있다. 

열의 값을 고유한 값 목록에 통합한 다음, 해당 목록을 열 제목으로 프로젝션한다. 일반적으로 여기에 새 열의 열 값에 대한 집계가 포함된다. 

 

아래와 같은 중복값이 있는 데이터를 PIVOT 연산자를 사용하여 Category 및 Orderyear를 기준으로 결과를 분석해보자. 

Category로 그룹핑하고, Orderyear 별로 Qty의 합계를 표시하면 된다.

SELECT  Category, [2019],[2020],[2021]
FROM  ( SELECT  Category, Qty, Orderyear FROM CategoryQtyYear) AS D 
          PIVOT(SUM(qty) FOR orderyear IN ([2019],[2020],[2021])) AS pvt;

PIVOT을 사용하려면 다음 3개의 요소를 연산자에 제공해야 합니다. 

• 그룹화 : Category, FROM 절에 입력 열을 제공합니다. 이러한 열에서 PIVOT은 집계할 데이터를 그룹화하는데 사용할 열을 결정합니다. 이 결정에는 PIVOT 연산자의 다른 요소로 사용되지 않는 열을 살펴보는 방법이 사용됩니다. 
• 분산 : Orderyear, 피벗된 데이터의 열 머리글로 사용할 쉼표로 구분된 값 목록을 제공합니다. 값은 원본 데이터에서 발생합니다. 
• 집계 : Qty, 그룹화된 행에서 수행할 집계함수(SUM 등)를 제공합니다. 

UNPIVOT은 PIVOT의 논리적 역방향이다. 행을 열로 변환하는 대신 열을 행으로 변환한다. 

이미 PIVOT된 데이터를 행 방향 테이블형식으로 전환할 때 사용된다. 

 

UNPIVOT을 사용하려면 다음 3개의 요소를 연산자에 제공해야 합니다. 

• UNPIVOT할 원본 열 2019, 2020, 2021
• UNPIVOT 값을 표시할 새 열의 이름 qty 
• UNPIVOT 값의 이름을 표시할 열의 이름 Orderyear

SELECT category, qty, orderyear
FROM #PivotedCategorySales
UNPIVOT(qty FOR orderyear IN([2019],[2020],[2021])) AS unpvt;

3개의 열이 UNPIVOT된다. Category와 Orderyear의 교집합에 대해 새 행이 만들어진다. 

UNPIVOT은 원래 데이터를 복원하지 않는다. PIVOT 집계 프로세스에서 세부 데이터는 손실되며 UNPIVOT에 원본의 세부 정보를 되돌리는 기능은 없다. 

728x90

GROUPING SETS

여러 컬럼을 동시에 GROUP BY 해야하는 경우, 여러 쿼리를 UNION ALL로 결합해야 한다. 동일한 쿼리에서 하려면 GROUP BY의 GROUPING SET 하위절을 사용하면 된다. 

SELECT <column list with aggregate(s)>
FROM <source>
GROUP BY 
GROUPING SETS(
    (<column_name>),--one or more columns
    (<column_name>),--one or more columns
    () -- empty parentheses if aggregating all rows  모든 row 총합을 위한 빈괄호
        );

SELECT Category, Orderyear, SUM(Qty) AS TotalQty
FROM CategoryQtyYear
GROUP BY 
    GROUPING SETS((Category),(Orderyear),())
ORDER BY Category, Orderyear;

맨 위에 NULL, NULL, 79155는 모든 ROW의 집계로 생성된 행이므로 NULL로 표시된다. 

CUBE 

CUBE는 가능한 모든 조합과 출력 그룹화를 결정한다.

SELECT Category, Orderyear, SUM(Qty) AS TotalQty
FROM CategoryQtyYear
GROUP BY CUBE(Category,Orderyear)--GROUP BY할 열 목록을 작성한다. 
ORDER BY Category, Orderyear; 
--Category, Orderyear의 모든 조합을 GROUP BY한다.

ROLLUP

SELECT Category, Orderyear, SUM(Qty) AS TotalQty
FROM CategoryQtyYear
GROUP BY ROLLUP(Category,Orderyear);

(Category, Orderyear), (Category) 조합의 그룹화와 모든 빈 ()의 집계가 표시된다. 열 순서가 중요하다. 

ROLLUP은 모든 GROUP BY의 총계와 함꼐 각 그룹화의 소계를 제공한다. 

출처 : https://learn.microsoft.com/ko-kr/training/modules/transform-data-by-implementing-pivot-unpivot-rollup-cube/