[Oracle SQL]8일차_23.06.29

 select ename, sal, sum(sal) over () as 토탈월급
  from emp;
여기 셀렉트문에 토탈월급 나오면 sal / sum(sal) over () as 토탈월급 해서 비율을 구할 수도 있어서 장점이다.

over () 안에 괄호에 다른 것 넣을 수 있다. 괄호안애 아무것도 안쓰면 전체 토탈월급이 출력된다.

예제. 이름, 월급, 월급의 누적치 출력

 select ename, sal, sum(sal) over (order by sal asc) as 누적치
  from emp;
데이터 출력된 누적치라는 컬럼명 보면 두번째 있는 1750이 800+950이고. 2850라는 값은 800+950+1100이 합해진 데이터 값이다.

문제 251. 이름, 나이, 나이에 대한 누적치 출력

나이가 중복이면 누적값을 처음부터 계산한다. 그래서 문법 추가해야함.

select ename, age, sum(age) over(order by age asc rows
                                  between unbounded preceding // 맨 첫번째 행부터
                                  and current row) as 누적치 // 현재 행 까지
 from emp17;

책 152페이지 참고
윈도우 기준
rows : 행을 기준으로 누적치를 구한다.
range : 범위를 기준으로 누적치를 구한다.
- unbounded preceding 맨 첫번째 행을 가리킨다.
- unbounded following 맨 마지막 행
- current row 현재 행 가리킨다

문제 252. 통신사, 이름, 나이, 나이에 대한 누적치를 출력하는데 나이에 대한 누적치가 통신사별로 각각 누적되게 하시오 (partition by)

select telecom, ename, age, sum(age) over( partition by telecom
                                  order by age asc rows
                                  between unbounded preceding 
                                  and current row) as 누적치 
 from emp17;

문제 253. 부거번호, 이름, 월급, 월급에 대한 누적치를 출력하는데 누적치가 부서번호별로 출력되도록

select deptno, ename, sal, sum(sal) over( partition by deptno
                                  order by sal asc rows
                                  between unbounded preceding 
                                  and current row) as 누적치 
 from emp;

윈도우 기준을 범위(range)기준으로 구현하는 환경 구성하기

emp테이블을 부서번호를 어센딩하게 정렬한 결과를 가지고 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;

answp 254. 이름, 입사일, 월급, 월급에 대한 누적치를 출력하고 정렬 기준을 월습이 아니라 입사한 사원 순으로 정렬!(emp2)

select ename, hiredate, sal, sum(sal) over( 
                                  order by hiredate asc rows
                                  between unbounded preceding 
                                  and current row) as 누적치 
 from emp2;

결과를 보면 행을 기준으로 월급을 누적시켰다.

※ 윈도우 기준을 range를 이용해서 입사일에 대한 범위로 월급을 누적시켜보겠다!

select ename, hiredate, sal, sum(sal) over( 
                                  order by hiredate asc range
                                  between unbounded preceding 
                                  and current row) as 누적치 
 from emp2;

range를 사용했기때문에 범위를 기준으로 (hiredate) 월급을 누적시키고 있다.

문제 255. (복습) 입사한년도(4자리), 이름, 월급을 출력하는데 입사한 년도가 1981, 1980 년도만 출력

select to_char(hiredate , 'RRRR'), ename, sal 
 from emp
 where to_char(hiredate , 'RRRR') in ('1981','1980');

데이터 분석 함수로 비율 출력하기(RATIO_TO_REPORT)

예제, 부서번호가 20번인 사원들의 사원번호, 이름, 월급, 월급에 대한 비율을 출력하시오.

select empno, ename, sal, ratio_to_report(sal) over () as 비율, 
                          sal / sum(sal) over() as 비교비율
   from emp
   where deptno = 20;

ratio_to_report(sal) over () as 비율,, sal / sum(sal) over() as 비교비율의 결과는 같다.

문제 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
    );

from 절의 서브쿼리를 사용했다 !!

문제 257. (복습)최근 출생아수와 관련한 테이블 생성하기.

년도 출생아수 합계출산율
2012 484.6 1.297
2013 436.5 1.187
2014 435.4 1.205
2015 438.4 1.239
2016 406.2 1.172
2017 357.8 1.052
2018 326.8 0.977
2019 302.7 0.918
2020 272.3 0.84
2021 260.6 0.81

create table korea_birth
 ( k_year   number(10),
   k_birth  number(10,2),
   k_birth_rate  number(10,2));
   
insert into korea_birth values(2012, 484.6, 1.297 );
insert into korea_birth values(2013, 436.5, 1.187 );
insert into korea_birth values(2014, 435.4, 1.205 );
insert into korea_birth values(2015, 438.4, 1.239 );
insert into korea_birth values(2016, 406.2, 1.172 );
insert into korea_birth values(2017, 357.8, 1.052 );
insert into korea_birth values(2018, 326.8, 0.977 );
insert into korea_birth values(2019, 302.7, 0.918 );
insert into korea_birth values(2020, 272.3, 0.84  );
insert into korea_birth values(2021, 260.6, 0.81  );

