4-1) BFILENAME 함수
4-2) COALESCE 함수
4-3) DECODE 함수
4-4) DEPTH 함수
4-5) DUMP 함수
4-6) EMPTY_BLOB 함수
4-7) EMPTY_CLOB 함수
4-8) EXISTSNODE 함수
4-9) EXTRACT(XML) 함수
4-10) EXTRACTVALUE 함수
4-11) GREATEST 함수
4-12) LEAST 함수
4-13) NLS_CHARSET_DECL_LEN 함수
4-14) NLS_CHARSET_ID 함수
4-15) NLS_CHARSET_NAME 함수
4-16) NULLIF 함수
4-17) NVL2 함수
4-18) PATH 함수
4-19) SYS_CONNECT_BY_PATH 함수
4-20) SYS_CONTEXT 함수
4-21) SYS_DBURIGEN 함수
4-22) SYS_EXTRACT_UTC 함수
4-23) SYS_GUID 함수
4-24) SYS_XMLAGG 함수
4-25) SYS_XMLGEN 함수
4-26) UID 함수
4-27) USER 함수
4-28) USERENV 함수
4-29) VSIZE 함수
4-30) XMLAGG 함수
4-31) XMLCOLATTVAL 함수
4-32) XMLCONCAT 함수
4-33) XMLFOREST 함수
4-34) XMLELEMENT 함수
5.그룹함수 Aggregate 함수
5-1) AVG* 함수
5-2) CORR* CORR* 함수
5-3) COUNT* 함수
5-4) COVAR_POP 함수
5-5) COVAR_SAMP 함수
5-6) CUME_DIST 함수
5-7) DENSE_RANK 함수
5-8) FIRST 함수
5-9) GROUP_ID 함수
5-10) Grouping 함수
5-11) GROUPING_ID 함수
5-12) LAST 함수
5-13) MAX 함수
5-14) MIN 함수
5-15) PERCENTILE_CONT 함수
5-16) PERCENTILE_DISC 함수
5-17) PERCENT_RANK 함수
5-18) RANK 함수
5-19) REGR_(linear regression) function* 함수
5-20) STDDEV 함수
5-21) STDDEV_POP 함수
5-22) STDDEV_SAMP 함수
5-23) SUM 함수
5-24) VAR_POP 함수
5-25) VAR_SAMP 함수
5-26) VARIANCE 함수
5-27) Grouping sets 함수
6. Analytic 함수
6-1) AVG* 함수
6-2) CORR* CORR* 함수
6-3) COUNT* 함수
6-4) COVAR_SAMP 함수
6-5) CUME_DIST 함수
6-6) DENSE_RANK 함수
6-7) FIRST 함수
6-8) FIRST_VALUE 함수
6-9) LAG 함수
6-10) LAST_VALUE 함수
6-11) LEAD 함수
6-12) NTILE 함수
6-13) RATIO_TO_REPORT 함수
6-14) ROW_NUMBER 함수
7. 객체 참조 함수
7-1) REF 타입
8. PseudoColumn을 의미하는 것
8-1) ROWID 컬럼
8-2) ROWNUM 컬럼
4-1) BFILENAME 함수
--------------------------------------------------------------------------------
서버 파일 시스템 상에 실제로 위치한 LOB 바이너리 파일의 위치한 BFILE locator를 반환한다.
【형식】
bfilename ('디렉토리‘,’파일이름‘)
【예제】BFILE을 insert하는 예
SQL> connect system/manager
SQL> host mkdir /export/home/oracle/bfile
SQL> create directory bfile_dir as '/export/home/oracle/bfile';
Directory created.
SQL> grant read on directory bfile_dir to jijoe;
Grant succeeded.
SQL> connect jijoe/joe_password
SQL> create table bfile_doc (id number, doc bfile);
SQL> insert into bfile_doc
1 values(1111,bfilename('bfile_dir','unix.hwp'));
1 row created.
SQL>
4-2) COALESCE 함수
--------------------------------------------------------------------------------
이 함수는 나열된 값 중에서 NULL이 아닌 첫 번째 값을 반환한다.
【예제】
SQL> select coalesce('','','arirang','kunsan') from dual;
COALESC
-------
arirang
SQL>
4-3) DECODE 함수
--------------------------------------------------------------------------------
DECODE는 일반적인 프로그래밍 언어의 IF문을 SQL 문자 또는 PL/SQL 안으로 끌어들여 사용하기 위하여 만들어진 오라클 함수이다.
따라서 일반 프로그래밍 언어의 IF문이 수행 할 수 있는 기능을 포함하고 있다.
select시의 decode 사용은 from 절만 빼고 어디에서나 사용할 수 있다.
일반 프로그래밍과 decode 함수를 서로 비교하여 보자.
IF문 Decode 함수 IF A=B THEN RETURN 'T';END IF; DECODE(A,B,'T') IF A=B THENRETURN 'T';ELSIF A=C THENRETURN 'F';ELSERETURN 'X';END IF; DECODE(A,B,'T',C,'F','X')
【형식】
DECODE(검색컬럼,조건1,결과값1,
조건2,결과값2,...,기본값);
【예제】
SQL> connect jijoe/jijoe_password
SQL> create table aa(
2 pid number(12) primary key,
3 addr varchar2(20),
4 name varchar2(10));
SQL> insert into aa values(1234,'kunsan','jijoe')
SQL> insert into aa values(3456,'seoul','sunny')
SQL> select * from aa;
PID ADDR NAME
---------- -------------------- ----------
1234 kunsan jijoe
3456 seoul sunny
SQL> select decode(pid,1234,name) name from aa;
NAME
----------
jijoe
SQL>
【예제】
SQL> desc ddd
Name Null? Type
----------------------------------------- -------- ----------------------------
NO NUMBER(4)
NAME VARCHAR2(10)
HIRDATE DATE
DEPTNO NUMBER(5)
SQL> select * from ddd;
NO NAME HIRDATE DEPTNO
---------- ---------- --------- ----------
1 student1 01-JAN-04 10
2 student2 01-FEB-04 10
3 student3 01-MAR-04 20
4 student4 01-MAY-04 30
SQL> select count(decode(to_char(hirdate,'MM'),'01',1)) "JAN",
2 count(decode(to_char(hirdate,'MM'),'02',1)) "FEB",
3 count(decode(to_char(hirdate,'MM'),'03',1)) "MAR",
4 count(*) "Total"
5 from ddd
6 where to_char(hirdate,'MM') >= '01' AND
7 to_char(hirdate,'MM') <= '06';
JAN FEB MAR Total
---------- ---------- ---------- ----------
1 1 1 4
SQL>
4-4) DEPTH 함수
--------------------------------------------------------------------------------
DEPTH( correlation_integer) 함수는 UNDER_PATH나 EQUALS_PATH 조건과 함께 사용되는 보조함수이다.
이 함수는 UNDER_PATH 조건에 상관관계의 수치를 반환한다.
【예제】
SQL> select * from resource_view;
SQL> select path(1), depth(2)
2 from resource_view
3 where under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 1)=1
4 and under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 2)=1;
PATH(1) DEPTH(2)
------------------------------------------ ----------
/xml.xsd 1
SQL>
4-5) DUMP 함수
--------------------------------------------------------------------------------
지정한 데이터의 위치와 길이 따위를 지정한 형식으로 반환한다.
【형식】
DUMP(expr [,반환형식[,시작위치[,길이]]] )
【예제】
SQL> select dump('Corea', 1016) from dual;
DUMP('COREA',1016)
-----------------------------------------------------
Typ=96 Len=5 CharacterSet=KO16KSC5601: 43,6f,72,65,61
SQL> select dump('Corea', 8,3,2) "Octal" from dual;
Octal
---------------------
Typ=96 Len=5: 162,145
SQL> select dump('Corea',16,3,2) "ASCII" from dual;
ASCII
-------------------
Typ=96 Len=5: 72,65
SQL>
4-6) EMPTY_BLOB 함수
--------------------------------------------------------------------------------
EMPTY_BLOB () 함수는 LOB 변수를 초기화하기 위하여 쓰이거나,
또는 INSERT 문이나 UPDATE 문에서 empty LOB 위치를 반환한다.
【예시】
UPDATE print_media SET ad_photo = EMPTY_BLOB();
4-7) EMPTY_CLOB 함수
--------------------------------------------------------------------------------
EMPTY_CLOB () 함수는 LOB 변수를 초기화하기 위하여 쓰이거나,
또는 INSERT 문이나 UPDATE 문에서 empty LOB 위치를 반환한다.
【예시】
UPDATE print_media SET ad_photo = EMPTY_CLOB();
4-8) EXISTSNODE 함수
--------------------------------------------------------------------------------
이 함수는 node의 존재여부를 확인하여 그 결과를 반환한다.
0은 노드가 남아 있지 않은 경우이고,
1은 아직 노드가 존재하는 경우이다.
【형식】
EXISTSNODE(XMLType_instance, XPath_string [,namespace_string] )
【예제】
SQL> select * from resource_view;
SQL> select res,any_path
2 from resource_view
3 where existsnode(res, 'xdbconfig.xml') =0;
26 rows selected.
SQL>
4-9) EXTRACT(XML) 함수
--------------------------------------------------------------------------------
이 함수는 existsnode와 유사한 함수이다.
【형식】
EXTRACT(XMLType_instance, XPath_string [,namespace_string] )
【예제】
SQL> select * from resource_view;
SQL> select extract(res,'xdbconfig.xml')
2 from resource_view;
26 rows selected.
SQL>
4-10) EXTRACTVALUE 함수
--------------------------------------------------------------------------------
이 함수는 existsnode와 유사한 함수로써 node의 스칼라 값을 반환한다.
【형식】
EXTRACTVALUE(XMLType_instance, XPath_string [,namespace_string] )
【예제】
SQL> select * from resource_view;
SQL> select extractvalue(res,'xdbconfig.xml')
2 from resource_view;
26 rows selected.
SQL>
4-11) GREATEST 함수
--------------------------------------------------------------------------------
GREATEST (expr,...) 함수는 expr 중에서 가장 큰 값을 반환한다.
【예제】
SQL> select greatest(20,10,30) from dual;
GREATEST(20,10,30)
------------------
30
SQL>
4-12) LEAST 함수
--------------------------------------------------------------------------------
LEAST (expr,...) 함수는 expr 중에서 가장 작은 값을 반환한다.
【예제】
SQL> select least(20,10,30) from dual;
GREATEST(20,10,30)
------------------
10
SQL> select least('bb','aa','cc') from dual;
GR
--
aa
SQL>
4-13) NLS_CHARSET_DECL_LEN 함수
--------------------------------------------------------------------------------
NLS_CHARSET_DECL_LEN (byte_count , char_set_id) 함수는 nchar로 선언된 폭을 반환한다.
【예제】
SQL> select nls_charset_decl_len
2 (200, nls_charset_id('ja16eucfixed')) from dual
NLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('JA16EUCFIXED'))
--------------------------------------------------------
100
SQL>
4-14) NLS_CHARSET_ID 함수
--------------------------------------------------------------------------------
nls_charset_id('text') 함수는 문자셋 이름에 대응하는 ID 번호를 반환한다.
여기서 text는 서버에서 지원되는 CHAR_CS나 NCHAR_CS이다.
【예제】
SQL> select nls_charset_id('ja16euc') from dual;
NLS_CHARSET_ID('JA16EUC')
-------------------------
830
SQL>
4-15) NLS_CHARSET_NAME 함수
--------------------------------------------------------------------------------
nls_charset_name('number') 함수는 문자섹 ID 번호에 대응하는 문자의 이름을 반환한다.
【예제】
SQL> select nls_charset_name(830) from dual;
NLS_CHA
-------
JA16EUC
SQL> select nls_charset_name(1) from dual;
NLS_CHAR
--------
US7ASCII
SQL>
4-16) NULLIF 함수
--------------------------------------------------------------------------------
NULLIF(expr1, expr2) 함수는
expr1과 expr2를 비교하여
같으면 null을 반환하고,
같지 않으면 expr1을 반환한다.
이는 CASE 문으로 쓰면 다음과 같다.
CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
【예제】
SQL> select nullif('aa','AA') from dual;
NU
--
aa
SQL> select nullif('aa','aa') from dual;
NU
--
SQL>
4-17) NVL2 함수
--------------------------------------------------------------------------------
NVL2(expr1, expr2, expr3) 함수는
expr1이 null이 아니면 expr2를 반환하고,
expr1이 null이면 expr3을 반환한다.
【예제】
SQL> select nvl2('','Corea','Korea') from dual;
NVL2(
-----
Korea
SQL> select nvl2('aa','Corea','Korea') from dual;
NVL2(
-----
Corea
SQL>
4-18) PATH 함수
--------------------------------------------------------------------------------
PATH(correlation_path) 함수는 under_path나 equals_path의 보조함수로서,
자원의 관계경로를 반환한다.
【예제】
SQL> select * from resource_view;
SQL> select path(1), depth(2)
2 from resource_view
3 where under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 1)=1?
4 and under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 2)=1;
PATH(1) DEPTH(2)
------------------------------------------ ----------
/xml.xsd 1
SQL>
4-19) SYS_CONNECT_BY_PATH 함수
--------------------------------------------------------------------------------
SYS_CONNECT_BY_PATH(column, char) 함수는 계층적 쿼리에서만 유효하며,
column의 절대 경로를 char로 지정한 문자로 분리하여 반환한다.
【예제】
SQL> select sys_connect_by_path(name, '/') from emp
2 start with name='jijoe'
3 connect by prior id=1101;
SYS_CONNECT_BY_PATH(NAME,'/')
-----------------------------
/jijoe
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
4-20) SYS_CONTEXT 함수
--------------------------------------------------------------------------------
이 함수는 namespace와 관계되는 parameter의 값을 반환한다.
【형식】
SYS_CONTEXT('namespace','parameter' [,length])
【예제】
SQL> select sys_context('userenv','session_user') from dual;
SYS_CONTEXT('USERENV','SESSION_USER')
-------------------------------------
JIJOE
SQL> select sys_context('userenv','lang') from dual;
SYS_CONTEXT('USERENV','LANG')
-----------------------------
US
SQL>
userenv에서 사용될 parameter는 다음과 같다.
AUDITED_CURSORID AUTHENTICATION_DATA BG_JOB_ID
CLIENT_IDENTIFIER CLIENT_INFO CURRENT_SCHEMA
CURRENT_SCHEMAID CURRENT_SQL CURRENT_USER
CURRENT_USERID DB_DOMAIN DB_NAME
ENTRY_ID EXTERNAL_NAME FG_JOB_ID
GLOBAL_CONTEXT_MEMORY HOST INSTANCE
IP_ADDRESS ISDBA LANG
LANGUAGE NETWORK_PROTOCOL NLS_CALENDAR
NLS_CURRENCY NLS_DATE_FORMAT NLS_DATE_LANGUAGE
NLS_SORT NLS_TERRITORY OS_USER
PROXY_USER PROXY_USERID SESSION_USER
SESSION_USERID SESSIONID TERMINAL
4-21) SYS_DBURIGEN 함수
--------------------------------------------------------------------------------
이 함수는 입력된 argument에 대한 DBURIType의 URL을 반환한다.
【형식】
SYS_DBURIGEN({column|attribute} [rowid],... [,'text()'])
【예제】
SQL> select sys_dburigen(id,name) from emp
2 where name='jijoe';
SYS_DBURIGEN(ID,NAME)(URL, SPARE)
------------------------------------------------------------------------
DBURITYPE('/PUBLIC/EMP/ROW[ID=''1104'']/NAME', NULL)
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
4-22) SYS_EXTRACT_UTC 함수
--------------------------------------------------------------------------------
sys_extract_utc(datetime_with_timezone) 함수는
UTC(coordinated universal time: Greenwich mean time) 시각을 반환한다.
【예제】
SQL> select systimestamp, sys_extract_utc(systimestamp) from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------
SYS_EXTRACT_UTC(SYSTIMESTAMP)
--------------------------------------------------------------------------
06-AUG-04 02.41.39.258976 PM +09:00
06-AUG-04 05.41.39.258976 AM
SQL>
4-23) SYS_GUID 함수
--------------------------------------------------------------------------------
sys_guid() 함수는 globally unique identifier를 반환한다.
【예제】
SQL> select sys_guid() from dual;
SYS_GUID()
--------------------------------
E0F6C6D5767C01ADE034080020B588F4
SQL>
4-24) SYS_XMLAGG 함수
--------------------------------------------------------------------------------
이 함수는 sys_xmlgen 문에서 만든 XML 문을 기본적으로 ROWSET 태그를 새로 추가 시킨다.
【형식】
SYS_XMLAGG( expr [fmt] )
【예제】
SQL> select sys_xmlagg(sys_xmlgen(name)) from emp
2 where name like 'j%';
SYS_XMLAGG(SYS_XMLGEN(NAME))
--------------------------------------------------------------------------
SQL>
4-25) SYS_XMLGEN 함수
--------------------------------------------------------------------------------
이 함수는 지정한 행이나 열을 XML 문으로 만들어 반환한다.
【형식】
SYS_XMLGEN( expr [fmt] )
【예제】
SQL> select sys_xmlgen(name) from emp
2 where name like 'j%';
SYS_XMLGEN(NAME)
--------------------------------------------------------------------------
SQL>
4-26) UID 함수
--------------------------------------------------------------------------------
UID 함수는 사용자의 유일한 ID를 정수로 반환한다.
【예제】
SQL> select uid from dual;
UID
----------
93
SQL>
4-27) USER 함수
--------------------------------------------------------------------------------
이 함수는 사용자의 이름을 반환한다.
【예제】
SQL> select user,uid from dual;
USER UID
------------------------------ ----------
JIJOE 93
SQL>
4-28) USERENV 함수
--------------------------------------------------------------------------------
USERENV('parameter') 함수는 사용자의 환경에 관한 정보를 반환한다.
parameter는 다음과 같은 것이 있다.
CLIENT_INFO ENTRYID ISDBA LANG
LANGUAGE SESSIONID TERMINAL
【예제】
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.KO16KSC5601
SQL>
4-29) VSIZE 함수
--------------------------------------------------------------------------------
VSIZE('expr') 함수는 expr이 표시되는 바이트 수를 반환한다.
【예제】
SQL> select name, vsize(name) from emp
2 where name like 'jijoe';
NAME VSIZE(NAME)
---------- -----------
jijoe 5
SQL>
$ cat .profile
..........
NLS_LANG=AMERICAN_AMERICA.KO16KSC5601 ☜ 한글 문자셋으로 설정
export NLS_LANG
NLS_LANG=AMERICAN_AMERICA.UTF8 ☜ UNICODE로 설정
export NLS_LANG
$
테이블에서 저장된 데이터가 한글 문자셋인지 유니코드인지 식별하려면 다음과 같이 확인해 볼 수 있다.
select 한글컬럼명, vsize(한글컬럼명) from 테이블명;
여기서 한글 컬럼에 한글이 3글자라면, vsize 결과가
9이면 unicode이고,
6이면 한글 문자셋으로 저장된 것임을 알 수 있다.
【예제】
SQL> select * from test;
ID NAME
---------- ----------------------------------------
1113 아리랑
1112 쓰리랑
SQL> select name, vsize(name) from test;
NAME VSIZE(NAME)
---------------------------------------- -----------
아리랑 6
쓰리랑 6
SQL>
4-30) XMLAGG 함수
--------------------------------------------------------------------------------
이 함수는 xmlelement에 의해서 XML 태그를 만든 문장을 모으는 기능이다.
【형식】
XMLAGG( XMLType_instance [order_by_clause])
【예제】
SQL> select xmlagg(xmlelement("name",e.name)) from emp e;
XMLAGG(XMLELEMENT("NAME",E.NAME))
--------------------------------------------------------------------------
SQL>
4-31) XMLCOLATTVAL 함수
--------------------------------------------------------------------------------
이 함수는 XML fragment를 만드는 기능이다
【형식】
XMLCOLATTVAL( value_expr [AS c_alias],...)
【예제】
SQL> select xmlcolattval(e.name,e.id,e.salary) from emp e;
XMLCOLATTVAL(E.NAME,E.ID,E.SALARY)
--------------------------------------------------------------------------
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
4-32) XMLCONCAT 함수
--------------------------------------------------------------------------------
XMLCONCAT( XMLType_instance,...) 함수는 XMLType instance를 series로 넣어 만드는 기능이다.
【예제】
SQL> select xmlconcat(
2 xmlelement("name",e.name),xmlelement("bonus",e.bonus))
3 from emp e;
XMLCONCAT(XMLELEMENT("NAME",E.NAME),XMLELEMENT("BONUS",E.BONUS))
--------------------------------------------------------------------------
SQL>
4-33) XMLFOREST 함수
--------------------------------------------------------------------------------
이 함수는 각각의 argument parameter를 XML로 변환한다.
【형식】
XMLFOREST( value_expr [AS c_alias],...)
【예제】
SQL> select xmlelement("emp",
2 xmlforest(e.id, e.name, e.bonus)) from emp e;
XMLELEMENT("EMP",XMLFOREST(E.ID,E.NAME,E.BONUS))
--------------------------------------------------------------------------
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
4-34) XMLELEMENT 함수
--------------------------------------------------------------------------------
이 함수는 XML 태그를 붙이는 기능이다
【예제】
SQL> select xmlelement("name",e.name) from emp e
2 where name like 'j%';
XMLELEMENT("NAME",E.NAME)
--------------------------------------------------------------------------
SQL>
5-1) AVG* 함수
--------------------------------------------------------------------------------
조건을 만족하는 행(row)의 평균을 값을 반환하며,
aggregate 함수나
analytic 함수로 사용된다.
【형식】
AVG( [DISTINCT | ALL] 컬럼명)
[ [OVER] (analytic 절)]
【예제】aggregate 예
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL> select avg(salary) from emp;
AVG(SALARY)
-----------
240
SQL>
【예제】analytic 예
SQL> select avg(distinct salary) over(partition by bonus)
2 from emp;
AVG(DISTINCTSALARY)OVER(PARTITIONBYBONUS)
-----------------------------------------
236.666667
236.666667
236.666667
250
SQL> select avg(salary) over(partition by bonus order by id
2 rows between 1 preceding and 1 following) as avg from emp;
AVG
----------
245
236.666667
235
250
SQL>
5-2) CORR* CORR* 함수
--------------------------------------------------------------------------------
집합 쌍의 상관관계 계수를 반환한다.
【형식】
CORR( expr1, expr2 ) [ [OVER] (analytic 절)]
【예제】
SQL> select corr(avg(bonus),max(bonus))
2 from employees
3 group by dept_no;
CORR(AVG(BONUS),MAX(BONUS))
---------------------------
1
SQL>
5-3) COUNT* 함수
--------------------------------------------------------------------------------
쿼리한 행의 수를 반환한다.
【형식】
COUNT([*|DISTINCT|ALL] 컬럼명) [ [OVER] (analytic 절)]
【예제】
SQL> select count(*) from emp;
COUNT(*)
----------
4
SQL> select count (distinct dept_no) from employees;
COUNT(DISTINCTDEPT_NO)
----------------------
2
SQL> select count (all dept_no) from employees;
COUNT(ALLDEPT_NO)
-----------------
4
SQL> select salary,count(*)
2 over (order by salary)
3 from emp;
SALARY COUNT(*)OVER(ORDERBYSALARY)
---------- ---------------------------
220 1
240 2
250 4
250 4
SQL>
5-4) COVAR_POP 함수
--------------------------------------------------------------------------------
이 함수는 number 쌍의 집합에 대한 population covariance를 반환한다.
【형식】
COVAR_POP(expr1, expr2 [ OVER (analytic 절)] )
【예제】
SQL> select covar_pop(bonus,salary) from emp;
COVAR_POP(BONUS,SALARY)
-----------------------
62.5
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
5-5) COVAR_SAMP 함수
--------------------------------------------------------------------------------
이 함수는 number 쌍의 집합에 대한 sample covariance를 반환한다.
【형식】
COVAR_SAMP(expr1, expr2 [ OVER (analytic 절)] )
【예제】
SQL> select covar_samp(bonus,salary) from emp;
COVAR_SAMP(BONUS,SALARY)
------------------------
83.3333333
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
5-6) CUME_DIST 함수
--------------------------------------------------------------------------------
이 함수는 그룹 값 내에서 어떤 값의 cumulative distribution(누적분포)을 계산한다.
【형식】
CUME_DIST(expr,... WITHIN GROUP (ORDER BY
expr [DESC | ASC] [NULLS {FIRST|LAST}],...)
또는
CUME_DIST() over ([query_partition_clause] order_by_clause)
【예제】
SQL> select cume_dist(230) within group
2 (order by salary ) from emp;
CUME_DIST(230)WITHINGROUP(ORDERBYSALARY)
----------------------------------------
.4
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
5-7) DENSE_RANK 함수
--------------------------------------------------------------------------------
그룹 내에서 순위를 반환한다.
【예제】
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL> select dense_rank(230, .05) within group
2 (order by salary, bonus) "Dense Rank"
3 from employees;
Dense Rank
----------
2
SQL>
5-8) FIRST 함수
--------------------------------------------------------------------------------
first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.
【형식】
집합함수 KEEP (
DENSE_RANK FIRST ORDER BY
expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
2 min(salary) keep (dense_rank first order by salary) "Worst",
3 max(salary) keep (dense_rank last order by salary) "Best"
4 from employees
5 order by id;
Worst Best
---------- ----------
220 250
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
5-9) GROUP_ID 함수
--------------------------------------------------------------------------------
GROUP() 함수는 group by로 분리되어 복제된 번호로 복제 횟수를 구분하도록 출력한다.
번호가 0부터 시작되므로 n번 복제되었으면 n-1의 번호가 출력된다.
【예제】
SQL> select dept_no, group_id() from employees
2 group by dept_no;
DEPT_NO GROUP_ID()
---------- ----------
10 0
20 0
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
5-10) Grouping 함수
--------------------------------------------------------------------------------
Grouping 함수는 Rollup이나 cube 연산자랑 함께 사용하여
grouping 함수에서 기술된 컬럼이 그룹핑시 사용되었는지 보여주는 함수이다.
특별히 연산의 기능은 없으며,
rollup이나 cube 연산 후 출력된 결과에 대한 사용자의 이해를 높이기 위해 사용된다.
즉, grouping 함수를 이용할 경우 출력되는 결과값 중 null 값이 있다면,
이 null 값이 rollup이나 cube 연산의 결과로 생성된 값인지,
원래 테이블상에 null 값으로 저장된 것인지 확인할 수 있다.
. grouping 함수는 인수로 하나의 값만을 가진다.
. grouping 함수에 사용된 인수는 group by 절에 기술된 값 중에 하나와 반드시 일치해야 한다.
. grouping 함수의 결과값으로 0 또는 1을 반환한다.
0은 해당인수로 쓰인 값이 rollup이나 cube 연산에 사용되었음을 나타나고,
1은 사용되지 않았음을 의미한다.
【형식】
SELECT 컬럼명,그룹함수(컬럼명), GROUPING(컬럼명)
FROM 테이블명
WHERE 조건
GROUP BY [ROLLUP | CUBE] 그룹핑하고자하는 컬럼명,...
HAVING 그룹조건
ORDER BY 컬럼명 또는 위치번호
【예제】
SQL> select grade,deptno,sum(salary),GROUPING(deptno)
2 from aaa
3 group by rollup(grade,deptno);
GRADE DEPTNO SUM(SALARY) GROUPING(DEPTNO)
---------- ---------- ----------- ----------------
1 10 100 0
1 20 500 0
1 30 300 0
1 900 1
2 10 400 0
2 20 200 0
2 30 600 0
2 1200 1
2100 1
9 rows selected.
SQL>
5-11) GROUPING_ID 함수
--------------------------------------------------------------------------------
GROUPING_ID(expr,...) 함수는 행과 관련되는 GROUPING 비트 벡터에 대응되는 수치를 반환한다.
【예제】
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL> select sum(salary), grouping_id(dept_no)
2 from employees
3 group by dept_no;
SUM(SALARY) GROUPING_ID(DEPT_NO)
----------- --------------------
500 0
460 0
SQL>
5-12) LAST 함수
--------------------------------------------------------------------------------
first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.
【형식】
집합함수 KEEP (
DENSE_RANK LAST ORDER BY
expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
2 min(salary) keep (dense_rank first order by salary) "Worst",
3 max(salary) keep (dense_rank last order by salary) "Best"
4 from employees
5 order by id;
Worst Best
---------- ----------
220 250
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
5-13) MAX 함수
--------------------------------------------------------------------------------
이 함수는 최대 값을 반환한다.
【형식】
MAX ([{DISTINCT|ALL}] expr) [OVER(analytic_clause)]
【예제】
SQL> select max(salary) over (partition by dept_no)
2 from employees;
MAX(SALARY)OVER(PARTITIONBYDEPT_NO)
-----------------------------------
250
250
240
240
SQL> select max(salary) from employees;
MAX(SALARY)
-----------
250
SQL>
5-14) MIN 함수
--------------------------------------------------------------------------------
이 함수는 최소 값을 반환한다.
【형식】
MIN ([{DISTINCT|ALL}] expr) [OVER(analytic_clause)]
【예제】
SQL> select min(salary) over (partition by dept_no)
2 from employees;
MIN(SALARY)OVER(PARTITIONBYDEPT_NO)
-----------------------------------
250
250
220
220
SQL> select min(salary) from employees;
MIN(SALARY)
-----------
220
SQL>
5-15) PERCENTILE_CONT 함수
--------------------------------------------------------------------------------
이 함수는 연속 모델에 대한 inverse distribution function이다.
【형식】
PERCENTILE_CONT(expr) WITHIN GROUP (ORDER BY expr [{DESC|ASC}])
[OVER (query_partition_cluause)]
【예제】
SQL> select dept_no,percentile_cont(0.5) within group
2 (order by salary DESC)
3 from employees GROUP BY dept_no;
DEPT_NO PERCENTILE_CONT(0.5)WITHINGROUP(ORDERBYSALARYDESC)
---------- --------------------------------------------------
10 250
20 230
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
5-16) PERCENTILE_DISC 함수
--------------------------------------------------------------------------------
이 함수는 불연속 모델에 대한 inverse distribution function이다.
【형식】
PERCENTILE_DISC(expr) WITHIN GROUP (ORDER BY expr [{DESC|ASC}])
[OVER (query_partition_cluause)]
【예제】
SQL> select dept_no,percentile_disc(0.5) within group
2 (order by salary DESC)
3 from employees GROUP BY dept_no;
DEPT_NO PERCENTILE_DISC(0.5)WITHINGROUP(ORDERBYSALARYDESC)
---------- --------------------------------------------------
10 250
20 240
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
5-17) PERCENT_RANK 함수
--------------------------------------------------------------------------------
이 함수는 CUME_DIST 함수와 유사하게 percent_rank 값을 반환한다.
【형식】
PERCENT_RANK(expr,...) WITHIN GROUP (ORDER BY expr [{DESC|ASC}]
[NULLS {FIRST|LAST}],...)
또는
PERCENT_RANK() OVER( [query_partition_clause] order_by_clause)
【예제】
SQL> select percent_rank(230,0.05) within group
2 (order by salary,bonus) from employees;
PERCENT_RANK(230,0.05)WITHINGROUP(ORDERBYSALARY,BONUS)
------------------------------------------------------
.25
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
5-18) RANK 함수
--------------------------------------------------------------------------------
이 함수는 그룹 내에서 위치를 반환한다.
【형식】
RANK(expr,...) WITHIN GROUP (ORDER BY expr [{DESC|ASC}]
[NULLS {FIRST|LAST}],...)
또는
RANK() OVER( [query_partition_clause] order_by_clause)
【예제】
SQL> select rank(230,0.05) within group
2 (order by salary,bonus) from employees;
RANK(230,0.05)WITHINGROUP(ORDERBYSALARY,BONUS)
----------------------------------------------
2
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
5-19) REGR_(linear regression) function* 함수
--------------------------------------------------------------------------------
선형 회귀(linear regression) 함수 ordinary-least squares regression line을 찾도록 한다.
사용되는 회귀함수는 자음 중 하나이다.
REGR_SLOPE REGR_INTERCEPT REGR_COUNT
REGR_R2 REGR_AVGX REGR_AVGY
REGR_SXX REGR_SYY REGR_SXY
【형식】
REGR { REGR_SLOPE|REGR_INTERCEPT|REGR_COUNT|REGR_R2|REGR_AVGX|
REGR_AVGY|REGR_SXX|REGR_SYY|REGR_SXY}
(expr1,expr2) [OVER (analytic_clause)]
【예제】
SQL> select regr_slope(salary,bonus) from employees
REGR_SLOPE(SALARY,BONUS)
------------------------
.533333333
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
5-20) STDDEV 함수
--------------------------------------------------------------------------------
이 함수는 standard deviation을 반환한다.
【형식】
STDDEV [{DISTINCT|ALL}] (expr) [OVER (analytic_clause)]
【예제】
SQL> select stddev(salary) from emp;
STDDEV(SALARY)
--------------
14.1421356
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
5-21) STDDEV_POP 함수
--------------------------------------------------------------------------------
이 함수는 population standard deviation을 반환한다.
【형식】
STDDEV_POP (expr) [OVER (analytic_clause)]
【예제】
SQL> select stddev_pop(salary) from emp;
STDDEV_POP(SALARY)
------------------
12.2474487
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
5-22) STDDEV_SAMP 함수
--------------------------------------------------------------------------------
이 함수는 cumulative sample standard deviation을 반환한다.
【형식】
STDDEV_SAMP (expr) [OVER (analytic_clause)]
【예제】
SQL> select stddev_samp (salary) from emp;
STDDEV_SAMP(SALARY)
-------------------
14.1421356
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
5-23) SUM 함수
--------------------------------------------------------------------------------
이 함수는 합계를 반환한다.
【형식】
SUM ([{DISTINCT|ALL}] expr) [OVER (analytic_clause)]
【예제】
SQL> select sum(salary) from emp;
SUM(SALARY)
-----------
960
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
5-24) VAR_POP 함수
--------------------------------------------------------------------------------
이 함수는 population variance를 반환한다.
【형식】
VAR_POP (expr) [OVER (analytic_clause)]
【예제】
SQL> select var_pop(salary) from emp;
VAR_POP(SALARY)
---------------
150
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
5-25) VAR_SAMP 함수
--------------------------------------------------------------------------------
이 함수는 sample variance를 반환한다.
【형식】
VAR_SAMP (expr) [OVER (analytic_clause)]
【예제】
SQL> select var_samp(salary) from emp;
VAR_SAMP(SALARY)
----------------
200
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
5-26) VARIANCE 함수
--------------------------------------------------------------------------------
이 함수는 variance를 반환한다.
【형식】
VARIANCE ([{DISTINCT|ALL}] expr) [OVER (analytic_clause)]
【예제】
SQL> select variance(salary) from emp;
VARIANCE(SALARY)
----------------
200
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
5-27) Grouping sets 함수
--------------------------------------------------------------------------------
Grouping sets 함수는 Group by의 확장된 형태로 하나의 쿼리문에서 원하는 그룹핑 조건을 여러 개 기술할 수 있으며,
grouping sets 함수 사용이 불가능한 이전 버전에서
복잡한 union all 연산자를 사용하여 기술하던 것을 간단하게
한 문장 안에서 해결할 수 있어 검색시 효율성이 증대 된다.
다시 말해서, grouping sets 함수를 사용하면,
group by ... union all을 사용한 것보다
SQL 문이 간단해지고 또한 실행시 성능이 빨라진다.
【형식】
SELECT 컬럼명,그룹함수(컬럼명), GROUPING(컬럼명)
FROM 테이블명
WHERE 조건
GROUP BY [ROLLUP | CUBE] 그룹핑하고자하는 컬럼명, ...
[GROUPING SETS (컬럼명,컬럼명, ...), ...]
HAVING 그룹조건
ORDER BY 컬럼명 또는 위치번호
【예제】
SQL> select grade,deptno,sum(salary)
2 from aaa
3 group by grouping sets(grade,deptno);
GRADE DEPTNO SUM(SALARY)
---------- ---------- -----------
1 900
2 1200
10 500
20 700
30 900
SQL> select grade,deptno,sum(salary)
2 from aaa
3 group by grouping sets((grade,name),(deptno,name));
GRADE DEPTNO SUM(SALARY)
---------- ---------- -----------
1 100
1 300
1 500
2 200
2 400
2 600
10 100
20 200
30 300
10 400
20 500
30 600
12 rows selected.
SQL>
【예제】Union all을 사용한 경우
SQL> select grade,deptno,sum(salary)
2 from aaa
3 group by grade,deptno
4 union all
5 select grade,deptno,sum(salary)
6 from aaa
7 group by grade,deptno;
GRADE DEPTNO SUM(SALARY)
---------- ---------- -----------
1 10 100
1 20 500
1 30 300
2 10 400
2 20 200
2 30 600
1 10 100
1 20 500
1 30 300
2 10 400
2 20 200
2 30 600
12 rows selected.
SQL>
composite columns란 rollup, cube, grouping sets 절과 같은 기능을 사용하면
표현되는 각 컬럼이 하나가 아닌 복수 개의 컬럼으로 정의되는 경우이며
다음 표를 보고 이해하자.
composite column 문의 경우 group by 문의 경우 group by grouping sets(a,b,c) group by a union allgroup by b union allgroup by c group by grouping sets(a,b,(b,c)) group by a union allgroup by b union allgroup by b,c group by grouping sets((a,b,c)) group by a,b,c group by grouping sets(a,(b),()) group by a union allgroup by b union allgroup by () group by grouping sets(a,rollup(b,c)) group by a union allgroup by rollup(b,c) group by rollup(a,b,c) group by (a,b,c) union allgroup by (a,b) union allgroup by (a) union allgroup by () group by cube(a,b,c) group by (a,b,c) union allgroup by (a,b) union allgroup by (a,c) union allgroup by (b,c) union allgroup by (a) union allgroup by (b) union allgroup by (c) union allgroup by ()
6-1) AVG* 함수
--------------------------------------------------------------------------------
조건을 만족하는 행(row)의 평균을 값을 반환하며,
aggregate 함수나
analytic 함수로 사용된다.
【형식】
AVG( [DISTINCT | ALL] 컬럼명)
[ [OVER] (analytic 절)]
【예제】aggregate 예
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL> select avg(salary) from emp;
AVG(SALARY)
-----------
240
SQL>
【예제】analytic 예
SQL> select avg(distinct salary) over(partition by bonus)
2 from emp;
AVG(DISTINCTSALARY)OVER(PARTITIONBYBONUS)
-----------------------------------------
236.666667
236.666667
236.666667
250
SQL> select avg(salary) over(partition by bonus order by id
2 rows between 1 preceding and 1 following) as avg from emp;
AVG
----------
245
236.666667
235
250
SQL>
6-2) CORR* CORR* 함수
--------------------------------------------------------------------------------
집합 쌍의 상관관계 계수를 반환한다.
【형식】
CORR( expr1, expr2 ) [ [OVER] (analytic 절)]
【예제】
SQL> select corr(avg(bonus),max(bonus))
2 from employees
3 group by dept_no;
CORR(AVG(BONUS),MAX(BONUS))
---------------------------
1
SQL>
6-3) COUNT* 함수
--------------------------------------------------------------------------------
쿼리한 행의 수를 반환한다.
【형식】
COUNT([*|DISTINCT|ALL] 컬럼명) [ [OVER] (analytic 절)]
【예제】
SQL> select count(*) from emp;
COUNT(*)
----------
4
SQL> select count (distinct dept_no) from employees;
COUNT(DISTINCTDEPT_NO)
----------------------
2
SQL> select count (all dept_no) from employees;
COUNT(ALLDEPT_NO)
-----------------
4
SQL> select salary,count(*)
2 over (order by salary)
3 from emp;
SALARY COUNT(*)OVER(ORDERBYSALARY)
---------- ---------------------------
220 1
240 2
250 4
250 4
SQL>
6-4) COVAR_SAMP 함수
--------------------------------------------------------------------------------
이 함수는 number 쌍의 집합에 대한 sample covariance를 반환한다.
【형식】
COVAR_SAMP(expr1, expr2 [ OVER (analytic 절)] )
【예제】
SQL> select covar_samp(bonus,salary) from emp;
COVAR_SAMP(BONUS,SALARY)
------------------------
83.3333333
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
6-5) CUME_DIST 함수
--------------------------------------------------------------------------------
이 함수는 그룹 값 내에서 어떤 값의 cumulative distribution(누적분포)을 계산한다.
【형식】
CUME_DIST(expr,... WITHIN GROUP (ORDER BY
expr [DESC | ASC] [NULLS {FIRST|LAST}],...)
또는
CUME_DIST() over ([query_partition_clause] order_by_clause)
【예제】
SQL> select cume_dist(230) within group
2 (order by salary ) from emp;
CUME_DIST(230)WITHINGROUP(ORDERBYSALARY)
----------------------------------------
.4
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
6-6) DENSE_RANK 함수
--------------------------------------------------------------------------------
그룹 내에서 순위를 반환한다.
【예제】
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL> select dense_rank(230, .05) within group
2 (order by salary, bonus) "Dense Rank"
3 from employees;
Dense Rank
----------
2
SQL>
6-7) FIRST 함수
--------------------------------------------------------------------------------
first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.
【형식】
집합함수 KEEP (
DENSE_RANK FIRST ORDER BY
expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
2 min(salary) keep (dense_rank first order by salary) "Worst",
3 max(salary) keep (dense_rank last order by salary) "Best"
4 from employees
5 order by id;
Worst Best
---------- ----------
220 250
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
6-8) FIRST_VALUE 함수
--------------------------------------------------------------------------------
이 함수는 서열화된 값에서 첫 번째를 출력한다.
【형식】
FIRST_VALUE ( expr ) OVER ( analytic_절)
【예제】
SQL> select salary,first_value(name)
2 over (order by salary asc)
3 from (select * from employees
4 where dept_no = 20
5 order by salary);
SALARY FIRST_VALU
---------- ----------
220 jijoe
240 jijoe
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
6-9) LAG 함수
--------------------------------------------------------------------------------
이 함수는 analytic 함수로서,
self join하지 않고 하나의 테이블에서 동시에 한 행(row) 이상을 접근할 수 있도록 한다.
【형식】
LAG ( value_expr [,offset] [,default] )
OVER ([query_partition_clause] order_by_clause )
【예제】
SQL> select name,salary,LAG(salary,1,0)
2 OVER (ORDER BY salary) FROM employees;
NAME SALARY LAG(SALARY,1,0)OVER(ORDERBYSALARY)
---------- ---------- ----------------------------------
jijoe 220 0
Joe 240 220
Cho 250 240
kim 250 250
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
6-10) LAST_VALUE 함수
--------------------------------------------------------------------------------
이 함수는 서열화된 값에서 마지막 번째를 출력한다.
【형식】
LAST_VALUE ( expr ) OVER ( analytic_절)
【예제】
SQL> select salary,last_value(name)
2 over (order by salary asc)
3 from (select * from employees
4 where dept_no = 20
5 order by salary);
SALARY LAST_VALUE
---------- ----------
220 jijoe
240 Joe
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
6-11) LEAD 함수
--------------------------------------------------------------------------------
이 함수는 analytic 함수로서, self join하지 않고
하나의 테이블에서 동시에 한 행(row) 이상을 접근할 수 있도록 한다.
【형식】
LEAD ( value_expr [,offset] [,default] )
OVER ([query_partition_clause] order_by_clause )
【예제】
SQL> select name,salary,LEAD(salary,1,0)
2 OVER (ORDER BY salary) FROM employees;
NAME SALARY LEAD(SALARY,1,0)OVER(ORDERBYSALARY)
---------- ---------- -----------------------------------
jijoe 220 240
Joe 240 250
Cho 250 250
kim 250 0
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
6-12) NTILE 함수
--------------------------------------------------------------------------------
이 함수는 analytic 함수로서, 데이터를 주어진 bucket 수 expr로 분리한다.
【형식】
NTILE ( expr ) OVER ([query_partition_clause] order_by_clause )
【예제】
SQL> select name,salary,NTILE(3) OVER (ORDER BY salary DESC)
2 FROM employees;
NAME SALARY NTILE(3)OVER(ORDERBYSALARYDESC)
---------- ---------- -------------------------------
Cho 250 1
kim 250 1
Joe 240 2
jijoe 220 3
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
6-13) RATIO_TO_REPORT 함수
--------------------------------------------------------------------------------
이 함수는 analytic 함수로서, 데이터 합에 대한 구성비를 계산한다.
【형식】
RATIO_TO_REPORT ( expr ) OVER ([query_partition_clause])
【예제】
SQL> select name,salary,RATIO_TO_REPORT(salary) OVER ()
2 FROM employees;
NAME SALARY RATIO_TO_REPORT(SALARY)OVER()
---------- ---------- -----------------------------
Cho 250 .260416667
Joe 240 .25
kim 250 .260416667
jijoe 220 .229166667
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
6-14) ROW_NUMBER 함수
--------------------------------------------------------------------------------
이 함수는 analytic 함수로서, 각 행(row)에 unique 번호를 부여한다.
【형식】
ROW_NUMBER () OVER ([query_partition_clause] order_by_clause )
【예제】
SQL> SELECT ROW_NUMBER() OVER (ORDER BY salary DESC),name
2 FROM employees;
ROW_NUMBER()OVER(ORDERBYSALARYDESC) NAME
----------------------------------- ----------
1 Cho
2 kim
3 Joe
4 jijoe
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
7-1) REF 타입
--------------------------------------------------------------------------------
테이블의 어떤 컬럼이 독립된 다른 객체 테이블을 참조하는 데이터 타입을 의미한다.
일반적으로 테이블을 만들때 사용하는 외부키(foreign-key)를 이용한 참조관계와 유사하다.
REF 타입의 컬럼 데이터를 읽을 때는 반드시 DEREF 함수를 사용한다.
REF 타입으로 정의된 컬럼에는 실제 데이터가 저장되는 것이 아니고 참조되는 객체가 존재하는 포인트정보만 가지고 있기 때문에 객체가 삭제 된다면, 해당 컬러은 정상적으로 검색되지 못한다.
이러한 현상을 REF의 Dangling 현상이고 한다.
이러한 dangling 현상을 방지하기 위해서는 삭제된 객체 정보를 참조하는 행의 컬럼 정보를 analyze 명령어를 이용하여 null 값으로 변경해 주어야 한다.
【예제】
SQL> connect jijoe/jijoe_password
connected
SQL> create type person_type as object(
2 first_name varchar2(10),
3 last_name varchar2(10),
4 phone varchar(12),
5 birthday varchar2(12));
6 /
Type created.
SQL> create type emp_type as object (
2 empno number,
3 emp person_type);
4 /
Type created.
SQL> create table emp2 of emp_type
2 oidindex emp_oid;
Table created.
SQL> insert into emp2 values(
2 emp_type(1000,person_type('junik','joe','123-1234','20-jul-04')));
1 row created.
SQL> create table dept(
2 empno number(4),
3 ename varchar2(15),
4 mgr REF emp_type SCOPE IS emp2);
Table created.
SQL> insert into dept
2 select empno, 'SCOTT', REF(e)
3 from emp2 e
4 where empno=1000;
1 row created.
【예제】
SQL> select ename,empno from dept;
ENAME EMPNO
--------------- ----------
SCOTT 1000
SQL> select mgr, DEREF(mgr) from dept;
MGR
--------------------------------------------------------------------------------
DEREF(MGR)(EMPNO, EMP(FIRST_NAME, LAST_NAME, PHONE, BIRTHDAY))
--------------------------------------------------------------------------------
0000220208DFA05B27A63701D9E034080020B588F4DFA05B27A63601D9E034080020B588F4
EMP_TYPE(1000, PERSON_TYPE('junik', 'joe', '123-1234', '20-jul-04'))
SQL>
【예제】
SQL> select empno,ename,mgr
2 from dept
3 where mgr is dangling;
no rows selected
SQL> analyze table dept validate REF update set dangling to NULL;
Table analyzed.
SQL>
8-1) ROWID 컬럼
--------------------------------------------------------------------------------
오라클에서 내부적으로 사용되는 컬럼을 pseudocolumn이라고 하며,
ROWID, ROWNUM등이 있다.
ROWID는 데이터베이스에서 컬럼이 위치한 장소이다.
【예제】
SQL> select rowid from test;
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select rowid from test
2 where rowidtochar(rowid) like '%AABAA%';
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select lengthb(rowidtonchar(rowid)), rowidtonchar(rowid)
2 from test;
LENGTHB(ROWIDTONCHAR(ROWID)) ROWIDTONCHAR(ROWID
---------------------------- ------------------
36 AAAHbHAABAAAMXCAAA
SQL>
여기서 rowid의 의미는 다음과 같다.
AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호
8-2) ROWNUM 컬럼
--------------------------------------------------------------------------------
오라클에서 내부적으로 사용되는 컬럼을 pseudocolumn이라고 하며,
ROWID, ROWNUM등이 있다.
ROWNUM은 테이블에서 select 되어진 일련 번호임
【예제】
SQL> select rownum,ename from emp;
ROWNUM ENAME
---------- ----------
1 CLARK
2 MILLER
3 JONES
4 ALLEN
5 MARTIN
6 CHAN
6 rows selected.
SQL> delete from emp where ename='JONES';
1 row deleted.
SQL> select rownum,ename from emp;
ROWNUM ENAME
---------- ----------
1 CLARK
2 MILLER
3 ALLEN
4 MARTIN
5 CHAN
SQL>
Posted by 홍반장
