[ MySQL ] 05. 집계함수 , 그룹조회, JOIN , 서브쿼리

 

집계함수 , 그룹조회, JOIN, 서브쿼리

 

 

# 집계 함수

여러 행의 데이터를 하나의 결과 값으로 요약하는 데 사용됩니다. 주로 그룹화된 데이터나 테이블 전체에 대해 계산을 수행하는 데 사용되며, 보고서나 분석에서 많이 활용됩니다.

 

 주로 GROUP BY 절과 함께 사용됩니다.

함수	설명
COUNT()	NULL을 제외한 값의 개수를 셉니다.
SUM()	NULL을 제외한 값들의 합계를 구합니다.
AVG()	NULL을 제외한 값들의 평균을 계산합니다.
MAX()	NULL을 제외한 값 중에서 최대값을 반환합니다.
MIN()	NULL을 제외한 값 중에서 최소값을 반환합니다.

 

예시

함수	예시
COUNT()	SELECT COUNT(*) FROM employees; → 테이블의 전체 행 수
SUM()	SELECT SUM(salary) FROM employees; → 급여 합계
AVG()	SELECT AVG(salary) FROM employees; → 평균 급여
MAX()	SELECT MAX(salary) FROM employees; → 최대 급여
MIN()	SELECT MIN(salary) FROM employees; → 최소 급여

 

합계와 평균은 숫자에대해서만 구할수 있음

최대값, 최소값은 숫자, 문자, 날짜 사용

문자는 사전순서에 따라 최대,최소 결정

날짜의 최소값은 가장 빠른(오래된) 날짜

날짜의 최대값은 가장 최근 날짜

 

COUNT() - 행의 개수를 셉니다.

COUNT 함수는 NULL이 아닌 값의 개수를 반환합니다.

COUNT(expression)
COUNT(*)

행수를 구할때 

 

SELECT COUNT(*) FROM employees;

모든 행의 개수

SELECT COUNT(salary) FROM employees;

salary가 NULL이 아닌 행의 개수

 

SELECT, HAVING절에서 사용
집계할 컬럼 이름은 그룹의 기준이 되는 컬럼 이름과 같아도 되고, 같지 않아도 됨
COUNT(1)은 하나의 값을 1로 세어주는 표현으로 COUNT 함수에 자주 사용 
GROUP BY가 없는 쿼리에서도 사용 가능하며, 이 때는 전체 로우에 함수가 적용 
테이블에 NULL 데이터가 없다면 COUNT(*) = COUNT([컬럼 이름])

 

 

집계 함수는 NULL 값을 무시하거나, 특정 방식으로 처리하는데, 이를 명확하게 이해하는 것이 쿼리 결과에 영향을 미칠 수 있습니다.  

 

COUNT()와 NULL

• COUNT(*): 테이블의 모든 행을 셉니다. 이 경우 NULL 값도 포함됩니다.
• COUNT(column): 지정된 컬럼에서 NULL이 아닌 값만 셉니다.

SELECT COUNT(*) AS total_rows FROM employees;

테이블의 모든 행을 셉니다(심지어 NULL 값도 포함).

 

SELECT COUNT(salary) AS non_null_salaries FROM employees;

salary 컬럼에서 NULL이 아닌 값만 셉니다.

 

SUM()와 NULL

• SUM(column): NULL 값은 무시하고, NULL이 아닌 숫자 값들의 합계를 구합니다.

SELECT SUM(salary) AS total_salary FROM employees;

salary 컬럼에서 NULL이 아닌 값들의 합계를 계산합니다. NULL 값은 무시됩니다.

 

AVG()와 NULL

• AVG(column): NULL 값은 무시하고, NULL이 아닌 값들만 평균을 계산합니다. 즉, NULL 값은 평균 계산에 포함되지 않습니다.

SELECT AVG(salary) AS average_salary FROM employees;

salary 컬럼에서 NULL이 아닌 값들만 포함한 평균을 계산합니다.

 

MAX()와 MIN()와 NULL

