sql 기본 / 데이터분석함수(dense_rank, ntile, cume_diste, listagg, lad, lead, sum+decode, pivot)

042 데이터 분석 함수로 순위 출력하기 2(DENSE_RANK)

/*이름과 직업과 월급과 순위를 출력하는데 
그 옆에 순위가 동일한 사람이 여러명인 경우 바로 다음 순위가 출력되게 하시오*/
select ename, job, sal, rank() over (order by sal desc) as 순위, 
                        dense_rank() over (order by sal desc) 순위2
 from emp
 where job in ( 'ANALYST','SALESMAN');

문제173. 부서번호, 이름, 입사일, 먼저 입사한 사원순으로 순위를 출력하시오(dense_rank를 사용)

select deptno, ename, hiredate, dense_rank() over (order by hiredate asc) 순위
 from emp;

문제174. 위의 결과를 다시 출력하는데, 부서번호별로 각각 먼저 입사한 사원순으로 순위를 부여하시오.

select deptno, ename, hiredate, dense_rank() over (partition by deptno
                                                    order by hiredate asc) 순위
 from emp;
 
/*partition by 는 분석함수 사용시 over () 괄호안에 쓰는 문법인데,
분류별로 각각 순위를 부여할 때 사용하는 키워드 < rank 함수에서만 씀 */

문제175. 직업, 이름, 월급, 직업별 월급이 높은 순위를 출력하시오

select job, ename, sal, dense_rank() over (partition by job order by sal desc) 순위
 from emp;

문제176. 통신사, 이름, 나이, 순위를 출력하는데 순위가 통신사별로 각각 나이가 높은 순으로 출력하시오.

select decode(lower(telecom),'skt','sk', lower(telecom)) 통신사, ename, age, 
      dense_rank() over (partition by decode(lower(telecom),'skt','sk', lower(telecom)) 
                         order by age desc) 순위
 from emp14;

 

 

043 데이터 분석 함수로 등급 출력하기(NTILE)

/* 직업이 ANALYST, MANAGER, CLERK인 사원들의 이름과 직업과 월급과 등급을 출력하는데 
   등급을 4등급으로 나눠서 출력하세요*/
select ename, job, sal, ntile(4) over (order by sal desc) 등급
 from emp;

문제177. 부서번호, 이름, 입사일, 입사한 사원순으로 5등급으로 나누는데, 부서번호별로 각각 등급을 부여하시오

select deptno, ename, hiredate, ntile(5) over (partition by deptno order by hiredate asc) 등급
 from emp;

문제178. class_type, 이름, 나이, 등급을 출력하는데 class_type별로 나이가 높은 순으로 3등급을 부여하시오 

select class_type, ename, age, ntile(3) over (partition by class_type order by age desc) 등급
 from emp14;

 

 

044 데이터 분석 함수로 순위의 비율 출력하기(CUME_DIST)

--이름과 월급과 순위과 자신의 월급의 순위에 대한 비율을 출력하세요
select ename, sal, dense_rank() over (order by sal desc) as 순위, 
                   cume_dist() over (order by sal desc) 비율
 from emp;

문제179. 이름, 나이, 주소를 출력하고 class_type별로 나이가 높은 순서대로 순위와 비율을 출력하시오

select class_type, ename, age, address, dense_rank() over (partition by class_type 
                                                           order by age desc) 순위,
                                        cume_dist() over (partition by class_type 
                                                           order by age desc) 비율
 from emp14;

문제180. 이름, 주소, 주소의 첫번째 공백전까지의 글자를 출력하시오 

select ename, address, regexp_substr(address, '[^ ]+', 1, 1)
 from emp14;
 
/* 
regular expression (정규식표현) substr
 ^ : not
 + : 여러 개 
 [^ ]+ : 공백이 아닌 게 여러개 있다는 뜻
 (~, 1,1) = 1번부터 찾아서 1을 찾으라는 뜻 ex) (~, 1,2) = 1번부터 찾아서 2를 찾음
 */

select ename, address, substr(address, 1, instr(address, ' ')-1)
 from emp14;

문제181. 이름, 잘라낸 주소를 출력하는데 잘라낸 주소를 asc하게 출력하시오

select ename, regexp_substr(address, '[^ ]+', 1, 1)
 from emp14
 order by 2 asc;

문제182. 서울특별시 > 서울시, 인천광역시 > 인천시 이름과 잘라낸 주소를 출력하시오 

select ename, decode(regexp_substr(address, '[^ ]+', 1, 1), '서울특별시','서울시','인천광역시','인천시',
                     regexp_substr(address, '[^ ]+', 1, 1)) as 주소
 from emp14
 order by 2 asc;

 

ㅁ view 생성 : 복잡한 sql을 단순하게 검색하고 싶을때 view를 만들어 사용

create view emp14_address
as
select ename, decode(regexp_substr(address, '[^ ]+', 1, 1), '서울특별시','서울시','인천광역시','인천시',
                      regexp_substr(address, '[^ ]+', 1, 1)) as address2
 from emp14
 order by 2 asc;

 

