7. DATA 분석함수(rank, dens_rank, listagg, ntile, lead, lag)

■ 데이터 분석 함수

[우리 교재엔 없는 내용]

 1. rank : 순위를 출력하는 함수

 

■ rank 함수

예제: 이름, 월급, 순위를 출력하는데

순위는 월급에 대한 순위이고

월급이 높은 사원부터 순위를 부여하시오

 

select ename, sal,

          rank( ) over (order by sal desc) 순위

from emp;

 

rank 순위를 출력하라

over ~이상

(order~desc)의 괄호내용대로

출력해라

 

 

문제153. 이름, 나이, 순위를 출력하는데

순위가

나이가 높은 순서대로

순위를 부여하시오

 

select ename, age,

rank()over(order by age desc)순위

from emp2;

 

 

문제154. 위의 결과를 다시 출력하는데

전공이 심리학과 학생들로 제한해서

출력하시오

 

select ename, age,

rank()over(order by age desc)순위

from emp2

where major like '%심리%';

 

 

문제155.

1981년도에 입사한 사원들의

이름과 입사일과 순위를 출력하는데

먼저 입사한 사원 순으로

순위를 부여하시오.

*to_char,

 to_date & between 이용해서

 

select ename, to_char(hiredate, 'rrrr'),

rank()over(order by to_char(hiredate, 'rrrr') asc)순위

from emp

where to_char(hiredate,'rrrr') ='1981'

 

좌변 건들면 안좋으니

튜닝

select ename, hiredate,

rank()over(order by hiredate asc)순위

   from emp

   where hiredate between

     to_date('1981/01/01', 'rrrr/mm/dd')

   and

     to_date('1981/12/31', 'rrrr/mm/dd');

 

 

문제156.

이름, 월급, 순위를 출력하는데

순위가 월급이 높은 사원부터

출력하시오

(같은순위가 있어도

다음 순위로 바로 이어지게 하시오)

 

select ename, sal,

dense_rank()over(order by sal desc)순위

from emp

 

■RANK(지정값)WITHIN GROUP

 

분석함수에서 지정값을 정해서

순위를 구해주는 함수다.

 

문제157.

월급이 2975 인 사원은

전체 사원들 중에

월급의 순위가

어떻게 되는가?

 

select 

rank(2975) within group (order by sal desc)순위

from emp;

 

*********

이름도 같이 출력하고 싶었으나

rank 그룹함수라서

group by ename로 묶어줘야 연산한다.

그룹:그룹 (대칭성)

 

select * from

(

select ename, sal,

rank()over(order by sal)

from emp)

where sal='2975';

 

그래서 tabel 값을 지정해줘서

그룹대칭성 조건 해제 하고

내가 where 로 조건을 만든다.

 

rank() over (order by~)

rank(숫자) within group (order by~)

 

 

■DENSE_RANK

동순위를 하나로 간주하고

다음 순위에 영향없이 차례대로

순위를 매기는 함수다.

 

문제158.

81년 11월 17일에 입사한 사원은

사원 테이블에서

몇 번째로 입사한 사원인가

(먼저 입사를 보통은 물어봄)

 

select *

from( select hiredate,

rank()over(order by hiredate asc) from emp

)

where hiredate = to_date('81/11/17', 'rr/mm/dd');

 

히히히히히히히히

select e.ename, 순위

from (select empno, dense_rank()over(order by hiredate asc)순위

      from emp) ee, emp e                        

 where ee.empno = e.empno

 and hiredate = to_date('81/11/17', 'rr/mm/dd');

 

 

 

문제159.

부서번호, 이름, 월급, 순위를 출력하는데

순위를 출력할 때

부서번호별로 각각 월급이 높은순서대로

순위를 출력하시오

**각각 이니까 그룹화랑은 다르지.

 

select deptno, ename, sal,

dense_rank()over(partition by deptno

                                 order by sal desc)순위

from emp;

 

partition by deptno  ->>부서별로 모아라

부서를 다 합친게 아니라

같은 부서를 쪼르르 나열한거다

 

그래서

select deptno, ename, sal,

dense_rank()over(partition by deptno

                                 order by sal desc)순위

from emp;

셀렉트의 각각의 부서번호, 이름, 연봉과

대칭이 되고 값이 출력되는 거다.

 

※partition by 는 group by 랑은 다르다

데이터 분석함수를 사용할 때

옵션으로 사용하는 키워드다

 

 

