1. ROW ---------> COLUMN : PIVOT 문 (SUM+DECODE 함수) 2. COLUMN ---> ROW: UNPIVOT 문
Q1. 부서번호, 부서번호 별 토탈월급을 가로로 출력하시오.
select sum(decode(deptno, 10,sal, null)) as "10",
sum(decode(deptno, 20, sal, null)) as "20",
sum(decode(deptno, 30, sal, null)) as "30"
from emp;
** 가로 컬럼 (부서번호_10,20,30)를 알아야 sql을 짤 수 있다는 것이 단점 (PL/SQL, R, 파이썬으로는 쉽게 가능함)
[PIVOT 사용]
select *
from (select deptno, sal from emp)
pivot (sum(sal) for deptno in (10,20,30) );
- SUM+DECODE 를 사용했 을 때보다 심플학 SQL 작성 가능 - SUB쿼리는 EMP테이블의 모든 컬럼을 가져오는 게 아니고 부서번호와 월급만 조회에서 가져옴 - 가로로 되길 원하는 컬럼을 SUB쿼리에 넣음 (ex. 부서번호별 토탈월급을 가로로 출력하는 결과를 보기위해서 필요한 데이터가 deptno와 sal만 필요로하지 다른 job, hiredate등이 필요하지 않음.)
select "10","20"
from (select deptno, sal from emp)
pivot (sum(sal) for deptno in (10,20,30) );
- 특정 데이터 열만 보고싶을 경우 select 다음 * 이 아닌 특정 데이터를 넣는다.(권장하지는 않음)
문제1. 부서번호, 부서번호별 최대월급을 가로로 출력하시오.
select *
from (select deptno, sal from emp)
pivot(max(sal) for deptno in(10,20,30));
문제2. 부서번호, 부서번호별 최대월급을 세로로출력하시오.
select deptno, max(sal)
from emp
group by deptno;
문제3. 부서번호, 부서번호별 평균월급을 가로출력하시오.
select *
from (select deptno, sal from emp)
pivot (avg(sal) for deptno in(10,20,30))
;
** pivot 절 안에는 round를 쓸수 없음(select 절에 가능)
select round("10"),round("20"),round("30")
from (select deptno, sal from emp)
pivot (avg(sal) for deptno in(10,20,30))
;
# as 사용할 때 유의
select round("10"),round("20"),round("30")
from (select deptno as 부서번호, sal from emp)
pivot (avg(sal) for 부서번호 in(10,20,30))
;
문제 4. 직업 중복을 제거하여 출력하시오.
select distinct job
from emp;
문제 5. 직업, 직업별 토탈월급을 출력하시오(세로)
select job, sum(sal)
from emp
group by job;
문제 6. 위 문제를 pivot을 사용하 가로로 출력하시오.
select *
from (select job, sal from emp)
pivot(sum(sal) for job in('ANALYST','CLERK','MANAGER','SALESMAN','PRESIDENT'));
select *
from (select job, sal from emp)
pivot(sum(sal) for job
in('ANALYST' as "ANALYST",'CLERK'as"CLERK",'MANAGER'as"MANAGER",'SALESMAN'as"SALESMAN",'PRESIDENT'as"PRESIDENT"));
문제 7.(복습) EMP19에서 성별, 성별 별 토탈나이를 세로로 출력하시오.
select gender, sum(age)
from emp19
where gender is not null
group by gender
;
문제 8. (복습) 위 결과를 가로로 출력하시오.
select *
from (select gender, age from emp19)
pivot( sum(age) for gender
in ('남' as"남",'여'as"여") );
문제9. 위 sql을 이용해 토탈나이가 아니라 평균나이로 추출하시오.
select round(avg(decode(gender, '남', age)) )as"남",
round(avg(decode(gender, '여', age)))as"여"
from emp19;
select round("남")as 남, round("여") as 여
from (select gender, age from emp19)
pivot( avg(age) for gender
in ('남' as"남",'여'as"여") );
문제 10. 세로로는 통신사, 가로로는 성별로 출력하는데 통신사별 평균나이를 출력하시오.
select lower(telecom),round(avg(decode(gender, '남', age)) )as"남",
round(avg(decode(gender, '여', age)))as"여"
from emp19
where telecom is not null
group by lower(telecom) ;
문제 17. 위 sql앞에 주소3글자를 출력하여 사는곳 별 통신사별 인원수를 다음과 같이 추출하시오.
select substr(address,1,3),
count(decode(lower(telecom), 'sk', ename) )as "sk",
count(decode(lower(telecom), 'kt', ename) )as "kt",
count(decode(lower(telecom), 'lg', ename) )as "lg",
count(decode(lower(telecom), 'lg알뜰', ename) )as "lg알뜰"
from emp19
where substr(address,1,3) is not null
group by substr(address,1,3);
문제 18. 위의 결과를 통신사가 아니라 성별로 추출하시오.
select substr(address,1,3)as 주소,
count(decode(gender, '여', ename) )as "여",
count(decode(gender, '남', ename) )as "남"
from emp19
where substr(address,1,3) is not null
group by substr(address,1,3);
문제 19. (난이도중) 위의 결과를 다음과 같이 나오게 하시오
select substr(address,1,3)as 주소,
count(decode(gender, '남', ename) )as "남",
count(decode(gender, '여', ename) )as "여",
count(*) as 총괄
from emp19
where substr(address,1,3) is not null
group by substr(address,1,3);
★ 중간 복습 문제 _점심시간★ 우리반 테이블에서 나이를 출력하고 나이별로 해당하는 학생들의 이름으로 가로로 출력하는데 이름 옆에 괄호 열고 성별이 출력되게 하시오.
select age,
listagg(ename||'('||gender||')',',')
from emp19
where age is not null
group by age;
2. unpivot문: column(컬럼) ---->row(행) ex) 절도가 많이 일어나느 요일이 언제인가? -> 컬럼이 데이터로 구성되어야 작성할 수 있다.
문제1. order2테이블을 이용하여 smith는 자전거를 몇대 가지고 있는지 조회하시오.
select bicycle
from order2
where ename='SMITH';
문제2. smith가 3대를 가지고있는건 무엇인가? (unpivot 사용)
select *
from order2
unpivot(건수 for 아이템 in(bicycle, camera, notebook));
임의로 작성한 '건수', '아이템' 이 컬럼명이됨
문제3. 위 결과를 order3 테이블로 만드시오.
create table order3
as
select *
from order2
unpivot(건수 for 아이템 in(bicycle, camera, notebook)) ;
select * from order3;
문제4. order3 테이블에서 이름이 smith 인 사원이 3개를 가지고 있는것을 추출하시오.
select ename,아이템,건수
from order3
where ename= 'SMITH' and 건수 = 3
문제5. crime_time의 컬럼을 unpivot문을 이용해서 데이터로 만드시오.
select *
from crime_time
unpivot(건수 for 시간 in(F0T3, F3T6, F6T9, F9T12, F12T15,F15T18,F18T21,F21T24));
컬럼명 나열하는 함수
DESC CRIME_TIME;
문제6. 위에서 출력되고 있는 결과를 crime_time2라는 테이블로 생성하시오.
DESC CRIME_TIME;
drop table crime_time3;
create table crime_time2
as
select *
from crime_time
unpivot(건수 for 시간 in(F0T3, F3T6, F6T9, F9T12, F12T15,F15T18,F18T21,F21T24));
commit;
select * from crime_time2;
문제7. crime_time2를 조회해서 살인이 가장 많이 일어나는 시간대를 출력하시오.
select *
from crime_time2
where crime_type ='살인'
order by 건수 desc fetch first 1 rows only;
또는
select crime_type, 시간, 건수, rank() over (order by 건수 desc nulls last) 순위
from crime_time2
where crime_type ='살인';
문제8. 선거범죄가 많이 일어나는 시간대를 1-3위 까지 출력하시오.
select *
from crime_time2
where crime_type ='선거범죄'
order by 건수 desc fetch first 3 rows only;
★★ 문제9. (복습문제+내일 쪽지 시험 5문제중 하나 형식) 이름과 커미션과 커미션에 대한 순위를 출력하는데 커미션에 대한 순위를 출력할 때 같은 순위가 2명이면 바로 그 다음 순위가 출력되게 하시오 !
select ename, comm, dense_rank() over(order by comm desc nulls last)
from emp;
★★ 문제 10. (복습문제 + 내일 쪽지 시험 5문제중 하나 형식) 직업이 SALESMAN 인 사원들의 부서번호를 출력하는데 중복을 제거해서 출력하시오 !
select distinct deptno
from emp
where job ='SALESMAN';
★★ 문제11. (쪽지시험 5문제중 하나 형식) 오늘날짜에서 100 달 뒤에 돌아오는 금요일의 날짜를 출력하시오 !
select next_day(add_months(TO_DATE('2024-05-22','RRRR-MM-DD'),100), '금요일')
from dual;
select next_day(add_months(TO_DATE(SYSDATE),100), '금요일')
from dual;
50. SUMOVER_누적데이터
예제50. 데이터 분석 함수로 누적 데이터 출력하기(SUM OVER) Q1. 사원 테이블에서 토탈월급을 출력하시오 !
select sum(sal) from emp;
문제1. 사원 테이블에서 사원이름, 월급, 사원 테이블의 토탈월급이 출력되게 하시오 !
select ename, sal, sum(sal)
from emp
group by ename, sal;
** 뮨에러는 나지않지만 원하는결과는아님
select ename, sal, sum(sal) over ()as "토탈월급"
from emp;
group by ename, sal;
괄호안에 뭔가를 써줄 수 있는데 아무것도 안쓰면 전체 토탈월급이 출력됩니다.
문제 2. 이름, 월급, 월급의 누적치를 출력하시오.
select ename, sal, sum(sal) over (order by sal asc) as 누적치
from emp;
문제 3. 사원번호, 이름, 월급, 월급의 누적치를 출력하는데 사원번호 순으로 정렬해서 월급의 누적치가 출력되게하시오.
select ename, sal, sum(sal) over(order by empno asc) as 누적치
from emp;
문제 4. 위의 결과에서 직업이 salesman인사원들의 데이터만 출력되게하시오.
select ename, job,sal, sum(sal) over(order by empno asc) as 누적치
from emp
where job='SALESMAN';
위 누적되는 SQL문법은 간략하게 작성한것이고 좀더 자세하게 기술하고 싶으면 다음과 같이 작성해야한다.
select ename, job,sal, sum(sal) over(order by empno rows
between unbounded preceding and current row) 누적치
from emp
where job='SALESMAN';
윈도우 기준
윈도우방식
설명
ROWS
UNBOUNDED PRECEDING
맨 첫번째 행을 가르킴
UNBOUNDED FOLLOWING
맨 마지막을 가르킴
CURRENT ROW
현재 행을 가르킴
문제 5. 우리반 테이블에서 이름, 나이, 나이의 누적치를 출력하는데 학생 번호 순서로 정렬되어 출력되면서 나이가 누적되게하시오,
select ename, age, sum(age) over(order by empno asc) 누적치
from emp19;
또는
select ename, age, sum(age) over(order by empno rows between unbounded preceding and current row) 누적치
from emp19;
문제 6. 위의 결과를 다시 추출하는데 성씨가 김, 이,박씨만출력되게하시오.
select ename, age, sum(age) over(order by empno rows between unbounded preceding and current row) 누적치
from emp19
where substr(ename,1,1) in('김','이','박')
and age is not null;
※ 누적데이터 구하는 문법에도 partition by를 쓸 수 있다.
문제 7. 부서번호, 이름, 월급, 월급의 누적치를 출력하시오.
select deptno, ename, sum(sal) over(order by empno asc rows between unbounded preceding and current row)누적치
from emp;
문제 8. 위의 결과에서 부서번호별 각 파티션해서 월급이 누적되게하시오.
select deptno, ename, sum(sal) over(partition by deptno order by empno asc rows between unbounded preceding and current row)누적치
from emp;
문제 9. 성별, 이름, 나이, 나이의 누적치를 출력하는데 성별별로 각각 나이가 누적되게하고 나이가 낮은 학생부터 높은 학생순으로 정렬해서 출력하시오
select age, ename, age, sum(age) over(order by age asc rows between unbounded preceding and current row) as 누적치
from emp19
where age is not null;
문제10. 통신사, 이름, 나이, 나이의 누적치가 출력되게하는데 나이가 낮은 학생부터 높은 학생 순으로 누적이 되게하고 통신사별로 각각 나이를 누적시키시
select lower(telecom), ename, age, sum(age)over(partition by lower(telecom)order by age asc rows
between unbounded preceding
and current row) 누적치
from emp19;
문제11. 사는지역(주소앞 3글자), 이름, 나이, 나이의 누적치를 출력하는데 나이의 누적치를 출력할 때 사는 지역별로 각각 나이의 누적치가 출력되게하시오.
select substr(address,1,3), ename, age, sum(age) over (partition by substr(address,1,3) order by age asc rows
between unbounded preceding and current row) 순위
from emp19;
문제 12. 입사한 년도 4자리, 이름, 월금, 월급의 누적치를 출력하는데 입사한 연도별로 각각 월급이 누적되게하고 월급이 낮은 사원부터 높은 사원순으로 누적되게하시오.
select to_char(hiredate,'RRRR')as 입사년도, ename, sal, sum(sal) over(partition by to_char(hiredate,'RRRR') order by sal asc rows
between unbounded preceding and current row) 누적치
from emp;
문제 1. 직업, 직업별 인원수를 가로로 출력하시오.
select count(decode(job, 'SALESMAN', empno)) as "SALESMAN", count(decode(job, 'CLERK', empno )) as "CLERK", count(decode(job, 'MANAGER', empno )) as "MANAGER", count(decode(job, 'PRESIDENT', empno )) as "PRESIDENT", count(decode(job, 'ANALYST', empno )) as "ANALYST" from emp;
문제 2. 부서번호,부서번호별 토탈워릅을 출력하는데 부서번호가 10번은 제외하고 부서번호별 토탈월급이 4000이상인 것만 출력하고 부서별 토탈월급이 높은 것 부터 출력하시오. select deptno, sum(sal) from emp where deptno <>'10' group by deptno having sum(sal) >=4000 order by sum(sal) desc;
문제3. 이름과 커미션과 커미션에 대한 순위를 출력하는데 커미션에 대한 순위를 출력할 때 같은 순위가 2명이면 바로 그 다음 순위가 출력되게하시오.
select ename, comm, dense_rank()over(order by comm desc nulls last) as 순위 from emp where comm is not null;
문제4. 오늘날짜에서 100달뒤에 돌아오는 금요일의 날짜를 출력하시오.
select next_day(add_months(sysdate,100),'금요일') from dual;
문제 5. 직업이 salesman인 사원들의 부서번호를 출력하는데 중복을 제거해서 출력하시오.
select distinct deptno from emp where job<>'SALESMAN';
