(스크랩)오라클 함수 정리

DECODE 함수

오라클에서만 지원

SELECT 내 비교 연산

이중으로 DECODE문 안에 DECODE 사용 가능

형식 1 

DECODE(컬럼명, '조건', 같으면, 다르면)

ex) DECODE(depth, '10', '기획부', '영업부')

형식 2

DECODE(컬럼명, '조건1', '값1', '조건2', '값2', '조건3', '값3', ...)

ex) DECODE(depth, '10', '기획부', '20', '영업부', 30, '비서실')

SUBSTR 함수

ORACLE 내장함수에는 LEFT(), RIGHT() 함수가 없다.

SUBSTR 함수를 통해 똑같이 구현이 가능하다.

ex) 3번째 자리부터 쭉

SELECT SUBSTR('ABC권경안Z9', 3)FROM DUAL;

C권경안Z9

ex) 4번째 자리부터 2글자

SELECT SUBSTR('ABC권경안Z9' 4, 2)FROM DUAL;

ex) 우측부터 추출하고 싶다면 마이너스(-)기호를 사용하면 된다. 우측 2글자

SELECT SUBSTR('ABC권경안Z9', -2)FROM DUAL;

Z9

UNION과 UNION ALL의 차이 및 주의 사항

http://intomysql.blogspot.kr/2011/01/union-union-all.html

ANSI SQL에서 제안하는 집합 연산 'UNION', 'INTERSECT', 'MINUS' 중에서 

MYSQL에서는 UNION 집합 연산만 제공하고 있다.

(하지만 MYSQL에서 INTERSECT나 MINUS를 다른 형태의 쿼리로 풀어서 사용할 수 있다.)

이 글에서는 UNION에 대해서 좀 더 자세히 알아 보고자 한다.

UNION 집합 연산은 다시 아래와 같이 두 가지 종류로 나누어진다.

- UNION ALL

- UNION DISTINCT

우리가 일반적으로 사용하는 방식인 아무런 추가 키워드 없이 UNION만 사용하는 것은 UNION DISTINCT를 줄여서 사용하고 있는 것이다.

UNION ALL과 UNION DISTINCT를 레코드가 많은 결과에 대해서 적용해 본 사람은 아마도 둘의 처리 방식에 대해서 의구심을 가져 본 적이 있을 것이다.

레코드 건 수가 많아지면 많아질수록 그 성능 차이는 엄청난 차이를 보여줄 것이다.

우선, 아래와 같이 2개씩 동일한 레코드 데이터를 가지고 있는 tab1과 tab2라는 테이블이 있다.

mysql>SELECT fdpk, fddata FROM tab1;
+------+--------+
| fdpk | fddata |
+------+--------+
|    1 | data1  |
|    2 | data2  |
+------+--------+
2 rows in set (0.00 sec)

mysql>SELECT fdpk, fddata FROM tab2;
+------+--------+
| fdpk | fddata |
+------+--------+
|    1 | data1  |
|    2 | data2  |
+------+--------+
2 rows in set (0.01 sec)

그러면 이 두 개의 테이블에 대해 각각 UNION과 UNION ALL을 사용하는 쿼리를 실행해보자.

mysql>SELECT fdpk, fddata
    -> FROM (
    ->   SELECT fdpk, fddata FROM tab1
    ->   UNION ALL
    ->   SELECT fdpk, fddata FROM tab2
    -> ) x;
+------+--------+
| fdpk | fddata |
+------+--------+
|    1 | data1  |
|    2 | data2  |
|    1 | data1  |
|    2 | data2  |
+------+--------+
4 rows in set (0.00 sec)

mysql>SELECT fdpk, fddata
    -> FROM (
    ->   SELECT fdpk, fddata FROM tab1
    ->   UNION
    ->   SELECT fdpk, fddata FROM tab2
    -> ) x;
+------+--------+
| fdpk | fddata |
+------+--------+
|    1 | data1  |
|    2 | data2  |
+------+--------+
2 rows in set (0.00 sec)

두 개의 쿼리 실행결과 UNION은 레코드가 반으로 줄었다.

이미 다들 알고 있다시피 UNION은 UNION DISTINCT와 동일한 작업을 하기 때문에 중복되는 레코드를 제거했음을 알 수 있다.

하지만, UNION ALL의 경우에는 별도의 중복 제거 과정을 거치지 않고 그냥 결과를 내려준다.

아주 중요한 내용이지만, 사실 이 내용을 다들 별로 신경쓰지 않고 모두들 UNION을 즐겨 사용한다.

안타깝게도, MYSQL의 실행계획에서는 둘의 차이를 전혀 느낄 수 없다.

TRIM 함수

http://dongdongfather.tistory.com/35

LTRIM은 왼쪽 잘라내기, RTRIM은 오른쪽 잘라내기라고 얘기했었습니다. TRIM은 문자열 양쪽을 잘라내는 함수입니다.

TRIM_SOURCE : TRIM 처리 할 문자열

LEADING : 왼쪽을 잘라낼거면 'LEADING' 입력

TRAILING : 오른쪽을 잘라낼거면 'TRAILING' 입력

BOTH : 양쪽을 잘라낼거면 'BOTH'입력

TRIM_CHARACTER : 잘라낼 문자

EX)

1. 잘라낼 문자를 입력하지 않은 경우

잘라낼 문자를 적용하지 않은 경우는 공백이 제거된다. 

위 예제에서 잘라낼 문자를 넣지 않았으므로, 양끝의 공백이 제거된 것을 볼 수 있다.

2. 잘라낼 문자만 입력한 경우

잘라낼 문자로 '*'를 입력하면 양쪽에 있는 것이 제거됩니다.

3. 앞, 뒷쪽, 양쪽의 지정된 문자를 잘라낼경우

