본문 바로가기
Cito 일/SQL

SQL 윈도우 함수 [Visual Explanation]

by citopes 2019. 10. 23.
반응형

이 기사의 목적은 SQL 창 함수 및 일부 고급 개념을 소개하는 것입니다. SQL에서 윈도우 함수는 합계 또는 평균과 같은 함수를 말하며, 결과는 현재 행을 기준으로 행을 설정합니다.

다룰 세부 사항이 많이 있지만 나중에 기사에서 다루는 것을 볼 수 있습니다. 이제는 창 기능이 작동하는 방식을 파고 프레임에 대해 더 많이 알게 될 것입니다.

 

이 레슨의 모든 예제는 Microsoft SQL Server Management Studio 및 AdventureWorks 데이터베이스를 기반으로합니다.  SQL Server 사용 설명서를 사용하여 이러한 무료 도구를 사용하여 시작할 수 있습니다  .

 

SQL 창 함수 소개

창 기능은 대부분 친숙합니다. 그것들을 특별하게 만드는 것은 쿼리 프로세서의 현재 행과 관련된 일련의 행에서 작동하도록 설정되어 있다는 것입니다. 이 세트를 창 프레임이라고하며 OVER 절을 사용하여 설정합니다 .

ORDER BY  PARTITION BY 매개 변수 를 도입하면 OVER 절이 까다로워 질 수 있지만 한 번에 하나씩 수행합니다. 이 기사의 끝 부분에서 이들 각각이 윈도우 함수 계산에 어떤 영향을 미치는지 살펴보고 다른 분석 함수 및 파티션 옵션을 준비 할 수 있습니다.

다음 다이어그램은이 기사에서 다루는 모든 개념을 정리 한 것입니다. 다음 섹션을 읽으면서이 그림으로 다시 돌아가서 전체 그림에 어떻게 적용되는지 이해하는 것이 좋습니다.

 

SQL 창 함수 개요

SQL OVER 조항

 

 

앞에서 언급했듯이 집계를 SQL 창 함수로 만드는 것은 OVER 절입니다. 여기에 모든 영업 사원의 총 SalesYTD를 계산하는 간단한 창 함수가 표시됩니다. 

SELECT BusinessEntityID
      ,TerritoryID     
      ,SalesYTD     
      ,SUM(SalesYTD) OVER() as TotalSalesYTD
FROM  Sales.SalesPerson S

출력을 실행할 때 :

SQL 창 함수 결과

 

TotalSalesYTD는 쿼리를 실행 한 것과 같은 값입니다.

SELECT SUM(SalesYTD)
FROM   Sales.SalesPerson

실제로 원하는 경우이 하위 쿼리를 사용하여 동일한 결과를 만들 수 있습니다.

SELECT BusinessEntityID     
      ,TerritoryID     
      ,SalesYTD     
      ,(SELECT SUM(SalesYTD) 
          FROM Sales.SalesPerson) as TotalSalesYTD
FROM Sales.SalesPerson S

 

 

그러나 SQL 창 기능을 자세히 살펴보면 하위 쿼리 만 사용하여 얻은 데이터를 빠르게 집계하는 기능을 능가합니다.

PARTITION BY를 통한 SQL OVER

SQL 파티션 BY는 윈도우 함수의 결과를 계산하는 그룹으로 행을 분할한다. 창 함수는 이러한 행에서 계산됩니다.

 

GROUP BY 절의 작동 방식과 유사 합니다. 그러나 GROUP BY 절의 결과는 요약 행이며, 창 함수를 사용하면 원래 행은 그대로 유지됩니다. 파티션은 논리적이며 계산 목적으로 만 사용됩니다.

다음은 이전 예제의 연속입니다. 이 경우 결과를 TerritoryID로 분할했습니다.    

SELECT BusinessEntityID     
      ,TerritoryID     
      ,SalesYTD     
      ,SUM(SalesYTD) OVER(PARTITION BY TerritoryID) as TotalSalesYTD
FROM   Sales.SalesPerson

TotalSalesYTD는 이제 각 지역에 대한 TotalSalesYTD를 나타냅니다.

하위 쿼리를 사용하여 동일한 쿼리를 수행 할 수 있습니까? 확실한! 아래와 비슷한 상관 하위 쿼리를 사용할 수 있습니다

SELECT BusinessEntityID
        ,TerritoryID
        ,SalesYTD
        ,(SELECT SUM(SalesYTD)
          FROM   Sales.SalesPerson
          WHERE  COALESCE(TerritoryID, -1) = COALESCE(S.TerritoryID, -1)
         ) AS TotalSalesYTD
FROM Sales.SalesPerson AS S
ORDER BY TerritoryID;

그러나 보시 다시피 하위 쿼리 는 작성하기가 더 번거로워지고 있습니다. 반면, SQL 창은보다 우아합니다.

이것을 알아 냈을 수도 있지만, 파티션을 지정하지 않으면 전체 결과가 파티션으로 간주됩니다. 다음 주제로 넘어갈 때 알아야 할 것이 중요합니다. 다음 주제는 계산을 위해 파티션 내에서 행을 정렬하는 방법과 관련이 있습니다.

ORDER BY를 사용한 SQL OVER

창 내 에서 ORDER BY 절을 사용하면 파티션 내에서 행이 처리되도록 설정합니다. PARTITION BY를 지정하지 않으면 파티션에 모든 행이 포함됩니다.

파티션 시작부터 현재 행까지 또는 현재 행 ORDER BY 값과 같은 행까지의 모든 행은 창 프레임의 일부로 간주됩니다. SUM과 같은 많은 창 기능은이 프레임 내에서 작동합니다. 즉, 창의 ORDER BY에 따라 누적 합계, 평균  을 설정하는 것이 간단 해집니다 .