• MAX() 및 MIN(): NULL 값은 무시하고, NULL이 아닌 값들만 대상으로 최대값과 최소값을 계산합니다.

SELECT MAX(salary) AS highest_salary FROM employees;

salary 컬럼에서 NULL이 아닌 값들 중 가장 큰 값을 반환합니다.

 

함수NULL 처리 방식

함수	설
COUNT(*)	NULL을 포함하여 모든 행을 셈
COUNT(column)	NULL이 아닌 값들만 셈
SUM(column)	NULL을 무시하고, NULL이 아닌 값들의 합계
AVG(column)	NULL을 무시하고, NULL이 아닌 값들의 평균
MAX(column)	NULL을 무시하고, NULL이 아닌 값 중 최대값 반환
MIN(column)	NULL을 무시하고, NULL이 아닌 값 중 최소값 반환

 

 

• 집계 함수는 기본적으로 NULL 값을 무시합니다. 그러나 상황에 따라 COALESCE 또는 IFNULL 함수를 사용하여 NULL을 처리할 수 있습니다.
• 데이터에서 NULL 값이 차지하는 비중이 클 경우, 이 값을 대체하는 방식이 결과에 큰 영향을 줄 수 있습니다.

 

 

 

 

# 그룹 조회

GROUP BY 절을 사용하여 데이터를 특정 기준에 따라 그룹화한 후, 각 그룹에 대해 집계된 정보를 반환하는 방식입니다. 이는 데이터 분석이나 통계 작업에서 자주 사용되며, 그룹 함수(집계 함수)와 결합하여 유용한 통계를 쉽게 계산할 수 있습니다.

 

SELECT [GROUP BY 대상 컬럼 이름], ..., [그룹 함수]
FROM [테이블 이름]
WHERE 조건식
GROUP BY [컬럼 이름];

 

 select {컬럼이름 [ as 별칭]…, 그룹함수(컬럼명)} from <테이블이름>
 [where 검색조건 ]
 [group by 컬럼이름 ]
 [order by 컬럼이름 [정렬옵션] ]

 

 

그룹핑 전에 WHERE절을 사용하여 그룹 대상을 먼저 선택가능.

GROUP BY 절에는 반드시 칼럼이름이 포함되어야 하며 별명 사용 불가.

SELECT 절에서 집계 함수 없이 나열된 칼럼 이름이나 표현식은
GROUP BY 절에 반드시 포함 되어야 함.

GROUP BY 절에 나열된 칼럼 이름은 SELECT 절에 명시하지 않아도 됨.

SELECT 절에서 그룹함수를 사용할 경우
group by 절에서 나눈 그룹 안에서 집계를 수행

 

• 컬럼에서 동일한 값을 가지는 로우를 그룹화하는 키워드
• 형식 : GROUP BY [컬럼 이름]
• 주로 그룹 별 데이터를 집계할 때 사용하며, 엑셀의 피벗 기능과 유사
• GROUP BY가 쓰인 쿼리의 SELECT절에는 GROUP BY 대상 컬럼과 그룹 함수만 사용 가능
  • GROUP BY 대상 컬럼이 아닌 컬럼을 SELECT하면, 에러 발생
• 여러 컬럼으로 그룹화 시, 키워드 뒤에 [컬럼 이름]을 복수 개로 입력
• 컬럼 번호로도 그룹화 가능
  • 컬럼 번호는 SELECT절의 컬럼 이름의 순서를 의미

 

 select {컬럼이름 [ as 별칭]…, 그룹함수(컬럼명)} from <테이블이름>
 [where 검색조건 ]
 [group by 컬럼1이름, 컬럼2이름, …, 컬럼n이름 ]
 [having 검색조건 ]
 [order by 컬럼이름 [정렬옵션 ASC|DESC] ]

 

WHERE 절 조건 -> GROUP BY하기 전 조건 -> 집계 대상 필터링 

HAVING 절조건 -> GROUP BY한 후 조건 -> 집계 후 표시할 행 필터링 

