Built-in function [Ledyx WIKI]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

= Built-in function =

Oracle 기준.

* SELECT, WHERE, ORDER BY 절에 사용 가능.
* Arguments로 함수 사용 가능.

{{tag>Data_Manipulation_Language SQL Database}}


= Single-row function =

== 문자형 함수 ==

=== LOWER, UPPER ===
Case 변환


=== ASCII, CHR ===
아스키코드 변환

* ASCII('문자열')

<sxh sql>
ASCII('A')
-- 65
</sxh>

* CHR(ASCII번호)

<sxh sql>

CHR(65)
-- 'A'
</sxh>


=== CONCAT ===
문자열 붙이기.
'문자열1'||'문자열2' 과 동일.

* CONCAT('문자열1', '문자열2')

<sxh sql>
CONCAT('Hello', ' World')
-- 'Hello World'
</sxh>

=== SUBSTR ===
문자열 추출

* SUBSTR('문자열', INDEX[, 추출할_개수])
[, 추출할_개수]를 생략할 경우 문자열 끝까지 추출

<sxh sql>
SUBSTR('Hello World', 7, 2)
-- 'or' 
</sxh>


=== Length ===
Length('문자열')

=== LTRIM, RTRIM, TRIM ===

* LTRIM('문자열'[, '지정문자'])

<sxh sql>
LTRIM('xxxHello Worldxxx', 'x')
-- ''Hello Worldxxx'
</sxh>


* RTRIM([, '지정문자'])

<sxh sql>
LTRIM('xxxHello Worldxxx', 'x')
-- 'xxxHello World'
</sxh>


* TRIM([leading|trailing|both] '지정문자' FROM '문자열')

Default : both

<sxh sql>
TRIM('x' FROM 'xxxHello Worldxxx')
-- 'Hello World' 
</sxh>


== 숫자형 함수 ==

=== ABS ===
ABS(숫자)

=== SIGN ===
양수/음수/0 구별

<sxh sql>
SIGN(-99)
-- -1

SIGN(0)
-- 0

SIGN(77)
-- 1
</sxh>

=== CEIL, FLOOR ===

* CEIL(숫자)
올림(숫자보다 크거나 같은 최소 정수 반환)

<sxh sql>
CEIL(38.123)
-- 39

CEIL(-38.123)
-- -38
</sxh>


* FLOOR(숫자)
버림(숫자보다 작거나 같은 최소 정수 반환)

<sxh>
FLOOR(38.123)
-- 38

FLOOR(-38.123)
-- -39
</sxh>


=== ROUND, TRUNC ===

* ROUND(숫자[, m])
m자리 올림

<sxh sql>
ROUND(77.77777, 3)
-- 77.778
</sxh>

* TRUNC(숫자[, m]) 
m자리 뒤 버림 (m 생략시 Default : 0) 

<sxh sql>
ROUND(77.77777, 3)
-- 77.777
</sxh>

=== SIN, COS, TAN ===
삼각함수

=== EXP, POWER, SQRT, LOG, LN ===

* EXP : 지수
* POWER : 거듭 제곱
* SQRT : 제곱근
* LOG : 로그
* LN : 자연 로그


== 날짜형 함수 ==

* SYSGATE
  * 현재 날짜와 시각 출력
* EXTRACT(YEAR|MONTH|DAY from d) 
  * 년/월/일 / 시/분/초 출력
* TO_NUMBER(TO_CHAR(d, 'YYYY'|'MM'|'DD')) 
  *  년/월/일 출력

<sxh sql>
SELECT ENAME, SYSDATE+1, HIREDATE, EXTRACT(YEAR from HIREDATE), TO_NUMBER(TO_CHAR(HIREDATE, 'MMDD'))
FROM EMP;
</sxh>

{{:database:dml_built-in_function_datefunction.jpg|}}


== 변환형 함수 ==

* TO_NUMBER('문자열')
* TO_CHAR(숫자|날짜[, FORMAT])
* TO_DATE('문자열'[, FORMAT])

<sxh sql>
SELECT TO_CHAR(855726.872/1101.32159, 'L999,999,999.999') --L은 Local 화폐
FROM DUAL;
-- ￦777.000
</sxh>


== CASE 표현식 ==

<sxh sql>
--※ 모든 반환값의 Type은 일치되어야 한다!
 
--SIMPLE CASE EXPRESSION 조건 : (CASE 뒤 기준 표현이) EQUI조건인 경우
SELECT
    CASE 칼럼|조건식 WHEN 결과값 THEN 반환값(칼럼명|칼럼값)
                    ...
                    [ELSE 반환값(칼럼명|칼럼값)] -- 생략시 NULL
    END[ [AS] 별칭]
