[DB] 변환 함수, 그룹 함수(집계 함수), 중복 배제(distinct), group by, 날짜 함수, 정렬

* 변환 함수

문자열 변환

+ to_char(컬럼명, 'pattern') : 숫자와 날짜를 문자로 변환한다.

- 날짜 letter : y - 년 / m - 월 / d - 일 / h - 시 / hh(12 or 24) - 시 / mi - 분 / s - 초 / dy - ex)월, 화, 수 ... / day - ex)월요일, 화요일, 수요일 ... / q - 분기 / am - 오전, 오후

- pattern이 아닌 문자열은 ""으로 묶어서 넣어준다.

- pattern이 너무 길면 오류가 난다.

[참고] letter - 문자 , pattern - letter의 집합, format - pattern의 일정한 형태

+ to_date(문자열, 'pattern') : 날짜형식의 문자열로 변환한다.

- sysdate와 같은 date형식의 값을 넣게 되면 년,월,일,시간등의 구분이 안된다. to_date를 사용하면 이러한 구분을 명확히 할 수 있다.

- 추가, 변경시에는 에러가 발생하지 않는다. 그러나 함수를 사용할 때에는 에러가 발생한다.

 

숫자 변환

+ to_number('문자열') : 파라미터값을 숫자로 바꿔준다.

------------------------------

---- 변환 함수

------------------------------

 

-- 문자열 ----------------------------------------------------

-- *숫자 변환

select to_char(2014, '00,000') is0, to_char(2014, '99,999') is9

from dual;

 

-- *날짜 변환

select to_char(sysdate, 'yyyy "new year"') date1, to_char(sysdate, 'yyyy-mm-dd hh12(hh24):mi:ss dy day q am') date2

from dual;

-- *날짜 포맷 에러 확인 예제(error msg : 날짜 형식이 내부 버퍼에 비해 너무 길다.)

select to_char(sysdate, 'yyyy " 년 " mm "월" dd "일" am day hh24 "시" mi "분" ss "초"') dateEx

from dual;

-- 문) 사원번호, 사원명, 연봉, 입사일을 조회하라. 단, 입사일은 월-일-년 요일의 형식으로 출력하라.

-- 또한 연봉은 3자리마다 ','를 넣어서 출력하라.

select empno, ename, to_char(sal,'9,999'), to_char(hiredate, 'mm-dd-yyyy day') hiredate

from emp;

-- 문) 입사년도가 1981년인 사원의 사원번호, 사원명, 입사일, 연봉, 직무를 조회하라.

select empno, ename, hiredate, sal, job

from emp

where substr(to_char(hiredate, 'yyyy-mm-dd'),1,4) like '1981';

-- └ 강사님 답안.ver where to_char(hiredate, 'yyyy') like '1981'

 

 

-- *날짜 변환 함수 실습 예제

create table test_date(

name varchar2(15 byte),

hiredate date

);

insert into test_date(name, hiredate) values ('이진기',sysdate);

insert into test_date(name, hiredate) values ('김종현','1990-04-08');

-- └날짜 형식의 문자열은 추가 가능하다. >> '1990-04-08'

insert into test_date(name, hiredate) values ('김기범',to_date('1991-09-23', 'yyyy-mm-dd'));

-- └문자열을 날짜로 변경할 수 있다. >> to_date('1991-09-23', 'yyyy-mm-dd'

select * from test_date;

 

-- *함수의 파라미터가 date인 경우에는 arguments는 반드시 동일형으로 넣어야 한다.

select to_char('2014-09-30', 'yyyy-mm-dd') from dual;

-- └error

select to_char(to_date('2014-09-30', 'yyyy-mm-dd'), 'yyyy-mm-dd') from dual;

-- └문자열의 형식이 날짜로 변환되어 실행된다.

 

 

 

-- 숫자 ----------------------------------------------------

select '9'-'2' col1, to_number('9')-to_number('2') col2 from dual;

-- └문자열은 산술연산이 되지 않는다. 따라서 문자열의 산술연산은 숫자로 변환하여 계산하여야 한다. 그러나 to_number()를 사용하지 않아도 자동변환되어 계산된다.

 

 

 

* 그룹 함수(집계 함수)

- where절에 사용할 수 없다.

- 여러 행을 모아서 하나의 결과를 만들어낼때 사용한다.

- group by절과 같이 사용하면 그룹별 집계를 할 수 있다.

- count, sum, avg, max, min 등을 얻을 수 있다.

 

+ count(컬럼명) : 행의 수를 셀 때 사용한다.

- null컬럼은 포함하지 않는다.

- 숫자와 문자를 가리지 않는다.

+ sum(), avg() : 합계, 평균

+ max(), min() : 최고값, 최저값

------------------------------

---- 그룹 함수

------------------------------

 

-- 문) emp테이블의 전체 인원수를 조회하라

