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

미분류 / 글쓴이

  • 일련의 결과를 피벗 및 피벗 해제하는 쿼리를 작성해 보겠습니다.

  • GROUPING SET, CUBE 및 ROLLUP을 사용하여 여러 그룹을 지정하는 쿼리를 작성해 보겠습니다.

피벗, 리버스 피벗

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

열의 값을 고유한 값 목록으로 결합한 다음 해당 목록을 열 헤더에 투영합니다.

일반적으로 여기에는 새 열에 대한 열 값 집계가 포함됩니다.

아래와 같이 값이 반복되는 데이터는 PIVOT 연산자를 사용하여 계산할 수 있습니다.

Category 및 Orderyear별로 결과를 분석해 보겠습니다.

범주별로 그룹화하고 주문 연도별로 수량 합계를 표시합니다.


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은 운영자에게 세 가지 요소를 제공해야 합니다.

  • 그룹화 기준: 범주, FROM 절에 입력 열을 제공하십시오. 이러한 열의 PIVOT은 집계를 위해 데이터를 그룹화하는 데 사용되는 열을 결정합니다.

    이 결정은 사용되지 않는 열을 PIVOT 연산자의 추가 요소로 처리하여 이루어집니다.

  • 분산: 주문 연도, 피벗된 데이터의 열 머리글로 사용할 쉼표로 구분된 값 목록을 제공합니다.

    값은 소스 데이터에서 가져옵니다.

  • 모두: 수량, 그룹화된 행에서 수행할 집계 함수(예: SUM)를 제공합니다.

UNPIVOT은 PIVOT의 논리적 반대입니다.

행을 열로 변환하는 대신 열을 행으로 변환합니다.

PIVOT된 데이터를 행 중심의 표 형식으로 변환할 때 사용합니다.

UNPIVOT을 사용하려면 운영자에게 세 가지 요소를 제공해야 합니다.

  • UNPIVOT의 소스 열 2019년, 2020년, 2021년
  • UNPIVOT 값을 표시하는 새 열의 이름입니다.

    수량
  • UNPIVOT 값 이름을 표시하는 열의 이름입니다.

    가입 연도
SELECT category, qty, orderyear
FROM #PivotedCategorySales
UNPIVOT(qty FOR orderyear IN((2019),(2020),(2021))) AS unpvt;

세 개의 열은 UNPIVOT입니다.

Category와 Oderyear의 교차점에 대해 새 행이 생성됩니다.


UNPIVOT은 원본 데이터를 복원하지 않습니다.

세부 데이터는 PIVOT 집계 중에 손실되며 UNPIVOT은 원래 세부 정보를 복원할 수 없습니다.

그룹화 집합

동시에 여러 열을 GROUP BY해야 하는 경우 여러 쿼리를 UNION ALL로 결합해야 합니다.

동일한 쿼리에서 이 작업을 수행하려면 GROUPING SET의 GROUP BY 절을 사용할 수 있습니다.

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는 모든 행을 집계하여 만든 행이므로 NULL로 표시됩니다.

입방체

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한다.


말아 올린

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

(Category, Orderyear), (Category) 조합의 그룹화 및 모든 bins()의 집계를 표시합니다.

열 순서가 중요합니다.

ROLLUP은 각 그룹화에 대한 소계와 모든 GROUP BY에 대한 합계를 제공합니다.


원천: https://learn.microsoft.com/en-us/training/modules/transform-data-by-implementing-pivot-unpivot-rollup-cube/