앞 쪽에 있는 문자를 제거할 경우는 'LEADING', 뒷 쪽에 있는 문자를 제거할 경우에는 'TRAILING',

양쪽 문자를 제거할 경우에는 'BOTH' 명령어를 사용합니다.

참고로 잘라낼 문자열을 '*AG'와 같이 문자열로 지정하면 오류가 발생합니다.

TRIM 함수는 LTRIM, RTRIM과 달리 하나의 문자만을 제거할 수 있기 때문입니다.

CONNECT BY LEVEL

http://www.ranoma.com/hard/54260

CONNECT BY LEVEL <=:변수 명령어를 쓰면 1부터 :변수까지 1씩 루트를 돌린다. (<=, <사용 가능함)

SELECT FROM DUAL을 하면 ROW 가 한 건만 나와야 하는데, 9건의 ROW가 나왔다. 한 마디로 ROW를 강제로 늘리는게 가능하다는 것.

LEVEL을 가공하면 시작점과 증가폭을 다양하게 만들 수 있다.

GROUP BY절과 HAVING 절

지금까지 알아 본 집계 함수의 예제는 모두 사원 전체를 기준으로 데이터를 추출했는데, 전체가 아닌 특정 그룹으로 묶어 데이터를 집계할 수도 있다. 이때 사용되는 구문이 바로 GROUP BY절이다. 그룹으로 묶을 컬럼명이나 표현식을 GROUP BY절에 명시해서 사용하며, GROUP BY 구문은 WHERE와 ORDER BY절 사이에 위치한다.

//입력
SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id
ORDER BY department_id;
 
 
//결과 
DEPARTMENT_ID  SUM(SALARY)
    ------------- ------------
              10         4400
              20        19000
              30        24900
              40         6500
              50       156400
              60        28800
              70        10000
              80       304500
              90        58000
             100        51608
             110        20308
                         7000
 
---->12개의 행이 선택됨

사원 테이블에서 각 부서별 급여의 총액을 구했다. 위 결과를 보면 30번 부서에 속한 사원들의 급여를 모두 합하면 24900임을 알 수 있다. 또 다른 쿼리를 수행해 보자.

//입력
SELECT *
FROM kor_loan_status;
 
//결과
    PERIOD   REGION    GUBUN               LOAN_JAN_AMT
    -------- -------- -------------------- --------------------
    201111   서울     주택담보대출          1.3E+14
    201112   서울     주택담보대출          1.3E+14
    201210   인천     주택담보대출          3.0E+13
    201211   인천     주택담보대출          3.0E+13
    201212   인천     주택담보대출          3.0E+13
    201111   광주     주택담보대출          8.7E+12
    201112   광주     주택담보대출          9.0E+12
    201210   광주     주택담보대출          9.5E+12
    ...
 
---->238개의 행이 선택됨

Kor_loan_status 테이블에는 월별, 지역별 가계대출 잔액(단위는 십억)이 들어있고, 대출 유형(GUBUN)은 '주택담보대출'과 '기타대출' 두 종류만 존재한다. 그럼 2013년 지역별 가계 대출 총 잔액을 구해 보자.

//입력
SELECT period, region, SUM(loan_jan_amt) totl_jan
FROM kor_loan_status
WHERE period LIKE '2013%'
GROUP BY period, region
ORDER BY period, region;
 
//결과
 
    PERIOD   REGION     TOTL_JAN
    -------- ---------- -------------
    201310   강원       18190.5
    201310   경기       281475.5
    201310   경남       55814.4
    ....
     
---->34개의 행이 선택됨.

이번엔 2013년 11월 총 잔액만 구해보자.

//입력
SELECT period, region, SUM(loan_jan_amt) totl_jan
FROM kor_loan_status
WHERE period = '201311'
GROUP BY region
ORDER BY region;
 
//결과 
SQL 오류: ORA-00979: GROUP BY 표현식이 아닙니다.

왜 오류가 발생한 것일까? 그룹 쿼리를 사용하면 SELECT 리스트에 있는 컬럼명이나 표현식 중, 집계 함수를 제외하고는 모두 GROUP BY 절에 명시해야 하는데, 앞의 쿼리는 period컬럼을 명시하지 않아 오류가 난 것이다. 2013년 데이터는 2013년 10월과 11월만 존재하며, where절에서 기간을 201311로 주었으므로 굳이 period를 그룹에 포함시킬 필요는 없지만, 구문 문법 상 GROUP BY절에 포함시켜야 한다.

HAVING 절은 GROUP BY절 다음에 위치해 GROUP BY한 결과를 대상으로 다시 필터를 거는 역할을 수행한다. 즉 HAVING 필터 조건 형태로 사용한다. 예를 들어, 위 쿼리 결과에서 대출잔액이 100조 이상인 건만 추출한다면 다음과 같이 쿼리를 작성하면 된다.

//입력
 
SELECT period, region, SUM(loan_jan_amt) totl_jan
FROM kor_loan_status
WHERE period = '201311'
GROUP BY period, region
HAVING SUM(loan_jan_amt) > 100000
ORDER BY region;
 
//결과
 
    PERIOD  REGION     TOTL_JAN
    ------- ---------- -----------
    201311   경기      282816.4
    201311   서울      334062.7

경기도와 서울의 대출잔액이 100조 이상인 것을 보면, 수도권 인구가 타 지역에 비해 많고 집값도 높다는 점을 유추해 볼 수 있다. 주의할 점은 WHERE절은 쿼리 전체에 대한 필터 역할을 하고, HAVING절은 WHERE 조건을 처리한 결과에 대해 GROUP BY를 수행 후 산출된 결과에 대해 다시 조건을 걸어 데이터를 걸러낸다는 점을 잊지 말자.