SQL 창 함수: 함수 사용에 대해 알아야 할 모든 것

DBMS 쿼리 언어로서의 SQL의 다양성은 수년에 걸쳐 증가했습니다. 광범위한 유틸리티와 다재다능함 덕분에 모든 데이터 분석가가 가장 좋아하는 제품입니다.

SQL의 일반 기능 외에도 고급 수준의 기능이 상당히 많이 있습니다. 이러한 함수는 일반적으로 창 함수로 알려져 있습니다. 복잡한 데이터를 처리하고 고급 계산을 수행하려는 경우 이를 사용하여 데이터를 최대한 활용할 수 있습니다.

창 기능의 중요성

SQL에서 여러 창 함수를 사용할 수 있으며 각각 일련의 계산을 수행하는 데 도움이 됩니다. 파티션 생성에서 행 순위 지정 또는 행 번호 할당에 이르기까지 이러한 창 함수는 모든 작업을 조금씩 수행합니다.

창 함수는 특정 데이터 세트 또는 행 모음에 대해 집계 함수를 적용할 때 유용합니다. 이러한 기능은 GROUP BY가 제공하는 집계 기능 이상입니다. 그러나 주요 차이점은 그룹화 기능과 달리 데이터가 단일 행으로 결합되지 않는다는 것입니다.

WHERE , FROM 및 GROUP BY 문 내에서 창 함수를 사용할 수 없습니다 .

창 함수의 구문

창 함수를 참조할 때 제대로 작동하려면 기본 구문 구조를 따라야 합니다. 명령을 잘못 구성하면 오류가 발생하고 코드가 실행되지 않습니다.

기본 구문은 다음과 같습니다.

SELECT columnname1,
{window_function}(columnname2)
OVER([PARTITION BY columnname1] [ORDER BY columnname3]) AS new_column
FROM table_name;

어디에:

• coulmnname1 은 선택하려는 첫 번째 열 이름입니다.
• {window_function} 은 sum, avg, count, row_number, rank 또는 dense_rank와 같은 집계 함수의 이름입니다.
• columnname2 는 창 함수를 적용할 열의 이름입니다.
• columnname3 은 파티션의 기반이 되는 세 번째 열 이름입니다.
• new_column 은 AS 키워드 를 사용하여 적용할 수 있는 새 열의 레이블입니다 .
• table_name 은 소스 테이블의 이름입니다.

창 함수는 가장 기본적인 SQL 명령과 다릅니다. SQL의 집계 함수와 달리 이러한 창 함수를 사용하여 고급 함수를 수행할 수 있습니다.

데이터 세트 준비

CREATE TABLE 문을 사용하여 SQL에서 새 테이블을 만들 수 있습니다 . 다음은 이 가이드에서 일부 창 함수를 정의하는 데 사용할 샘플 데이터 세트입니다.

합계 함수 설명

범주 열 내의 각 값에 대한 총 판매액을 계산한다고 가정합니다. 방법은 다음과 같습니다.

SELECT category, color,
sum(sale_price)
OVER (order by category) AS total_sales
FROM sahil.sample;

위의 코드에서 SQL 문은 원본 데이터 세트에서 범주와 색상을 가져옵니다. sum 함수는 sale_price 열을 더합니다. OVER 절이 범주 열별로 정렬을 지정하므로 범주별로 수행합니다. 최종 결과는 다음과 같습니다.

Avg() 창 함수를 사용하는 방법

sum 함수와 마찬가지로 avg 함수 를 사용하여 데이터 행당 평균을 계산할 수 있습니다 . 합계 대신 평균 판매액이 포함된 열이 표시됩니다.

SELECT category, color,
avg(sale_price)
OVER (order by category) AS avg_sales
FROM sahil.sample;

Count() 창 함수 사용 방법 배우기

sum 및 avg 함수와 유사하게 SQL의 카운트 창 함수는 매우 간단하며 다른 두 함수와 동일한 라인을 따라 작동합니다. count 함수를 전달하면 새 열 내 각 값의 총 개수를 얻습니다.

카운트를 계산하는 방법은 다음과 같습니다.

