[SQL] DB스터디 - 03

데이터 형 변환, 그룹 함수, GROUP BY절, HAVING절

변환 함수

- 데이터 유형을 변환 할 때는 함수를 사용

- 잘못된 데이터 타입이 입력되면 '암시적 데이터 형 변환'을 시킴

-> Oracle DB 한정

-> 에러를 피할 수 있음

- '명시적 데이터 형 변환'을 하는 것이 가장 좋음

-> 실제로 값을 변환 시키는 것

---

암시적 데이터 유형 변환

- 사용자가 입력한 값이 단일적인 값인 경우 사용 가능

- 변환이 가능한 문자 혹은 숫자같은 경우에 사용 됨

- LIKE 명령어의 경우 문자만 검사 하지만 조건에 숫자가 들어가면 암시적으로 형 변환을 시킴

예)

-> 

SELECT *

FROM emp

WHERE deptno = 10;

-- 10이 숫자

-> 

SELECT *

FROM emp

WHERE deptno = '10';

-- 10이라는 문자

-- 암시적인 형 변환을 시켜 줌

-> 동일한 결과를 출력 해 줌

---

명시적 데이터 유형 변환

- 날짜와 숫자는 문자(Character)형태로 변환 시키는 것은 가능

- 그 외에 명시적으로 형 변환을 시키려면 TO_NUMBER, TO_DATE 등과 같은 함수 사용

예) 명시적 형 변환과 암시적 형 변환의 차이

-> 

SELECT ROUND('2018/12/28','DD')

FROM DUAL;

-- 에러 : 실제로 /와 DD같은 경우 숫자로 바꿀 수 있는 값이 없음

-> 

SELECT ROUND(TO_DATE('2018/12/28'),'DD')

FROM DUAL;

-- 성공 : 문자를 날짜로 변환 시키고 ROUND함수를 적용 시킨다.

-> 

SELECT *

FROM emp

WHERE hiredate = '1981/09/28';

-- 성공 : 문자 이지만 시스템 환경에 따라 문자의 형태가 날짜로 되어 있으면 가능

---

날짜에 TO_CHAR 함수 사용

- 적절한 형식을 이용해서 날짜를 변환하여 출력할 수 있음

예) 날짜 형식 변환

-> 

SELECT hiredate,

TO_CHAR(hiredate,'YYYY'),

TO_CHAR(hiredate,'MM'),

TO_CHAR(hiredate,'DD'),

TO_CHAR(hiredate,'DD MONTH, YYYY')

FROM emp;

-- 형 변환 TO_ 함수 사용

-- | 1987/04/19 | 1987 | 04 | 19 | 19 4월 , 1987 |

-> 

SELECT hiredate,

TO_CHAR(hiredate,'DD MONTH, YYYY'),

TO_CHAR(hiredate,'DD MONTH, YYYY','NLS_DATE_LANGUAGE=AMERICAN')

FROM emp;

-- | 1987/04/19 | 19 4월 , 1987 | 19 APRIL    , 1987 |

->

SELECT hiredate, 

TO_CHAR(hiredate,'DD MONTH, YYYY'),

TO_CHAR(hiredate,'fmDD Month, YYYY')

FROM emp;

-- 대,소문자 구분 함

-- 언어, 지역, 날짜언어 AMERICAN으로 설정 후

-- | 19-APR-87 | 19 APRIL    , 1987 | 19 April, 1987 |

-> 

SELECT hiredate,

TO_CHAR(hiredate,'HH:MI:SS'),

TO_CHAR(hiredate,'DD Month, YYYY HH24:MI:SS'),

TO_CHAR(hiredate,'DD Month, YYYY HH:MI:SS AM')

FROM emp;

-- 시간 표시

-- W: 주차, WW: 해당 년도의 주차, Q: 분기(1~4분기), DAY: 요일(대,소문자 구분), DY: 요일의 숫자 값

-- Ddspth: 날짜 서수 표현

---

숫자에 TO_CHAR 함수 사용

- 숫자의 출력 형태를 바꿔 주면서 문자로 변경 해 줌

예)

-> 

SELECT sal,

TO_CHAR(sal, '999,999.00'),

TO_CHAR(sal,'000,999.00'),

TO_CHAR(sal, 'L999,999.00')

FROM emp;

-- | 3000    | 3,000.00  | 003,000.00 | $3,000.00 |

---

TO_NUMBER와 TO_DATE의 사용

- TO_NUMBER 함수를 사용하여 문자열을 숫자 형식으로 변환할 수 있음

- TO_DATE 함수를 사용하여 문자열을 날짜 형식으로 변환할 수 있음

- 단, 숫자로 바꾸거나 날짜로 바꾸거나 해당 형식은 맞춰 줘야 함

- 작은 따옴표가 되어 있는 '문자'를 변환하는 과정 임

-> 

SELECT TO_NUMBER('01234567')

FROM DUAL;

-- 문자를 숫자로 변환

-- | 1234567 |

-> 