문제183. emp14_address로 쿼리를 하는데 이름, 나이, address2를 출력하고, 그 옆에 순위를 출력하는데 address2별로 각각 나이가 많은 순서대로 출력하시오.

select ename, age, address2, dense_rank() over (partition by address2
                                                  order by age desc) 순위
 from emp14_address;

 

 

045 데이터 분석 함수로 데이터를 가로로 출력하기(LISTAGG)

listagg(컬럼명, 구분기호)

group by 

# listagg를 정렬하고 싶다면  listagg(ename,',') within group (order by sal desc) 식으로 작성

--부서번호를 출력하고 해당 부서별로 속한 사원들의 이름을 가로로 출력하세요
select deptno, listagg(ename,',') within group (order by ename asc)
 from emp
 group by deptno;

ㅇlistagg는 데이터 분석함수 중 유일하게 group by 절이 필요함.

문제184. 직업, 직업별로 속한 사원들의 이름을 가로로 출력하는데 월급이 높은순으로 출력하시오.

select job, listagg(ename, ',') within group (order by sal desc)
 from emp
 group by job;

문제185. emp14_address를 쿼리하는데 address2에 속한 학생들의 이름을 가로로 출력하시오. 단, 나이가 높은 순으로 정렬

select address2, listagg(ename, ',') within group (order by age desc)
 from emp14_address
 group by address2;

문제186. (점심시간 문제) 다음과 같이 결과를 출력하세요. 

강원도    차은재(27)

경기도    전근호(32), 노명철(31),......

select address2, listagg(ename||'('||age||')', ',') within group (order by age desc) as 이름
 from emp14_address
 group by address2;

 

046 데이터 분석 함수로 바로 전 행과 다음 행 출력하기(LAG, LEAD)

lag(컬럼명, 전 행의 위치)* : 이전 행 출력

lead(컬럼명, 다음행의 위치)* : 다음 행 출력

* : 1인 경우 바로 전 행, 2인 경우 전전행, 3인 경우 전전전행 (다음행도 마찬가지)

보통 값차이를 출력하고 싶을 때 사용

/* 직업이 ANALYST, MANAGER인 사원들의 사원번호와 이름과 월급을 출력하는데 
 다음과 같이 월급의 그 전행과 그 다음행이 출력되게 하세요*/
select empno, ename, sal, lag(sal,1) over (order by sal asc) 이전행,
                          lead(sal,1) over (order by sal asc) 다음행
 from emp;

문제187. 이름, 입사일, 바로 전에 입사한 사원과의 간격일을 출력하시오

select ename, hiredate, hiredate - lag(hiredate, 1) over (order by hiredate asc)
 from emp;

문제188. 우리반데이터에서 이름, 생일, 생일과의 간격일을 출력하시오 

select ename, birth, birth - lag(birth,1) over (order by birth asc)
 from emp14;

 

 

047 ROW를 COLUMN으로 출력하기 1(SUM+DECODE)

--부서번호와 부서번호별 토탈월급을 출력하는데 다음과 같이 가로로 출력하세요
select deptno, sum(sal)
 from emp
 group by deptno;
 --세로로 출력됨
 
select sum(decode(deptno, 10, sal, 0)) as "10",
       sum(decode(deptno, 20, sal, 0)) as "20",
       sum(decode(deptno, 30, sal, 0)) as "30"
 from emp;

 

ㅁ 회사의 서버 시스템 구조

OLTP서버(OnLine Transaction Processing) -> 실시간 데이터 처리

DW서버(Data Ware house, 데이터 창고) -> 히스토리성 데이터 저장 < 보통 이 서버를 통해 데이터분석 수행

 

문제190. 부서번호, 보너스를 출력하는데 부서번호가 10번이면 자기자신의 월급이 출력되게 하고 그렇지 않으면 그냥 0이 출력되게 하시오 

select deptno, decode(deptno, 10, sal, 0) 보너스
 from emp;

문제191. 부서번호, 부서번호별 토탈월급을 가로로 출력하시오

select sum(decode(deptno, 10, sal, 0)) as "10",
       sum(decode(deptno, 20, sal, 0)) as "20",
       sum(decode(deptno, 30, sal, 0)) as "30"
 from emp;

문제192. 우리반 테이블에서 class_type, class_type별 토탈나이를 세로로 출력하시오

select class_type, sum(age) as 나이
 from emp14
 group by class_type;

문제193. 위의 결과를 가로로 출력하시오

select sum(decode(class_type, 'A', age,0)) as A,
       sum(decode(class_type, 'B', age,0)) as B
 from emp14;

문제194. 직업, 직업별 토탈월급을 세로로 출력하시오

select job, sum(sal)
 from emp
 group by job;

문제195. 위의 결과를 가로로 출력하시오