HAVING 없는 GROUP BY는 존재하지만, GROUP BY없는 HAVING 은 존재하지 않음

 

GROUP BY 절에서 두 개 이상의 컬럼을 콤마(,)로 구분하여 명시할 경우
첫 번째 컬럼에 대한 그룹을 형성하고,
각 그룹 안에서 두 번째 컬럼에 대한 2차 그룹을 형성

 

# HAVING  그룹에 조건 처리 

SELECT [GROUP BY 대상 컬럼 이름], ..., [그룹 함수]
FROM [테이블 이름]
WHERE 조건식
GROUP BY [컬럼 이름]
HAVING 조건식;

 

 

• 가져올 데이터 그룹에 조건을 지정해주는 키워드
• 형식 : HAVING 조건식
• 조건식이 True(참)이 되는 그룹만 선택
• HAVING절의 조건식에서는 그룹 함수를 활용
• HAVING 절에 열 별칭 사용 가능(권고하지 않음)
  HAVING  절 실행후 SELECT 가 처리 mysql에서는 별칭으로 써도 가능하지만 다른 DBMS에서는 안된다 

 

SELECT 담당자직위
       ,도시
       ,COUNT(*) AS 고객수
       ,AVG(마일리지) AS 평균마일리지
FROM 고객
GROUP BY 담당자직위
         ,도시
ORDER BY 1, 2;

 

 

키워드문법작성 순서실행 순서

 

키워드	문법	작성순서	실행순서
SELECT	SELECT [컬럼 이름]	1	5
FROM	FROM [테이블 이름]	2	1
WHERE	WHERE 조건식	3	2
GROUP BY	GROUP BY [컬럼 이름]	4	3
HAVING	HAVING 조건식	5	4
ORDER BY	ORDER BY [컬럼 이름]	6	6

 

 

입력 순서쿼리실행 순서

입력순서	쿼리	실행순서
1	SELECT type, COUNT(1), MAX(weight)	5
2	FROM test	1
3	WHERE name LIKE '%a%'	2
4	GROUP BY type	3
5	HAVING MAX(height) > 1	4
6	ORDER BY 3;	6

 

 

 

 

COUNT

• 그룹의 값 수를 세는 함수
• 형식 : COUNT([컬럼 이름])
• SELECT, HAVING절에서 사용
• 집계할 컬럼 이름은 그룹의 기준이 되는 컬럼 이름과 같아도 되고, 같지 않아도 됨
• COUNT(1)은 하나의 값을 1로 세어주는 표현으로 COUNT 함수에 자주 사용.
• GROUP BY가 없는 쿼리에서도 사용 가능하며, 이 때는 전체 로우에 함수가 적용
• 테이블에 NULL 데이터가 없다면 COUNT(*) = COUNT([컬럼 이름])

SELECT [GROUP BY 대상 컬럼 이름], ..., COUNT([컬럼 이름])
FROM [테이블 이름]
GROUP BY [컬럼 이름]
HAVING 조건문;

 

SELECT 부서, COUNT(*) AS 직원수
FROM 직원
WHERE 나이 > 30   -- 개별 행을 필터링 (30세 이상 직원만 선택)
GROUP BY 부서;

 

 

 

SUM

• 그룹의 합을 계산하는 함수
• 형식 : SUM([컬럼 이름])
• SELECT, HAVING절에서 사용
• 집계할 컬럼 이름은 그룹의 기준이 되는 컬럼 이름과 같아도 되고, 같지 않아도 됨
• GROUP BY가 없는 쿼리에서도 사용 가능하며, 이 때는 전체 로우에 함수가 적용 

SELECT [GROUP BY 대상 컬럼 이름], ..., SUM([컬럼 이름])
FROM [테이블 이름]
GROUP BY [컬럼 이름]
HAVING 조건문;

 

 

AVG

• 그룹의 평균을 계산하는 함수
• 형식 : AVG([컬럼 이름])
• SELECT, HAVING절에서 사용
• 집계할 컬럼 이름은 그룹의 기준이 되는 컬럼 이름과 같아도 되고, 같지 않아도 됨
• GROUP BY가 없는 쿼리에서도 사용 가능하며, 이 때는 전체 로우에 함수가 적용 

