★점심문제: 사원 테이블에서 입사한 년도(4자리)를 출력하고 입사한 년도별 인원수를 출력하는데1980년은 제외하고 출력하고 입사한 년도별 인원수가 2명 이상인것만 출력하고 입사한 년도별 인원수가 높은것 부터 출력하시오 !
select to_char(hiredate, 'RRRR')as 입사년도, count(*)
from emp
where to_char(hiredate, 'RRRR')<>'1980'
group by to_char(hiredate, 'RRRR')
having count(*)>= 2
order by to_char(hiredate, 'RRRR') desc;
[TIL 9] _240523
★ 누적치를 구하는 분석 함수의 옵션들
문법: select ename, sal, sum(sal) over (order by sal asc(desc) rows(range) between unbounded preceding and current row) as 누적치 from emp;
옵션: order by 컬럼 asc/desc , rows/ range
rows: 행을 기준으로 누적치를 출력
range: 범위를 기준으로 누적치를 출력
unbounded preceding: 맨 마지막 행
current row: 현재 행
rows: 행의 기준으로 누적치를 구하는것
range: 범위르 기준으로 누적치를 구하는 것
Q1. range 를 사용하여 누적 데이터 출력하기
select ename, sal,
sum(sal) over(order by sal asc range
between unbounded preceding
and current row) as 누적치
from emp;
- 누적 데이터에서 5350 이 2번 나오고 있습니다.
- rows 옵션처럼 행의 값을 기준으로 누적하는게 아니라 값의 범위에 따라 누적하고 있습니다.
* 범위(range) 를 기준으로 range 윈도우 기준을 테스트하기
create table emp2
as
select *
from emp
order by deptno asc;
select * from emp2;
Q2. emp2 테이블의 사원들의 입사일을 부서번호별로 각각 다음과 같이 변경하시오.
update emp2
set hiredate ='81/01/05'
where deptno =10;
update emp2
set hiredate ='81/02/17'
where deptno =20;
update emp2
set hiredate ='81/03/21'
where deptno =30;
commit;
select deptno, job, sum(sal)
from emp
group by deptno, job;
문제 3. ( 올리브영에서 많이 사용하는 SQL) 위의 결과를 다시 출력하는데 다음과 같이 rollup을 같이써서 출력하시오
select deptno, job, sum(sal)
from emp
group by rollup( deptno, job );
설명: group by rollup(deptno, job) 이라고 하게 되면 3가지의 grouping 된 결과가 출력됩니다. 다음 3가지 입니다. 1. deptno, job 2. deptno 3. 전체
=> rollup 괄호안에 컬럼을 2개를 썼으면 grouping 된 결과가 3개가 나옵니다. rollup 괄호안에 적은 컬럼의 갯수 + 1개 만큼 grouping 된 결과가 출력되는 것.
문제5. 위의 결과를 다음과 같이 추출하시오.
select deptno, nvl(job,'부서 토탈'), sum(sal)
from emp
group by rollup( deptno, job );
★ 문제 6. ( 진짜 올리브영에서 많이 사용하는 SQL) 위의 결과를 다시 출력하는데 다음과 같이 출력하시오
SELECT deptno, DECODE(deptno, NULL, '전체토탈:', nvl(job, '부서토탈:')), sum(sal)
FROM emp
GROUP BY ROLLUP(deptno, job)
ORDER BY deptno;
문제7. 부서번호, 부서번호별 토탈월급을 출력하는데 맨아래쪽에 전체 토탈월급을 출력하시오.
select deptno, sum(sal)
from emp
group by rollup(deptno);
문제8. emp19에서 성별, 통신사, 성별별 통신사별 평균 나이 출력하시오.
select gender, lower(telecom), round( avg(age))
from emp19
where gender is not null
group by gender, lower(telecom)
order by gender asc;
문제9. 위의 결과에서 성별별 평균 나이도 같이 출력되게하고 전체 평균도 맨 아래에 출력되게하시오.
select gender, lower(telecom), round( avg(age))
from emp19
where gender is not null
group by rollup(gender, lower(telecom))
order by gender asc;
※ grouping 된 결과 1. gender, lower(telecom) 2. gender 3. 전체
문제 10. 위의 결과에서 평균나이-> 인원수로 출력하시오
select gender, lower(telecom), count(*)
from emp19
where gender is not null
group by rollup(gender, lower(telecom))
order by gender asc;
53. GROUPING SET
예제 54. 데이터 분석 함수 집계 결과 출력하기(GROUPING SET) = ROLLUP/CUBE 보다 결과를 예상하기 쉬워서 자주 사용하는 함수
Q1. 부서번호, 부서번호 별 토탈월급을 출력하는데 맨아래에 전체 토탈월급을 출력하시오.
1)rollup
select deptno, sum(sal)
from emp
group by rollup(deptno);
2)grouping sets
select deptno, sum(sal)
from emp
group by grouping sets(deptno,());
# grouping sets (컬럼 , ())
= () 가 전체를 나타냄
문제1. 직업과 직업별 토탈월급을 출력하는데 맨 아래 전체 토탈월급도 출력하시오
select job, sum(sal)
from emp
group by grouping sets(job,());
문제2. 위의 결과를 cube 작성과 같이 전체 토탈로 나오게하시오.
select job, sum(sal)
from emp
group by grouping sets(job,())
order by job nulls first;
문제3. 우리반 테이블에서 통신사, 통신사별 인원수를 출력하는데 전체 인원수가 맨아래가 되게 출력하시오,( grouping sets)
select nvl(lower(telecom),'전체건수'), count(*)
from emp19
where lower(telecom) is not null
group by grouping sets (lower(telecom),())
;
문제4. 아래의 rollup 결과를 grouping sets로구현하시오.
select gender, lower(telecom), count(*)
from emp19
where lower(telecom)is not null
group by rollup( gender, lower(telecom));
↓
select gender, lower(telecom), count(*)
from emp19
where gender is not null
group by grouping sets( (gender, lower(telecom)), (gender),() );
문제5. 아래의 rollup 결과를 grouping sets 로 수행하시오.
select deptno, job, sum(sal)
from emp
group by rollup(deptno, job);
↓
select deptno, job, sum(sal)
from emp
group by grouping sets((deptno, job),(deptno),())
order by deptno ;
문제5. (올리브영 요청 sql) 다음과 같이 결과를 출력하시오.
select empno, ename, sum(sal)
from emp
where empno is not null
group by grouping sets((empno,ename ),());
문제6. 이름, 커미션(null->0) 출력하시오,
select ename, nvl(comm,0)
from emp;
문제 7. 위의 결과에서 comm이 null인사원들은 no comm으로 출력되게하시오,
select ename,nvl( to_char(comm), 'no comm')
from emp;
문제8.(복습문제) 부서번호, 부서번호별 토탈월급을 출력하는데 맨 아래쪽에 전체 토탈월급을 출력하시오
select deptno, sum(sal)
from emp
group by rollup(deptno);
문제 9. 위의 결과에서 전체 토탈이라는 글씨가 아래에 출력되게하시오.
select nvl(to_char(deptno),'전체토탈:'), sum(sal)
from emp
group by rollup(deptno);
55. ROW_NUMBER
예제55. 데이터 분석 함수로 출력결과 넘버링하기(ROW_NUMBER) : ROW_NUMBER 함수는 출력결과에 번호를 넘버링하는 함수
Q1. 직업이 SALESMAN 인 사원들의 이름, 월급, 직업을 출력하시오.
Select ename, sal, job
from emp
where job = 'SALESMAN';
Q2. 위에서 출력되는 결과 앞에 번호를 순서대로 부여해서 출력하시오.
Select rownum,ename, sal, job
from emp
where job = 'SALESMAN';
select rownum, ename, sal, deptno
from emp
where deptno in (10,20)
;
문제2. 사원 테이블에서 모든 컬럼의 데이터를 출력하는데 위에 3줄의 행만 출력하시오 !
select * from emp
where rownum <=3;
문제 3. 직업이 salesman 사람들의 이름, 월금, 직업을 출력하는데 앞 rownum 을 이용하여 숫자를 넘버링하세요
Select rownum,ename, sal, job
from emp
where job = 'SALESMAN';
문제 4. 위 결과에서 월급이 높은 사원부터 출력하시오
Select rownum,ename, sal, job
from emp
where job = 'SALESMAN'
order by sal desc;
설명: 출력되는 번호가 1,2,3 순이 아니게됨 -> order by가 맨 나중에 수행되었기 때
문제 5. 위 결과에서 rownum 순서가 1,2,3 이 되도록하시오.
Select rownum,ename, sal, job
from (select ename, sal, job from emp
where job = 'SALESMAN'
order by sal desc
);
설명: from 절에 subquery 를 사용 하여 rownum 의 순서가 제대로 되게끔 함 - 괄호안에 있는 쿼리문의 결과가 하나의 집합이 되면서 마치 테이블처럼 사용되게 함 (in line view) ** row_number() 을 이용하면 서브쿼리 이용안해도됨
select row_number() over (order by sal desc) 번호,
ename, sal, job from emp
where job = 'SALESMAN';
문제 6. emp19 에서 통신사가 sk인 학생들의 이름,나이, 통신사를 출력하는데 나이가 높은 학생부터 출력하시오. 그리고 맨앞 숫자를 순서대로 넘버링하시오.
select row_number() over (order by age desc) 번호, ename, age, lower(telecom)
from emp19
where age is not null
order by age desc;
문제 7. 통신사가 'sk' 학생중에서 나이가 2번째로 많은 학생의 이름나이 통신사 출력하시오
select 번호, ename, age, telecom
from (
select row_number() over (order by age desc) 번호, ename, age, telecom
from emp19
where lower(telecom)='sk'
)
where 번호 = 2;
문제 8. 월급을 5번째로 많이 받는 사원의 이름과 월급을 출력하시오
select 번호, ename, sal
from(
select row_number() over(order by sal desc) 번호, ename, sal
from emp
)
where 번호=5;
56.ROWNUM
예제 56. 출력되는 행 제한하기 1 (ROWNUM) ※ ROWNUM() 함수와 ROWNUM의 차이점? 1. row_number() 함수는 정렬된 결과에 번호를 넘버링하고 싶을 때 사용
select row_number() over ( order by sal desc ) 번호, ename, sal
from emp
where job='SALESMAN';
- 주로 큰테이블의 일부 데이터를 보고싶을 때 사용
2. rownum 은 그냥 쿼리문의 출력 결과에 번호를 넘버링하고 싶을 때 사용
select rownum as 번호, ename, sal
from emp
where job='SALESMAN';
문제1. 사원 테이블에서 사원이름, 월급을 출력하는데 ROWNUM이 1번인 것을 출력하시오,
select rownum, ename, sal
from emp
where rownum = 1;
2번만 나오게 못함
select rownum, ename, sal
from emp
where rownum <=2;
57. Simple Top-n queries
예제 57. 출력되는 행 제한하기 2 (Simple Top-n queries) - 정렬된 결과에서 상위 몇개의 데이터를 가져오는 방법
order by 컬럼명 asc/desc fetch first 숫자 rows only
Q 사원테이블에서 이름과 월급을 출력하는데 월급이 높은 사원부터 출력하고 출력된 결과에서 2번째 행까지 출력하시오.
select ename, sal
from emp
order by sal desc fetch first 2 rows only;
56.EQUI JOIN
예제 58. 여러테이블의 데이터를 조인해서 출력하기 1. (EQUI JOIN) 여러 테이블의 결과를 하나의 결과로 출력하려면 조인문법을 알아야 됨 - JOIN이란? : 여러개의 테이블의 컬럼들의 결과를 하나로 모아서 출력해주는 SQL 문법 ex) king사원은 어느 부서에서 근무하는가 ? emp 테이블 가지고만은 알 수 없음
select * from dept;
테이블명 : dept <--- 부서에 대한 정보를 담고 있는 테이블 - deptno :부서번호 - dname : 부서명 - loc : 부서위치
문제1. emp와 dept를 조인해서 이름과 부서(loc)를 출력하시오.
select ename, loc
from emp, dept
where emp.deptno =dept.deptno;
문제2. (삼성 디스플레이) 아래 그림처럼 출력하시오.
select nvl(job,'토탈값') as job , sum(decode(deptno, '10',sal)) as "10" ,
sum(decode(deptno, '20',sal)) as "20" ,
sum(decode(deptno, '30',sal)) as "30" , sum(sal) as 토탈값
from emp
group by rollup(job)
order by 1 asc;
