Windows Function

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');

​