이미지로 Freepik

SQL(Structured Query Language)은 데이터를 관리하고 조작하는 데 사용되는 프로그래밍 언어입니다. 그렇기 때문에 구조적이고 효율적인 방식으로 데이터베이스와 상호 작용하려면 SQL 쿼리가 매우 중요합니다.

SQL의 그룹화는 데이터를 구성하고 분석하는 강력한 도구 역할을 합니다. 복잡한 데이터 세트에서 의미 있는 통찰력과 요약을 추출하는 데 도움이 됩니다. 그룹화의 가장 좋은 사용 사례는 데이터 특성을 요약하고 이해하여 기업의 분석 및 보고 작업을 돕는 것입니다.

일반적으로 그룹 내 통계를 계산하기 위해 데이터 세트 레코드를 공통 데이터로 결합해야 하는 요구 사항이 많습니다. 이러한 인스턴스의 대부분은 일반적인 시나리오로 일반화될 수 있습니다. 그런 다음 유사한 종류의 요구 사항이 나타날 때마다 이러한 시나리오를 적용할 수 있습니다.

SQL의 GROUP BY 절은 다음과 같은 용도로 사용됩니다.

1. 일부 열의 데이터 그룹화
2. 그룹을 단일 행으로 축소
3. 그룹의 다른 열에 대해 집계 작업을 수행합니다.

그룹화 열 = 그룹화 열의 값은 그룹의 모든 행에 대해 동일해야 합니다.

집계 열 = 집계 열의 값은 일반적으로 합계, 최대값 등과 같이 함수가 적용되는 것과 다릅니다.

집계 열은 그룹화 열이 아니어야 합니다.

시나리오 1: 합계를 찾기 위한 그룹화

판매 테이블에 있는 모든 카테고리의 총 판매액을 계산한다고 가정해 보겠습니다.

따라서 카테고리별로 그룹화하고 모든 카테고리의 개별 매출을 집계하겠습니다.

select category, 
sum(amount) as sales
from sales
group by category;

그룹화 열 = 카테고리

집계 열 = 금액

집계 함수 = sum()

시나리오 2: 개수를 찾기 위한 그룹화

각 부서의 직원 수를 계산한다고 가정해 보겠습니다.

이 경우 부서별로 그룹화하여 모든 부서의 직원 수를 계산합니다.

select department, 
count(empid) as emp_count
from employees
group by department;

그룹화 열 = 부서

집계 열 = empid

집계 함수 = 개수

시나리오 3: 평균을 찾기 위한 그룹화

각 부서 직원의 평균 급여를 계산한다고 가정해 보겠습니다.

마찬가지로 다시 부서별로 그룹화하고 모든 부서 직원의 평균 급여를 별도로 계산합니다.

select department, 
avg(salary) as avg_salary
from employees
group by department;

그룹화 열 = 부서

집계 열 = 급여

집계 함수 = 평균

시나리오 4: 최대값/최소값을 찾기 위한 그룹화

각 부서 직원의 최고 급여를 계산한다고 가정해 보겠습니다.

부서를 그룹화하고 모든 부서의 최대 급여를 계산합니다.

select department, 
max(salary) as max_salary
from employees
group by department;

그룹화 열 = 부서

집계 열 = 급여

집계 함수 = 최대

시나리오 5: 중복 항목을 찾기 위한 그룹화

데이터베이스에서 중복되거나 동일한 고객 이름을 찾고 싶다고 가정해 보겠습니다.

고객 이름별로 그룹화하고 집계 함수로 개수를 사용합니다. 또한 집계 함수에 대한 절을 사용하여 1보다 큰 개수만 필터링합니다.

select name, 
count(*) AS duplicate_count
from customers
group by name
having count(*) > 1;

그룹화 열 = 이름

집계 열 = *

집계 함수 = 개수

갖는 = 집계 함수에 적용할 필터 조건

SQL의 PARTITION BY 절은 다음 용도로 사용됩니다.

1. 일부 열의 데이터 그룹화/분할
2. 개별 행은 유지되며 지원 하나로 합쳐진
3. 그룹/파티션의 다른 열에 대해 순위 지정 및 집계 작업을 수행합니다.

분할 열 = 데이터를 그룹화하는 열을 선택합니다. 파티션 열의 데이터는 각 그룹마다 동일해야 합니다. 지정하지 않으면 전체 테이블이 단일 파티션으로 간주됩니다.

정렬 열 = 분할 열을 기반으로 생성된 각 그룹을 사용하여 그룹의 행을 정렬/정렬합니다.

순위 함수 = 순위 함수 또는 집계 함수가 파티션의 행에 적용됩니다.

시나리오 6: 그룹에서 가장 높은 레코드를 찾기 위한 분할

모든 카테고리에서 어떤 책이 가장 많이 팔렸는지, 그리고 가장 많이 팔린 책의 매출액도 ​​계산하고 싶다고 가정해 보겠습니다.

이 경우 그룹화하면 모든 범주의 레코드가 단일 행으로 줄어들기 때문에 group by 절을 사용할 수 없습니다.

하지만 각 카테고리에서 어떤 책이 가장 많이 팔렸는지 확인하려면 카테고리와 함께 도서명, 금액 등의 기록 세부정보가 필요합니다.

select book_name, amount
row_number() over (partition by category order by amount) as sales_rank
from book_sales;