문제 160.

 통신사, 이름, 나이, 순위를 출력하는데

순위가 통신사별로 각각

나이가 높은 순서대로

순위를 부여하라

select lower(telecom),

        ename,

       age,

        dense_rank()over(partition by lower(telecom)

                                         order by age desc)순위

    from emp2;

 

*****점심시간 문제와 개념비교

lower(telecom)TC 로 지정했던 이유는

 서브쿼리에서 lower(telecom) 로 해서

그것만 개별로 돌리면 대문자로 나온다.

그래서

최상단의 select lower(telecom) 이랑

호환안됨.

 

이미 쿼리에서

변수함수로 썼기때문에

별칭 지정해서

함수역할 못하게 할 것.

 

 

■데이터 분석 함수

1.rank

2. dense_rank

3. listagg

 

■listagg 함수

 " 데이터를 가로로 출력하는 함수"

예:

  부서번호, 해당 부서번호인

  사원들의 이름을 가로로 출력하시오

select deptno,

listagg(ename, ',') within group(order by ename)

from emp

group by deptno;

 

이름과 이름을 콤마로 구분해라

그룹 별로

이름 순서로

+반드시

order by,

group by랑 함께 와야 한다.

 

 

문제161.

입사한 년도, 입사한 년도 별로

입사한 사원들의 이름을

가로로 출력하시오

***아직도 년도,날짜의 개념이 빈약하고

hiredate 의 그룹화는

년/월/일로 나눈다는 걸 깨달아야함.

 

select to_char(hiredate,'rrrr')입사년도,

listagg(ename,',') within group(order by to_char(hiredate, 'rrrr') )이름

from emp

group by to_char(hiredate,'rrrr');

 

 

문제162.

통신사, 학생이름을 출력하는데

아래와 같이

나이도 같이 출력되게 하고

나이가 높은순서대로 출력되게하시오

답

SELECT LOWER(telecom),

listagg(ename||'('||age||')', ',') within GROUP ( ORDER BY age DESC )

FROM emp2

GROUP BY LOWER(telecom);

 

select lower(telecom),

Listagg(ename||'('||'age'||')', ',') within group

(order by age desc)

from emp2

group by lower(telecom);

 

싱글 쿼테이션 붙이면 이런 값 나옴

주의 해야함.

 

 

■ 데이터 분석함수

1. rank

2. dense_rank

3. listagg

4. ntile

 

 

■ ntile 데이터 분석 함수

 "등급을 출력하는 함수"

예:

  select ename, sal,

ntile(4) over (order by sal desc) 등급

from emp;

 

***14개의 sal 로 등급을 나눈게 아니라

0~5000 을 4등급으로 나눴음. (ntile(4) )

 

select ename, sal,

ntile(4) over (order by sal desc) 등급

from emp;

4개로 등급화 하라

 

 

 

문제163.

위의 결과를 다시 출력하는데

등급이 1등급인 사원들만

출력하시오

select ename, sal,

ntile(4) over (order by sal desc) 등급

from emp

where 등급='1'

 

이건 안됨. 왜냐면 실행 순서가

from->where->select

 

 

select *

from (

        select ename, sal,

            ntile(4) over (order by sal desc) 등급

                  from emp )

where 등급 ='1';

 

실행순서: 바깥 from->

            sub 쿼리의 from ->

            sub 쿼리의 select -> where 절

 

문제 164.

통신사, 이름, 나이, 순위를 출력하는데

통신사 별로 나이 순위가 1등인 학생들만 출력하시오.

 

 select * from

    (select telecom, ename, age,

 dense_rank()over(partition by telecom

                  order by age desc)as "순위"

      from emp2)

 where 순위 = 1;

 

 

select *

from(

select lower(telecom), ename, age,

dense_rank()over (partition by lower(telecom) order by age desc)순위

from emp2

)

where 순위 = '1';

 

 

문제165.

통신사, 이름, 나이, 순위를 출력하는데

순위가 통신사별로 각각 나이가 높은 순서대로

출력하시오

 

select *

from(

select lower(telecom), ename, age,

dense_rank()over (partition by lower(telecom) order by age desc)순위

from emp2);

 

dense_ 안쓰면

111,4,5,6, 이런식으로 순위 매겨짐.

 

 

문제 166.점심시간 문제

아래와 같이

결과를 출력하시오

*등수는 나이순대로 매겨짐.

**등수가 나이순대로 매겨지니