FROM 테이블명
 
 
--SEARCHED CASE EXPRESSION 조건 : EQUI조건이 아닌 경우 (좀 더 다양한 조건을 가질 경우)
SELECT
    CASE WHEN 조건식 THEN 반환값(칼럼명|칼럼값)
         ...
         [ELSE 반환값(칼럼명|칼럼값)] -- 생략시 NULL
    END[ [AS] 별칭]
FROM 테이블명
</sxh>


* 예시

<sxh sql>
SELECT ENAME, HIREDATE,
       CASE EXTRACT(YEAR FROM HIREDATE) WHEN 1981 THEN 'Since 1980'
                                        ELSE 'ETC'
       END HIREYEAR,
        
       SAL,
       CASE WHEN SAL>=2000 THEN 1000
            ELSE (CASE WHEN SAL>=1000 THEN 500
                  ELSE 0
                  END)
       END BONUS
FROM EMP;
</sxh>

{{:database:dml-built-in_function_case.jpg|}}


=== DECODE ===

Oracle 전용. CASE 표현이 EQUI 조건일 때, 짧게 표현할 수 있으므로 유용.

<code sql>
DECODE(표현식|칼럼, 기준값1, 반환값1[, 기준값2, 반환값2, ... , Default값])
-- Default값 : CASE표현의 ELSE 뒤 반환값
</code>

* 예시
부서별로 월별 입사자의 평균 급여 조회 

<sxh sql>
SELECT DEPTNO,
  AVG(CASE HIREMONTH WHEN 1 THEN SAL END) M01,
  AVG(CASE HIREMONTH WHEN 2 THEN SAL END) M02,
  AVG(CASE HIREMONTH WHEN 3 THEN SAL END) M03,
  AVG(CASE HIREMONTH WHEN 4 THEN SAL END) M04,
  AVG(CASE HIREMONTH WHEN 5 THEN SAL END) M05,
  AVG(CASE HIREMONTH WHEN 6 THEN SAL END) M06,
  AVG(DECODE(HIREMONTH, 7, SAL)) M07,
  AVG(DECODE(HIREMONTH, 8, SAL)) M08,
  AVG(DECODE(HIREMONTH, 9, SAL)) M09,
  AVG(DECODE(HIREMONTH, 10, SAL)) M10,
  AVG(DECODE(HIREMONTH, 11, SAL)) M11,
  AVG(DECODE(HIREMONTH, 12, SAL)) M12
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) HIREMONTH, SAL FROM EMP)
GROUP BY DEPTNO;
</sxh>

{{:database:dml-built-in_function_decode.jpg|}}



== NULL 관련 함수 ==

=== NVL ===

* 반환값을 NULL이 아닌 값으로 대체할 때 사용.
  * 산술연산에서 데이터값이 NULL일 때 유용!
  * 공집합 대신 다른 값 출력할 때!

<sxh sql>
SELECT
    NVL(NULL 판단 대상, NULL일 때 대체값)
FROM 테이블명;
</sxh>



=== NULLIF ===

* 반환값을 NULL로 대체할 때 사용.
* 두 인자가 같으면 NULL을, 다르면 표현식1 반환

<sxh sql>
SELECT
    NULLIF(EXPR1, EXPR2)
FROM 테이블명;
</sxh>


=== COALESCE ===

* 인자들값 중 NULL이 아니면 그 인자값을, 모두 NULL이면 NULL 반환

<sxh sql>
SELECT
    COALESCE(EXPR1, EXPR2, ...)
FROM 테이블명;
</sxh>


= Multi-row function =

== Aggregate function ==

여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수
* GROUP BY 절은 행들을 소그룹화 한다.
* SELECT, HAVING, ORDER BY절에 사용할 수 있다.


|<100%>|
^  문법  ^  설명  ^
|  <nowiki>COUNT(*|표현식)</nowiki>  |  NULL 제외 계산\\ 문자,날짜 Type에도 사용 가능  |
|  <nowiki>MAX([DISTINCT|ALL] 표현식)</nowiki>\\ <nowiki>MIN([DISTINCT|ALL] 표현식)</nowiki>  |  문자,날짜 Type에도 사용 가능  |
|  <nowiki>SUM([DISTINCT|ALL] 표현식)</nowiki>  |  NULL 제외 계산  |
|  <nowiki>AVG([DISTINCT|ALL] 표현식)</nowiki>  |:::|
|  <nowiki>STDDEV([DISTINCT|ALL] 표현식) </nowiki>  |  표준편차  |
|  <nowiki>VARIAN([DISTINCT|ALL] 표현식) </nowiki>  |  분산  |


* 활용법
  *  "집계함수(CASE()) ~ GROUP BY" (예제 참조)
  * NULL 처리 함수


* 예시

팀별 각 포지션(FW, MF, DF, GK)의 인원수와 팀별 전체 인원수, 평균키 출력 