SELECT [GROUP BY 대상 컬럼 이름], ..., AVG([컬럼 이름])
FROM [테이블 이름]
GROUP BY [컬럼 이름]
HAVING 조건문;

 

 

MIN

• 그룹의 최솟값을 반환하는 함수
• 형식 : MIN([컬럼 이름])
• SELECT, HAVING절에서 사용
• 집계할 컬럼 이름은 그룹의 기준이 되는 컬럼 이름과 같아도 되고, 같지 않아도 됨.
• GROUP BY가 없는 쿼리에서도 사용 가능하며, 이 때는 전체 로우에 함수가 적용

SELECT [GROUP BY 대상 컬럼 이름], ..., MIN([컬럼 이름])
FROM [테이블 이름]
GROUP BY [컬럼 이름]
HAVING 조건문;

 

 

MAX

• 그룹의 최댓값을 반환하는 함수
• 형식 : MAX([컬럼 이름])
• SELECT, HAVING절에서 사용
• 집계할 컬럼 이름은 그룹의 기준이 되는 컬럼 이름과 같아도 되고, 같지 않아도 된다.
• GROUP BY가 없는 쿼리에서도 사용 가능하며, 이 때는 전체 로우에 함수가 적용된다.

SELECT [GROUP BY 대상 컬럼 이름], ..., MAX([컬럼 이름])
FROM [테이블 이름]
GROUP BY [컬럼 이름]
HAVING 조건문;

 

 

SELECT 부서, COUNT(*) AS 직원수
FROM 직원
GROUP BY 부서
HAVING COUNT(*) > 5;  -- 그룹화된 결과에 대해 조건을 적용

 

SELECT 부서, COUNT(*) AS 직원수
FROM 직원
WHERE 나이 > 30   -- 개별 행 필터링 (30세 이상인 직원만)
GROUP BY 부서
HAVING COUNT(*) > 5;  -- 그룹화된 데이터 필터링 (직원 수가 5명 이상인 부서만)

 

 

 

# JOIN

 

두 개 이상의 테이블을 결합하여 관련된 데이터를 조회하는 SQL 연산입니다.

데이터베이스는 일반적으로 여러 테이블로 구성되어 있으며, 각 테이블은 특정한 데이터 집합을 가지고 있습니다. JOIN을 사용하면 이러한 테이블 간의 연관성을 바탕으로 데이터를 통합하여 하나의 결과로 반환할 수 있습니다.

 

• 여러 테이블에서 필요한 데이터를 통합하여 조회.
• 테이블 간의 관계(일대일, 일대다, 다대다)를 기반으로 관련된 데이터를 찾기.
• 중복된 데이터의 관리 및 저장 공간 절약을 위해 데이터를 여러 테이블로 분리한 후, 다시 통합하여 사용할 수 있도록 함.

 

JOIN의 종류

 

 

 

종류	설
INNER JOIN	두 테이블에서 일치하는 데이터만 반환.
LEFT JOIN	왼쪽 테이블의 모든 데이터를 반환하고, 오른쪽 테이블에서 일치하는 데이터가 있으면 반환, 없으면 NULL.
RIGHT JOIN	오른쪽 테이블의 모든 데이터를 반환하고, 왼쪽 테이블에서 일치하는 데이터가 있으면 반환, 없으면 NULL.
FULL JOIN	두 테이블에서 일치하는 데이터뿐만 아니라, 일치하지 않는 데이터도 모두 반환.
CROSS JOIN	두 테이블의 카티전 곱을 반환. (모든 가능한 조합)
SELF JOIN	동일한 테이블 내에서 두 레코드를 비교하여 조인.

 

 

INNER JOIN

두 테이블에서 일치하는 데이터만을 반환합니다. 일치하지 않는 데이터는 결과에 포함되지 않습니다.

