SQL 창 함수 및 기능 이해를 위한 초보자 가이드

채용 과정 전반에 걸쳐 저는 우리 팀의 재능 있는 많은 후보자들을 만나는 즐거움을 누렸습니다. 우리 업무에는 복잡한 데이터 세트를 처리하는 일이 포함되기 때문에 각 후보자가 현명한 솔루션을 찾는 능력을 측정하는 것이 중요했습니다. 나는 그들의 숙련도를 평가하기 위해 SQL 의 창 기능에 대한 경험에 대해 물었습니다. 대부분의 사람들이 이러한 기능에 대해 알고 있었지만 효과적으로 사용할 수 있는 사람은 거의 없었습니다.

창 기능이 등장한 지 거의 20년이 되었음에도 불구하고 많은 SQL 개발자는 여전히 이를 이해하기 어렵다고 생각합니다. 숙련된 개발자라도 StackOverflow가 수행하는 작업을 실제로 이해하지 못한 채 코드를 복사하여 붙여넣는 것은 드문 일이 아닙니다. 이 글은 도움을 드리기 위해 준비되었습니다! 이해하기 쉬운 방식으로 창 기능을 설명하고 실제 세계에서 어떻게 작동하는지 보여주는 예를 제공하겠습니다.

창 기능에 대해 들어보셨나요? 많은 문제를 해결할 수 있는 훌륭한 분석 도구입니다. 예를 들어 클라이언트 ID와 같은 공통 속성을 공유하는 행 집합을 계산해야 한다고 가정해 보겠습니다. 이것이 바로 창 기능이 유용한 곳입니다! 집계 함수처럼 작동하지만 함께 그룹화하는 대신 각 행의 고유성을 유지할 수 있습니다. 또한 창 기능의 결과는 출력 선택 시 추가 필드로 표시됩니다. 이는 분석 보고서를 작성하거나, 이동 평균 및 누계를 계산하거나, 다양한 기여 모델을 알아낼 때 매우 유용합니다.

SQL 및 창 함수의 세계에 오신 것을 환영합니다! 이제 막 시작했다면 잘 찾아오셨습니다. 이 글은 초보자에게 친숙하며 명확한 설명이 있고 복잡한 용어나 고급 개념이 없습니다. 해당 주제를 완전히 처음 접하는 경우에도 쉽게 따라갈 수 있습니다.

콘텐츠 개요

• 윈도우 함수와 함께 사용되는 함수 유형
  • 집계 함수
  • 순위 기능
  • 가치 함수
• 집계 창 함수
• 주요 시사점

윈도우 함수와 함께 사용되는 함수 유형

행 집합(소위 창)에 대해 창 함수를 적용할 수 있는 세 가지 주요 함수 유형이 있습니다. 이는 집계 함수, 순위 함수, 값 함수입니다. 아래 이미지에서 각 카테고리에 속하는 다양한 기능의 이름을 볼 수 있습니다.

집계 함수

이는 데이터 그룹에 대해 수학적 연산을 수행하여 단일 누적 값을 생성합니다. 이는 평균, 총 행 수, 최대값 또는 최소값, 각 창이나 파티션 내의 총 합계를 비롯한 다양한 집계를 계산하는 데 사용됩니다.

• SUM: 해당 열의 모든 값을 더합니다.

• COUNT: NULL 값을 제외한 열의 값 개수를 계산합니다.

• AVG: 열의 평균값을 찾습니다.

• MAX: 열에서 가장 높은 값을 식별합니다.

• MIN: 열에서 가장 낮은 값을 식별합니다.

  
  

순위 기능

이는 파티션의 각 행에 순위 또는 순서를 지정하는 데 사용됩니다. 이는 일련 번호를 할당하거나 특정 값을 기준으로 순위를 매기는 등 특정 기준을 평가하여 수행됩니다.

• ROW_NUMBER: 파티션의 각 새 레코드에 순차적 순위 번호를 할당합니다.
• RANK: 결과 집합의 각 행에 대한 순위를 지정합니다. 이 경우 시스템이 동일한 값을 감지하면 동일한 순위를 할당하고 다음 값을 건너뜁니다.
• DENSE_RANK: 결과 집합의 파티션 내 각 행에 순위를 할당합니다. RANK 함수와 달리 이 함수는 후속 값을 건너뛰지 않고 동일한 값에 대한 순위를 반환합니다.
• NTILE: 현재 행이 어느 그룹에 속하는지 결정할 수 있습니다. 그룹 수는 괄호 안에 표시됩니다.
  