select count(ename) total_count, count(comm) comm_count, count(mgr) mgr_count, count(*) from emp;

-- └comm, mgr의 count를 통해 null은 포함하지 않는 것을 확인 할 수 있다.

 

-- 문) emp테이블에서 보너스를 받지 못하는 사원수를 조회하라

select count(empno)-count(comm) no_comm from emp;

 

-- *그룹 함수는 group by절로 묶여지지 않았을 때, 여러행이 조회되는 컬럼과 같이 사용하면 error가 발생한다.

select count(empno), ename from emp;

-- └error msg : 단일 그룹의 그룹 함수가 아닙니다.

 

 

-- 합계 ----------------------------------------------------

-- 문) 사원연봉의 합, 최고연봉, 최저연봉, 평균을 조회하라

select sum(sal) 총합, max(sal) 최고연봉, min(sal) 최저연봉, trunc(avg(sal),2) 평균 from emp;

-- 문) 최고연봉과 최저연봉의 차이를 출력하라

select max(sal)-min(sal) from emp;

 

-- *where절에는 그룹함수를 사용할 수 없다.

-- 문) 평균연봉이상인 사원의 사원명, 사원번호, 연봉을 조회하라.

select ename, empno, sal from emp where avg(sal) < sal;

-- └error msg : 그룹함수는 허가되지 않습니다.

 

 

 

* 중복 배제(distinct)

- 컬럼의 값 중 동일값을 출력하지 않을때 사용한다.

- error가 발생하지 않는다.

- 여러행이 조회되는 컬럼과 같이 사용될 때에는 중복배제가 되지 않는다.

- 조회하는 컬럼앞에 기술한다.

ex) select distinct 컬럼명

-- 중복 배제 -----------------------------------------------

select distinct deptno, empno from emp;

 

 

 

* group by

- 문법

select

from

group by 그룹으로 묶을 컬럼명,,,

- 조회한 값을 그룹(동일값)으로 묶어서 출력할 때 사용한다.

- 중복값이 출력된지 않는다.

- 그룹함수와 같이 사용한다.

- 그룹으로 묶여진 컬럼 이외의 컬럼이 조회되면 error가 발생한다.

- 그룹으로 묶을 조건은 having으로 사용

group by 그룹으로 묶을 컬럼명

having 그룹으로 묶을 조건

- 그룹의 총합을 낼때에는 rollup, cube를 사용할 수 있다.

+ cube : 합계를 먼저 조회하고, 그룹의 결과를 출력한다.

+ roll : 그룹의 결과를 먼저 출력하고, 합계를 나중에 출력한다.

ex)

gropu by rollup|cube(그룹으로 묶일 컬럼명)

-- group by의 사용 -----------------------------------------------

-- 문) emp테이블에서 부서번호를 출력하라. 단, 동일부서는 하나만 출력하라

select distinct deptno from emp;

-- └distinct or group by┐

select deptno

from emp

group by deptno;

 

-- *error 예제

select distinct deptno, empno from emp;

-- └distinct or group by┐

select deptno, empno

from emp

group by deptno;

-- └그룹으로 묶여지지 않은 컬럼이 조회되면 에러가 발생한다. distinct는 문제 없다.

 

-- *그룹함수와 함께 사용되면 그룹별 집계를 낼 수 있다.