SELECT [컬럼 이름]
FROM [테이블 A 이름]
INNER JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름]
WHERE 조건식;

 

SELECT 컬럼명1, 컬럼명2
FROM 테이블1
INNER JOIN 테이블2 ON 테이블1.컬럼 = 테이블2.컬럼;

 

LEFT JOIN  

왼쪽 테이블의 모든 데이터를 반환하고, 오른쪽 테이블에서 일치하는 데이터가 있으면 해당 데이터를 함께 반환합니다. 오른쪽 테이블에 일치하는 데이터가 없으면 NULL로 반환됩니다.

SELECT [컬럼 이름]
FROM [테이블 A 이름]
LEFT JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름]
WHERE 조건식;

 

 

RIGHT JOIN  

오른쪽 테이블의 모든 데이터를 반환하고, 왼쪽 테이블에서 일치하는 데이터가 있으면 해당 데이터를 함께 반환합니다. 왼쪽 테이블에 일치하는 데이터가 없으면 NULL로 반환됩니다.

SELECT [컬럼 이름]
FROM [테이블 A 이름]
RIGHT JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름]
WHERE 조건식;

 

 

 

OUTER JOIN

• 두 테이블에 있는 모든 값 합치기
• MySQL에서는 OUTER JOIN 키워드가 없다.
• 따라서 UNION 키워드를 사용하여 LEFT JOIN과 RIGHT JOIN을 함께 쓴다.
• UNION : 두 쿼리의 결과를 중복을 제외하고 합쳐서 보여주는 집합 연산자

SELECT [컬럼 이름]
FROM [테이블 A 이름]
LEFT JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름]
UNION
SELECT [컬럼 이름]
FROM [테이블 A 이름]
RIGHT JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름];

 

 

CROSS JOIN

두 테이블의 카티전 곱(Cartesian product)을 반환합니다. 두 테이블의 모든 가능한 조합을 생성합니다.

SELECT [컬럼 이름]
FROM [테이블 A 이름]
CROSS JOIN [테이블 B 이름]
WHERE 조건식;

 

 

SELF JOIN

동일한 테이블 내에서 두 개의 레코드를 비교할 때 사용됩니다. 보통 테이블에 별칭을 사용하여 두 개의 테이블처럼 다룹니다.

SELECT [컬럼 이름]
FROM [테이블 A 이름] AS t1
INNER JOIN [테이블 A 이름] AS t2
ON t1.[컬럼 A 이름] = t2.[컬럼 A 이름]
WHERE 조건식;

 

 

---

 

 

# 서브쿼리 

하나의 쿼리 안에 포함된 또 다른 쿼리를 의미합니다. 내부 쿼리가 먼저 실행되고, 그 결과가 외부 쿼리에 전달되어 최종 결과를 생성하는 방식으로 사용됩니다. 서브쿼리는 효율적으로 데이터를 조회하거나 특정 조건을 만족하는 데이터를 필터링하는 데 유용합니다.

서브쿼리는 주로 SELECT, INSERT, UPDATE, DELETE와 같은 SQL 명령문에 포함될 수 있습니다.

 

 

• 서브쿼리: 하나의 SQL 명령문의 처리 결과를 다른 SQL 명령문에 전달하기 위해 두 개 이상의 SQL문을 하나의 SQL문으로 연결한 형태입니다.
• 사용 목적: 메인 쿼리에서 검색 조건을 설정하기 위해 다른 결과 집합에서 값을 찾는 데 사용됩니다.
• 메인쿼리와 서브쿼리: 서브쿼리를 포함한 SQL문을 메인쿼리라고 하며, 서브쿼리는 SELECT 문의 결과를 다른 SQL 명령문에 사용할 수 있습니다.
• 표기 방법: 서브쿼리는 SELECT 문의 시작과 끝에 **괄호( )**를 사용해 메인쿼리와 구분합니다.

 