파티션 나누기 열 = 카테고리

주문 열 = 금액

순위 함수 = row_number()

이 쿼리는 book_sales 테이블의 모든 행을 제공하며 행은 모든 도서 범주에서 정렬되며 가장 많이 팔린 도서가 행 번호 1이 됩니다.

이제 각 카테고리에서 가장 많이 팔린 책을 얻으려면 행 번호 1 행만 필터링하면 됩니다.

select category, book_name, amount from (
select category, book_name, amount
row_number() over (partition by category order by amount) as sales_rank
from book_sales
) as book_ranked_sales
where sales_rank = 1;

위의 필터는 각 카테고리의 최고 판매 도서와 각 최고 판매 도서의 판매 금액만 제공합니다.

시나리오 7: 그룹의 누적 합계를 찾기 위한 분할

판매된 판매 누계(누적 합계)를 계산한다고 가정해 보겠습니다. 모든 제품에 대해 별도의 누적 합계가 필요합니다.

product_id로 파티션을 나누고 날짜별로 파티션을 정렬하겠습니다.

select product_id, date, amount,
sum(amount) over (partition by product_id order by date desc) as running_total
from sales_data;

파티션 나누기 열 = product_id

순서 열 = 날짜

순위 함수 = sum()

시나리오 8: 그룹 내 값 비교를 위한 분할

모든 직원의 급여를 해당 부서의 평균 급여와 비교하고 싶다고 가정해 보겠습니다.

따라서 부서별로 직원을 분할하고 각 부서의 평균 급여를 구하겠습니다.

직원의 개인 급여에서 평균을 더 쉽게 빼서 직원의 급여가 평균보다 높거나 낮은지 계산할 수 있습니다.

select employee_id, salary, department,
avg(salary) over (partition by department) as avg_dept_sal
from employees;

분할 열 = 부서

주문 열 = 주문 없음

순위 함수 = avg()

시나리오 9: 결과를 동일한 그룹으로 나누기 위한 분할

급여를 기준으로 직원을 4개의 동일한(또는 거의 동일한) 그룹으로 나누고 싶다고 가정해 보겠습니다.

따라서 각 직원 그룹의 숫자 ID를 갖는 또 다른 논리 열 Tile_id를 파생시킵니다.

그룹은 급여를 기준으로 생성됩니다. 첫 번째 타일 그룹은 급여가 가장 높은 그룹이 됩니다.

select employee_id, salary,
ntile(4) over (order by salary desc) as tile_id
from employees;

파티셔닝 열 = 파티션 없음 - 전체 테이블이 동일한 파티션에 있음

주문 열 = 급여

순위 함수 = ntile()

시나리오 10: 데이터의 분리 또는 격차를 식별하기 위한 분할

순차적인 product_id 열이 있고 여기서 차이를 식별하고 싶다고 가정해 보겠습니다.

따라서 product_id가 순차적인 경우 동일한 번호를 갖는 또 다른 논리 열 island_id를 파생시킵니다. product_id에서 중단이 식별되면 island_id가 증가됩니다.

select product_id,
row_number() over (order by product_id) as row_num,
product_id - row_number() over (order by product_id) as island_id,
from products;

파티셔닝 열 = 파티션 없음 - 전체 테이블이 동일한 파티션에 있음

주문 열 = product_id

순위 함수 = row_number()

Group By 및 Partition By는 다음과 같은 많은 문제를 해결하는 데 사용됩니다.

정보 요약: 그룹화를 사용하면 모든 그룹의 데이터를 집계하고 정보를 요약할 수 있습니다.

패턴 분석: 데이터 하위 집합 내의 패턴이나 추세를 식별하고 데이터 집합의 다양한 측면에 대한 통찰력을 제공하는 데 도움이 됩니다.

통계 분석: 평균, 개수, 최대값, 최소값 및 그룹 내의 기타 집계 함수와 같은 통계 측정값을 계산할 수 있습니다.

데이터 정리: 그룹 내의 중복, 불일치 또는 이상 현상을 식별하여 데이터 정리 및 품질 개선을 보다 쉽게 ​​관리할 수 있도록 도와줍니다.

코호트 분석: 집단 기반 분석, 시간 경과에 따른 개체 그룹 추적 및 비교 등에 유용합니다.
 
 

Hanu ~을 실행하다 HelperCodes 블로그 주로 다루는 것은 SQL 치트 시트. 저는 풀 스택 개발자이며 재사용 가능한 자산을 만드는 데 관심이 있습니다.

• SEO 기반 콘텐츠 및 PR 배포. 오늘 증폭하십시오.
• PlatoData.Network 수직 생성 Ai. 자신에게 권한을 부여하십시오. 여기에서 액세스하십시오.
• PlatoAiStream. 웹3 인텔리전스. 지식 증폭. 여기에서 액세스하십시오.
• 플라톤ESG. 탄소, 클린테크, 에너지, 환경, 태양광, 폐기물 관리. 여기에서 액세스하십시오.
• PlatoHealth. 생명 공학 및 임상 시험 인텔리전스. 여기에서 액세스하십시오.
• 출처: https://www.kdnuggets.com/sql-group-by-and-partition-by-scenarios-when-and-how-to-combine-data-in-data-science?utm_source=rss&utm_medium=rss&utm_campaign=sql-group-by-and-partition-by-scenarios-when-and-how-to-combine-data-in-data-science