복수행 함수 (GROUP 함수)
: 여러건의 데이터를 동시에 입력 받아서 결과값 1건을 만들어 주는 함수
COUNT 함수
: 입력되는 데이터의 총 건수를 반환
: *은 NULL 값을 포함한 결과
: 컬럼명이 들어가면 NULL 값을 제외한 결과
SUM 함수
: 입력된 데이터들의 합계 값을 구하는 함수
AVG 함수
: 입력된 값들의 평균 값을 구해주는 함수
: 계산시 해당 컬럼의 NULL값은 제외함
MAX 함수 / MIN 함수
: MAX - 주어진 데이터 중 가장 큰 값을 반환
: MIN - 주어진 데이터 중 가장 작은 값을 반환
: 날짜일 경우 최근 날짜가 크고 이전 날짜가 작다
: 문자(영어)의 경우 Z가 가장 크고 A가 가장 작다
STDDEV 함수 / VARIANCE 함수
: STDDEV - 표준 편차를 구하는 함수
: VARIANCE - 분산을 구하는 함수
GROUP BY
: GROUP BY 뒤에 오는 컬럼 값을 기준으로 먼저 모아놓고 SELECT 절에 적혀 있는 그룹 함수를 적용하게 됩니다.
: 10g R1 버전 까지는 GROUP BY 결과가 정렬 되어 출력되었음
: 10g R2 버전부터는 초기화 파라미터 파일(prile 이나 spfile)에 "_gby_hash_aggregation_enabled"=FALSE로 설정
: 속도는 느려지지만, GROUP BY시 자동 정렬해줌
HAVING 절을 사용해 그룹핑한 조건으로 검색
: 그룹 함수를 조건으로 할 경우 WHERE 절이 아닌 HAVING을 사용
: GROUP의 범위를 지정 (그룹화된 대상을 출력에서 제한)
: 조건식이 참인 결과 출력
ROLLUP 함수
: 각 기분별 소계를 요약해서 보여주는 함수
: GROUP BY의 확장된 형태로 사용하기가 쉬움
: 복잡한 SQL을 짧게 만듬
: 해당 데이터들을 반복적으로 읽는 것을 최소화
: 속도를 빠르게 해줌
CUBE 함수
: 소계와 전체 합계까지 출력하는 함수
(student table)
create table student
( studno number(4) primary key,
name varchar2(30) not null,
id varchar2(20) not null unique,
grade number check(grade between 1 and 6),
jumin char(13) not null,
birthday date,
tel varchar2(15),
height number(4),
weight number(3),
deptno1 number(3),
deptno2 number(3),
profno number(4)) tablespace users;
insert into student values (
9411,'James Seo','75true',4,'7510231901813',to_date('1975-10-23','YYYY-MM-DD'),'055)381-2158',180,72,101,201,1001);
insert into student values (
9412,'Rene Russo','Russo',4,'7502241128467',to_date('1975-02-24','YYYY-MM-DD'),'051)426-1700',172,64,102,null,2001);
insert into student values (
9413,'Sandra Bullock','Bullock',4,'7506152123648',to_date('1975-06-15','YYYY-MM-DD'),'053)266-8947',168,52,103,203,3002);
insert into student values (
9414,'Demi Moore','Moore',4,'7512251063421',to_date('1975-12-25','YYYY-MM-DD'),'02)6255-9875',177,83,201,null,4001);
insert into student values (
9415,'Danny Glover','Glover',4,'7503031639826',to_date('1975-03-03','YYYY-MM-DD'),'031)740-6388',182,70,202,null,4003);
insert into student values (
9511,'Billy Crystal','Crystal',3,'7601232186327',to_date('1976-01-23','YYYY-MM-DD'),'055)333-6328',164,48,101,null,1002);
insert into student values (
9512,'Nicholas Cage','Cage',3,'7604122298371',to_date('1976-04-12','YYYY-MM-DD'),'051)418-9627',161,42,102,201,2002);
insert into student values (
9513,'Micheal Keaton','Keaton',3,'7609112118379',to_date('1976-09-11','YYYY-MM-DD'),'051)724-9618',177,55,202,null,4003);
insert into student values (
9514,'Bill Murray','Murray',3,'7601202378641',to_date('1976-01-20','YYYY-MM-DD'),'055)296-3784',160,58,301,101,4007);
insert into student values (
9515,'Macaulay Culkin','Culkin',3,'7610122196482',to_date('1976-10-12','YYYY-MM-DD'),'02)312-9838',171,54,201,null,4001);
insert into student values (
9611,'Richard Dreyfus','Dreyfus',2,'7711291186223',to_date('1977-11-29','YYYY-MM-DD'),'02)6788-4861',182,72,101,null,1002);
insert into student values (
9612,'Tim Robbins','Robbins',2,'7704021358674',to_date('1977-04-02','YYYY-MM-DD'),'055)488-2998',171,70,102,null,2001);
insert into student values (
9613,'Wesley Snipes','Snipes',2,'7709131276431',to_date('1977-09-13','YYYY-MM-DD'),'053)736-4981',175,82,201,null,4002);
insert into student values (
9614,'Steve Martin','Martin',2,'7702261196365',to_date('1977-02-26','YYYY-MM-DD'),'02)6175-3945',166,51,201,null,4003);
insert into student values (
9615,'Daniel Day-Lewis','Day-Lewis',2,'7712141254963',to_date('1977-12-14','YYYY-MM-DD'),'051)785-6984',184,62,301,null,4007);
insert into student values (
9711,'Danny Devito','Devito',1,'7808192157498',to_date('1978-08-19','YYYY-MM-DD'),'055)278-3649',162,48,101,null,null);
insert into student values (
9712,'Sean Connery','Connery',1,'7801051776346',to_date('1978-01-05','YYYY-MM-DD'),'02)381-5440',175,63,201,null,null);
insert into student values (
9713,'Christian Slater','Slater',1,'7808091786954',to_date('1978-08-09','YYYY-MM-DD'),'031)345-5677',173,69,201,null,null);
insert into student values (
9714,'Charlie Sheen','Sheen',1,'7803241981987',to_date('1978-03-24','YYYY-MM-DD'),'055)423-9870',179,81,102,null,null);
insert into student values (
9715,'Anthony Hopkins','Hopkins',1,'7802232116784',to_date('1978-02-23','YYYY-MM-DD'),'02)6122-2345',163,51,103,null,null);
GROUPING SETS 함수
: 그룹핑 조건이 여러 개일 경우 아주 유용하게 사용될 수 있습니다.
: 지정한 그룹화 대상 열의 값과 동일한 수준으로 출력함
: 지정한 열이 계층적으로 분류되지 않고 각각 따로 그룹화 한 뒤 연산을 수행함
LISTAGG 함수
: 11g에서 추가된 함수
: 나열하고 싶은 컬럼 이름을 먼저 적고 데이터들을 구분할 구분자를 작은 따옴표 사이에 기록
: RETURN값이 4,000byte가 넘을 경우 오류가 발생
XMLAGG XML 함수
: 10g 이상에서 사용 가능
: RETURN 값이 4,000byte가 넘을 경우 사용
PIVOT 함수
: 11g 에서 추가된 함수
: 지정한 테이블 행의 일부를 열로 바꾸어 2차원 표 형태로 출력할 때 사용
UNPIVOT 함수
: PIVOT의 반대 개념
: column 단위를 row 단위로 변경해줌
: 합쳐 있는 것을 풀어서 보여 주는 역할
LAG 함수
: 이전 행 값을 가져올 때 사용하는 함수
LAG(컬럼명, X, Y)
X : X번째 이전 행 선택 값
Y : 기본 출력 값(기준이 될 행 번호)
LEAD 함수
: LAG 함수와 반대로 이후의 값을 가져오는 함수
LEAD(컬럼명, X, Y)
X : X번째 이후 행 선택 값
Y : 기본 출력 값(기준이 될 행 번호)
RANK 함수
: 주어진 컬럼 값의 그룹에서 값의 순위를 계산한 후 순위를 출력해줌
DENSE_RANK 함수
: RANK 함수와 비슷
: 동일한 순위를 하나의 건수로 취급하므로 연속된 순위를 보여줌
ROW_NUMBER 순위 함수
: 동일한 값이라도 고유한 순위를 부여
: ORACLE 에 경우 ROWID가 작은 값에 먼저 순위를 부여
(panmae table)
create table panmae
( p_date varchar2(8) not null,
p_code number not null,
p_qty number ,
p_total number ,
p_store varchar2(5) );
insert into panmae values ('20110101',100,3,2400,'1000');
insert into panmae values ('20110101',101,5,4500,'1001');
insert into panmae values ('20110101',102,2,2000,'1003');
insert into panmae values ('20110101',103,6,5400,'1004');
insert into panmae values ('20110102',102,2,2000,'1000');
insert into panmae values ('20110102',103,5,4500,'1002');
insert into panmae values ('20110102',104,3,2400,'1002');
insert into panmae values ('20110102',105,2,3000,'1000');
insert into panmae values ('20110103',100,10,8000,'1004');
insert into panmae values ('20110103',100,2,1600,'1000');
insert into panmae values ('20110103',100,3,2400,'1001');
insert into panmae values ('20110103',101,4,3600,'1003');
insert into panmae values ('20110104',100,2,1600,'1002');
insert into panmae values ('20110104',100,4,3200,'1003');
insert into panmae values ('20110104',100,5,4000,'1004');
insert into panmae values ('20110104',101,3,2700,'1001');
insert into panmae values ('20110104',101,4,3600,'1002');
insert into panmae values ('20110104',101,3,2700,'1003');
insert into panmae values ('20110104',102,4,4000,'1001');
insert into panmae values ('20110104',102,2,2000,'1002');
insert into panmae values ('20110104',103,2,1800,'1003');
SUM( ) OVER 함수를 활용한 누계 구하기
RATIO_TO_REPORT( ) 함수를 활용한 판매 비율 구하기
LAG 함수를 활용한 차이 구하기
'DataBase > OracleDB' 카테고리의 다른 글
| DDL 명령과 딕셔너리 (0) | 2020.09.04 |
|---|---|
| Constraint (제약 조건) (0) | 2020.09.04 |
| JOIN (0) | 2020.09.03 |
| 단일행 함수 (0) | 2020.09.01 |
| SELECT (0) | 2020.08.31 |