• SELECT 절에서 사용: SELECT (서브쿼리) AS 별칭 FROM 테이블
• WHERE 절에서 사용: WHERE 컬럼 = (서브쿼리)
• FROM 절에서 사용: FROM (서브쿼리) AS 별칭
• HAVING 절에서 사용: HAVING 집계함수 > (서브쿼리)
• EXISTS 사용: WHERE EXISTS (서브쿼리)
• 상관 서브쿼리: 외부 쿼리의 각 행에 대해 서브쿼리가 실행됨

 

 

• 단일행 서브쿼리: 하나의 행만 반환하며, 비교 연산자(=, >, < 등)와 사용됩니다.
• 다중행 서브쿼리: 여러 행을 반환하며, 다중 행 연산자(IN, ANY, ALL)와 함께 사용됩니다.

 

1. 단일행 서브쿼리 (Single-Row Subquery)

단일행 서브쿼리는 단 하나의 행을 반환하는 서브쿼리입니다. 주로 비교 연산자(=, >, <, >=, <=, <>)와 함께 사용되며, 메인쿼리가 단일 값과 비교할 수 있도록 합니다.

특징:

• 서브쿼리가 하나의 행을 반환합니다.
• 서브쿼리 결과를 메인쿼리에서 단일 값으로 처리합니다.

 

SELECT name, salary 
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

 

서브쿼리는 직원들의 평균 급여(AVG(salary))를 계산해 단일 값을 반환하고, 메인쿼리는 그 값을 기준으로 급여가 평균보다 높은 직원들을 검색합니다.

비교 연산자:

• =, >, <, >=, <=, <> 등과 함께 사용됩니다.

 

2. 다중행 서브쿼리 (Multi-Row Subquery)

다중행 서브쿼리는 여러 행을 반환할 수 있는 서브쿼리입니다. 주로 다중 행 연산자(IN, ANY, ALL)와 함께 사용되며, 메인쿼리는 여러 값과 비교할 수 있습니다.

특징:

• 서브쿼리가 여러 행을 반환할 수 있습니다.
• 메인쿼리에서 다수의 값과 비교하여 데이터를 처리합니다.

IN 연산자 사용

SELECT name, department_id 
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

 

 

---

 

 

 

1. SELECT 절에 있는 서브쿼리

서브쿼리를 SELECT 절에서 계산된 값으로 사용하는 형식입니다. 주로 단일 값을 반환하는 스칼라 서브쿼리가 사용됩니다.

SELECT 컬럼1, 
(SELECT 서브쿼리 FROM 테이블 WHERE 조건) AS 별칭
FROM 테이블
WHERE 조건;

 

SELECT name,
(SELECT AVG(salary) FROM employee) AS avg_salary
FROM employee;

모든 직원의 평균 급여를 계산하고, 각 직원의 이름과 함께 출력합니다.

 

 

2. WHERE 절에 있는 서브쿼리

서브쿼리를 WHERE 절에서 사용하여 조건을 설정하는 형식입니다. 단일 값 또는 다중 값을 반환할 수 있습니다.

SELECT 컬럼명
FROM 테이블
WHERE 컬럼명 = (SELECT 서브쿼리 FROM 테이블 WHERE 조건);

SELECT name, salary
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);

평균 급여보다 높은 급여를 받는 직원을 조회합니다.

 

3. WHERE 절에 있는 서브쿼리

서브쿼리를 WHERE 절에서 사용하여 조건을 설정하는 형식입니다. 단일 값 또는 다중 값을 반환할 수 있습니다.

SELECT 컬럼명
FROM 테이블
WHERE 컬럼명 = (SELECT 서브쿼리 FROM 테이블 WHERE 조건);

SELECT name, salary
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);

 

 

4. FROM 절에 있는 서브쿼리

서브쿼리를 FROM 절에 사용하여 임시 테이블처럼 사용하는 형식입니다. 주로 파생 테이블로 사용됩니다.

SELECT 컬럼명
FROM (SELECT 서브쿼리 FROM 테이블 WHERE 조건) AS 별칭
WHERE 조건;