SalesPerson의 누계 또는 TotalSalesYTD를 계산하는 예를 살펴 보겠습니다. 영업 사원의 BusinessEntityID별로 창을 주문하여이를 수행합니다.

SELECT BusinessEntityID     
      ,TerritoryID     
      ,SalesYTD     
      ,SUM(SalesYTD) OVER(ORDER BY BusinessEntityID) as TotalSalesYTD
FROM   Sales.SalesPerson

결과는 다음과 같습니다

 

OVER를 사용한 윈도우 함수 결과

TotalSalesYTD가 점차 커지고 결국 이전에 계산 된 TotalSalesYTD와 같습니다.

윈도우 기능 결과 정렬

이제 BusinessEntityID와 다른 방식으로 결과를 주문하면 어떻게 될지 궁금 할 것입니다.

TerritoryID로 주문하면 누적 합계 계산이 손상됩니까?

실제로는 그렇지 않습니다! SQL 윈도우 함수의 계산 이유 는 실행 순서의 SELECT 단계 ( 결과 순서 이전)에 발생합니다. 확실하지 않은 경우이 단계별 비교를 확인하십시오.

SQL 창 함수 및 정렬 된 결과

 

왼쪽의 결과는 오른쪽의 결과와 다르게 정렬되지만 각 BusinessEntityID에 해당하는 TotalSalesYTD는 동일하게 유지됩니다. 두 가지 경우에 이것을 지적하는 곳을 볼 수 있습니다 (강조 표시된 행 참조).

핵심은 SQL 창 함수가 계산을 수행 할 때 행의 논리적 순서에 따라 수행되며 최종 결과와 무관하다는 것입니다. 이러한 행은 창의 하위 집합이므로 프레임이라고합니다.

두 개의 창 기능 사용

창 함수를 하나의 쿼리로 결합하여 예제를 계속합시다. 아래에는 두 개의 윈도우 기능이있는 곳이 있습니다. 하나는 컴퓨터 총계와 다른 하나는 총계입니다.

SELECT BusinessEntityID     
      ,TerritoryID     
      ,SalesYTD     
      ,SUM(SalesYTD) OVER(ORDER BY BusinessEntityID)
       as RunningTotalSalesYTD     
      ,SUM(SalesYTD) OVER() as GrandTotalSalesYTD
FROM   Sales.SalesPerson
ORDER BY BusinessEntityID

이는 동일한 쿼리 내에 두 개의 프레임이있을 수 있음을 보여줍니다. 

두 개의 창 기능

 

RunningTotalSalesYTD의 프레임은 BusinessEntityID를 통해 현재 행의 순서로 정의됩니다. ORDER BY가 없으므로 GrandTotalSalesYTD 프레임은 전체 파티션을 포함합니다.

이제 SQL 창 함수 개념을 더 잘 이해 했으므로 OVER 절에서 PARTITION BY와 ORDER BY가 함께 작동하는 방법을 살펴 보겠습니다.

PARTITION BY 및 ORDER BY를 사용한 SQL OVER

OVER 절 내에서 PARTION BY 및 ORDER BY를 함께 사용하면 강력한 조합이 제공됩니다. 이렇게하면 그룹 내에서 누적 합계와 같은 계산을 수행 할 프레임을 만들 수 있습니다.

 

각 영업 영역 내에서 영업 담당자별로 누계를 계산하는 데 사용 된 쿼리를 고려하십시오.

SELECT BusinessEntityID     
      ,TerritoryID     
      ,SalesYTD     
      ,SUM(SalesYTD) OVER(PARTITION BY TerritoryID ORDER BY BusinessEntityID)  as RunningTotalSalesYTDTerritory
FROM  Sales.SalesPerson

 

 

결과에서 새 TerritoryID (파티션)가 발생하면 실행중인 총 재설정이 표시됩니다.

주문 및 파티션 기준

 

몇 가지 주요 개념으로 돌아가서 마무리하겠습니다.

SQL 창 기능 키 개념

다음은이 기사에서 살펴본 주요 개념 중 일부입니다. 나는 많은 부분이 다루어 졌음을 알고 있지만, 개념을 옮기기 전에 개념과 전문 용어를 이해하는 것이 중요합니다.

OVER — 이것은 이전 기능이 윈도우 기능이라는 신호입니다. ORDER BY 또는 PARTITION BY와 같이 OVER 절에 포함 된 항목은 창의 프레임 방식을 나타냅니다. 의미, 윈도우 함수는 어떻게 계산됩니까?

주문 : 기능을 누적 합계로 만드는 것입니다. 프레임 내에서 행이 함수로 처리되는 순서를 결정합니다.

PARTITION – 그룹을 만드는 것입니다. 부분은 하나 이상의 열일 수 있습니다. 새 파티션 하나가 종료되면 창 기능 결과가 재설정됩니다.

 

일부 관찰 :  

ORDER BY가 없으면 합계가 누계가 아닙니다.

 

 

동일한 쿼리에서 다른 창을 가질 수 있습니다. 예를 들어 일, 월 및 연도 별 누계를 포함하는 것이 정말 쉽습니다!

기억해야 할 핵심 사항은 order by와 partition by로 정의 된 프레임이 정렬 순서와 완전히 분리되어 있다는 것입니다. 창 함수 내에서 정의한 창과는 아무런 관련이 없습니다. 결과가 먼저 계산되므로 결과에 영향을 미치지 않습니다.

 

반응형

댓글