<sxh sql>
SELECT TEAM_ID,
  NVL(SUM(DECODE(POSITION, 'FW', 1)), 0) FW,
  NVL(SUM(DECODE(POSITION, 'MF', 1)), 0) MF,
  NVL(SUM(DECODE(POSITION, 'DF', 1)), 0) DF,
  NVL(SUM(DECODE(POSITION, 'GK', 1)), 0) GK,
  COUNT(*) SUM,
  ROUND(AVG(HEIGHT), 2) AVG_HEIGHT
FROM PLAYER
GROUP BY TEAM_ID;
</sxh>


== Group function ==
(집계 함수 제외하고,) 소그룹 간의 소계(전체가 아닌 어느 한 부분만을 셈한 합계) 계산

=== ROLLUP ===

* 집계 함수 제외하고, 소그룹 간의 소계(전체가 아닌 어느 한 부분만을 셈한 합계) 계산

<sxh sql>
SELECT DNAME, JOB, COUNT(*) "TOTAL EMP", SUM(SAL) "TOTAL SAL"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB) --DNAME, JOB에 대한 "소"그룹 집"계" 출력
ORDER BY DNAME, JOB;
</sxh>

{{:database:dml-built-in_funciton-rollup.jpg|}}

=== CUBE ===

* GROUP BY 항목들간 다차원적인 소계 계산
* ROLLUP 함수 같은 각 소계 출력 및 전체 총계까지 출력

<sxh sql>
SELECT DNAME, JOB, COUNT(*) "TOTAL EMP", SUM(SAL) "TOTAL SAL"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE(DNAME, JOB)
ORDER BY DNAME, JOB;
</sxh>

{{:database:dml-built-in_funciton-cube.jpg|}}


=== GROUPING ===

* ROLLUP/CUBE 함수와 함께 사용되는 함수로, 어떤 칼럼이 해당 Grouping 작업에 사용되었는 지 아닌 지 구별해주는 역할.
  * 사용되었으면 0, 아니면 1 (= ROLLUP/CUBE 함수에 의해 계산된 결과는 1, 아니면 0)
* **CASE/DECODE** 함수를 이용해, 소계를 나타내는 필드에 사용자 문자열을 정의해 보고서 작성시 유용하게 활용 가능.

<sxh sql>
SELECT DNAME, GROUPING(DNAME),
       JOB, GROUPING(JOB),
       COUNT(*) "TOTAL EMP", SUM(SAL) "TOTAL SAL"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB);
</sxh>

{{:database:dml_built-in_function_grouping.jpg|}}


=== GROUPING SETS ===

특정 항목에 대한 소계 계산

* 일반 그룹함수 이용

<sxh sql>
SELECT 'ALL DEPTS' DNAME, JOB, COUNT(*) "TOTAL EMP", SUM(SAL) "TOTAL SAL"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY JOB
UNION ALL
SELECT DNAME, 'ALL JOBS' JOB, COUNT(*) "TOTAL EMP", SUM(SAL) "TOTAL SAL"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME;
</sxh>


* GROUPING SETS 함수 이용

<sxh sql>
SELECT DECODE(GROUPING(DNAME), 1, 'ALL DEPTS', DNAME) DNAME,
       DECODE(GROUPING(JOB), 1, 'ALL JOBS', JOB) JOB,
       COUNT(*) "TOTAL EMP", SUM(SAL) "TOTAL SAL"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS(JOB, DNAME); --칼럼간의 평등관계로 순서가 바뀌어도 결과는 같다.
</sxh>


{{:database:dml_built-in_function_groupingsets.jpg|}}



== Window function ==

* 부분적으로 행과 행간의 관계를 쉽게 정의하기 위한 함수 
  * PARTITION BY : 전체 집합을 기준에 의해 소그룹으로 나눔.
  * ORDER BY : 어떤 항목에 대해 **순위**를 지정할 지 기술.
  * WINDOWING : 함수의 대상이 되는 **행** 기준의 **범위**를 강력하게 지정. (SQL Server 미지원)
    * ROWS : 물리적인 결과 행의 수
    * RANGE : 논리적인 값에 의한 범위

<sxh sql>
SELECT WINDOW_FUNCTION (ARGUMENTS) OVER
       ([PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절])
FROM 테이블명;
</sxh>


=== 그룹 내 순위(Rank) 관련 함수 ===

==== RANK ====
특정 칼럼에 대한 순위를 구하는 함수 (단, 동일 순위가 있으면 그 수만큼 순위가 넘어가버림) 

<sxh sql>
SELECT JOB, ENAME, SAL,
       RANK() OVER (ORDER BY SAL DESC) ALL_RANK, --전체 급여 순위
       RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK --직업별 급여 순위
FROM EMP;
</sxh>

==== DENSE_RANK ====
RANK 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급.

<sxh sql>
SELECT JOB, ENAME, SAL,
       RANK()       OVER (ORDER BY SAL DESC) RANK,
       DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK
