SQLShack (한국어)

No comments

이 기사에서는 SQL PARTITION BY 절을 다룹니다. 특히 select 문에서 GROUP BY와의 차이점입니다. 또한 SQL PARTITION BY의 다양한 사용 사례를 살펴볼 것입니다.

SQL PARTITION BY를 사용하여 결과 집합을 파티션으로 나누고 파티션 된 데이터의 각 하위 집합에 대해 계산을 수행합니다.

샘플 준비 데이터

샘플 데이터베이스 SQLShackDemo에 Orders 테이블을 만들고 추가 쿼리를 작성하기 위해 레코드를 삽입하겠습니다.

저는 ApexSQL Generate를 사용하여이 기사에 샘플 데이터를 삽입합니다. Orders 테이블을 마우스 오른쪽 버튼으로 클릭하고 테스트 데이터를 생성합니다.

ApexSQL 생성을 시작합니다. Orders 테이블에 데이터를 삽입하는 스크립트를 생성했습니다. 이 스크립트를 실행하여 Orders 테이블에 100 개의 레코드를 삽입합니다.

insert 문을 실행하면 다음 이미지의 Orders 테이블에서 데이터를 볼 수 있습니다.

SQL GROUP BY 절을 사용하여 지정된 열별로 결과를 그룹화하고 Avg (), Min (), Max ()와 같은 집계 함수를 사용하여 필요한 값을 계산합니다.

Group By 함수 구문

1
2
3
4

SELECT 표현식, 집계 함수 ()
FROM 테이블
WHERE 조건
GROUP BY 표현식

Orders 테이블에서 다음 값을 찾으려고한다고 가정합니다.

  • 도시의 최소 주문 값
  • 도시의 최대 주문 값
  • 도시의 평균 주문 값

GROUP BY 절로 다음 쿼리를 실행하여 이러한 값을 계산합니다.

1
2
3
4
5
6

SELECT Customercity,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM ( Orderamount) TotalOrderAmount
FROM.
GROUP BY Customercity;

다음 스크린 샷에서 평균, 최소 및 CustomerCity별로 그룹화 된 최대 값입니다.

이제 출력에 CustomerName 및 OrderAmount 열도 추가하려고합니다. select 문에 이러한 열을 추가하고 다음 코드를 실행하겠습니다.

1
2
3
4
5
6

SELECT Customercity, CustomerName, OrderAmount,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM (Orderamount) TotalOrderAmount
FROM.
GROUP BY Customercity;

이 쿼리를 실행하면 오류 메시지가 표시됩니다. . SQL GROUP BY 절에서 Group by 절에서도 사용되는 경우 select 문에서 열을 사용할 수 있습니다. GROUP BY 절의 일부가 아닌 select 절의 열은 허용되지 않습니다.

SQL을 사용할 수 있습니다. 이 문제를 해결하려면 PARTITION BY 절을 사용하십시오. 다음 섹션에서 자세히 살펴 보겠습니다.

SQL PARTITION BY

OVER 절과 함께 SQL PARTITION BY 절을 사용하여 집계를 수행해야하는 열을 지정할 수 있습니다. . 이전 예에서는 CustomerCity 열과 함께 Group By를 사용하고 평균, 최소 및 최대 값을 계산했습니다.

다음 쿼리를 사용하여 SQL PARTITION BY 절로이 시나리오를 다시 실행 해 보겠습니다.

1
2
3
4
5

SELECT Customercity,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;

출력에서 다음과 유사한 집계 값을 얻습니다. GROUP By 절. SQL PARTITION BY 및 GROUP BY 절 출력의 출력에 차이가 있음을 알 수 있습니다.

그룹화 기준

SQL PARTITION BY

Group By 절을 사용하여 제한된 수의 레코드를 얻습니다. / p>

PARTITION BY 절을 사용하여 테이블의 모든 레코드를 가져옵니다.

하나를 제공합니다. 결과 집합의 그룹당 행. 예를 들어, GROUP BY 절에서 CustomerCity의 각 그룹에 대한 결과를 얻습니다.