SELECT name, avg_salary
FROM employee e, (SELECT dept_id, AVG(salary) AS avg_salary 
FROM employee GROUP BY dept_id) avg_salaries
WHERE e.dept_id = avg_salaries.dept_id;

서브쿼리를 FROM 절에 사용하여 임시 테이블처럼 사용하는 형식입니다. 주로 파생 테이블로 사용됩니다.

 

5. HAVING 절에 있는 서브쿼리

서브쿼리를 HAVING 절에 사용하여 그룹 조건을 설정하는 형식입니다. 그룹화된 데이터에 대한 조건을 설정할 때 유용합니다.

SELECT 컬럼명, 집계함수(컬럼)
FROM 테이블
GROUP BY 컬럼
HAVING 집계함수(컬럼) > (SELECT 서브쿼리 FROM 테이블 WHERE 조건);

 

SELECT dept_id, AVG(salary)
FROM employee
GROUP BY dept_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employee);

평균 급여가 전체 평균보다 높은 부서들을 조회합니다.

 

6. EXISTS 서브쿼리

EXISTS 연산자를 사용하여 서브쿼리 결과의 존재 여부를 확인하는 형식입니다. 서브쿼리가 결과를 반환하면 TRUE, 그렇지 않으면 FALSE를 반환합니다.

SELECT 컬럼명
FROM 테이블
WHERE EXISTS (SELECT 1 FROM 테이블 WHERE 조건);

SELECT name
FROM employee e
WHERE EXISTS (SELECT 1 FROM department d WHERE e.dept_id = d.dept_id AND d.unit_id = 'C');

 

7. 상관 서브쿼리

서브쿼리가 외부 쿼리의 값을 참조하는 형식입니다. 서브쿼리가 외부 쿼리의 각 행에 대해 한 번씩 실행됩니다.

SELECT 컬럼명
FROM 테이블1 별칭1
WHERE 컬럼명 > (SELECT 집계함수(컬럼명) FROM 테이블2 별칭2 WHERE 별칭1.컬럼 = 별칭2.컬럼);

 

SELECT e1.name, e1.salary
FROM employee e1
WHERE e1.salary > (SELECT AVG(e2.salary) FROM employee e2 WHERE e1.dept_id = e2.dept_id);

각 직원이 속한 부서의 평균 급여보다 높은 급여를 받는 직원을 조회합니다.

 

---

 

# RANK()

 

MySQL에서 결과 집합 내에서 각 행에 순위를 부여하는 데 사용됩니다. 동일한 값에 대해 동일한 순위가 할당되며, 그 다음 순위는 건너뜁니다.  특정 컬럼을 기준으로 순위를 매길 때 유용합니다.

 

RANK() OVER ([PARTITION BY partition_expression] ORDER BY order_expression)

 

 

• PARTITION BY: (선택사항) 특정 그룹으로 결과 집합을 나누고 각 그룹 내에서 순위를 매기기 위해 사용합니다.
• ORDER BY: 결과 집합 또는 파티션 내에서 순위를 매길 열을 기준으로 정렬합니다.
• 정렬된 데이터에서 현재 행의 순위를 반환함 
• 만일 같은 값이 여러개 있으면 같은 순위를 부여하고, 다음 순위는 중복된 순위 개수만큼 건너뛰고 부여 
• over 절 안에 order by 문으로 순위를 정하기 위한 정렬 기준을 지정함 
• 영역을 구분해서 순위를 표시하고 싶으면 partition by 문에 기준열을 지정 

 

SELECT 
    name, 
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

 

name	salary	rank
Eve	7500	1
Frank	7500	1
David	7000	3
Bob	6000	4
Charlie	6000	4
Alice	5000	6

• "Eve"와 "Frank"는 동일한 1등 순위를 가집니다.
• 다음 순위는 2등이 생략되고 바로 3등부터 시작합니다.

 

• 동일한 값: 동일한 값은 같은 순위를 갖고,
• 순위 건너뜀: 다음 순위는 건너뛴다.
• PARTITION BY를 통해 그룹화된 데이터에 별도의 순위를 부여할 수 있습니다.