FROM EMP;
</sxh>

* 4, 12행 주목!

{{:database:dml_built-in_function_dense_rank.jpg|}}


==== ROW_NUMBER ====
동일값이라도 고유한 순위 부여. 동일 등수를 인정하지 않음.

<sxh sql>
SELECT JOB, ENAME, SAL,
       RANK()       OVER (ORDER BY SAL DESC) RANK,
       ROW_NUMBER() OVER (ORDER BY SAL DESC) DENSE_RANK --SAL 기준에 따른 줄번호순
FROM EMP;
</sxh>


=== 그룹 내 집계(Aggregate) 관련 함수 ===

==== SUM ====

<sxh sql>
SELECT MGR, ENAME, SAL,
       SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM --MGR별 급여 합계
FROM EMP;
</sxh>


==== MAX ====

<sxh sql>
SELECT MGR, ENAME, SAL,
       MAX(SAL) OVER (PARTITION BY MGR) MGR_MAX --MGR별 급여 최대값
FROM EMP;
</sxh>


==== MIN ====

<sxh sql>
SELECT MGR, ENAME, SAL,
       MIN(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE) MGR_MIN --MGR별 급여 최소값, 입사일 기준 정렬
FROM EMP;
</sxh>


==== AVG ====

<sxh sql>
SELECT MGR, ENAME, SAL,
       ROUND(AVG(SAL) OVER (PARTITION BY MGR), 2) MGR_AVG --MGR별 급여 평균
FROM EMP;
</sxh>


==== COUNT ====

<sxh sql>
SELECT ENAME, SAL,
       COUNT(*) OVER (ORDER BY SAL  RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) SIM_CNT
FROM EMP;
 
--RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING : 현재 행의 급여값을 기준으로 급여가 -50 ~ +150 범위 내에 포함된 모든 행이 대상
</sxh>


=== 그룹 내 행 순서 관련 함수 ===
모두 SQL Server에서 미지원.

==== FIRST_VALUE, LAST_VALUE ====
* FIRST_VALUE : 파티션별 윈도우에서 가장 먼저 나온 값 반환.
* LAST_VALUE : 파티션별 윈도우에서 가장 나중에 나온 값 반환.

<sxh sql>
SELECT DEPTNO, ENAME, SAL,
       FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC
                                ROWS UNBOUNDED PRECEDING) AS DEPT_RICH
FROM EMP;
 
--ROWS UNBOUNDED PRECEDING : 현재 행 지준으로 "파티션 내 첫 번째 행까지" 범위 지정
</sxh>

{{:database:dml_built-in_function_first_value.jpg|}}

==== LAG, LEAD ====
* LAG : 파티션별 윈도우에서 이전 몇 번째 행의 값 반환. (지연, 미루기)
* LEAD : 파티션별 윈도우에서 이후 몇 번째 행의 값 반환. (당기기, 이끌기)


{{:database:dml_built-in_function_lag.jpg|}}


=== 그룹 내 비율 관련 함수 ===
**NTILE** 이외 함수들은 SQL Server에서 미지원. 

==== RATIO_TO_REPORT ====
파티션 내 전체 칼럼 합계값에 대한 백분율 반환.

==== PERCENT_RANK ====
파티션별 윈도우에서 제일 먼저 나오는 행값을 0, 가장 늦게 나오는 행값을 1로 하여 행 순서별 백분율 반환. (**상위 몇퍼센트에 있는가?**)

* 예시
같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치쯤에 있는 지 0과 1사이의 값으로 조회. 

<sxh sql>
SELECT DEPTNO, ENAME, SAL,
       PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) PER_RANK
FROM EMP;
</sxh>

{{:database:dml_built-in_function_percent_rank.jpg|}}


==== CUME_DIST ====
파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율 반환. 


==== NTILE(n) ====
파티션별 전체 건수를 n등분한 Argument값 반환 (조 짜기)


== ROWNUM ==
테이블이나 집합에서 **원하는 __만큼__의 행만 가져오고 싶을 때** 사용하는 가상 칼럼(Pseudo Column)

SQL Server에서는
<code sql>
-- (SELECT 절에서)
TOP(n) WITH TIES
-- WITH TIES : 동일 수치 데이터 추가
</code>

<sxh sql>
SELECT문
WHERE ROWNUM 비교연산자 수치
 
-- 예시 : 3행까지 가져올 때
SELECT *
FROM MYTABLE
WHERE ROWNUM <= 3;
</sxh>

* 테이블 내의 고유한 키나 인덱스 값 생성. (☞ 오름차순으로 번호가 가상 생성되는 특성 이용!)

<sxh sql>
UPDATE 테이블명
SET 칼럼명 = ROWNUM;
</sxh>

relational_database/built-in_function.txt · Last modified: 2021/02/07 04:15 by ledyx