이름도 나이순서대로 나열되야 겠지요???

 

답

SELECT

TC,

 LISTAGG(ENAME || '(' || RANK_AGE_TELECOM || '등)', ', ' ) WITHIN GROUP (ORDER BY AGE DESC)

 AS LISTAGG_STR_ENAME_RANK

FROM (

SELECT

UPPER(TELECOM) AS TC,

 ENAME,

 AGE,

 DENSE_RANK() OVER ( PARTITION BY UPPER(TELECOM) ORDER BY AGE DESC)

 AS RANK_AGE_TELECOM

FROM EMP2

)

GROUP BY TC;

 

************rank 에서 partition by 한 이유

>>등급을 telecom 전체에서 매기는게 아니라

각 텔레콤 마다 등급을 매기기 때문에

텔레콤으로 partition by 해줘야 한다.

 

-------------

다른답

 

select telecom as tl,

listagg(e.ename||'('||ee.rank_age||'등)', ',') within group(order by e.age desc)등수

from(select ename, dense_rank()over(partition by telecom

                             order by age desc) as rank_age

     from emp2) ee, emp2 e

where ee.ename = e.ename                     

group by telecom;

 

-------------------

 

*****************

용원처럼 TC로 별칭지정 안하고

출력하면 에러뜬다.

왜냐하면

첫번째 문장의 select lower(telecom) 이

telecom 이라는 테이블에 함수 lower 를 씌워서 갖고 오라는 건지

서브쿼리의 lower(telecom) 을 갖고 오라는 건지

컴퓨터가 판단 못한다.

그래서

즉, 함수붙이는 건 중복함수 수행 안되게

별칭 지정 해주는게 좋다.

 

 

 

■ 데이터 분석 함수

1. rank

2. dens_rank

3. listagg

4. ntile

5. 그룹함수와 관련한 데이터 분석함수

 

문제167.

토탈월급을 출력하시오

 

select sum(sal)

from emp;

 

 

문제 168.

이름, 월급, 사원 테이블 전체의 토탈월급을

출력하시오

select ename, sal, sum(sal)

from  emp

group by ename, sal;

 

값이 나오긴 한데, 원하는 값이 아니다

 

■ 그룹함수OVER( )

select ename, sal,

       sum(sal) over () as 전체토탈

  from emp;

 

토탈값은 사원테이블 전체로 묶어서 나온 값.

앞의 컬럼과 함께 안묶이고

단독적으로 그룹토탈값 빼주는 기능

즉 모든사원의 sum(sal).

이녀석이 좋은게

그룹바이 안해도 먹힌다는거.

 

select ename, sal, sum(sal) over () 전체토탈

from emp;

 

over 안에 아무것도 안 썼기 때문에 '전체토탈'

이 나온거고

()안에 뭐 넣으면 그 값의 토탈이 나옴.

 

over() <<<이 녀석이 그룹함수를 개개항목이랑

함께 쓸 수 있게 해줌.

 

 

문제169.

이름, 월급, 사원 테이블의 월급의 평균값을 출력하시오

select ename, sal, round(avg(sal) over())평균

from emp;

이렇게 뽑아주면

나중에 현업에서 작업할 때

개인의 연봉과 평균연봉을 비교할 수 있다.

 

 

 

문제170.

이름, 월급, 사원 테이블의 월급의 평균값을

출력하는데

자기 월급이 사원 테이블의 월급의

평균값보다 더 큰 사원들만 출력하시오

 

select ename, sal, round(avg(sal) over () )

from emp

where sal > round( avg(sal)over() );

**이거 안됨.

왜??????

별칭지정을 하는 이유를 잘 모르는거임

 

*함수를 썼으면 변수다*

 

where   sal     =      ( avg(sal)over( ) )

      개개항목        개개항목+함수 적용된 변수

 

( avg(sal)over( ) ) <<<이 값이

             select 절에 쓴 ( avg(sal)over( ) ) 인지

           새로이 함수적용해서 뽑으라는건지

           컴퓨터는 알 수 없다.

그래서 별칭지정이 꼭 필요하다

 

 

 

용원답

SELECT * FROM

(

 SELECT   ENAME,   SAL,

  ROUND( (AVG(SAL)  OVER()) , 2 ) AS 평균값

 FROM EMP

)

WHERE SAL>평균값

 

 

select ename, sal, round(avg(sal) over ())

from emp;

 

select * from

 (

   select ename, sal, round(avg(Sal)over()) as 평균값

   from emp)

   where sal > 평균값;

 

 

 

