윈도우 함수
테이블의 행과 행 사이 관계를 쉽게 정의하기 위해 MySQL에서 제공하는 함수
OVER 절이 들어간 함수
query-2(sqlDB초기화코드).sql 활용
# 아래쿼리문은 항상 쌍으로 실행한다.
# 의미: 만약 sqldb가 존재한다면 삭제하고
# 다시 sqldb를 만들어라.
drop database if exists sqldb; #데이터중에 sqldb가 있으면 삭제하라.
create database sqldb;
use sqldb;
-- 회원 테이블 작성
drop table if exists user;
create table user(
userId varchar(8) not null primary key, -- 사용자 아이디(PK)
username varchar(10) not null, -- 이름
birthyear int not null, -- 출생연도
addr varchar(4) not null, -- 주소
mobile1 varchar(3), -- 휴대폰 앞자리번호(010,016,017,019,011)
mobile2 varchar(8), -- 휴대폰 나머지 번호(하이폰(-)을 제외)
height smallint, -- 키(samllint 2바이트)
mdate date -- 회원 가입일
);
-- 회원 구매 테이블 생성
drop table if exists buy;
create table buy(
-- auto_increment명령어 : mysql엔진이 데이터가 들어올때마다,
-- 1씩 자동 증가시켜준다.
num int auto_increment not null primary key,
-- userId는 여기서는 PK가 될수 없다. 일반적으로 한 테이블에 PK는
-- 오로지 하나만 존재한다.
userId varchar(8) not null,
prodName varchar(6) not null, -- 물품명
groupName varchar(4), -- 분류
price int, -- 단가
amount smallint not null, -- 수량
-- user에 있는 userId를 참조하라. 여기서는 userId가 외래키(FK)
foreign key(userId) References user(userId)
);
-- user에 데이터 추가
insert into user values('LSG', '이승기', 1987, '서울', '011', '11111111', '182', '2008-08-08');
select * from user;
delete from user;
-- user애 데이터 추가
insert into user values
('LSG', '이승기', 1987, '서울', '011', '11111111', 182, '2008-08-08'),
('KBS', '김범수', 1979, '경남', '011', '22222222', 173, '2012-04-04'),
('KKH', '김경호', 1971, '전남', '019', '33333333', 177, '2007-07-07'),
('JYP', '조용필', 1950, '경기', '011', '44444444', 166, '2009-04-04'),
('SSK', '성시경', 1979, '서울', null, null, 186, '2013-12-12'),
('LJB', '임재범', 1963, '서울', '016', '66666666', 182, '2009-09-09'),
('YJS', '윤종신', 1969, '경남', null, null, 170, '2008-08-08'),
('EJW', '은지원', 1972, '경북', '011', '88888888', 174, '2014-3-3'),
('JKW', '조관우', 1965, '경기', '011', '99999999', 172, '2010-10-10'),
('BBk', '바비킴', 1973, '서울', '010', '00000000', 176, '2013-05-05');
select * from user;
delete from buy;
-- buy에 데이터 추가
insert into buy values
(null, 'KBS' ,'운동화', null, 30, 2),
(null, 'KBS' ,'노트북', '전자', 1000, 1),
(NULL, 'JYP' ,'모니터', '전자', 200, 1),
(null, 'BBK' ,'모니터', '전자', 200, 5),
(null, 'KBS' ,'청바지', '의류', 50, 3),
(null, 'BBK' ,'메모리', '전자', 80, 10),
(null, 'SSK' ,'책', '서적', 15, 5),
(null, 'EJW' ,'책', '서적', 15, 2),
(null, 'EJW' ,'청바지', '의류', 50, 1),
(null, 'BBK' ,'운동화', NULL, 30, 2),
(NULL, 'EJW' ,'책', '서적', 15, 1),
(NULL, 'BBK' ,'운동화', NULL, 30, 2)
;
select * from buy;
query-8(윈도우 함수, 순위함수, 분석함수, 피벗테이블).sql
순위함수
use sqldb;
-- 순위 매기기 : row_number() over()
select row_number() over(order by height desc)as '키순위', username, addr, height
from user;
-- 같은 키가 존재하면 이름으로 순위 정해보기
select row_number() over(order by height desc, username)as '키순위', username, addr, height
from user;
-- rank() over() : 일반적으로 가장 많이 사용되는 순위함수
-- 같은 키가 존재하면 동일한 등수로 만들고 동일한 등수의 수만큼 띄우고 순위를 정함.
select rank() over(order by height desc)as '키순위', username, addr, height
from user;
-- dense_rank() over()
-- 같은 키가 존재하면 동일한 등수로 만들고 등수의 수만큼 띄우지 않고 순위를 정했다.
select dense_rank() over(order by height desc)as '키순위', username, addr, height
from user;
-- over(partition by 컬럼명) : 컬럼명에 따라서 그룹지어서 그룹내에서 순위를 정하기
-- 123
select addr, username, height,
row_number() over(partition by addr order by height desc)as '지역별 키순위'
from user;
-- 1223
select addr, username, height,
rank() over(partition by addr order by height desc)as '지역별 키순위'
from user;
-- ntile(정수) : 해당하는 row개수에 "그룹"으로 나누어주는 함수
select ntile(2) over(order by height desc)as '빈번호', username, addr, height
from user;
-- 키순위 상관없이 가나다순으로 5개씩 2개로 나눠서 출력하시오.
select ntile(2) over(order by username asc)as '빈번호', username, addr, height
from user;
-- 3개로 나눔
select ntile(3) over(order by username asc)as '빈번호', username, addr, height
from user;
-- 4개로 나눔
select ntile(4) over(order by username asc)as '빈번호', username, addr, height
from user;
-- 10개로 나눔
select ntile(10) over(order by username asc)as '빈번호', username, addr, height
from user;
분석함수
-- 분석함수
-- lead(컬럼명, offset, default) : 현재 row를 기준으로 다음 행을 참조
-- lag(컬럼명, offset, default) : 현재 row를 기준으로 이전 행을 참조
select username, addr, height as '키',
height - (lead(height, 1, 0) over(order by height desc)) as '다음 사람과 키 차이'
from user;
-- lead(height, 2, 0) 2 => 다음다음 값
select username, addr, height as '키',
height - (lead(height, 2, 0) over(order by height desc)) as '다음 사람과 키 차이'
from user;
-- lag 이전 사람과 비교
select username, addr, height as '키',
height - (lag(height, 1, 0) over(order by height desc)) as '이전 사람과 키 차이'
from user;
-- 지역별 최대키와 차이
-- first_value(컬럼) : 정렬된 값 중에서 첫번째 값을 의미함.
-- last_value(컬럼) : 정렬된 값 중에서 맨 마지막 값을 의미함.
select addr, username, height as '키',
height - (first_value(height) over(partition by addr order by height desc)) as '지역별 최대키와 차이'
from user;
-- last_value() 사용할때는 반드시 over절에
-- rows between unbounded preceding and unbounded following : 정렬결과의 처음과 끝을 대상으로 함.
select addr, username, height as '키',
height - (last_value(height) over(partition by addr order by height desc
rows between unbounded preceding and unbounded following))
as '지역별 최대키와 차이'
from user;
-- 누적 백분율 함수 cume_dist() : 0~1사이를 리턴함(==> 100을 곱해줌)
select addr, username, height as '키',
(cume_dist() over(partition by addr order by height desc)) * 100 as '누적인원 백분율'
from user;
피벗테이블
case ~~ when ~~ end 구문
-- case ~~ when ~~ end 구문
-- sql의 조건문에 해당(if/switch문)
-- 조건값에 따른 처리를 구분할수 있음
-- case when 조건값1 then ~~
-- when 조건값2 than ~~
-- else ~~
-- end
-- 피벗테이블 : 한열에 포함된 여러 값들을 출력, 이를 여러 열로 변환하여 테이블 반환식 회전, 필요하면 집계까지 수행
drop table if exists pivot;
create table pivot(
uname varchar(10),
season varchar(5),
amount int
);
insert into pivot values
('김범수','겨울',10),('윤종신','여름',15),
('김범수','가을',25),('김범수','봄',5),
('김범수','봄',37),('윤종신','겨울',10),
('김범수','여름',14),('김범수','겨울',15),
('김범수','겨울',22),('윤종신','여름',64);
select *
from pivot;
--피벗 테이블을 만드는 첫번째 (sum(), case when...then...end)
-- 만약에 시즌이 봄이면 더해라
select uname,
sum(case when season='봄' then amount end) as '봄'
from pivot
group by uname;
select uname,
sum(case when season='봄' then amount end) as '봄',
sum(case when season='여름' then amount end) as '여름',
sum(case when season='가을' then amount end) as '가을',
sum(case when season='겨울' then amount end) as '겨울'
from pivot
group by uname;
-- 피버 테이블을 만드는 두번째 방법 (sum(), if())
-- (만약에 season이 봄이면 amount를 sum해라), null에 대해 0으로 출력
select uname,
sum(if (season='봄', amount,0)) as '봄',
sum(if (season='여름', amount,0)) as '여름',
sum(if (season='가을', amount,0)) as '가을',
sum(if (season='겨울', amount,0)) as '겨울'
from pivot
group by uname;
-- 시즌별로 합집계
select season,
sum(if(uname = '김범수', amount, 0)) as '김범수',
sum(if(uname = '윤종신', amount, 0)) as '윤종신',
sum(amount) as '함계'
from pivot
group by season
order by amount;
