▣ 복습
- select 6가지절 :
select job, sum(sal)
from emp
where deptno in ( 10, 20 )
group by job
having sum(sal) >= 3000
order by job asc ;-
함수 :
-
단일행 함수 : 문자, 숫자, 날짜, 변환, 일반
-
복수행 함수 : max, min, avg, sum, count
-
데이터 분석 함수 : rank, dense_rank, ntile(등급), cume_dist(순위의 비율),
lag, lead, sum+decode, pivot, unpivot데이터 분석 함수가 주로 수행 ↓ OLTP 서버 DW 서버
-
(실시간으로 데이터 입출력 발생) ( 히스토리성 데이터가 저장 )
▣ 050 데이터 분석 함수로 누적 데이터 출력하기(SUM OVER)
예제. 사원 테이블에서 토탈월급을 출력하시오 !
select sum(sal)
from emp;
예제. 이름, 월급, 사원 테이블의 토탈월급을 출력하시오 !
select ename, sal , sum(sal) over () as 토탈월급
from emp;
※ 설명: sum(sal) over 다음에 괄호에 아무것도 안쓰면 전체 토탈월급
이 출력되어집니다.
예제. 이름, 월급, 월급의 누적치를 출력하시오
select ename, sal, sum(sal) over ( order by sal asc ) 누적치
from emp;
select ename, sal, sum(sal) over ( order by sal asc rows
between unbounded preceding
and current row ) 누적치
from emp;
select ename, sal, sum(sal) over ( order by sal asc rows
between unbounded preceding
and unbounded following )
from emp;※ 윈도우 기준은 2가지가 있는데 rows 와 range 입니다.
rows 는 행을 기준으로 누적치를 구하는것이고
range 는 범위를 기준으로 누적치를 구하는 것입니다.
문제251. 우리반 테이블에서 이름, 나이, 나이에 대한 누적치를 출력하시오
select ename, age, sum(age) over ( order by age asc rows
between unbounded preceding
and current row ) 누적치
from emp17;
문제252. 통신사 , 이름, 나이, 나이에 대한 누적치를 출력하는데
나이에 대한 누적치가 통신사별로 각각 누적되게 하시오 !
select telecom, ename, age, sum(age) over ( partition by telecom
order by age asc rows
between unbounded preceding
and current row ) 누적치from emp17;
문제253. 부서번호, 이름, 월급, 월급에 대한 누적치를 출력하는데
누적치가 부서번호별로 각각 월급을 누적해서 출력하시오!
select deptno, ename, sal, sum(sal) over ( partition by deptno
order by sal asc rows
between unbounded preceding
and current row ) 누적치from emp;
- 윈도우 기준을 범위(range) 기준으로 구현하는 환경 구성하기
- emp 테이블을 부서번호를 ascending 하게 정렬한 결과를 가지고
emp2 라는 테이블을 생성합니다.
create table emp2
as
select *
from emp
order by deptno asc ;- emp2 테이블의 부서번호가 10번인 사원들의 입사일을 81/01/05 로
변경합니다.
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 * from emp2;문제254. emp2 테이블에서 이름, 입사일, 월급, 월급에 대한 누적치를
출력하시오 ! 그런데 누적할때 정렬기준을 월급이 아니라
입사한 사원순으로 정렬기준을 정해서 출력하세요
select ename, hiredate, sal, sum(sal) over ( order by hiredate asc rows
between unbounded preceding
and current row ) 누적치
from emp2;
※ 결과를 보면 그냥 행을 기준으로 월급을 누적 시켰습니다.
윈도우 기준을 range 를 이용해서 입사일에 대한 범위로 월급을
누적 시키겠습니다.
select ename, hiredate, sal, sum(sal) over ( order by hiredate asc range
between unbounded preceding
and current row ) 누적치
from emp2;
※ 날짜의 범위를 기준으로 월급을 누적 시키고 있습니다.
문제255. (복습문제) 입사한 년도(4자리), 이름, 월급을 출력하는데
입사한 년도가 1981, 1980 년도만 출력되게 하시오 !
select to_char(hiredate, 'RRRR'), ename, sal
from emp
where to_char(hiredate, 'RRRR') in ( '1981', '1980' ) ;
▣ 051 데이터 분석 함수로 비율 출력하기(RATIO_TO_REPORT)
예제. 부서번호가 20번인 사원들의 사원번호, 이름, 월급, 월급에 대한
비율을 출력하시오 !
create table emp_test2
as
select empno, ename, sal, ratio_to_report(sal) over () as 비율,
sal / sum(sal) over () as 비교비율
from emp
where deptno = 20 ;문제256. 위의 SQL에서 비율 데이터의 합이 1인지 확인하시오 !
select sum(비율), sum(비교비율)
from (
select empno, ename, sal, ratio_to_report(sal) over () as 비율,
sal / sum(sal) over () as 비교비율
from emp
where deptno = 20
) ;문제257. (복습문제) 최근 출생아수와 관련한 테이블을 생성하시오!
요청데이터 분석가 ----------> dba, 데이터 엔지니어
create table korea_birth
( k_year number(10),
k_birth number(10,2),
k_birth_rate number(10,2) );
SQL> select * from korea_birth;K_YEAR K_BIRTH K_BIRTH_RATE
------ -------- ------------
2012 484.6 1.3 <--- 48만4천 6백명
2013 436.5 1.19
2014 435.4 1.21
2015 438.4 1.24
2016 406.2 1.17
2017 357.8 1.05
2018 326.8 .98
2019 302.7 .92
2020 272.3 .84
2021 260.6 .81문제258. korea_birth 테이블에서 k_year , k_birth , 바로 이전행의 k_birth
를 출력하시오 !
select k_year as 년도, k_birth "출생아수" ,
lag(k_birth) over ( order by k_year asc ) "전년도 출생아수"from korea_birth;
▣ 052 데이터 분석 함수로 집계 결과 출력하기 1(ROLLUP)
→ 나타나는 결과의 개수에 대해서 한번 생각해보자!
예제1. 부서번호, 부서번호별 토탈월급을 출력하시오 !
select deptno, sum(sal)
from emp
group by deptno;
예제2. 부서번호, 부서번호별 토탈월급을 출력하는데
부서번호별 토탈월급들에 대한 전체 합계가 맨 아래에 출력되게
하시오 !
select deptno, sum(sal)
from emp
group by rollup( deptno );
※ rollup 을 사용했더니 전체 집계가 맨 아래에 출력되었습니다.
문제259. 통신사, 통신사별 인원수를 출력하는데 맨 아래에 전체 인원수
가 출력되게하시오 !
select telecom, count(*)
from emp17
group by rollup(telecom);
문제260. 직업, 직업별 토탈월급을 출력하는데 맨 아래에 전체 토탈월급
이 출력되게하고 토탈월급들을 출력할 때 천단위 콤마 표시를
출력하시오 !
select job, to_char( sum(sal), '999,999' ) as 토탈월급
from emp
group by rollup( job );
문제261. 위의 결과를 아래와 같이 출력되게 하시오 !
JOB 토탈월급
------------------ ----------
ANALYST 6,000
CLERK 4,150
MANAGER 8,275
PRESIDENT 5,000
SALESMAN 5,600
전체집계: 29,025
select nvl( job, '전체집계:'), to_char( sum(sal), '999,999' ) as 토탈월급
from emp
group by rollup( job );점심시간 문제
치킨 프랜차이즈 매장수가 많은 치킨 프랜차이즈명과 매장수 그리고 순위를 출력하는데 1위부터 5위까지만 출력하세요(marker_2022 테이블 활용)
select 프랜차이즈명, 매장수, dense_rank() over ( order by 매장수 desc ) as 순위
from(
select case when 상호명 like '%BBQ%' then 'BBQ치킨'
when 상호명 like '%BHC%' then 'BHC치킨'
when 상호명 like '%교촌%' then '교촌치킨'
when 상호명 like '%처갓집%' then '처갓집치킨'
when 상호명 like '%네네%' then '네네치킨'
when 상호명 like '%지코바%' then '지코바양념치킨'
when 상호명 like '%굽네%' then '굽네치킨'
when 상호명 like '%자담%' then '자담치킨'
when 상호명 like '%호식이%' then '호식이두마리치킨'
when 상호명 like '%푸라닭%' then '푸라닭치킨'
else 상호명 end as 프랜차이즈명, count(*) as 매장수
from market_2022
where 상호명 like '%치킨%' or 표준산업분류명 like '%치킨%' or 상권업종중분류명 like '%닭%' or
상권업종소분류명 like '%치킨%'
group by case when 상호명 like '%BBQ%' then 'BBQ치킨'
when 상호명 like '%BHC%' then 'BHC치킨'
when 상호명 like '%교촌%' then '교촌치킨'
when 상호명 like '%처갓집%' then '처갓집치킨'
when 상호명 like '%네네%' then '네네치킨'
when 상호명 like '%지코바%' then '지코바양념치킨'
when 상호명 like '%굽네%' then '굽네치킨'
when 상호명 like '%자담%' then '자담치킨'
when 상호명 like '%호식이%' then '호식이두마리치킨'
when 상호명 like '%푸라닭%' then '푸라닭치킨'
else 상호명 end
-- order by 2 desc
)
order by 순위 desc fetch first 5 rows only;**※ 레포팅성향의 SQL** :
1. rollup
2. cube
3. grouping sets
4. grouping문제262. 부서번호, 직업, 부서번호별 직업별 토탈월급을 출력하시오!
select deptno, job, sum(sal)
from emp
group by deptno, job
order by deptno, job;
문제263. 입사한 년도(4자리), 직업, 입사한 년도별 직업별 토탈월급을
출력하시오 !
select to_char( hiredate, 'RRRR'), job, sum(sal)
from emp
group by to_char(hiredate, 'RRRR'), job
order by to_char(hiredate, 'RRRR'), job ;
문제264. 부서번호, 직업, 부서번호별 직업별 토탈월급을 출력하는데
다음과 rollup 을 써서 출력하시오 !
select deptno, job, sum(sal)
from emp
group by rollup( deptno, job );※ rollup 함수안에 컬럼의 갯수 + 1 개 만큼 집계한 결과 그룹이 출력
※ 출력되는 결과 :
- 부서번호별 직업별 토탈월급
- 부서번호별 토탈월급
- 전체 토탈월급
select deptno, sum(sal)
from emp
group by rollup(deptno);※ 출력되는 결과 :
- 부서번호별 토탈월급
- 전체
문제265. 아래의 SQL을 실행하기전에 출력될 결과를 예측해보시오 !
select deptno, job, mgr, sum(sal)
from emp
group by rollup( deptno, job, mgr );
※ 출력결과:
- deptno, job, mgr
- deptno, job
- deptno
- 전체
문제266. 아래의 SQL의 출력으로 예상되는 결과를 적으시오 !
select gender, telecom, sum(age)
from emp17
group by rollup( gender, telecom );
※ 출력되는 결과 :
- gender, telecom
- gender
- 전체
문제267. (SQLP 주관식) 현업에서의 많이 작성하는 SQL 사례.
문제268번의 결과를 다음과 같이 출력하시오 !
GENDER TELECOM SUM(AGE)
-------------------- -------------------- ----------
남 kt 110
남 lg 25
남 sk 147
남 토탈: 282
여 kt 56
여 lg 121
여 sk 118
여 토탈: 295
토탈: 577
답: select gender, nvl( telecom,'토탈:') , sum(age)
from emp17
group by rollup( gender, telecom );문제268. (하신분들은 답글로 달아주세요) 위의 결과를 아래와 같이 출력하시오!
GENDER TELECOM SUM(AGE)
-------------------- -------------------- ----------
남 kt 110
남 lg 25
남 sk 147
남 토탈: 282
여 kt 56
여 lg 121
여 sk 118
여 토탈: 295
전체토탈: 577
select gender, NVL(telecom, case when gender is null then '전체토탈:'
when gender is not null and telecom is null then '토탈:'
else telecom end) as telecom
, sum(age)
from emp17
group by rollup( gender, telecom );
진짜 좋은 방법이네ㅎㅎ
방법1)
SELECT gender,
CASE WHEN gender IS NULL THEN '전체 토탈 : '
WHEN telecom IS NULL THEN '토탈 : '
ELSE telecom END AS telecom,
SUM(age)
FROM emp17
GROUP BY ROLLUP(gender, telecom);
방법 2)
SELECT gender,
DECODE(gender,NULL,'전체 토탈 :',nvl(telecom, '토탈 :')) AS telecom,
SUM(age)
FROM emp17
GROUP BY ROLLUP(gender, telecom);
방법 3)
SELECT deptno,
CASE WHEN GROUPING(deptno) = 1
AND GROUPING(JOB) = 1 THEN '전체 토탈:'
WHEN GROUPING(deptno) = 0
AND GROUPING(JOB) = 1 THEN '토탈 :'
ELSE JOB END AS telecom,
SUM(sal)
FROM emp
GROUP BY ROLLUP(deptno, JOB);※ 레포팅성 SQL 함수 4가지 :
- rollup
- cube
- grouping sets
- grouping
문제269. grouping 함수를 이용해서 아래의 결과를 출력하고 이해하시오!
select deptno, grouping(deptno), job, grouping(job), sum(sal)
from emp
group by rollup( deptno, job );
▣ 053 데이터 분석 함수로 집계 결과 출력하기 2(CUBE)
cube 는 rollup 과는 다르게 집계결과를 맨위에 출력하는 함수 입니다.
예제1. 부서번호, 부서번호별 토탈월급을 출력하는데 전체 토탈월급을
맨 위에 출력하시오 !
select deptno, sum(sal)
from emp
group by cube(deptno);
문제270. 통신사, 통신사별 인원수를 출력하는데 전체 학생 인원수가 맨위에
출력되게 하시오 !
select telecom, count(*)
from emp17
group by cube(telecom);
문제271. 문제270번의 결과를 아래와 같이 출력하시오 !
TELECOM COUNT(*)
-------------------- ----------
전체: 19
kt 5
lg 5
sk 9
select nvl( telecom, '전체:'), count(*)
from emp17
group by cube( telecom );문제272. ( 오라클 메뉴얼 교재 연습문제의 난이도 중에 해당하는 문제)
부서번호, 부서번호별 토탈월급을 출력하는데 전체 토탈월급이
아래와 같이 출력되게 하시오 !
DEPTNO SUM(SAL)
-------- ----------
전체: 29025
10 8750
20 10875
30 9400
select nvl( to_char(deptno) ,'전체:'), sum(sal)
from emp
group by cube(deptno);문제273. (복습문제) 부서번호, 직업, 부서번호별 직업별 토탈월급을 출력하시오
select deptno, job, sum(sal)
from emp
group by deptno, job
order by deptno, job ;
문제274. 위의 결과를 다시출력하는데 cube 를 사용해서 출력하시오!
select deptno, job, sum(sal)
from emp
group by cube( deptno, job ); group by cube(deptno,mgr, job) ;
※ 집계결과 : 1. deptno, job ※ 집계결과 : 1. 전체
2. deptno 2. deptno,mgr, job
3. job 3. deptno
4. 전체 4. mgr
5. job
6. deptno, mgr
7. deptno, job
8. mgr, job
select deptno, mgr, job, sum(sal)
from emp
group by cube( deptno, mgr, job );
※ cube 집계 결과 2의 n(cube안에 있는 컬럼의 개수)승 결과가 나온다.
▣ 054 데이터 분석 함수로 집계 결과 출력하기 3(GROUPING SETS)
앞에서 배운 cube 와 rollup 보다 결과를 예상하기 쉬워서 많이 사용하는
레포팅 함수 입니다.
예제. 부서번호, 부서번호별 토탈월급을 출력하는데 맨 아래에 전체 토탈월급을
출력하시오 !
select deptno, sum(sal)
from emp
group by grouping sets( deptno, () );
※ 예상되는 결과 : 1. deptno
2. () <---- 전체
※ 내가 만약 전체를 안보겠다면 () 빼면되고 보겠다면 넣으면 됩니다.
문제275. 아래의 SQL의 결과를 grouping sets 로 구현하시오 !
select deptno, job, sum(sal) 1. deptno, job
from emp 2. deptno
group by rollup( deptno, job ); 3. 전체
답:
select deptno, job, sum(sal)
from emp
group by grouping sets( (deptno,job ), deptno, () );
문제276. 아래의 SQL의 결과를 grouping sets 로 수행하시오 !
select deptno, sum(sal)
from emp
group by cube(deptno);
답: select deptno, sum(sal)
from emp
group by grouping sets( deptno, () )
order by deptno asc nulls first;
문제277. (현업에서 사용하는 SQL) 아래와 같이 결과를 출력하시오 !
SELECT empno, ename, sum(sal)
from emp
group by grouping sets( (empno, ename), () );
▣ 055 데이터 분석 함수로 출력 결과 넘버링 하기(ROW_NUMBER)
출력되는 결과의 번호를 넘버링하는 함수
예제. 직업이 SALESMAN 인 사원의 이름, 월급, 직업을 출력하시오 !
select ename, sal, job
from emp
where job='SALESMAN';
예제. 위의 SQL에서 출력되는 결과에 번호를 순서대로 앞에 부여하시오 !
select rownum, ename, sal, job
from emp
where job='SALESMAN';
※ rownum 은 출력되는 결과 데이터에 numbering 하는 shadow 컬럼 입니다.
문제278. 위의 결과를 다시 출력하는데 월급이 높은 사원부터 출력하시오 !
select rownum, ename, sal, job
from emp
where job='SALESMAN'
order by sal desc;
문제279. 위의 결과에서 앞에 번호가 1, 2, 3, 4 로 출력되게 하시오 !
select rownum, ename, sal, job
from (
select ename, sal, job
from emp
where job='SALESMAN'
order by sal desc
);
※ order by 절이 먼저 수행될 수 있도록 from 절의 서브쿼리로 감싸줍니다.
select row_number() over ( order by sal desc ) 번호 , ename, sal, job
from emp
where job='SALESMAN';문제280. 통신사가 kt 인 학생들의 이름과 나이와 통신사를 출력하는데
나이가 높은 학생부터 출력하고 앞에 번호를 붙여서 출력하시오 !
select row_number() over ( order by age desc ) 번호 , ename, age, telecom
from emp17
where telecom='kt' ;
문제281. 통신사가 kt 인 학생중에서 나이가 2번째로 많은 학생을 출력하시오 !
select *
from (
select row_number() over ( order by age desc ) 번호 , ename, age, telecom
from emp17
where telecom='kt'
)where 번호 = 2;
▣ 056 출력되는 행 제한하기 1(ROWNUM)
예제: market_2022 테이블의 모든 행과 모든 컬럼을 출력하시오 !
select * from market_2022;
예제. 위의 결과를 다시 보는데 맨위에 5개정도의 데이터만 출력하시오 !
select *
from market_2022
where rownum < 6;
문제282. (오늘의 마지막 문제) market_2022 테이블에서 치킨 프랜차이즈점의 매장건수와 순위를
를 출력하고 코로나 이전인 market_2017 테이블도 10위까지 출력하시오
2022년 현황 2017년 현황
상호명 매장건수 순위
BBQ치킨 229 1
BHC치킨 201 2
교촌치킨 147 3
처갓집양념치킨 89 4
굽네치킨 85 5 ?
네네치킨 84 6
지코바양념치킨 63 7
호식이두마리치킨 63 7
자담치킨 63 7
깐부치킨 52 10
select 프랜차이즈명, 매장수, dense_rank() over ( order by 매장수 desc ) as 순위
from(
select case when 상호명 like '%BBQ%' then 'BBQ치킨'
when 상호명 like '%BHC%' then 'BHC치킨'
when 상호명 like '%교촌%' then '교촌치킨'
when 상호명 like '%처갓집%' then '처갓집치킨'
when 상호명 like '%네네%' then '네네치킨'
when 상호명 like '%지코바%' then '지코바양념치킨'
when 상호명 like '%굽네%' then '굽네치킨'
when 상호명 like '%자담%' then '자담치킨'
when 상호명 like '%호식이%' then '호식이두마리치킨'
when 상호명 like '%푸라닭%' then '푸라닭치킨'
else 상호명 end as 프랜차이즈명, count(*) as 매장수
from market_2022
where 상호명 like '%치킨%' or 표준산업분류명 like '%치킨%' or 상권업종중분류명 like '%닭%' or
상권업종소분류명 like '%치킨%'
group by case when 상호명 like '%BBQ%' then 'BBQ치킨'
when 상호명 like '%BHC%' then 'BHC치킨'
when 상호명 like '%교촌%' then '교촌치킨'
when 상호명 like '%처갓집%' then '처갓집치킨'
when 상호명 like '%네네%' then '네네치킨'
when 상호명 like '%지코바%' then '지코바양념치킨'
when 상호명 like '%굽네%' then '굽네치킨'
when 상호명 like '%자담%' then '자담치킨'
when 상호명 like '%호식이%' then '호식이두마리치킨'
when 상호명 like '%푸라닭%' then '푸라닭치킨'
else 상호명 end
-- order by 2 desc
)
order by 매장수 desc fetch first 10 row only;