SELECT TO_DATE('2018/12/29','YYYY/MM/DD')

FROM DUAL;

-- 문자를 날짜로 변환

-- | 28-DEC-18 |

---

함수의 중첩

- 함수는 중첩이 가능 함

- 가장 안쪽에 있는 함수부터 수행

---

NULL 처리 함수

- 데이터 타입에 상관 없이 사용 가능

- NVL : NULL 값에 임의의 값을 추가

-> SELECT를 할 때 NULL 값을 변환할 때 해당 컬럼의 형태를 맞춰서 변환 해주어야 함

-> 숫자만 있는 컬럼 mgr에 NULL값을 변환 할 때, NVL(mgr, 'No') 라고 작성하는 SELECT문은 불가능

-> 따라서, 형 변환을 맞춰 주어야 한다. --> NVL(TO_CHAR(mgr), 'No')

예) NVL

-> 

SELECT comm, NVL(comm, 0)

FROM emp;

-- | NULL | 0    |

- NVL2 : 컬럼의 값이 NULL인 경우와 NULL이 아닌 경우를 처리하는 함수

---

조건부 처리 함수

- SQL 문에서 IF-ELSE 논리를 사용할 수 있음

- CASE식, DECODE함수 가 있음

- UNION ALL 명령어를 사용하는 것 보다 SQL 명령문의 성능을 증가시킬 수 있음

- 기본적으로 WHERE절은 조건에 맞지 않는 부분은 모두 제거하여 출력 해 줌

예) DEPTNO가 10은 salary 10%증가, 20은 20%증가, 30은 30%증가 시키는 SQL명령어

-> CASE - ELSE 식

SELECT deptno, empno, ename, sal,

CASE deptno

WHEN 10 THEN sal * 1.1

WHEN 20 THEN sal * 1.2

WHEN 30 THEN sal * 1.3

ELSE sal END

FROM emp;

-- WHEN 다음에 직접 조건 식을 비교할 수 있음

-- IN, LIKE, BETWEEN 등과 같은 연산자도 사용 가능

-- 해당 조건들을 AND, OR로 묶을 수 있음

-> DECODE 함수

SELECT deptno, empno, ename, sal,

DECODE(deptno,

10, sal * 1.1,

20, sal * 1.2,

30, sal * 1.3,

sal)

FROM emp;

-- 해당 컬럼의 특정 값이 있는 경우에 적합 함

-- 특정 값이 있는 경우에만 수행하는 함수

-> DECODE 함수에 NULL이 존재하는 경우

SELECT MAX(DECODE(job, 'MANAGER', NULL, sal))

FROM emp

WHERE deptno = 30;

-- 검색된 결과의 값이 문자열로 되어있기 때문에 950이 검색 됨

-- 데이터 타입이 없는 NULL이 연산에 참여하게 되기 때문에 암시적 형 변환이 이루어 짐

-- 따라서 모든 SELECT 반환 값이 모두 문자열로 변경 됨

-- DECODE에서는 비교하는 값의 형이 맞지 않을 때 첫 번째 값에 맞춰서 암시적 형 변환 진행

-- NULL인 경우 VARCHAR2로 변환 됨

-> 올바르게 만든 SQL 명령문

SELECT MAX(DECODE(job, 'MANAGER', TO_NUMBER(NULL), sal))

FROM emp

WHERE deptno = 30;

---

그룹 함수

- 모든 그룹 함수는 NULL은 제외하고 계산 됨

- ORDER BY를 사용해서 NULL을 정렬 시킬 때 처음 혹은 뒤에 나오도록 설정할 수 있음

예)

->

SELECT *

FROM emp

ORDER BY comm DESC NULLS LAST;

AVG 및 SUM 함수

- 숫자 데이터의 합계와 평균을 반환 시키는 함수

- 문자나 날짜가 포함된 컬럼에 사용되면 에러

- AVG는 값이 있는 집합의 평균 값 이므로 주의 하여 사용

- SUM( column ) / total_rows 와 같이 사용하면 전체 행의 개수로 나누게 됨

- AVG( NVL( column, 0 ) )으로 NULL값을 0으로 적용해서 개산할 수 있음

예)

->

SELECT SUM(sal)

FROM emp;

SELECT AVG(sal)

FROM emp;

MIN과 MAX 함수

- 정렬이 되는 모든 데이터 타입에 사용 가능

- 날짜, 숫자, 문자 등등 가능

예)

->

SELECT MAX(sar)

FROM emp;

SELECT MIN(hiredate)

FROM emp;

COUNT 함수

- 행의 개수를 집계하는 함수

- 따로 컬럼 명이 입력되지 않음

예)

->

SELECT COUNT(*)

FROM emp;

SELECT COUNT(deptno)

FROM emp;

-- NULL이 없는 행의 개수

SELECT COUNT(DISTINCT deptno)

FROM emp;

-- 중복을 제거 한 행의 개수

---

데이터 그룹 생성 : GROUP BY 절