문제171.

부서번호, 부서번호별 평균월급을 출력하시오

(partition by를 써서)

 

select

   deptno,

   round(avg(sal)over(partition by deptno))

                                     평균월급

from emp;

 

***partition by 가 되네 !!

 

 

 

문제172. 부서번호, 이름, 월급, 자기가 속한

부서번호의 평균월급을 출력하시오

 

select  deptno, ename, sal,

 avg(sal) over (partition by deptno) 부서평균

from emp;

***round 둘러주려면

over 뒤 항목까지 쫙.

select  deptno, ename, sal,

 round(avg(sal) over (partition by deptno)) 부서평균

from emp;

 

 

문제173.

위의 결과를 다시 출력하는데

자기의 월급이 자기 부서의 평균월급보다

높은 사원들만

출력하는데

그들의 직업이 무엇인지 확인하시오

 

**여기서부터 서브쿼리 필요함***

메인 where 가 지정하는 avg 값인지

               새로운 함수 avg 값인지

알수 없으니까.

 

select *

from

(

select  deptno, sal, job,

 round(avg(sal) over (partition by deptno)) 부서평균

from emp

)

where sal> 부서평균;

 

 

 

문제174.

이름, 직업, 직업별 인원수를

출력하시오

select ename, job,

count(*)  over (partition by job)인원수

from emp;

 

■누적치 출력 함수

문제175.

사원번호, 이름, 월급, 월급의 누적치를 출력하시오

select empno, ename, sal,

 sum(sal) over (order by empno rows

                  between unbounded preceding

                     and

                             current row) 누적치

from emp;

 

*****왜 empno 를 order by 뒤에 썼냐면

그냥 사원번호 순으로 누적치가 보고 싶어서

그런거다

ename든 deptno든 상관없다.

 

※설명:

 1. unbounded preceding :       제일 첫 번째 행

2. current rot             :    현재 행

3. unbounded following:   맨 마지막 행

 

order by empno 사원번호 순으로 정렬하라

between

unbounded preceding 제일 첫 번째 행

 and

 current row 현재 행

 

만약

current row 대신

unbounded followin 을 쓰면

사실상 전체 토탈 값이 나옴.

 

 

문제176.

위의 결과를 다시 출력하는데

부서번호, 사원번호, 이름, 월급, 월급의 누적치를

출력하는데

월급의 누적치가

부서번호별 각각 월급의 누적치를

출력하시오

 

select deptno, empno, ename, sal,

 sum(sal) over (partition by deptno

                   order by empno rows

                  between unbounded preceding

                     and

                             current row) 누적치

from emp;

 

 

■ 데이터 분석 함수

1. rank

2. dens_rank

3. listagg

4. ntile

5. 그룹함수와 관련한 데이터 분석함수

6. lead, lag

 

■LEAD & LAG

앞행의 값을 다음행으로 가져와서 출력하여

비교가능 하게 해주는 함수

 

예:

부서번호, 이름, 월급, 월급의 그 전행,

                           월급의 그 다음행

을 출력

select empno, ename, sal,

         lead(sal,1) over (order by empno)as lead,

        lag(sal,1) over (order by empno) as

lag

from emp;

 

 

 

화면 캡처: 2018-10-19 오후 2:30

전행과의 차이를 구할 때 이용하는 함수

lead 값이

 다음행 sal 로 나타난다.

 

lag 값이

 전행 sal 로 나타난다.

 

 

문제177.

위의 결과를 다시 출력하는데

부서번호, 이름, 월급, 월급의 그 전행,

                           월급의 그 다음행

을 출력하는데

부서번호로 각각 나오게 하시오.

 

select deptno, empno, ename, sal,

         lead(sal,1) over (partition by deptno

                      order by empno)as lead,

        lag(sal,1) over (partition by deptno

                      order by empno) as lag

from emp;

 

 

문제178.

최대월급을 받는 사원의 이름과 월급을

출력하시오

 

select *

from

(select ename, sal, max(sal)over() as "최대월급"

from emp)

where sal = 최대월급;

 

 

소진답

SELECT *FROM

(

SELECT

ename,

sal,

DENSE_RANK() OVER(ORDER BY SAL DESC) 순위

FROM EMP)

WHERE 순위=1;

 

select *

from

(select ename, sal,

dense_rank()over(order by sal desc) 순위

from emp)

order by 순위 asc;