Window Function는 행 과 행 사이의 관계를 쉽게 정의하기 위해서 제공되는 함수
윈도우 함수를 잘 활용한다면 복잡한 SQL을 쉽게 활용할 수 있다
윈도우 함수는 OVER 절이 들어간 함수라고 보면 되는데 윈도우 함수와 함께 사용되는 집계 함수로는 AVG(), COUNT0, MAX(), MIN(), STDDEV(), SUM(), VARIANCE() 등이 있다.
윈도우 함수와 함께 사용되는 비집계 함수에는 CUME_DIST(), DENSE_RANK(), FIRST_VALUE(), LAG(), LAST_VALUE(), LEAD(), NTH_VALUE(), NTILE(), PERCENT_RANK(), RANK(), ROW_NUMBER() 등이 있다. (Analytic Function 분석 함수)
0) 관계형 데이터베이스에서 group by를 사용 할 때 주의할 점
- tStaff 테이블에서 depart 별로 depart 와 salary의 평균을 조회
select depart, avg(salary)
from tStaff
group by depart;
- tStaff 테이블에서 depart 별로 depart 와 salary를 조회
select depart, salary
from tStaff
group by depart;
오라클의 경우 에러, mysql의 경우 버전에 따라 에러, mariaDB는 조회 됨
group by의 경우 그룹화 한 항목별로 하나만 출력 가능한데, salasry의 경우 2개 이상이 될 수 있어서 어떤 것을 출력할 지 지정하지 않아 에러, mysql의 fork인 mariaDB에서는 이 문법을 허용함.
> 윈도우 함수는 그룹화를 수행해도 행 단위로 출력한다.
- 그룹 별 집계나 비집계 함수를 사용할 수 있는 함수
1) 사용 형식
select <윈도우 함수이름>( [매개변수] ) OVER(
[PARTITION BY 그룹화 할 항목 나열 ]
ORDER BY 정렬 할 항목 >
출력 할 컬럼의 이름
From 테이블 이름
[where 조건];
- [ @@ ] 파티션은 선택인데, 그룹을 만드는 것, 파티션이 없으면 전체 대이터를 가지고 수행
2) 순위 관련 함수
- ROW_NUMBER : 중첩되지 않는 일렬번호를 부여
- DENSE_RANK : 중첩 된 순위에 동일한 순위를 할당하고 다음 순위도 건너뛰지 않고 부여
- RANK : 중첩 된 순위에 동일한 순위를 할당하고 다음 순위를 건너뛰고 부여
- NTILE : 등분 ( 그룹화 ? ) 100을 3등분 뭐 그런거
> 실습
usertbl 테이블에서 데뷔가 빠른 순서대로 j_name과 debut_date를 기준으로 조회
순위관련 함수 사용 ( RANK , NTILE )
select rank() over(order by debut_date) "데뷔가 빠른 순서", j_name, debut_date
from jtable1;
- NTILE 의 경우 (_)에 몇 등분을 할 지 넣어야 함.
데뷔가 빠른 순서대로 3등분 할 경우
select ntile(3) over(order by debut_date) "데뷔가 빠른 순서", j_name, debut_date
from jtable1;
# addr 별로 그룹화 하여 순위를 조회하는 경우( group by가 아닌 partition by 사용 )
# 그룹 화 할 경우, partition by에 사용한 기준을 꼭 같이 출력해줘야 함 (아래의 경우 addr)
select rank() over(partition by addr order by debut_date) "나이가 많은 순서", addr, j_name, debut_date
from jtable1;
- group by와 partition by의 차이점 ★★★★★
group by는 그룹당 하나의 해당 데이터만 출력
partition by는 그룹 전체 해당 데이터를 행단위로 출력
3) Analytic Function
- CUME_DIST() : 누적 합
- LEAD(컬럼, 인덱스) : 다음 행
- LAG(컬럼, 인덱스) : 이전 행
- FIRST_VALUE(컬럼) : 첫 행
- LAST_VALUE(컬럼) : 마지막 행
- PERCENT_RANK() : 백분율 순위
- 위 예제 tStaff 테이블 생성 구조
CREATE TABLE tStaff (
name CHAR (15) PRIMARY KEY,
depart CHAR (10) NOT NULL,
gender CHAR(3) NOT NULL,
joindate DATE NOT NULL,
grade CHAR(10) NOT NULL,
salary INT NOT NULL,
score DECIMAL(5,2) NULL
);
INSERT INTO tStaff VALUES ('김유신','총무부','남','2000-2-3','이사',420,88.8);
INSERT INTO tStaff VALUES ('유관순','영업부','여','2009-3-1','과장',380,NULL);
INSERT INTO tStaff VALUES ('안중근','인사과','남','2012-5-5','대리',256,76.5);
INSERT INTO tStaff VALUES ('윤봉길','영업부','남','2015-8-15','과장',350,71.25);
INSERT INTO tStaff VALUES ('강감찬','영업부','남','2018-10-9','사원',320,56.0);
INSERT INTO tStaff VALUES ('정몽주','총무부','남','2010-9-16','대리',370,89.5);
INSERT INTO tStaff VALUES ('허난설헌','인사과','여','2020-1-5','사원',285,44.5);
INSERT INTO tStaff VALUES ('신사임당','영업부','여','2013-6-19','부장',400,92.0);
INSERT INTO tStaff VALUES ('성삼문','영업부','남','2014-6-8','대리',285,87.75);
INSERT INTO tStaff VALUES ('논개','인사과','여','2010-9-16','대리',340,46.2);
INSERT INTO tStaff VALUES ('황진이','인사과','여','2012-5-5','사원',275,52.5);
INSERT INTO tStaff VALUES ('이율곡','총무부','남','2016-3-8','과장',385,65.4);
INSERT INTO tStaff VALUES ('이사부','총무부','남','2000-2-3','대리',375,50);
INSERT INTO tStaff VALUES ('안창호','영업부','남','2015-8-15','사원',370,74.2);
INSERT INTO tStaff VALUES ('을지문덕','영업부','남','2019-6-29','사원',330,NULL);
INSERT INTO tStaff VALUES ('정약용','총무부','남','2020-3-14','과장',380,69.8);
INSERT INTO tStaff VALUES ('홍길동','인사과','남','2019-8-8','차장',380,77.7);
INSERT INTO tStaff VALUES ('대조영','총무부','남','2020-7-7','차장',290,49.9);
INSERT INTO tStaff VALUES ('장보고','인사과','남','2005-4-1','부장',440,58.3);
INSERT INTO tStaff VALUES ('선덕여왕','인사과','여','2017-8-3','사원',315,45.1);
- 위 예제 jtable1 테이블 생성 구조
CREATE table jtable1(
j_id CHAR(8) NOT NULL PRIMARY KEY,
j_name VARCHAR(10) NOT NULL,
j_number INT NOT NULL,
addr CHAR(2) NOT NULL,
phone1 CHAR(3),
phone2 CHAR(8),
height SMALLINT,
debut_date DATE );
INSERT INTO jtable1 VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015.10.19');
INSERT INTO jtable1 VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016.08.08');
INSERT INTO jtable1 VALUES('OMY', '오마이걸', 7, '서울', NULL, NULL, 160, '2015.04.21');
INSERT INTO jtable1 VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015.01.15');
INSERT INTO jtable1 VALUES('GRL', '소녀시대', 8, '서울', '02', '44444444', 168, '2007.08.02');
INSERT INTO jtable1 VALUES('ITZ', '잇지', 5, '경남', NULL, NULL, 167, '2019.02.12');
'DX Data School' 카테고리의 다른 글
Python의 Exception Handling(예외처리) (1) | 2024.01.30 |
---|---|
NoSQL (MongoDB) (1) | 2024.01.30 |
Transaction 실습 (0) | 2024.01.30 |
[SQL]DDL, DML, DCL, DQL, TCL 개념과 종류 (0) | 2024.01.30 |
1~4주차 복습 (0) | 2024.01.30 |