commit;

number(10,2)숫자 10자리를 잡고 그중에 2자리는 소수점 허용 -> 아닌거 8자리 소수점인거 2자리!!

문제 258. 위에 생성한 테이블에서 k_year, k_birth, 바로 이전행의 k_birth 출력

select k_year as 년도, k_birth as 출생아수, lag(k_birth) over (order by k_year asc) as "전년도 출생아수"
 from korea_birth;

데이터 분석 함수로 집계 결과 출력하기 1(ROLLUP)

예제2. 부서번호ㅡ 부서번호별 토탈월급을 출력하는데 부서번호별 전체 토탈월급이 아래에 출력되게 하시오

select deptno, sum(sal)
 from emp
 group by rollup(deptno);

rollup을 사용했더니 전체집계인 29025 출력이 되었다!

문제 259. 통신사, 통신사별 인원수를 출력하는데 맨 아래에 전체 인원수가 출력되게 하시오

select telecom, count(*)
 from emp17
 group by rollup(telecom);

문제 260. 직업, 직업별 토탈월급을 출력하는데 맨 아래에 전체 토탈월급이 출력되게 하고 토탈월급들을 출력할 때 천단위 표시

 select job, to_char(sum(sal),'999,999')
 from emp
 group by rollup(job);

문제 261. 아래와 같이 출력

 select nvl(job, '전체집계:'), to_char(sum(sal),'999,999')
 from emp
 group by rollup(job);

점심시간 문제

치킨 프랜차이즈 매장수가 많은 치킨 프랜차이즈명과 매장수 그리고 순위를 출력하는데 1위부터 5위까지만 출력하세요(marker_2022 테이블 활용)

SELECT 상호명, 건수, 순위
  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 '노랑통닭'
                ELSE 상호명 END AS 상호명, count(*) AS 건수 , DENSE_RANK () OVER(ORDER BY COUNT(*) DESC) AS 순위
     from market_2022
     where 상권업종중분류명 = '닭/오리요리' and 상호명 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 '노랑통닭'
                ELSE 상호명 END
 )
 WHERE 순위 <= 5;

※ 레포팅성 SQL
※ 레포팅성향의 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. 부서번호, 직업, 부서번호별 직업별 토탈월급을 출력하는데 roll up 사용하기

select deptno , job, sum(sal)
  from emp
  group by rollup(deptno,job);
※출력되는 결과

부서변호별 , 직업별, 토탈월급이 출력된다.

부서번호별 토탈월급 출력된다.

전체 토탈월급이 출력된다.
-> rollup 함수안에 컬럼의 갯수 + 1개만큼 집계한 결과 그룹이 출력된다.

  select deptno , sum(sal)
  from emp
  group by rollup(deptno);

출력되는 결과: 1. 부서번호별 토탈월급, 2. 전체 토탈월급 -> 롤업 안에 있는 컬럼 + 1개 (총두개)의 결과가 나온다.

아래의 SQL을 실행하기 전에 출력될 결과를 예측해보시오.

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

※ 출력 결과
1. deptno, job, mgr
2. deptno, job
3. deptno
4. 전체

문제 266. 아래의 sql의 출력으로 예상되는 결과를 적으시오

select gender, telecom, sum(age)
 from emp17
 group by rollup(gender, telecom);

※ 출력 결과
1. gender, telecom 별
2. gender 별
3. 전체

문제 267. (SQLP 주관식) 현업에서의 정말 많이 작성하는 SQL 사례.
-> 문제 268번의 결과를 다음과같이 출력하시오

select gender, nvl(telecom,'토탈:'), sum(age)
  from emp17
  group by rollup(gender, telecom);

문제 268. 위의 결과를 아래와 같이 출력

select gender , case when gender is null and telecom is null then '전체토탈:'
                     when telecom is null then '토탈:'  else telecom end telecom , 
                     sum(age) as 나이
  from emp17
  group by rollup(gender, telecom);

문제 269. grouping함수를 이용해서 아래의 결과를 출력하고 이해하시오

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

데이터 분석 함수로 집계 결과 출력하기 2(CUBE)

예제. 부서번호, 부서번호별 토탈월급을 출력하는데 전체 토탈월급을 맨 위에 출력

select deptno, sum(sal)
 from emp
 group by cube(deptno);

문제 270. 통신사, 통신사별 인원수를 출력하는데 전체 학생 인원수가 맨위에 출력되게 하시오

 select telecom, count(*)
 from emp17
 group by cube(telecom);

문제 271. 위 결과를 전체 표시하기

 select nvl(telecom,'전체:'), count(*)
 from emp17
 group by cube(telecom);

문제 272. 부서번호, 부서번호별 토탈월급을 출력하는데 전체 토탈월급이 아래와 같이 출력

 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. 위 결과 다시 출력하는데 큐브를 사용하세요

