기록을 합시다.
[SQL] ORACLE SQL 공부 -4- 본문
입사 첫 주라 정신이 이상하게 없어서 공부를 못 했다. ㄱ=.. 정신 차려야 할텐데..
하루에 1~2시간이라도 짬 내서 공부해야 할 것 같다..
건수 출력하기(COUNT)
SELECT COUNT(empno)
FROM emp;COUNT(EMPNO)
------------
14- COUNT 함수는 NULL 값을 무시한다.
SELECT COUNT(COMM)
FROM emp;COUNT(COMM)
-----------
4 데이터 분석 함수로 순위 출력하기 (RANK)
SELECT ename, job, sal, RANK() over (ORDER BY sal DESC) 순위
FROM emp
WHERE job in ('ANALYST', 'MANAGER');ENAME JOB SAL 순위
---------- --------- ---------- ----------
FORD ANALYST 3000 1
SCOTT ANALYST 3000 1
JONES MANAGER 2975 3
BLAKE MANAGER 2850 4
CLARK MANAGER 2450 5- RANK()는 순위를 출력하는 데이터 분석 함수
- RANK() 뒤에 OVER 다음에 나오는 괄호 안에 출력하고 싶은 데이터를 정렬하는 SQL 문장을 넣으면 그 칼럼 값에 대한 데이터의 순위가 출력된다.
- 위의 예제에서는 월급이 높은 사원부터 출력되게 ORDER BY 절을 사용하였다.
- 출력된 순위에 1등이 2명이라면, 3등이 바로 출력되는데 이 때, 3등이 아니라 2등을 출력하기 위해서는 DENSE_RANK 함수를 사용해야 한다.
SELECT ename, job, sal, DENSE_RANK() over (ORDER BY sal DESC) 순위
FROM emp
WHERE job in ('ANALYST', 'MANAGER');ENAME JOB SAL 순위
---------- --------- ---------- ----------
FORD ANALYST 3000 1
SCOTT ANALYST 3000 1
JONES MANAGER 2975 2
BLAKE MANAGER 2850 3
CLARK MANAGER 2450 4- 아래는 직업별로 월급이 높은 순위를 부여해서 각각 출력
SELECT ename, sal, job, RANK() over (PARTITION BY job ORDER BY sal DESC) as 순위
FROM emp;ENAME SAL JOB 순위
---------- ---------- --------- ----------
SCOTT 3000 ANALYST 1
FORD 3000 ANALYST 1
MILLER 1300 CLERK 1
ADAMS 1100 CLERK 2
JAMES 950 CLERK 3
SMITH 800 CLERK 4
JONES 2975 MANAGER 1
BLAKE 2850 MANAGER 2
CLARK 2450 MANAGER 3
KING 5000 PRESIDENT 1
ALLEN 1600 SALESMAN 1
ENAME SAL JOB 순위
---------- ---------- --------- ----------
TURNER 1500 SALESMAN 2
MARTIN 1250 SALESMAN 3
WARD 1250 SALESMAN 3- 직업 별로 묶어서 순위를 부여하기 위해 ORDER BY 앞에 PARTITION BY JOB을 이용하였다.
데이터 분석 함수로 순위 출력 (DENSE_RANK)
SELECT ename, job, sal, RANK() over (ORDER BY sal DESC) AS RANK,
DENSE_RANK() over(ORDER BY sal DESC) AS DENSE_RANK
FROM emp
WHERE job in ('ANALYST', 'MANAGER');ENAME JOB SAL RANK DENSE_RANK
---------- --------- ---------- ---------- ----------
FORD ANALYST 3000 1 1
SCOTT ANALYST 3000 1 1
JONES MANAGER 2975 3 2
BLAKE MANAGER 2850 4 3
CLARK MANAGER 2450 5 4- 위의 쿼리 결과는 RANK와 DENSE_RANK의 차이점을 알려주고 있다.
- RANK함수는 1위가 두 개 있으면, 2위 없이 바로 그 다음 순위가 3위이다.
- 그에 반해 DENSE_RANK함수는 1위가 두 개 이 있으면, 그 다음 순위는 2위이다.
- 아래의 쿼리는 81년도에 입사한 사원들의 직업, 이름, 월급, 순위를 출력하는데, 직업별로 월급이 높은 순서대로 순위를 부여한 쿼리이다.
SELECT job, ename, sal, DENSE_RANK() OVER (PARTITION BY job ORDER BY sal DESC) 순위
FROM emp
WHERE hiredate BETWEEN to_date('1981/01/01', 'RRRR/MM/DD')
AND to_date('1981/12/31', 'RRRR/MM/DD');JOB ENAME SAL 순위
--------- ---------- ---------- ----------
ANALYST FORD 3000 1
CLERK JAMES 950 1
MANAGER JONES 2975 1
MANAGER BLAKE 2850 2
MANAGER CLARK 2450 3
PRESIDENT KING 5000 1
SALESMAN ALLEN 1600 1
SALESMAN TURNER 1500 2
SALESMAN WARD 1250 3
SALESMAN MARTIN 1250 3데이터 분석 함수로 등급 출력하기(NTILE)
- 등급을 나누기 위해서는 NTILE 함수를 이용할 수 있다.
SELECT ename, job, sal,
NTILE(4) over (order by sal desc nulls last) 등급
FROM emp
WHERE job in ('ANALYST', 'MANAGER', 'CLERK');ENAME JOB SAL 등급
---------- --------- ---------- ----------
FORD ANALYST 3000 1
SCOTT ANALYST 3000 1
JONES MANAGER 2975 1
BLAKE MANAGER 2850 2
CLARK MANAGER 2450 2
MILLER CLERK 1300 3
ADAMS CLERK 1100 3
JAMES CLERK 950 4
SMITH CLERK 800 4- NTILE의 괄호 안에 넣은 숫자는 등급을 해당 숫자로 나누겠다는 뜻이다.
- 위의 쿼리는 월급을 기준으로 4개의 등급으로 나눈 것이다.
- nulls last는 null은 맨 아래에 출력하겠다는 뜻이다.
데이터 분석 함수로 순위의 비율 출력하기(CUME_DIST)
SELECT ename, sal, RANK() over (order by sal desc) as RANK,
DENSE_RANK() over (order by sal desc) as DENSE_RANK,
CUME_DIST() over (order by sal desc) as CUM_DIST
FROM emp;ENAME SAL RANK DENSE_RANK CUM_DIST
---------- ---------- ---------- ---------- ----------
KING 5000 1 1 .0714285714
SCOTT 3000 2 2 .214285714
FORD 3000 2 2 .214285714
JONES 2975 4 3 .285714286
BLAKE 2850 5 4 .357142857
CLARK 2450 6 5 .428571429
ALLEN 1600 7 6 .5
TURNER 1500 8 7 .571428571
MILLER 1300 9 8 .642857143
WARD 1250 10 9 .785714286
MARTIN 1250 10 9 .785714286
ENAME SAL RANK DENSE_RANK CUM_DIST
---------- ---------- ---------- ---------- ----------
ADAMS 1100 12 10 .857142857
JAMES 950 13 11 .928571429
SMITH 800 14 12 1- 위의 결과는 1위부터 14위까지 있다. 사원 KING의 CUME_DIST 열의 첫 번째 행 0.071428571인 것은 1/14로 계산된 결과이다.
- 전체 14등 중 1등의 비율인 0.07로 출력되었다.
- 2등은 2명이어서 3/14로 계산되어 0.214285714로 출력되었다.
데이터 분석 수로 데이터를 가로로 출력(LISTAGG)
SELECt deptno, LISTAGG(ename, ',') within group (order by ename) as EMPLOYEE
FROM emp
GROUP BY deptno;DEPTNO
----------
EMPLOYEE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10
CLARK,KING,MILLER
20
ADAMS,FORD,JONES,SCOTT,SMITH
30
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARDDEPTNO
----------
EMPLOYEE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10
CLARK,KING,MILLER
20
ADAMS,FORD,JONES,SCOTT,SMITH
30
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD- LISTAGG 함수는 데이터를 가로로 출력하는 함수
- LISTAGG에 구분자로 콤마를 사용하여 이름이 콤마로 구분
- WITHIN GROUP은 ‘~이내의’라는 뜻으로 group 다음에 나오는 괄호에 속한 그룹의 데이터를 출력하겠다는 뜻
SELECT deptno, LISTAGG(ename, ',') within group (order by ename) as EMPLOYEE
FROM emp
GROUP BY job;JOB
---------
EMPLOYEE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ANALYST
FORD,SCOTT
CLERK
ADAMS,JAMES,MILLER,SMITH
MANAGER
BLAKE,CLARK,JONES
JOB
---------
EMPLOYEE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRESIDENT
KING
SALESMAN
ALLEN,MARTIN,TURNER,WARD데이터 분석 함수로 바로 전 행과 다음 행 출력 (LAG, LEAD)
SELECT empno, ename, sal,
LAG(sal, 1) over (order by sal asc) "전 행",
LEAD(sal, 1) over (order by sal asc) " 다음 행"
FROM emp
WHERE job in ('ANALYST', 'MANAGER');EMPNO ENAME SAL 전 행 다음 행
---------- ---------- ---------- ---------- ----------
7782 CLARK 2450 2850
7698 BLAKE 2850 2450 2975
7566 JONES 2975 2850 3000
7902 FORD 3000 2975 3000
7788 SCOTT 3000 3000- LAG 함수는 바로 전 행의 데이터를 출력하는 함수
- LEAD 함수는 바로 다음 행의 데이터를 출력하는 함수
SELECT empno, ename, hiredate,
LAG(hiredate, 1) over (order by hiredate asc) "전 행",
LEAD(hiredate, 1) over (order by hiredate asc) "다음 행"
FROM emp
WHERE job in ('ANALYST', 'MANAGER'); EMPNO ENAME HIREDATE 전 행 다음 행
---------- ---------- -------- -------- --------
7566 JONES 81/04/01 81/05/01
7698 BLAKE 81/05/01 81/04/01 81/05/09
7782 CLARK 81/05/09 81/05/01 81/12/11
7902 FORD 81/12/11 81/05/09 82/12/22
7788 SCOTT 82/12/22 81/12/11SELECT deptno, empno, ename, hiredate,
LAG(hiredate, 1) over (partition by deptno order by hiredate asc) "전 행",
LEAD(hiredate, 1) over (partition by deptno order by hiredate asc) "다음 행"
FROM emp;DEPTNO EMPNO ENAME HIREDATE 전 행 다음 행
---------- ---------- ---------- -------- -------- --------
10 7782 CLARK 81/05/09 81/11/17
10 7839 KING 81/11/17 81/05/09 82/01/11
10 7934 MILLER 82/01/11 81/11/17
20 7369 SMITH 80/12/11 81/04/01
20 7566 JONES 81/04/01 80/12/11 81/12/11
20 7902 FORD 81/12/11 81/04/01 82/12/22
20 7788 SCOTT 82/12/22 81/12/11 83/01/15
20 7876 ADAMS 83/01/15 82/12/22
30 7499 ALLEN 81/02/11 81/02/23
30 7521 WARD 81/02/23 81/02/11 81/05/01
30 7698 BLAKE 81/05/01 81/02/23 81/08/21
DEPTNO EMPNO ENAME HIREDATE 전 행 다음 행
---------- ---------- ---------- -------- -------- --------
30 7844 TURNER 81/08/21 81/05/01 81/09/10
30 7654 MARTIN 81/09/10 81/08/21 81/12/11
30 7900 JAMES 81/12/11 81/09/10- 위의 쿼리는 deptno로 구분해서 나누고, 고용된 날 순서대로 그 전 행과 그 앞행의 칼럼들을 출력해주는 쿼리이다.
원래 partition에 대해 잘 몰랐었는데, 직접 쿼리 실행해보기도 하고 책에 예제도 풍부해서 이해가 잘 간 것 같다.
근데 역시 within group은 무슨 느낌인지는 알겠는데 정확히는 이해가 안 가는 그런 느낌.. 보충학습이 필요할 것 같다.
'공부 > DB' 카테고리의 다른 글
| [Oracle] With문 VS Select 서브 쿼리 (1) | 2023.12.16 |
|---|---|
| ORA-29275 부분 다중 바이트 문자 오류 해결 (0) | 2023.10.19 |
| [SQL] ORACLE SQL 공부 -2- (0) | 2023.07.29 |
| [SQL] ORACLE SQL 공부 -1- (0) | 2023.07.26 |
| [docker] 팀원들이랑 사용할 oracle 도커 이미지 만들기 (1) | 2023.05.30 |
'공부/DB' Related Articles
more
Comments