어제 문제
--1. subway 테이블을 사용하여(없을경우 테이블 생성 후 subway.csv 파일 업로드)
select * from subway;
--1) 노선별 승차가 가장 많은 시간대 확인
select *
from (select s.*,
max(승차) over(partition by 노선번호) as max_승차
from subway s
where s.노선번호 = 노선번호)
where 승차=max_승차;
--2) 시간대별로 승차가 가장 많은 순서대로 3위까지의 노선 출력(dense_rank 기준 3위)
select *
from (select 노선번호, dense_rank() over(order by 승차 desc) as 순위
from (select *
from (select s.*,
max(승차) over(partition by 노선번호) as max_승차
from subway s
where s.노선번호 = 노선번호)
where 승차=max_승차))
where 순위<=3;
-- 강사님 풀이... 2번은 잘못 풀었네 시간대별인데 그걸 생각 못한듯.
select *
from (select s.*,
dense_rank() over(partition by 시간 order by 승차 desc) as 순위
from subway s)
where 순위<=3;--2. 코테
-- [ 문제 설명 ]
-- 3년 이상 연속 참가자 이름 출력(carol, john)
-- [ 데이터 만들기 ]
create table participation
(
name varchar (30) not null,
year int not null,
unique (name, year)
);
insert into participation values('john', 2003);
insert into participation values('lyla', 1994);
insert into participation values('faith', 1996);
insert into participation values('john', 2002);
insert into participation values('carol', 2000);
insert into participation values('carol', 1999);
insert into participation values('john', 2001);
insert into participation values('carol', 2002);
insert into participation values('lyla', 1996);
insert into participation values('lyla', 1997);
insert into participation values('carol', 2001);
insert into participation values('john', 2009);
commit;
select * from participation;
-- 이전, 그이전 참가연도 확인
select name, year,
lag(year) over(partition by name
order by year) + 1 as "이전참가연도+1",
lag(year,2) over(partition by name
order by year) + 2 as "그이전참가연도+2"
from participation;
-- 조건 만족하는 애들의 이름으로 묶어서 정답출력
select name
from (select name, year,
lag(year) over(partition by name
order by year) + 1 as "이전참가연도+1",
lag(year,2) over(partition by name
order by year) + 2 as "그이전참가연도+2"
from participation)
where year = "이전참가연도+1"
and "이전참가연도+1" = "그이전참가연도+2"
group by name;
-- 강사님 방법 - 나랑 차이점은 1년 전을 생각 안 한 부분이랑 이름 출력할 때 distinct 사용한 부분
select distinct name
from (select p.*,
lag(year,2) over(partition by name order by year) as value
from participation p)
where year-value = 2;수업 내용
— [집계함수]
— 1. sum
— 누적합 연산 범위. 특히 between 생략한 누적합4는 함 보기
— between 생략 시 between A and B 에서 A만 전달함.
select e.sal,
sum(sal) over(order by sal) as 누적합1,
sum(sal) over(order by sal, empno) as 누적합2,
sum(sal) over(order by sal
rows between unbounded preceding and current row) as 누적합3,
sum(sal) over(order by sal
rows unbounded preceding) as 누적합4
from emp e;— [기타 윈도우 함수]
— 1. ntile
ntile(n) over([partition by ...]
order by ...);— 정해진 n개 그룹으로 행을 나눌 때 사용
— 나누어 떨어지지 않으면 앞쪽 그룹에 더 많이 배치
— 100행을 3개 그룹으로 나누면 34, 33, 33행으로 분리됨.
-- 예제) 직원을 두 그룹으로 분리하기 위해 그룹번호 지정
select e.*,
ntile(2) over(order by hiredate) as 그룹번호
from emp e;-- 예제) 14명의 직원을 3개 그룹으로 분리하기 위해 그룹번호 지정 - 5,5,4로 나눠짐
select e.*,
ntile(3) over(order by hiredate) as 그룹번호
from emp e;— 2. ratio_to_report
ratio_to_report(대상) over([partition by …]);
— 전체 중 특정 값의 비율
-- 예제) 각 부서별로 각 직원의 급여의 차지 비율
select e.*,
ratio_to_report(sal) over(partition by deptno) as 비율1,
sal/sum(sal) over(partition by deptno) as 비율2
from emp e;— 3. cume_dist
— 각 행의 위치에 대한 누적 비율
— 4. percent_rank
— 각행의 위치에 대한 상대 비율 (0~1 사이로 리턴)
-- 예제) cume_dist, percent_rank 차이
select empno, deptno, sal,
cume_dist() over(partition by deptno order by sal) as 비율1,
percent_rank() over(partition by deptno order by sal) as 비율2
from emp;
-- cume_dist : 몇개 중에 몇개인지 분수를 그대로 소수로 표현하는 느낌.
-- percent_rank : 0부터 시작하고 1로 끝난다.— [계층형 질의]
— 한 테이블 내 각 행끼리 상하 관계를 갖는 경우 그 depth(level)를 표현하는 방법
select
from
where
start with 시작조건
connect by 연결조건;
— prior를 붙이는 위치에 따라 상위로 갈 수도 있고 하위로 갈 수도 있으니 주의
— 먼저 봐서 상수로 만들 컬럼에 prior 붙이면 됨
— level을 써서 몇 레벨인지 확인할 수 있다.
— ** where/connect by 차이점
— ** 파싱순서 : start with -> connect by -> where
-- 예제) 사장실부터 시작해서 하위부서 연결
select d.*, level
from dept2 d
start with dcode = '0001'
connect by pdept = prior dcode;-- 예제) department 내에 있는 학과끼리의 상하관계를 표현
-- 단, 공과대학과 인문대학이 root node가 되도록
select d.*, level,
lpad(' ',(level-1)*2)||dname
from department d
start with part is null
connect by prior deptno = part;-- 예제) dept2 테이블을 사용하여 H/W지원 팀으로부터의 상위부서 출력
-- 단, H/W지원팀이 root node가 되도록
select d.*, level,
lpad(' ',(level-1)*2)||dname
from dept2 d
start with dcode = '1004'
connect by prior pdept = dcode-- 문제) 다음 sQL 실행 결과로 가장 적절한 것은?
select d.*, level
from dept2 D
where area='서울지사'
start with pdept is null
connect by pdept = prior dcode;
-- 사장실이 서울지사가 아니라서 안 나오네.
select d.*, level
from dept2 D
start with pdept is null
connect by pdept = prior dcode and area='서울지사';
-- 여기서는 사장실이 나오고 영업3팀은 안 나오네. 영업3팀 안나올건 예측했는데 사장실이 나올 줄 몰랐다
-- start with절은 where절로 짤리는 거 아니면 무조건 출력— [계층형 질의 추가 함수 / 가상컬럼]
— 1) 가상 컬럼
level; : 트리구조의 각 단계 표현한 숫자(depth)
connect_by_isleaf; : leaf node 여부
connect_by_iscycle; : 순환구조 여부. 순환이 시작되는 부분이 1이 된다.
— 2) 가상 함수
connect_by_root 컬럼명; : root node의 특정 컬럼값 출력
sys_connect_by_path(컬럼, 구분자); : root node부터 현재 node까지의 모든 연결과정 출력
order siblings by 컬럼 : 레벨 같은 행들 중에서 order by;
-- 활용예)
select dname, level,
lpad(' ',(level-1)*2)||dname as "부서명-----",
connect_by_isleaf,
connect_by_root dname,
sys_connect_by_path(dname,'-')
from dept2 D
start with pdept is null
connect by prior dcode = pdept
order siblings by dname desc;— ** nocycle 옵션
— connect by nocycle – 이렇게 사용하는 옵션
— 순환구조를 깨기 위함
-- 예제) 순환구조를 갖는 경우의 계층형 질의 출력결과
create table dept3
as
select * from dept2 where dname = '사장실';
insert into dept3 values('0002','경영지원부','0001','서울본사');
update dept3 set pdept = '0002' where dcode = '0001';
select * from dept3;
commit;
select d.*, connect_by_iscycle
from dept3 d
start with dcode = '0001'
connect by pdept = prior dcode;
-- error : connect by 루프 발생
select d.*, connect_by_iscycle
from dept3 d
start with dcode = '0001'
connect by nocycle pdept = prior dcode;
-- 정상출력— [그룹 함수]
— 소계 출력 함수
— group by절에 전달
— 1. grouping sets(A,B) : A, B
— 나열 순서 중요X & 전체 소계 출력X
— 예) group by grouping sets(deptno,job) -> deptno별 그룹연산 결과+job별 그룹연산 결과
— 2. rollup(A,B) : A, (A,B), (전체소계)
— 나열 순서 중요 & 전체 소계 출력
— 예) group by rollup(deptno,job) -> deptno별 그룹연산 결과+deptno,job별 그룹연산 결과+전체소계
— 3. cube sets(A,B) : A, B, (A,B), (전체소계)
— 나열 순서 중요X & 전체 소계 출력
— 예) group by cube sets(deptno,job) -> deptno별 결과+job별 결과+deptno,job별 결과+전체소계
-- 예) 부서별 급여 총합 + 업무별 급여 총합 + 전체 급여 총합
-- union all 사용
select deptno as deptno, null as job, sum(sal) as 급여총합
from EMP
group by deptno
union all
select null, job, sum(sal)
from EMP
group by job
union all
select null, null, sum(sal)
from emp;
-- grouping sets 사용
select deptno, job, sum(sal) as 급여총합
from EMP
group by grouping sets(deptno, job, null);
--또는 null대신 () 사용
select deptno, job, sum(sal) as 급여총합
from EMP
group by grouping sets(deptno, job, ());-- 예제) emp에서 deptno별, deptno + job별, 전체 급여 총합 출력
-- union all 사용
select deptno, null as job, sum(sal) as sum_sal
from EMP
group by deptno
union all
select deptno, job, sum(sal)
from EMP
group by deptno, job
union all
select null, null, sum(sal)
from emp;
-- rollup 사용
select deptno, job, sum(sal)
from EMP
group by rollup(deptno, job);-- 예제) emp에서 deptno별, job별, deptno+job별, 전체 급여 총합 출력
-- union all 사용
select deptno, null as job, sum(sal) as sum_sal
from EMP
group by deptno
union all
select null, job, sum(sal)
from EMP
group by job
union all
select deptno, job, sum(sal)
from EMP
group by deptno, job
union all
select null, null, sum(sal)
from emp;
-- cube 사용
select deptno, job, sum(sal)
from EMP
group by cube(deptno, job);
-- grouping sets 사용
select deptno, job, sum(sal)
from emp
group by grouping sets(deptno, job, (deptno, job), ());— [pivot / unpivot]
— 1. pivot
— long data -> wide data
— unstack은 위쪽에 존재하는 컬럼
— stack은 왼쪽에 존재하는 행
— value는 가운데에 값들 의미.
select
from 테이블명 또는 서브쿼리(unstack,stack,value 컬럼만 여기 있어야됨)
pivot(value값 for unstack컬럼명 in(값1, 값2,…));
— value값에는 집계함수 형태로 들어감(sum, avg, count, …)
— from절에는 unstack,stack,value 컬럼만 들어가야함
— from절에 존재하는 컬럼 중 unstack, value 컬럼 뺀 모든 컬럼이 stack처리된다
-- 예제) emp에서 각 부서내 job별로 인원 수 파악
select *
from (select empno, job, deptno from emp)
pivot(count(empno) as 인원수
for deptno in (10, 20, 30));
-- 또는! * 사용하기. 대신 empno 빼기
select *
from (select job, deptno from emp)
pivot(count(*) as 인원수
for deptno in (10, 20, 30));
-- 또는! deptno로 세기
select *
from (select job, deptno from emp)
pivot(count(deptno) as 인원수
for deptno in (10, 20, 30));
-- 근데 job을 count하는 건 안됨.
-- stack 컬럼은 value 컬럼 자리에 들어갈 수 없음-- 예제) 아래 테이블 생성 후 dcast_ex2.csv 파일 로딩 후
-- 연도별, 메뉴별 판매수량에 대한 총합을 교차표형식으로 출력
create table pivot1(
year number,
name varchar2(10),
qty number,
price number);
select * from pivot1;
select *
from (select year, name, qty from pivot1)
pivot(count(qty) for name in ('latte','mocha','americano'));
select *
from (select year, name, qty from pivot1)
pivot(count(qty) for year in ('2000','2001'));— 2. unpivot
— wide data -> long data