select deptno, job, sum(sal)
  from emp
  group by cube(deptno, job);
현재 큐브 안의 컬럼이 두개니까 2의 2승해서 결과는 4개가 나온다.

만약 컬럼이 3개면, 2의 3승해서 8개가 나온다
-> 집계결과는 2의 n승해서 나온다.

※ 집계결과
1. deptno, job
2. deptno
3. job
4. 전체

group by cube(deptno, mgr ,job); 의 집계결과->

전체

deptno, mgr ,job

deptno

mgr

job

deptno, mgr

deptno, job

mgr ,job

데이터 분석 함수로 집계 결과 출력하기 3(GROUPING SETS)

예제. 부서번호, 부서번호별 토탈월급을 출력하는데 , 맨 아래에 전체 토탈월급을 출력하시오!

select deptno, sum(sal)
  from emp
  group by grouping sets(deptno, ());

※ 예상되는 결과 1. deptno , 2. () <---전체

만약 () 가 없으면 그냥 부서번호별 토탈월급만 나온다.
내가 만약 전체를 보고싶지 않다면 ()를 빼면 된다.

문제 275. 아래의 SQL결과를 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, ());
롤업처럼 결과를만들고싶으면 이렇게 하면된다. grouping sets안에는 예상결과를 다 넣어줘야한다.

위 코드에서 그룹핑 셋츠 안에 () 를 빼면 전체 결과가 빠지고, 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;

order by 절에 nulls first 사용!!!

문제 277. 아래와 같이 결과 출력
이름 , 월급 , 맨밑에 전체토탈!

내 답
select ename , sum(sal)
 from emp
 group by grouping sets(ename, ());
정답
select empno, ename , sum(sal)
 from emp
 group by grouping sets((empno,ename), ());
만약 이름이 중복될 상황을 대비해서 사원번호도 넣어주기!

데이터 분석 함수로 출력 결과 넘버링 하기(ROW_NUMBER)

출력되는 결과의 번호를 넘버링하는 함수
예제. 직업이 SALESMAN인 사원의 이름, 월급, 직업을 출력하시오

select ename, sal, job
 from emp
 where job = 'SALESMAN';

예제. 위의 SQL에서 출력되는 결과에 번호를 순서대로 부여여하시오

 select rownum, ename, sal, job
  from emp
  where job = 'SALESMAN';

※ rownum은 출력되는 결과 데이터에 넘버링하는 shawdow 컬럼입니다.

문제 278. 위의 결과를 다시 출력하는데 월급이 높은 사원부터 출력

  select rownum, ename, sal, job
  from emp
  where job = 'SALESMAN'
  order by sal desc;
order by 정렬하니까 rownum이 뒤죽박죽이다!

문제 279. 위 결과에서 앞 번호가 1,2,3,4 로 출력되게 하시오

 select rownum, ename, sal, job
   from(
      select ename, sal, job
      from emp
      where job = 'SALESMAN'
      order by sal desc
       );
from 서브쿼리를 사용했다! order by 는 맨 마지막에 수행되니까, 먼저 실행될 수 있도록 서브쿼리를 써주고 그 결과를 rownum 부여!!!

이렇게 서브쿼리 사용하지 않으려면, ROW_NUMBER 사용!!
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;

서브쿼리 사용! 윈도우함수는 웨어절에 쓸 수 없다.

출력되는 행 제한하기 1(ROWNUM)

예제. market_2022 테이블의 모든 행, 컬럼 출력
예제. 위 결과에서 5개만 출력하기

select *
 from market_2022
 where rownum < 6;

문제 282. 치킨 프랜차이즈점의 매장 건수와 순위를 출력하고 코로나 이전인 market_2017테이블도 10위까지 출력하세요

select 상호명, 건수, 순위
  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 '지코바양념치킨'
                WHEN 상호명 LIKE '%자담치%' THEN '자담치킨'
                ELSE 상호명 END AS 상호명, count(*) AS 건수 , DENSE_RANK () OVER(ORDER BY COUNT(*) DESC) AS 순위
     from market_2022
     where  상호명 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 '지코바양념치킨'
                WHEN 상호명 LIKE '%자담치%' THEN '자담치킨'
                ELSE 상호명 END
 )
 where  순위 <= 10
 order by 2 desc fetch first 10 row only;

select 상호명, 건수, 순위
  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 '지코바양념치킨'
                WHEN 상호명 LIKE '%자담치%' THEN '자담치킨'
                ELSE 상호명 END AS 상호명, count(*) AS 건수 , DENSE_RANK () OVER(ORDER BY COUNT(*) DESC) AS 순위
     from market_2017
     where  상호명 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 '지코바양념치킨'
                WHEN 상호명 LIKE '%자담치%' THEN '자담치킨'
                ELSE 상호명 END
 )
 where  순위 <= 10
 order by 2 desc fetch first 10 row only;

망구씨

Slow and steady wins the race.

이전 포스트