SELECT category, color,
count(category)
OVER (order by category) AS item_count
FROM sahil.sample;

Row_Number() 창 함수

위에 나열된 다른 창 함수와 달리 row_number()는 약간 다르게 작동합니다. row_number() 함수는 order by 절에 따라 각 행에 행 번호를 할당합니다. 시작 행 번호는 1입니다. row_number는 끝까지 각 행에 해당 값을 할당합니다.

다음은 row_number() 함수의 기본 구조입니다.

SELECT category, color,
row_number()
OVER (order by category) AS item_number
FROM sahil.sample;

그러나 각 범주 항목에 별도의 행 번호를 할당하려는 경우에는 어떻게 됩니까? 위의 구문은 범주 내에 저장된 항목에 관계없이 롤링 일련 번호를 설정합니다. 예를 들어, 가전제품 범주에는 고유한 번호가 있어야 하고 그 뒤에 바인더 등이 와야 합니다.

파티션 기능을 사용하여 이 간단하면서도 실용적인 작업을 수행할 수 있습니다 . partition 키워드는 각 범주 항목을 기준으로 지정된 행 번호를 지정합니다.

SELECT category, color,
row_number()
OVER (partition by category order by category) AS item_number
FROM sahil.sample;

Rank() 및 Dense_Rank() 함수

rank() 함수 는 row_number() 함수 와 다르게 작동 합니다. 순위 값을 정의하기 위한 기준으로 사용하려면 order by 함수 내에서 열 이름을 지정해야 합니다. 예를 들어 다음 코드 예제에서는 order by 함수 내에서 색상 열을 사용할 수 있습니다. 그런 다음 쿼리는 해당 순서를 사용하여 각 행에 순위 값을 할당합니다.

아래 코드 구문을 사용하여 SQL에서 순위 함수를 전달할 수 있습니다.

SELECT category, color,
rank()
OVER (order by color) AS item_rank
FROM sahil.sample;

이 기능이 어떻게 작동하는지 이해하려면 출력을 살펴보십시오.

order by 함수는 색상 범주를 정렬하는 반면 rank 함수는 각 색상에 순위를 지정합니다. 그러나 동일한 색상 값은 모두 동일한 순위를 갖지만 다른 색상은 별도의 순위를 갖습니다. 검은색은 데이터 세트 내에서 세 번 나타납니다. 1, 2, 3의 순위 값을 할당하는 대신 검은색 항목은 1의 순위를 얻습니다.

그러나 다음 색상인 Brown은 순위 2가 아닌 순위 4를 얻습니다. 순위 함수는 값을 건너뛰고 다음 연대순 값을 다른 항목에 할당합니다. 보다 의미 있는 순위 값을 할당하려면 dense_rank() 함수를 사용할 수 있습니다.

dense_rank 함수는 order by 함수 중에 순위 값을 건너뛰지 않습니다. 예를 들어, 처음 세 가지 색상 항목(검은색)은 순위 1을 갖습니다. 그러나 다음 색상(갈색)은 순위 4가 아니라 번호 매기기 목록에서 다음 연대순 번호인 순위 2를 갖습니다. dense_rank 함수는 항목 목록에 의미 있는 값을 할당하므로 보다 실용적인 창 함수입니다.

SQL에서 dense_rank 함수를 사용하는 방법은 다음과 같습니다.

SELECT category, color,
dense_rank()
OVER (order by color) AS item_rank
FROM sahil.sample;

다음은 이 함수의 출력이 어떻게 보이는지에 대한 예입니다.

구조를 위한 SQL 함수

SQL의 창 함수는 고급 분석 작업을 수행하는 데 이상적입니다. 그러나 다른 많은 SQL 명령을 사용하여 최고의 계산 기술을 확보할 수 있습니다. 여러 결과를 한 번에 결합하고 계산할 때 SQL의 하위 쿼리를 사용하는 것보다 더 좋은 것은 없습니다.

하위 쿼리는 고급 기능을 수행하여 결과의 ​​품질을 향상시키는 훌륭한 도구입니다. 시간의 필요에 따라 쿼리를 사용자 지정하고 요구 사항에 맞게 보다 효과적으로 쿼리를 만들 수 있습니다.