이 튜토리얼에서는 SQL에서 PARTITION BY 절의 작동 방식을 배우고 이를 사용하여 보다 세부적인 하위 집합을 위해 데이터를 분할하는 방법을 알아봅니다.
통사론:
PARTITION BY 절의 구문부터 시작해 보겠습니다. 구문은 사용하는 상황에 따라 달라질 수 있지만 일반적인 구문은 다음과 같습니다.
열1, 열2, ...을 선택하세요.
OVER(PARTITION BY partition_column1, partition_column2, ...)
FROM table_name
주어진 구문은 다음 요소를 나타냅니다.
- 열1, 열2 - 결과 집합에 포함하려는 열을 나타냅니다.
- PARTITION BY 열 – 이 절은 데이터를 분할하거나 그룹화하는 방법을 정의합니다.
샘플 데이터
PARTITION BY 절을 사용하는 방법을 보여주기 위해 샘플 데이터로 기본 테이블을 만들어 보겠습니다. 이번 예에서는 제품 정보를 저장하는 기본 테이블을 만들어 보겠습니다.
CREATE TABLE 제품(
product_id INT PRIMARY KEY AUTO_INCREMENT,
제품_이름 VARCHAR( 255 ),
카테고리 VARCHAR( 255 ),
가격 십진수( 10 , 2 ),
수량 INT,
만료_날짜 DATE,
바코드 BIGINT
);
끼워 넣다
~ 안으로
제품(제품_이름,
범주,
가격,
수량,
만료 날짜,
바코드)
값( '셰프모자 25cm' ,
'빵집' ,
24.67 ,
57 ,
'2023-09-09' ,
2854509564204 );
끼워 넣다
~ 안으로
제품(제품_이름,
범주,
가격,
수량,
만료 날짜,
바코드)
값( '메추리알 - 통조림' ,
'식료품 저장실' ,
17.99 ,
67 ,
'2023-09-29' ,
1708039594250 );
끼워 넣다
~ 안으로
제품(제품_이름,
범주,
가격,
수량,
만료 날짜,
바코드)
값( '커피 - 에그노그 카푸치노' ,
'빵집' ,
92.53 ,
10 ,
'2023-09-22' ,
8704051853058 );
끼워 넣다
~ 안으로
제품(제품_이름,
범주,
가격,
수량,
만료 날짜,
바코드)
값( '배 - 가시투성이' ,
'빵집' ,
65.29 ,
48 ,
'2023-08-23' ,
5174927442238 );
끼워 넣다
~ 안으로
제품(제품_이름,
범주,
가격,
수량,
만료 날짜,
바코드)
값( '파스타 - 엔젤헤어' ,
'식료품 저장실' ,
48.38 ,
59 ,
'2023-08-05' ,
8008123704782 );
끼워 넣다
~ 안으로
제품(제품_이름,
범주,
가격,
수량,
만료 날짜,
바코드)
값( '와인 - 프로세코 발도비아데네' ,
'생산하다' ,
44.18 ,
삼 ,
'2023-03-13' ,
6470981735653 );
끼워 넣다
~ 안으로
제품(제품_이름,
범주,
가격,
수량,
만료 날짜,
바코드)
값( '페스트리 - 프렌치 미니 모듬' ,
'식료품 저장실' ,
36.73 ,
52 ,
'2023-05-29' ,
5963886298051 );
끼워 넣다
~ 안으로
제품(제품_이름,
범주,
가격,
수량,
만료 날짜,
바코드)
값( '오렌지 - 통조림, 만다린' ,
'생산하다' ,
65.0 ,
1 ,
'2023-04-20' ,
6131761721332 );
끼워 넣다
~ 안으로
제품(제품_이름,
범주,
가격,
수량,
만료 날짜,
바코드)
값( '돼지 어깨 살' ,
'생산하다' ,
55.55 ,
73 ,
'2023-05-01' ,
9343592107125 );
끼워 넣다
~ 안으로
제품(제품_이름,
범주,
가격,
수량,
만료 날짜,
바코드)
값( 'Dc Hikiage Hira Huba' ,
'생산하다' ,
56.29 ,
53 ,
'2023-04-14' ,
3354910667072 );
샘플 데이터 설정이 완료되면 계속 진행하여 PARTITION BY 절을 사용할 수 있습니다.
기본 사용법
이전 테이블의 각 제품 범주에 대한 총 항목을 계산한다고 가정합니다. PARTITION BY를 사용하여 항목을 고유한 카테고리로 나눈 다음 각 카테고리의 총 수량을 결정할 수 있습니다.
예는 다음과 같습니다.
선택하다상품명,
범주,
수량,
SUM(수량) OVER (PARTITION BY 카테고리) AS total_items
에서
제품;
주어진 예에서는 '범주' 열을 사용하여 데이터를 분할합니다. 그런 다음 SUM() 집계 함수를 사용하여 각 카테고리의 총 항목을 개별적으로 결정합니다. 결과에는 각 카테고리의 총 항목이 표시됩니다.
PARTITION BY 절 사용
요약하면 PARTITION BY 절의 가장 일반적인 사용 사례는 창 기능과 결합되는 것입니다. 윈도우 기능은 각 파티션에 별도로 적용됩니다.
PARTITION BY와 함께 사용하는 일반적인 창 기능 중 일부는 다음과 같습니다.
- SUM() – 각 파티션 내의 열 합계를 계산합니다.
- AVG() – 각 파티션 내 열의 평균을 계산합니다.
- COUNT() – 각 파티션 내의 행 수를 계산합니다.
- ROW_NUMBER() – 각 파티션 내의 각 행에 고유한 행 번호를 할당합니다.
- RANK() – 각 파티션 내의 각 행에 순위를 할당합니다.
- DENSE_RANK() – 각 파티션 내의 각 행에 밀집 순위를 할당합니다.
- NTILE() – 데이터를 각 파티션 내의 분위수로 나눕니다.
그게 다야!
결론
이 자습서에서는 SQL의 PARTITION BY 절을 사용하여 데이터를 다양한 세그먼트로 분할한 다음 각 결과 파티션에 개별적으로 특정 작업을 적용하는 방법을 배웠습니다.