- 테이블의 행을 더 작은 그룹으로 나눔

- WHERE절 다음에 위치

- 그룹을 지정하고 싶은 컴럼의 명칭을 지정할 수 있음

- 주의점 : 모든 컬럼은 동일한 개수의 행을 가져야 함

- 즉, SELECT의 결과노 나오는 표집합은 항상 2차원 배열의 구조를 가짐

- GROUP BY 절은 SELECT 리스트에 없어도 가능

- 여러 가지로 그룹을 나눌 수 있음

예) 

-> 부서별로 각각의 salary를 구하는 SQL

SELECT SUM(sal)

FROM emp

GROUP BY deptno;

-- 각 그룹 별로 한 번씩 SUM함수 호출

-> 에러 구문

SELECT job, deptno, SUM(sal)

FROM emp;

GROUP BY deptno;

-- 에러가 나오는 SQL 명령문

-- job에서 나오는 결과의 행의 개수가 맞지 않으므로 에러

-> 년도별 상품 총 합

SELECT

    TO_CHAR(time_id,'YYYY'),

    SUM(amount_sold)

FROM sales

GROUP BY TO_CHAR(time_id,'YYYY');

-> 년도별로 동일한 상품들의 합

SELECT

    TO_CHAR(time_id,'YYYY'),

    prod_id,

    SUM(amount_sold)

FROM 

    sales

GROUP BY 

    TO_CHAR(time_id,'YYYY'),

    prod_id;

---

HAVING 절

- WHERE절과 같은 작업을 하는 절

- GROUP BY절에 조건을 추가하기 위해서는 HAVING 절을 이용

- HAVING절을 사용하는 이유 : WHERE절이 GROUP BY절 보다 먼저 실행 되기 때문에 그룹을 나눌 수 없음

예)

-> employees테이블에 부서별로 그룹이 된 상태에서 salary의 총 합이 10000보다 큰 경우만 출력

SELECT 

    department_id, MAX(salary)

FROM 

    employees

GROUP BY 

    department_id

HAVING

    MAX(salary)>10000;

->

-- HAVING절을 이용한 GROUP BY의 조건절

-- 테이블 emp에서 부서별로 GROUP을 진행 하고 sal의 합이 10000보다 큰 경우

SELECT 

deptno, SUM(sal)

FROM 

emp

GROUP BY 

deptno

HAVING 

SUM(sal) > 10000

AND 

deptno IN (10,20);

-- deptno의 가지 수가 100가지 인 경우에는 효율이 떨어짐(index활용 불가)

SELECT 

deptno, SUM(sal)

FROM 

emp

WHERE 

deptno IN (10,20)

GROUP BY 

deptno

HAVING 

SUM(sal) > 10000;

-- deptno의 가지 수를 먼저 줄여 놓고 질의를 수행(index활용 가능)

    

---

ROLLUP, CUBE

- GROUP BY절에서 사용

- GROUP이 된 집합별로 추가 결과값을 출력할 수 있음

- ROLLUP혹은 CUBE에서 첫 번째로 지정하는 컬럼을 기준으로 그룹핑을 실시

- 작업 단위를 재한할 수 있음

예)

->

-- 부서별 급여의 합

SELECT deptno, job, SUM(sal)

FROM emp

GROUP BY deptno, ROLLUP(job) ;

-- em테입블의 사원 정보를 검색하면서 각 부서의 급여 합계를 출력

SELECT 

deptno, empno, ename, SUM(sal) AS sal

FROM emp

GROUP BY 

deptno, ROLLUP((empno,ename)) ;

-- emp 테이블에서 deptno, job 컬럼으로 Grouping 된 급여의 합계를 PIVOTING시켜서 출력

-- 1차 적으로 DB에서 데이터 출력 형태를 가공 시킬 수 있음

-- 단일행 함수를 사용해서 DECODE를 사용하는 것은 좋지 않음

-- 이유 : DECODE는 단일행 함수 이기 때문에 DECODE한번에 모든 행의 수 만큼 돌아야 하기 때문

SELECT 

    deptno, 

    NVL(SUM(DECODE(job, 'ANALYST', sal)),0) AS analyst,

    NVL(SUM(DECODE(job,'CLERK', sal)),0) AS clerk,

    NVL(SUM(DECODE(job,'MANAGER', sal)),0) AS manager,

    NVL(SUM(DECODE(job,'PRESIDENT', sal)),0) AS president,

    NVL(SUM(DECODE(job,'SALESMAN', sal)),0) AS salesman

FROM emp

GROUP BY deptno

ORDER BY deptno ;

-- PIVOT절을 사용

SELECT *

FROM ( SELECT deptno, job, sal

FROM emp )

PIVOT (

SUM(sal) FOR job IN 

            (

                'ANALYST' AS analyst,

                'CLERK' AS clerk,

                'MANAGER' AS manager,

                'PRESIDENT' AS president,

                'SALESMAN' AS salesman )

        )

ORDER BY deptno ;

-- 결과 --