-- 문) emp테이블의 부서별 사원수를 구하여라

select deptno, count(ename), sum(sal), avg(sal)

from emp

group by deptno;

-- 문) 매니저별 관리 사원수를 출력하라

select mgr, count(empno)

from emp

where mgr is not null

group by mgr;

 

 

-- *그룹으로 묶을 조건 : having절(그룹 함수를 사용할 수 있다.)

-- 문) 부서의 사원수가 4명 이상인 부서의 사원수를 조회하라.

select deptno, count(empno)

from emp

group by deptno

having count(empno) >= 4;

-- 문) emp테이블에서 부서별 평균 연봉이 2000이상인 부서의 부서번호, 평균연봉, 연봉의 총합을 조회하라.

-- 단, 평균연봉은 정수부만 취하시오.

select deptno, trunc(avg(sal),0) avg_sal, sum(sal) sum_sal

from emp

group by deptno

having avg(sal) >= 2000;

 

-- *error 예제

-- 부서별 최고연봉(그룹), 최고연봉자명(개별)을 조회하라

select max(sal), ename

from emp

group by deptno;

-- └그룹 데이터와 개별 데이터는 한번에 조회할 수 없다. => 서브쿼리중 스칼라서브쿼리를 사용하면 문제 해결 가능

 

-- *그룹별 합계와 총합을 조회할 때에는 cube, rollup을 사용한다.

-- 문) 부서별 연봉합과 전체 연봉합을 조회

select deptno, sum(sal) 총합 from emp group by cube(deptno);

select deptno, sum(sal) 총합 from emp group by rollup(deptno);

 

 

 

* 날짜 함수

+ add_months(날짜, 더할 개월 수) : 월을 더한 결과

+ months_between(큰 날짜, 작은 날짜) : 두 개월간의 차이

-- 날짜 함수 -----------------------------------------------

-- 월을 더했을 때 날짜

select add_months(sysdate, 3) from dual;

select add_months(hiredate, 3) from emp;

 

-- 두 날짜간 개월 차이

select months_between('2014-12-24', sysdate) from dual;

 

 

 

* 정렬

- 조회되는 레코드를 원하는 형태(오름, 내림차순)로 출력할 때 사용한다.

- order by절을 사용한다. select의 가장 마지막줄에 기술한다.

- 문법

select

from

where

group by

having

order by 정렬할 컬럼명 asc|desc, 두번째 정렬할 컬럼명 asc|desc,,,

- Oracle의 기본 정렬은 primary key가 설정된 컬럼의 오름차순 정렬이다.

- 오름차순은 asc, 내림차순은 desc로 수행한다. 이때 asc는 생략가능하다.

- 문자열이 숫자를 가지고 있을 때에는 자릿수의 정렬을 한다.(왼쪽을 기준으로 모은 후 비교 정렬한다.)

ex) 1,2,11,3,290,1230,35 ----(asc)----> 1,11,1230,2,290,3,35

-- 정렬 -----------------------------------------------

-- 문) 사원번호, 사원명, 입사일, 연봉을 조회하라. 단, 연봉의 오름차순으로 정렬하여 출력하라.

-- 이때 연봉이 같다면 사원번호의 내림차순으로 정렬하라

select empno, ename, hiredate, sal

from emp

order by sal asc, empno desc;

 

-- *컬럼명 대신 컬럼 위치로 대신할 수 있다.

/*select 1, 2, 3, 4 <-(위치를 나타내는 숫자)from emporder by 4 asc, 1 desc;*/

select empno, ename, hiredate, sal

from emp

order by 4 asc, 1 desc;

 

-- 정렬 예제

create table test_orderby(

num varchar2(5 byte)

);

 

insert into test_orderby(num) values ('5');

insert into test_orderby(num) values ('456');

insert into test_orderby(num) values ('13');

insert into test_orderby(num) values ('312');

insert into test_orderby(num) values ('64');

insert into test_orderby(num) values ('987');

insert into test_orderby(num) values ('79');

commit;

select * from test_orderby;

 

---- 문자열의 정렬

select num from test_orderby order by num asc;