select sum(decode(job, 'SALESMAN',sal,0)) as SALESMAN,
       sum(decode(job, 'CLERK',sal,0)) as CLERK,
       sum(decode(job, 'ANALYST',sal,0)) as ANALYST,
       sum(decode(job, 'MANAGER',sal,0)) as MANAGER,
       sum(decode(job, 'PRESIDENT',sal,0)) as PRESIDENT
 from emp;

ㅇ그룹함수는 null값을 무시함

위의 SQL의 속도를 높이는 방법

select sum(decode(job, 'SALESMAN',sal,null)) as SALESMAN,
       sum(decode(job, 'CLERK',sal,null)) as CLERK,
       sum(decode(job, 'ANALYST',sal,null)) as ANALYST,
       sum(decode(job, 'MANAGER',sal,null)) as MANAGER,
       sum(decode(job, 'PRESIDENT',sal,null)) as PRESIDENT
 from emp;

 

 

문제196. (복습문제) 직업이 ANALYST인 사원들의 이름과 월급과 직업과 입사일을 출력하는데 입사일이 최근에 입사한 순서대로 출력하시오

select ename, sal, job, hiredate
 from emp
 where job = 'ANALYST'
 order by hiredate desc;

문제197. (복습문제) 월급이 1000에서 4000 사이인 사원들의 이름과 월급과 직업을 출력한는데 월급이 높은 사원부터 출력하시오

select ename, sal, job
 from emp
 where sal between 1000 and 4000
 order by sal desc;

문제198. (복습문제) 직업, 직업별 인원수를 출력하는데 직업별인원수가 3명 이상인것만 출력하시오

select job, count(*)
 from emp
 group by job
 having count(*) >= 3;

문제199. (복습문제) 부서번호, 부서번호별 토탈월급을 출력하는데 천단위 표시를 하시오

select deptno, to_char(sum(sal),'999,999,999') as 토탈월급
 from emp
 group by deptno

문제200. (복습문제) 이름, 입사일, 입사한 년도를 4자리로 출력하시오

select ename, hiredate, to_char(hiredate, 'RRRR')
 from emp;

문제201. (복습문제) 입사한 년도(4자리), 입사한 년도별 토탈월급을 출력하시오(세로출력)

select to_char(hiredate,'RRRR'), sum(sal)
 from emp
 group by to_char(hiredate,'RRRR');

문제202. 입사한 년도(4자리), 입사한 년도별 토탈월급을 출력하시오. 단, 가로출력

select sum(decode(to_char(hiredate,'RRRR'),'1980',sal,null)) as "1980",
       sum(decode(to_char(hiredate,'RRRR'),'1981',sal,null)) as "1981",
       sum(decode(to_char(hiredate,'RRRR'),'1982',sal,null)) as "1982",
       sum(decode(to_char(hiredate,'RRRR'),'1983',sal,null)) as "1983"
from emp;

 

 

048 ROW를 COLUMN으로 출력하기 2(PIVOT)

행을 컬럼으로 출력하는 함수 : pivot

컬럼을 행으로 출력하는 함수 : unpivot

--부서번호, 부서번호별 토탈월급을 가로로 출력하시오
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;
 
--수식이 너무 길어짐 >  PIVOT함수로 구현가능

--일단, 테이블에서 컬럼 선별해서 가져오기
select *
 from ( select deptno, sal from emp );
 
--pivot문 넣기 
select *
 from ( select deptno, sal from emp )
 pivot ( sum(sal) for deptno in (10,20,30));
/* pivot은 회전하다는 뜻 > 가로로 회전된 결과를 보겠다는 의미
 토탈월급을 출력하는데 부서번호에 대한(for) 각각의 결과를(in (컬럼명))보겠다*/

문제203. 우리반 테이블에서 쿼리를 작성하는데 class_type, class_type별 토탈나이를 출력하시오(세로출력)

select class_type, sum(age)
 from emp14
 group by class_type;

문제204. 위의 결과를 가로로 출력하시오

select *
 from ( select class_type, age from emp14)
 pivot ( sum(age) for class_type in ('A' as "A",'B' as "B"));
 
 -- as "A" > A로만 출력됨

문제205. 직업, 직업별 토탈월급을 출력하시오

select job, sum(sal)
 from emp
 group by job;

문제206. 위의 결과를 가로로 출력하시오(pivot문)

select *
 from (select job, sal from emp)
 pivot (sum(sal) for job in ('SALESMAN','MANAGER','CLERK','ANALYST','PRESIDENT'));

문제207. (오늘의 마지막 문제) emp14_address를 조회해서 아래와 같이 지역, 지역별 평균나이를 출력하시오 

select round(강원도)as "강원도",
       round(경기도) as "경기도", 
       round(서울시) as "서울시",
       round(울산) as "울산", 
       round(인천시) as "인천시", 
       round(충청북도) as "충청북도"
 from (select age, address2 from emp14_address)
 pivot(avg(age) for address2 in ('강원도' as "강원도", 
                                 '경기도' as "경기도",
                                 '서울시' as "서울시",
                                 '울산' as "울산", 
                                 '인천시' as "인천시", 
                                 '충청북도' as "충청북도"));