[ORACLE] SQL 함수 활용 2

in Study on Oracle
[ORACLE] SQL 함수 활용 2

SQL 함수 활용 2

기본 SQL 이해 - 기본 함수 사용

변환 함수 : 날짜에 TO_CHAR 함수 사용

요소결과예시
YYYY전체 년도를 숫자로 표기2009
YEAR영문의 년도 표기TWO THOUSAND NINE
MM월의 두 자릿수 표기11
MONTH월의 전체 표기NOVEMER
MON월의 세 글자 약식 표기NOV
DY요일의 세 글자 약식 표기FRI
DAY요일의 전체 표기FRIDAY
D해당 주의 날짜 표기6
DD해당 월의 날짜 표기25
DDD해당 년의 날짜 표기329
SELECT  employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired
  FROM  employees
 WHERE  last_name = 'Higgins'
 EMPLOYEE_IDMONTH_HIRED
120506/94

  • 시간 요소는 날짜에서 시간 부분의 형식 지정 가능

    | HH24:MI:SS AM | 15:45:32 PM | | — | — |

      SELECT  TO_CHAR(sysdate, 'YYYY-MM-DD:HH:MI:SS AM')
    FROM  dual;
     TO_CHAR(SYSDATE, ‘YYYY-MM-DD:HH:MI:SSAM’)
    12021-07-10:02:03:57 PM
      -- HH뒤에 24를 추가하면 24시간제로 출력된다.
  -- 24시간제에서는 AM/PM을 쓰지 않는게 자연스러우므로 제외.
    
  SELECT  TO_CHAR(sysdate, 'YYYY-MM-DD:HH24:MI:SS')
    FROM  dual;
     TO_CHAR(SYSDATE, ‘YYYY-MM-DD:HH24:MI:SS’)
    12021-07-10:14:03:57

  • 문자열은 큰 따옴표로 묶어서 추가 가능

    | DD “of” MONTH | 12 of OCTOBER | | — | — |

  • 숫자 접미어는 숫자를 영어 철자로 표기

    | ddspth | fourteenth | | — | — |

    ddspth 는 “날짜-스펠링-서수”를 뜻한다.

      -- 예제
  -- 모든 사원의 성과 채용 날짜를 표시하되 
  -- 날짜를 "Seventeenth of June 1987 12:00:00 AM" 형식으로 출력
    
  SELECT  last_name, TO_CHAR(hire_date, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') HIREDATE
    FROM  employees;
     LAST_NAMEHIREDATE
    1WhalenSeventeenth of September 1987 12:00:00 AM
    2HartsteinSeventeenth of February 1996 12:00:00 AM

    fmDdspth 에서의 “fm”은 0이나 공백을 제거해주는 역할을 한다.

변환 함수 : 숫자에 TO_CHAR 함수 사용

요소결과
9자릿수 표기
0강제 0 표기
$달러 통화기호 표기
L해당 나라의 통화기호 표기
.소수점 표기
,천 단위 구분 기호 표기
SELECT  TO_CHAR(salary, '$99,999.00') SALARY
  FROM  employees
 WHERE  last_name = 'Ernst';
 SALARY
1$6,000.00

정수의 자릿수가 지정된 범위를 초과하는 경우 숫자 대신 “#”으로 값이 출력된다.

함수 중첩

함수는 안쪽부터 실행된다.

F3(F2(F1(col, arg1), arg2), arg3)

-- F1, F2, F3 순으로 실행

SELECT  last_name,
	UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US'))
  FROM  employees
 WHERE  department_id = 60;
 LAST_NAMEUPPER(CONCAT(SUBSTR(LAST_NAME, 1, 8),’_US’))
1HunoldHUNOLD_US
2ErnstERNST_US
3LorentzLORENTZ_US

Null 관련 함수

함수설명
NVLnull 값을 실제 값으로 변환합니다.
NVL2expr1이 null이 아닌 경우 NVL2는 expr2를 반환합니다. expr1이 null인 경우 NVL2는 expr3을 반환합니다. 인수 expr1은 임의의 데이터 유형을 가질 수 있습니다.
NULLIF두 표현식을 비교하여 같으면 null을 반환하고 같지 않으면 첫번째 표현식을 반환합니다.
COALESCE표현식 리스트에서 null이 아닌 첫번째 표현식을 반환합니다.

본래 NULL 값은 “알 수 없는 값”이므로 연산, 비교가 불가능하다.

하지만 위 함수들을 사용하여 연산, 비교를 가능하게 할 수 있다.

  • NVL
    • 표현식의 Data Type이 반드시 일치해야 함 두 표현식의 데이터 타입이 일치하지 않을 경우, 다음의 규칙을 따른다.
      1. expr1이 문자 데이터일 경우, 값을 비교하기 전에 expr2의 데이터 타입을 expr1의 데이터 타입과 일치시킨다. 또한 expr1의 문자 집합에서 VARCHAR2 를 반환한다.
      2. expr1이 숫자 데이터일 경우, 두 표현식 중 더 높은 우선순위를 가지는 데이터 타입으로 나머지를 묵시적 형변환시키고, 그 데이터 타입으로 값을 반환한다.
      3. 형변환이 불가능할 경우, 에러를 출력한다.
        • NVL(commission_pct, 0)
        • NVL(hire_date, ‘01-JAN-97’)
        • NVL(job_id, ‘No Job Yet’)
        SELECT  last_name, salary, NVL(commission_pct, 0),
  	(salary*12) + (salary*12*NVL(commision_pct, 0)) AN_SAL
    FROM  employees;
       LAST_NAMESALARYNVL(COMMISSION_PCT,0)AN_SAL
      1Whalen4400052800
      2Hartstein130000156000
      3Fay6000072000
      4Higgins120000144000
      5Zlotkey105000.2151200

      NVL(expr1, expr2) 는 “expr1”을 “expr2”로 변환하라는 의미이다.

  • NVL2
    • 첫 번째 표현식이 null이 아니면 두 번째 표현식을 반환, 첫 번째 표현식이 null이면 세 번째 표현식을 반환함 - NVL(expr1, expr2, expr3)
      SELECT  last_name, salary, commission_pct,
  	NVL2(commission_pct, 'SAL+COMM', 'SAL') income
    FROM  employees WHERE department_id IN (50, 80);
     LAST_NAMESALARYCOMMISSION_PCTINCOME
    1Mourgos5800(null)SAL
    2Rajs3500(null)SAL
    3Zlotkey105000.2SAL+COMM
    4Abel110000.3SAL+COMM
  • NULLIF
    • NULLIF는 expr1과 expr2를 비교, 두 표현식이 같으면 null을 반환, 다르면 expr1을 반환 - NULLIF(expr1, expr2)
      SELECT  first_name, LENGTH(first_name) "expr1",
  	last_name, LENGTH(last_name) "expr2",
  	NULLIF(LENGTH(first_name), LENGTH(last_name)) result
    FROM  employees;
     FIRST_NAMEexpr1LAST_NAMEexpr2RESULT
    1Ellen5Abel45
    2Curtis6Davies6(null)
    3Lex3De Haan73
    4Bruce5Ernst5(null)
  • COALESCE
    • COALESCE 함수는 여러 대체 값을 수용할 수 있음 - COALESCE(expr1, expr2, … exprn)
    • 첫 번째 표현식이 null이 아닌 경우 COALESCE 는 해당 표현식을 반환, 그렇지 않은 경우 나머지 표현식에 계속 COALESCE 수행
      SELECT  last_name, employee_id,
  	COALESCE(TO_CHAR(commission_pct), TO_CHAR(manager_id),
  		'No commission and no manager')
    FROM  employees;
     LAST_NAMEEMPLOYEE_IDCOALESCE(TO_CHAR(COMMISSI…
    1Whalen200101
    2Hartstein201100
    3King100No commission and no manager
    4Zlotkey149.2
    5Abel174.3

조건부 표현식 관련 함수(DECODE)

SQL 문에서 IF-THEN-ELSE 논리를 사용할 수 있다.

SELECT  last_name, job_id, salary,
	DECODE(job_id, 'IT_PROG',  1.10*salary,
								 'ST_CLERK', 1.15*salary,
								 'SA_REP',   1.20*salary,
				 salary)
	REVISED_SALARY
FROM  employees;
 LAST_NAMEJOB_IDSALARYREVISED_SALARY
1ErnstIT_PROG60006600
2LorentzIT_PROG42004620
3MourgosST_MAN58005800
4RajsST_CLERK35004025
5TaylorSA_REP860010320

job_id가 ‘IT_PROG’일 경우 salary에 1.10을 곱하는 식이다.

job_id가 ‘IT_PROG’, ‘ST_CLERK’, ‘SA_REP’ 모두 아닐 경우 그냥 salary를 출력한다.

모든 행에 대해서 값을 비교하게 된다.

조건부 표현식 관련 함수(CASE)

DECODE 와 마찬가지로 SQL 문에서 iF-THEN-ELSE 논리를 사용할 수 있다.

SELECT  last_name, job_id, salary,
		CASE job_id WHEN 'IT_PROG'  THEN 1.10*salary
								WHEN 'ST_CLERK' THEN 1.15*salary
								WHEN 'SA_REP'   THEN 1.20*salary
		ELSE salary END "REVISED_SALARY"
  FROM  employees;
 LAST_NAMEJOB_IDSALARYREVISED_SALARY
1WhalenAD_ASST44004400
2HunoldIT_PROG90009900
3MourgosST_MAN58005800
4RajsST_CLERK35004025
5TaylorSA_REP860010320

job_id가 ‘IT_PROG’일 경우 salary에 1.10을 곱한다.

job_id가 ‘IT_PROG’, ‘ST_CLERK’, ‘SA_REP’ 모두 아닐 경우 그냥 salary를 출력한다.

DECODE 와 비슷하지만 WHEN , THEN 정도가 추가되었다.

또 조건비교에 있어서 크다(>), 작다(<), 같다(=) 로직이 가능하다.

SELECT  last_name, salary,
		(CASE WHEN salary < 5000  THEN 'Low'
					WHEN salary < 10000 THEN 'Medium'
					WHEN salary < 20000 THEN 'Good'
					ELSE 'Excellent'
		 END) as qualified_salary
  FROM  employees;

DECODE 는 단순한 equal(=) 비교이다.

단순 비교가 필요할 때는 DECODE 를, 크기 비교와 같은 로직을 사용해야 할 때는 CASE 를 사용하도록 하자.

CASE 함수 응용 - Virtual Column

가상 칼럼이란?

테이블에 포함되는 일반적인 컬럼은 해당 데이터 타입에 맞는 데이터만 갖지만, 가상 칼럼은 여러 개의 데이터를 연산한 결과값을 저장하는 칼럼이다(11g NF).

CREATE TABLE copy_emp as SELECT * FROM emp; -- emp 테이블의 복사본 copy_emp 테이블 생성
 ALTER TABLE copy_emp -- copy_emp 테이블에 sal_grade라는 virtual column 생성
   ADD (sal_grade varchar(6)
		AS (CASE
					WHEN sal between 1 and 1000 THEN 'LOW'
					WHEN sal between 1001 and 2000 THEN 'MEDIUM'
					WHEN sal between 2001 and 3000 THEN 'HIGH'
					ELSE 'ULTRA'
				END) virtual);

SELECT  ename, sal, sal_grade
	FROM  copy_emp;
INSERT  INTO copy_emp(empno, ename, sal) values (9999,'LEE',4000);
-- 새 데이터 추가시 sal_grade에 해당하는 데이터는 넣지 않았지만, 자동으로 값이 할당됨

SELECT  table_name, column_name, data_type, data_default
  FROM  user_tab_columns
 WHERE  table_name='COPY_EMP';
-- virtual column에 대한 정보는 user_tab_columns라는 dictionary에서 볼 수 있음
-- data_default에서 해당 case문 확인 가능

pivot / unpivot 함수

  • pivot()
    • row 형태의 데이터를 column 형태로 보여주는 쿼리를 row-to-column
  • unpivot()
    • column 형태를 row 형태로 보여주는 쿼리를 column-to-row

DECODE 를 이용하여 복잡한 조건식 평가나 여러 칼럼을 이용한 조건식을 평가해야 하는 경우, 문장의 복잡성과 성능에 문제점을 가질 수 있다.

이를 11g 부터는 PIVOT 과 UNPIVOT 절을 통해서 보다 쉽게 구현할 수 있다.

   SELECT  department_id,
		   SUM(DECODE(job_id, 'IT_PROG', salary)) "IT MAN",
		   SUM(DECODE(job_id, 'SA_REP', salary)) "SALES MAN",
		   SUM(DECODE(job_id, 'ST_CLERK', salary)) "STOCK MAN"
     FROM  employees
 GROUP BY department_id;

-- 위는 아래와 같다.

SELECT  *
  FROM  (SELECT  department_id, job_id, salary
					 FROM  employees)
 PIVOT  (SUM(salary) FOR job_id IN ('IT_PROG' AS "IT MAN"
																	, 'SA_REP' AS "SALES MAN"
																	, 'ST_CLERK' AS "STOCK MAN"));
 DEPARTMENT_IDIT MANSALES MANSTOCK MAN
1(null)(null)7000(null)
290(null)(null)(null)
320(null)(null)(null)
4110(null)(null)(null)
550(null)(null)11700
  • 예시 1 : 부서 별 사원수 집계 현황
   SELECT  department_id, COUNT(*)
     FROM  employees
 GROUP BY  department_id;
 DEPARTMENT_IDCOUNT(*)
1 1
2903
3202
41102
5505
6803
7603
8101
SELECT  *
  FROM  (SELECT  department_id
					 FROM  employees)
 PIVOT  (COUNT(*)
				 FOR (department_id)
				 IN (10, 20, 50, 60, 80, 90, 110, 190));
 102050608090110190
112533320
  • 예시 2 : 사원의 월 별 급여 집계
SELECT  * FROM
	(SELECT '1월' 월, '이태정' 성명, 110 급여 FROM DUAL UNION ALL
	 SELECT '1월' 월, '이태정' 성명, 305 급여 FROM DUAL UNION ALL
	 SELECT '1월' 월, '이태서' 성명, 210 급여 FROM DUAL UNION ALL
	 SELECT '2월' 월, '이태정' 성명, 120 급여 FROM DUAL UNION ALL
	 SELECT '2월' 월, '이태서' 성명, 220 급여 FROM DUAL)
 PIVOT  (SUM(급여) FOR 월 IN ('1월', '2월'));

 SELECT  * FROM
	( SELECT '이태정' 성명, 140 일월, 120 이월 FROM DUAL UNION ALL
	  SELECT '이태서' 성명, 210 일월, 220 이월 FROM DUAL)
UNPIVOT  (급여 FOR 월 IN (일월, 이월));
 성명‘1월’‘2월’
1이태정415120
2이태서210220
 성명월급여
1이태정일월140
2이태정이월120
3이태서일월210
4이태서이월220

© 2022. All rights reserved. 신동민의 블로그