가치 함수

이를 통해 그룹에 있는 여러 행 간의 값을 쉽게 비교할 수 있으며 해당 그룹의 첫 번째 또는 마지막 값과 값을 비교할 수도 있습니다. 즉, 창의 여러 행 사이를 쉽게 이동하고 창의 시작이나 끝에서 값을 확인할 수 있습니다.

• LAG 또는 LEAD: 자체 조인 작업을 수행할 필요 없이 이전 또는 후속 행의 데이터에 액세스합니다. 이러한 함수는 시간 경과에 따른 차이 계산과 같이 동일한 결과 집합 또는 파티션 내의 행을 다른 행과 비교해야 하는 문제를 해결할 때 특히 유용합니다.
• FIRST_VALUE 또는 LAST_VALUE: 정의된 창 또는 파티션에서 첫 번째 또는 마지막 값을 검색합니다. 이러한 함수는 특정 기간 내의 차이를 계산하려는 경우 특히 유용합니다.

창 함수를 시작하기 위해 가상의 '급여' 테이블을 만들고 데이터로 채워보겠습니다.

테이블 생성:

 create table salary ( employee_id smallint, employee_name varchar(10), department varchar(20), city varchar(20), salary real )

테이블 채우기:

 insert into salary values ( 1 ,'Tony' ,'R&D', 'New York', 3000); insert into salary values ( 2 ,'James' ,'Project management', 'London', 4000); insert into salary values ( 3 ,'Dina' ,'Engineering', 'Tokyo', 5000); insert into salary values ( 4 ,'Annie' ,'Security', 'London', 3000); insert into salary values ( 5 ,'Tom' ,'R&D', 'New York', 3500); insert into salary values ( 6 ,'Stan' ,'Project management', 'New York', 4200); insert into salary values ( 7 ,'Jessa' ,'Sales', 'London', 5300); insert into salary values ( 8 ,'Ronnie' ,'R&D', 'Tokyo', 2900); insert into salary values ( 9 ,'Andrew' ,'Engineering', 'New York', 1500); insert into salary values (10,'Dean' ,'Sales', 'Tokyo', 3700)

'급여' 테이블이 성공적으로 채워졌는지 확인해 보겠습니다.

 select * from salary 

다음 쿼리는 테이블에 있는 직원의 이름과 급여를 표시합니다.

 select employee_name, salary from salary 

급여 합계, 평균 급여, 최대값, 최소값 및 행 수를 계산하는 것은 집계 함수의 일반적인 사용 사례입니다.

집계함수를 적용하면 급여가 집계되어 한 줄에 표시됩니다.

하지만 'salary' 테이블에 있는 직원의 이름과 급여를 표시하고 세 번째 열에 모든 급여의 합계를 표시하려면 어떻게 해야 할까요? 이 값은 모든 행에서 동일해야 합니다.

윈도우 기능을 활용해볼 수 있는 절호의 기회!

 select employee_name, salary, sum(salary) over() as sum_salary from salary 

sum(salary) over() 의 각 행에서 급여의 합을 계산하는 윈도우 함수를 자세히 살펴보겠습니다.

over() 표현식은 함수가 작동하는 창 또는 행 집합을 정의합니다. 이 예에서 창은 전체 테이블입니다. 즉, 함수가 모든 행에 적용된다는 의미입니다.

over() 표현식은 over() 이전에 요청된 함수와 쌍을 이룰 때만 작동합니다.

예를 들어 sum(salary) over() . 여기서 sum() 집계 함수입니다. 그리고 전체 표현식 sum(salary) over() 집계 창 함수입니다.

앞서 말했듯이 윈도우 함수가 적용되는 모든 함수는 집계 함수, 순위 함수, 값 함수의 세 가지 그룹으로 나눌 수 있습니다.

집계 함수 sum() , count() , avg() , min() , max() over() 표현식과 함께 집계 창 함수 그룹을 만듭니다.

이 기사에서는 이러한 특정 유형의 창 기능에 중점을 둘 것입니다.

집계 창 함수

예제로 돌아갑니다!

