SQL 창 함수 소개

당신이 미워하고 싶지만 알아야 할 매우 강력한 기능

SQL 창 함수는 몇 가지 매우 강력하고 유용한 기능을 제공합니다. 그러나 많은 사람들에게 표준 SQL과 너무 생소하기 때문에 배우고 이해하기 어렵고 구문이 이상하고 자주 피합니다.

창 함수는 집계와 유사한 계산 함수로 간단하게 설명할 수 있지만 GROUP BY 절을 통한 일반 집계가 결합된 다음 집계되는 개별 행을 숨기는 경우 창 함수는 개별 행에 액세스할 수 있으며 해당 행의 일부 속성을 추가할 수 있습니다. 결과 집합입니다.

이 SQL 창 함수 자습서에서는 창 함수를 시작하고, 이점과 사용 시기에 대해 설명하고, 개념에 도움이 되는 실제 예를 제공하겠습니다.

데이터에 대한 창

SQL에서 가장 많이 사용되는 중요한 기능 중 하나는 특정 방식으로 데이터 행을 집계하거나 그룹화하는 기능입니다. 그러나 어떤 경우에는 필요한 항목에 따라 그룹화가 매우 복잡해질 수 있습니다.

순위, 상위 x개 목록 등을 얻기 위해 쿼리 결과를 반복하고 싶었던 적이 있습니까? 시각화 도구에 적합한 데이터를 준비하고 싶었지만 거의 불가능하거나 너무 복잡하여 가치가 없다는 분석 프로젝트가 있습니까?

창 기능을 사용하면 일을 더 쉽게 할 수 있습니다. 쿼리 결과를 얻은 후(예: WHERE 절 및 표준 집계 후) 창 함수는 나머지 행(데이터 창 )에서 작동하여 원하는 것을 얻습니다.

우리가 살펴볼 창 기능 중 일부는 다음과 같습니다.

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

오버 이지

OVER 절은 창 함수를 지정하는 것으로 문에 항상 포함되어야 합니다. OVER 절의 기본값은 전체 행 집합입니다. 예를 들어, 회사 데이터베이스의 직원 테이블을 보고 각 행의 총 직원 수와 함께 회사에 입사한 시간을 포함하여 각 직원의 정보를 표시해 보겠습니다.

 SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

위의 많은 창 함수와 마찬가지로 창을 사용하지 않는 더 친숙한 방식으로 작성할 수도 있습니다. 이 간단한 예에서는 나쁘지 않습니다.

 SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

그러나 이제 행의 직원과 같은 달에 시작한 직원 수를 표시하려고 한다고 가정해 보겠습니다. 각 행에 대해 해당 월로만 개수를 좁히거나 제한해야 합니다. 어떻게 이루어지나요? 다음과 같이 창 PARTITION 절을 사용합니다.

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

파티션을 사용하면 특정 값을 기준으로 창을 섹션으로 필터링할 수 있습니다. 각 섹션은 종종 창틀 이라고 합니다.

더 나아가 같은 달에 시작한 직원 수를 알고 싶었을 뿐만 아니라 그 달에 시작한 순서도 표시하려고 한다고 가정해 보겠습니다. 이를 위해 친숙한 ORDER BY 절을 사용할 수 있습니다. 그러나 창 함수 내에서 ORDER BY 는 쿼리 끝에서와 약간 다르게 작동합니다.

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

이 경우 ORDER BY 는 파티션의 시작(이 경우 직원이 시작한 월 및 연도)에서 현재 행으로 이동하도록 창을 수정합니다. 따라서 각 파티션에서 카운트가 다시 시작됩니다.

순위를 매기다

창 함수는 순위 지정 목적으로 매우 유용할 수 있습니다. 이전에는 COUNT 집계 함수를 사용하여 직원이 회사에 합류한 순서를 확인할 수 있음을 보았습니다. ROW_NUMBER() , RANK() 및 DENSE_RANK() 와 같은 창 순위 함수를 사용할 수도 있습니다.

다음 달에 새 직원을 추가하고 파티션을 제거한 후 차이점을 확인할 수 있습니다.

 SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