지정된 테이블의 각 레코드와 함께 집계 된 열을 제공합니다.

Orders 테이블에는 15 개의 레코드가 있습니다. SQL PARTITION BY의 쿼리 출력에서 Min, Max 및 평균 값과 함께 15 개의 행을 얻습니다.

이전 예에서 GROUP BY 절의 일부가 아닌 열을 추가하려고하면 오류 메시지가 표시됩니다.

SQL PARTITION BY 절을 사용하여 select 문에 필수 열을 추가 할 수 있습니다. . CustomerName 및 OrderAmount 열을 추가하고 다음 쿼리를 실행하겠습니다.

1
2
3
4
5
6
7

SELECT Customercity,
CustomerName,
OrderAmount,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount ,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;

우리는 다음의 출력과 함께 CustomerName 및 OrderAmount 열을 얻습니다. 집계 된 함수. 또한 Orders 테이블에서 사용 가능한 모든 행을 가져옵니다.

다음 스크린 샷에서 CustomerCity Chicago에 대해 다음을 수행 할 수 있습니다. 집계 (Avg, Min 및 Max)를 수행하고 각 열에 값을 제공합니다.

마찬가지로, 다음과 같은 다른 집계 함수를 사용할 수 있습니다. SQL PARTITION BY 절을 사용하여 특정 도시의 총 주문 수를 알아 내기 위해 계산합니다.

1
2
3
4
5
6
7
8

SELECT Customercity,
CustomerName,
OrderAmount,
COUNT (OrderID) OVER (PARTITION BY Customercity) AS CountOfOrders,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITIO N BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;

특정 도시에 대한 주문 수를 볼 수 있습니다. 예를 들어, 오스틴시에서 두 개의 주문이 있습니다. CountofOrders 열에 값 2가 표시됩니다.

ROW_NUMBER ()가있는 PARTITION BY 절

ROW_NUMBER () 함수와 함께 SQL PARTITION BY 절을 사용하여 각 행의 행 번호를 갖습니다. SQL PARTITION BY 절과 함께 ROW_NUMBER를 사용하도록 다음 매개 변수를 정의합니다.

  • PARTITION BY 열 –이 예에서는 CustomerCity 열의 데이터를 분할하려고합니다.
  • Order By : ORDER BY 열에서 행 번호를 정의하는 열 또는 조건을 정의합니다. 이 예에서는 OrderAmount 열의 데이터를 정렬하려고합니다.
1
2
3
4
5
6
7
8
9
10

SELECT Customercity,
CustomerName,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS “Row Number”,
OrderAmount,
COUNT (OrderID) OVER (PARTITION BY Customercity) AS CountOfOrders,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;

다음 스크린 샷에서 CustomerCity Chicago를 볼 수 있습니다. , 가장 높은 금액이 7577.90 인 주문에 대한 행 번호 1이 있습니다. 내림차순 OrderAmount로 행 번호를 제공합니다.

누적 합계 값이있는 PARTITION BY 절

우리가 파티션의 주문에 대한 누적 합계를 얻고 싶습니다.누적 합계는 현재 행과 파티션의 다음 행이어야합니다.

예를 들어, 시카고시에서 4 개의 주문이 있습니다.

CustomerCity

CustomerName

순위

OrderAmount

누적 총 행

누적 합계

시카고

Marvin

랭크 1 +2

시카고

로렌스

2 + 3 등급

시카고

Alex

3 + 4 등급

시카고

제롬

순위 4

다음 쿼리에서 지정하는 ROWS 절은 현재 행 (CURRENT ROW 사용)과 다음 행 (1 FOLLOWING 사용)을 선택합니다. 또한 CustomerCity (OVER (PARTITION BY Customercity ORDER BY OrderAmount DESC 사용)에 파티션이있는 sum (Orderamount)를 사용하여 해당 행의 합계를 계산합니다.

1
2
3
4
5
6
7

SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS “Row Number”,
CONVERT (VARCHAR (20), SUM (orderamount) OVER (고객 도시 별 분할
현재 행과 다음 1 행 사이의 OrderAmount DESC 행별 주문), 1) 누적 합계로,

마찬가지로 다음을 사용하여 누적 평균을 계산할 수 있습니다. 다음 쿼리 SQL PARTITION BY 절을 사용합니다.

1
2
3
4
5
6
7

SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS “Row Number”,
CONVERT (VARCHAR (20), AVG (orderamount) OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC ROWS between Current ROW AND 1 FOLLOWING) , 1) AS CumulativeAVG