직원의 이름을 요청합시다. 급여; 모든 급여의 합계; 평균, 최대 및 최소 급여; 직원 수.

 select employee_name, salary, sum(salary) over(), avg(salary) over(), max(salary) over(), min(salary) over(), count(*) over() from salary 

이제 창 기능이 무엇인지 명확해졌으므로 작업에 유용할 수 있는 몇 가지 사례를 살펴보겠습니다.

 select employee_name, salary, sum(salary)over(), salary/sum(salary)over() as share from salary order by salary/sum(salary)over() desc 

네 번째 열에서는 각 급여에 대한 총 급여 예산의 비율을 계산했습니다. Jessa의 급여는 전체 급여 예산의 거의 15%에 달합니다.

또한 order by 뒤에 정렬 salary/sum(salary)over() 비율을 계산하는 수식을 배치했습니다. 창 기능은 출력 select 뿐만 아니라 정렬 order by 에서도 찾을 수 있습니다.

또 다른 예: 급여를 회사의 평균 급여와 비교해 보겠습니다.

 select employee_name, salary, avg(salary)over(), salary-avg(salary)over() as diff_salary from salary order by salary-avg(salary)over() 

보시다시피 Andrew의 급여는 평균보다 2110이 적고 Jessa의 급여는 평균보다 1690이 높습니다.

직원 이름, 부서, 급여라는 세 개의 열을 요청해 보겠습니다. 그리고 부서별로 분류해보겠습니다.

 select employee_name, department, salary from salary order by department 

이제 동일한 세 개의 열과 모든 직원의 급여 합계가 포함된 열을 요청하겠습니다. 여러분은 이미 창 기능을 사용하여 이 작업을 수행할 수 있다는 것을 알고 있습니다.

 select employee_name, department, salary, sum(salary)over() from salary order by department 

그러나 마지막 열에 표시된 것처럼 모든 급여의 합계가 아니라 각 부서의 급여 합계를 요청하려면 어떻게 해야 할까요?

over() 표현식에 매개변수 partition by 추가하여 이를 수행할 수 있습니다.

 select employee_name, department, salary, sum(salary)over(), sum(salary)over(partition by department) from salary order by department

Partition by 하면 모든 행(전체 창)이 아닌 열 섹션에 창 기능을 적용할 수 있습니다.

단순한 그룹화처럼 보이지 않나요? 각 부서의 급여 합계를 계산하려면 부서별로 그룹화하고(창 기능 속어의 섹션) 금액을 계산합니다.

 select department, sum(salary) from salary group by department 

본질적으로 그룹화와 partition by 의 차이점은 group by 별로 그룹당 하나의 행을 반환하는 반면, partition by 함수의 결과가 group by 사용한 집계 함수의 결과와 동일하더라도 모든 행에 집계를 제공한다는 것입니다. 그룹을 기반으로 기능합니다.

창 기능으로 돌아가 보겠습니다.

 select employee_name, department, salary, sum(salary)over(partition by department), salary/sum(salary)over(partition by department) as shape from salary order by department 

창 함수, 특히 매개 partition by 사용하면 부서 급여 합계에서 각 직원 급여의 몫을 계산할 수 있습니다. 또는 예를 들어 급여를 부서의 평균 급여와 비교합니다.

주요 시사점

요약하자면:

• 창 함수는 현재 행과 관련이 있는 일련의 행에 대해 계산을 수행합니다.

• 윈도우 함수가 적용되는 주요 함수 종류로는 집계함수, 순위함수, 값함수 등이 있으며,

• 윈도우 함수를 사용하려면 쿼리 결과 집합 내에서 윈도우(행 집합)를 정의하는 over() 절을 적용해야 합니다. 그런 다음 창 함수는 창의 각 행에 대한 값을 계산합니다.

• 집계를 수행하려는 열을 지정하려면 over() 절에 partition by 절을 추가해야 합니다. Partition by 은 그룹화와 다소 유사하지만 그룹당 하나의 행을 반환하는 대신 집계 함수가 적용된 모든 행을 반환합니다.

  
  

지금은 이것입니다! 다음 몇 편의 기사에서는 초보자에게 적합한 간단한 예제를 통해 고급 SQL 개념을 살펴볼 예정이니 계속 지켜봐 주시기 바랍니다!