마리아DB (2) _ 윈도우함수, 조인

이 게시물에서는 윈도우함수와 조인에 대해 정리하려고 한다.

 

1. 윈도우 함수

함수들을 쭉 정리하다가 낯선 단어를 발견했다. 윈도우 함수 ? 사실 처음들어봤다. 윈도우함수가 대체 무엇일까 ?

윈도우함수란, 행과 행 간의 비교, 연산을 하기 위한 함수라고 한다. 윈도우 함수에는 순위함수와 분석함수가 있다.

 

참고로 윈도우 함수에는 OVER 문구가 필수로 들어간다. 아래 코드로 확인해보자.

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER([PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절]) FROM 테이블명;

 

순위함수

순위함수는 특정 컬럼의 순위를 구하는 함수로 RANK(), DENSE_RANK(), ROW_NUMBER, NTILE() 이 있다.

 

RANK()

ORDER BY를 포함한 쿼리문에서 특정 컬럼의 순위를 구하는 함수로, 동일한 값에 대해 같은 순위를 부여하고 중간 순위를 비운다. 즉, 순위가 < 1, 1, 3 ,4, 5> 이런식으로 나온다.

SELECT RANK() OVER(ORDER BY height DESC) AS 'height',
		 NAME,
		 addr,
		 height
FROM usertbl;

 

 

DENSE_RANK()

RANK 함수와 비슷하지만 동일한 값에 대해서는 같은 순위를 부여하고 중간 순위를 비우지 않는다는 차이점이 있다.

<1, 1, 2, 3, 4> 이런 식으로 나온다.

SELECT DENSE_RANK() OVER(ORDER BY height DESC) AS 'rank',
		 NAME,
		 addr,
		 height
FROM usertbl;

 

 

ROW_NUMBER

고유한 값을 부여하는 함수이다.

SELECT addr,
		 ROW_NUMBER() OVER(PARTITION BY addr ORDER BY height DESC),
		 NAME,
		 height
FROM usertbl
ORDER BY addr
;

 

 

NTILE

n개의 그룹으로 나누는 함수이다.

SELECT NTILE(2) OVER(ORDER BY height DESC),
		 NAME,
		 addr,
		 height
FROM usertbl;

 

해당 코드는 height의 내림차순을 기준으로 두 그룹으로 나누어 같은 그룹끼리 같은 수를 반환한다.

 

 

분석함수

LEAD

이후 몇 번째 행의 값을 가져오는 함수로, 두번째 인자에 몇 번째 뒤의 행을 가져올지 결정한다. 디폴트는 1이다. 세번째 인자에는 가져올 행이 없을 경우 디폴트를 지정한다.

-- 키 순서대로 정렬 후 다음 사람과 키 차이를 조회
SELECT NAME,
 addr,
 height,
 height - LEAD(height,1) OVER(ORDER BY height DESC)
FROM usertbl
;

 

 

LAG

LEAD와 비슷하지만, LAG은 뒤가 아니라 앞의 행 값을 가져온다. 이후 설명은 같다.

-- 키 순서대로 정렬 후 이전 사람과 키 차이를 조회
SELECT NAME,
 addr,
 height,
 height - LAG(height,1) OVER(ORDER BY height DESC)
FROM usertbl
;

FIRST_VALUE

가장 먼저 나온 값을 구한다. 즉, 처음 나온 행만 가져온다.

-- 지역별로 가장 키가 큰 사람과의 키 차이를 조회
SELECT addr,
 NAME,
 height,
 FIRST_VALUE(height) OVER(PARTITION BY addr  ORDER BY height DESC) - height
FROM usertbl
;

내림차순 기준으로는 MAX값이, 오름차순 기준으로는 MIN값이 나올 것이다.

 

---

2. 조인 (JOIN)

테이블을 다룰 때, 하나의 테이블만 다루는 게 아니라 여러 테이블을 함께 다룰 일이 많다. 테이블을 각각 다루는 게 아니라 하나로 묶어내는데 이를 조인이라고 한다. 

 

내부조인 (INNER JOIN)

일반적으로 조인이라고 부르는 것이 이 내부조인이다. 조인을 할 땐 조건이 있는데, 이 조건을 만족시키는 교집합에 있는 데이터들이 반환된다.

-- 사번, 직원명, 부서명, 직급명 조회
SELECT * FROM job;
SELECT e.emp_id AS '사번',
		 e.emp_name AS '직원명', 
		 d.dept_title AS '부서명', 
		 j.job_name AS '직급명'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN job j on e.job_code = j.job_code
;

 

위 코드는 예시문제와 코드이다. 조인 조건은 ON뒤에 쓰고, 여러 테이블을 조인해야 한다면 INNER JOIN을 뒤에 더 써주면 된다. JOIN 된 테이블에 다시 JOIN한다고 이해하면 된다.

 

추가로 AS는 ALIAS의 약자로 별칭이라는 의미이다. 저런식으로 칼럼명을 줄 수 있다. 조인을 하기 위해선, 조인하려는 열이 같은 타입이어야 한다.

 

 

외부조인 (OUTER JOIN) 

내부조인이 교집합이었으니, 외부조인은 합집합 아닐까 ? 난 비슷하게 이해했다!

외부조인은 조인의 조건에 만족되지 않는 행까지도 조회하기 위해 사용되는 조인이다. 외부조인에는 세가지 종류가 있다.

 

LEFT OUTER JOIN

왼쪽 테이블의 데이터가 모두 조회되는 조인이다. 밴다이어그램으로 생각한다면 (합집합 - 오른쪽원 + 교집합) 그런 그림이 떠오른다.

 

반대로 오른쪽 테이블의 데이터가 모두 조회되는 조인은 RIGHT OUTER JOIN이고, 모두 다 나오는게 FULL OUTER JOIN 이다. 보통 LEFT OUTER JOIN을 많이 사용한다.

 

 

 

상호 조인 (CROSS JOIN) 

 

양쪽 테이블의 모든 행을 조인하는 것이다. A테이블에 행이 3개, B테이블에 행이 3개라면, 전체 행의 개수는 3 * 3 = 9가 된다. 모든 행끼리 조인된다고 보면 된다. 

 

 

자체 조인 (SELF JOIN) 

자기자신과 조인하는 것으로, 별칭으로 테이블의 이름을 다르게 주어 조인한다. 이를 이용하는 대표적인 문제로는 사수 구하는 문제가 있다.

SELECT e.emp_id AS '사번',
		 e.emp_name AS '사원이름',
		 e.dept_code AS '부서코드',
		 m.emp_id AS '사수 사번',
		 m.emp_name AS '사수이름' 
FROM employee e
INNER JOIN employee m ON m.emp_id = e.manager_id;

 

---

다음 게시글에선 데이터베이스 모델링과 서브쿼리에 대해 다룰 예정이다.