PARTITION BY 절이있는 UNBOUNDED PRECEDING 행

SQL PARTITION BY 절과 함께 ROWS UNBOUNDED PRECEDING을 사용하여 파티션의 행을 선택할 수 있습니다. 현재 행과 가장 높은 값 r 앞 현재 행 이후.

다음 표에서 행 1을 볼 수 있습니다. 이 파티션에 높은 값을 가진 행이 없습니다. 따라서 누적 평균 값은 1 행 OrderAmount와 동일합니다.

Row2의 경우 현재 행 값 (7199.61)과 최고 값 행 1 (7577.9)을 찾습니다. 이 두 금액의 평균을 계산합니다.

행 3의 경우 현재 값 (6847.66)과이 값 (7199.61 및 7577.90)보다 높은 금액 값을 찾습니다. 이들의 평균을 계산하고 반환합니다.

CustomerCity

CustomerName

순위

OrderAmount

누적 평균 행

누적 평균

시카고

Marvin

랭크 1

시카고

로렌스

순위 1 + 2

시카고

Alex

랭크 1 + 2 + 3

시카고

제롬

순위 1 + 2 + 3 + 4

다음 쿼리를 실행하여 샘플 데이터로이 결과를 얻습니다.

1
2
3
4
5
6
7
8

SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS “Row Number”,
CONVERT (VARCHAR (20), AVG (orderamount) OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC ROWS UNBOUNDED PRECEDING), 1) AS CumulativeAvg
FROM.;

결론

이 기사에서는 SQL PARTIION BY 절과 GROUP BY 절과의 비교를 살펴 보았습니다. 또한 몇 가지 예를 통해 사용법을 배웠습니다. 이 기사가 도움이 되셨기를 바라며 아래 의견에 질문이 있으시면 언제든지 문의하십시오.

  • 작성자
  • 최근 게시물
인도 구르 가온에서 MCSA 인증 및 Microsoft 인증 트레이너로서 13 년의 경력을 보유한 Rajendra는 성능 최적화, 모니터링, 고 가용성에 중점을 둔 다양한 대기업에서 근무합니다. , 재해 복구 전략 및 구현. 그는 SQL Server, Azure, MySQL, Linux, Power BI, 성능 조정, AWS / Amazon RDS, Git 및 현재까지 1,000 만 명이 넘는 독자가 시청 한 관련 기술에 대한 수백 개의 권위있는 기사의 저자입니다.
그는 SQL Server Always On 가용성 그룹에 대한 50 부작 시리즈를 통해 단일 주제에 대한 가장 큰 무료 온라인 기사 모음 중 하나를 작성했습니다. SQL Server 커뮤니티에 대한 공헌을 바탕으로 2020 년과 2021 년 SQLShack에서 지속적으로 권위있는 “올해의 최우수 저자”를 비롯한 다양한 상을 수상했습니다.
Raj는 항상 새로운 도전에 관심이 있으므로 컨설팅이 필요한 경우 그의 글에서 다루는 주제에 대한 도움은 [email protected]으로 연락 할 수 있습니다.
Rajendra Gupta의 모든 게시물보기

Rajendra Gupta의 최신 게시물 (전체보기)
  • SQL Server Always On 가용성의 세션 시간 초과 그룹-2021 년 2 월 8 일
  • AWS RDS SQL Server에 대한 마이너 및 메이저 버전 업그레이드 수행-2021 년 1 월 29 일
  • AWS RDS PostgreSQL 인스턴스 배포-2021 년 1 월 27 일

답글 남기기

이메일 주소를 발행하지 않을 것입니다. 필수 항목은 *(으)로 표시합니다