차이점을 볼 수 있습니다. ROW_NUMBER() 는 주어진 파티션 내에서 순차적 카운트를 제공합니다(그러나 파티션이 없으면 모든 행을 통과합니다). RANK() 는 ORDER BY 절을 기반으로 각 행의 순위를 제공합니다. 동점을 표시한 후 다음 순위를 건너뜁니다. DENSE_RANK 도 동점을 표시하지만 동점이 없는 것처럼 다음 연속 값으로 계속됩니다.

기타 순위 기능은 다음과 같습니다.

• CUME_DIST – 파티션 내에서 현재 행의 상대적 순위를 계산합니다.
• NTILE – 각 창 파티션의 행을 가능한 한 균등하게 나눕니다.
• PERCENT_RANK – 현재 행의 백분율 순위

또한 이 예에서 단일 쿼리에 여러 개의 Window 함수가 있을 수 있으며 파티션과 순서가 각각 다를 수 있음을 주목하십시오!

행 및 범위 및 프레임, 오 마이

OVER() 절 내에서 창 프레임을 추가로 정의하거나 제한하려면 ROWS 및 RANGE 를 사용할 수 있습니다. ROWS 절을 사용하여 파티션에 포함된 행을 현재 행의 이전 또는 이후로 지정할 수 있습니다.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

이 예에서 창 프레임은 첫 번째 행에서 현재 행에서 1을 뺀 값으로 이동하고 각 행에 대해 창 크기가 계속 증가합니다.

범위는 약간 다르게 작동하며 다른 결과를 얻을 수 있습니다.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

범위에는 현재 행과 동일한 ORDER BY 값이 있는 창 프레임의 행이 포함됩니다. 따라서 ORDER BY 가 고유하지 않은 경우 RANGE 로 중복을 얻을 수 있습니다.

일부는 ROWS 를 물리적 연산자로 설명하고 RANGE 는 논리적 연산자로 설명합니다. 어쨌든 ROWS 및 RANGE 의 기본값은 항상 UNBOUNDED PRECEDING AND CURRENT ROW 입니다.

다른 무엇?

대부분의 표준 집계 함수는 창 함수와 함께 작동합니다. 우리는 이미 예에서 COUNT 를 보았습니다. 기타에는 SUM , AVG , MIN , MAX 등이 있습니다.

창 기능을 사용하면 LAG 및 LEAD 및 FIRST_VALUE 및 LAST_VALUE 를 사용하여 이전 레코드와 후속 레코드에 모두 액세스할 수도 있습니다. 예를 들어, 각 행에 이번 달의 판매 수치와 지난 달 판매 수치의 차이를 표시하려고 한다고 가정해 보겠습니다. 다음과 같이 할 수 있습니다.

 SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

기본적으로 SQL 창 기능은 매우 강력합니다.

이것은 SQL 창 기능에 대한 간략한 소개이지만 이 기능이 수행할 수 있는 모든 것을 보는 데 관심을 불러일으킬 수 있기를 바랍니다. 우리는 창 함수가 집계 함수가 수행하는 방식과 유사한 계산을 수행하지만 개별 행 내의 데이터에 액세스할 수 있다는 이점이 추가되어 매우 강력하다는 것을 배웠습니다. 항상 OVER 절을 포함하고 PARTITION BY , ORDER BY 및 집계 호스트( SUM , COUNT 등) 및 기타 위치 함수( LEAD , LAG )를 포함할 수 있습니다. 또한 윈도우 프레임과 데이터 섹션을 캡슐화하는 방법에 대해서도 배웠습니다.

SQL의 다른 특징은 창 기능을 다르게 구현할 수 있으며 일부는 모든 창 기능이나 절을 구현하지 않았을 수 있습니다. 사용 중인 플랫폼에 대한 설명서를 확인하십시오.

SQL 개발자로서 SQL 데이터베이스 성능 튜닝에 관심이 있는 경우 개발자를 위한 SQL 데이터베이스 성능 튜닝 을 확인하십시오.

즐거운 창구여!

특정 구현에 대한 자세한 내용은 다음을 참조하세요.

• PostgreSQL 구현에 대한 PostgreSQL의 Window Functions 문서.
• SELECT - OVER 절(Transact-SQL) Microsoft의 문서.
• SQL Server 구현 및 2부에 대한 훌륭한 개요를 보려면 SQL Server의 Window Functions를 참조 하세요.