[DB] function

[작성일: 2023. 03. 22]

function

single row function

한 줄 입력, 한 줄 출력

numeric

abs(x) 절대값
ceil(x) 올림
floor(x) 내림
round(x, y) 반올림

-- abs : 절대값
select abs(1.1) from dual;
select abs(-50.1) from dual;

-- ceil : 올림
select ceil(2.83) from dual;
select ceil(-2.83) from dual;

-- floor : 내림
select floor(3.14) from dual;
select floor(-3.14) from dual;

-- round : 반올림
select round(2.83) from dual;
select round(-2.83) from dual;
select round(-2.83, 3) from dual;
select round(2.83, -3) from dual;

-- 특정한 테이블에서 값을 가져오는 것이 아닐 때 dual 테이블을 사용함.

text

lower(str) 소문자
upper(str) 대문자
length(str) 길이 구하기
substr(str, m , n) 문자열 일부를 선택하기
replace(s1, s2, s3) s1(컬럼명)에서 s2를 찾아 s3로 바꾸기
concat(s1, s2, s3...) 문자열들을 연결해줌.

-- lower, upper, length
-- 한글은 3byte로 인식.
select lower('Good Morning'),
upper('Good Morning'),
length('Good Morning'),
length('굿모닝')
from dual;

-- substr
select substr('Good Morning', 3, 6),
substr('Good Morning', 3)
from dual;

-- 영화 이름과 개봉 일을 출력하되 아래와 같은 형식이 되게 하기
-- 베놈2 04월18일
select movie_name, concat(substr(open_date,5,2),'월',substr(open_date,7,2),'일') from movie;

select substr(movie_name,1, 3) from movie;

-- 영화 제목을 출력하되 뒤에서 2글자만 출력하시오.
select substr(movie_name,-2) from movie;
-- 영화 제목을 출력하되 뒤에서 2번째 글자부터 1개만 출력하기
select substr(movie_name,-2, 1) from movie;

-- replace : 특정한 문자가 나오면 바꿔준다. 중첩해서 사용 가능하다.
SELECT REPLACE(REPLACE('서울시', '서울', 'SEOUL'), '시', ' city') AS city FROM DUAL;

date

add_months() 월을 더해줌.
sysdate() 현재 시간이 출력됨.

char

to_char() 문자로 바꿔줌.

-- to_char
SELECT sysdate(), to_char(sysdate(), 'yyyymmdd') AS yyyymmdd1
				, to_char(sysdate(), 'yyyy/mm/dd') AS yyyymmdd2
				, to_char(sysdate(), 'dd-mm-yyyy') AS yyyymmdd3
				, to_char(sysdate(), 'dd-mm-yy') AS yyyymmdd4 
				, to_char(sysdate(), 'HH') AS HH
				, to_char(sysdate(), 'hh24miss') AS hh24miss
				, to_char(sysdate(), 'yyyymmddhh24miss') AS date
				FROM DUAL;

group(aggregate) function

여러 줄 입력, 한 줄 출력

**count(*), distinct**

-- Aggregate Function
-- 1. count(*) 인덱스가 있다면 인덱스 넣기, 보통은 primary key로 카운팅 함. 
-- null은 세지 않음.
SELECT count(*), count(x_loc) FROM hptl_mast;

-- 선행학습 : 지역별 병원 수를 구하시오.
SELECT SIDO_CD_NM, count(*) FROM hptl_mast GROUP BY SIDO_CD_NM;

-- 2. distinct : 중복 제외
SELECT DISTINCT TYP_CD_NM FROM HPTL_MAST;

-- count와 distinct의 조합
-- 병원이 있는 시군구 수 구하기
SELECT count(DISTINCT SIGGU_CD_NM) FROM hptl_mast;

min(), max(), avg(), sum()

-- 3. min(), max(), avg(), sum()
-- 최소 의사 수, 최대 의사 수, 평균 의사 수, 총 의사 수를 구해보시오.

SELECT min(DOC_NUM), MAX(DOC_NUM), AVG(DOC_NUM), SUM(DOC_NUM) FROM HPTL_MAST;

-- 시도별 최소 의사 수, 최대 의사 수, 총 의사 수
SELECT SIDO_CD_NM, MIN(DOC_NUM), MAX(DOC_NUM), SUM(DOC_NUM) FROM HPTL_MAST GROUP BY SIDO_CD_NM;

order by, group by, having

SQL의 결과를 정렬하기 위한 문법
order by 순서 정렬하기
- order by 컬럼명1 [asc|desc], 컬럼명2
group by 그룹으로 묶기
- Aggregate 함수를 그룹별로 나누어서 적용시킬 때 사용하는 문법
- group by 컬럼명1, 컬럼명2, ...
having group by 등으로 나온 결과를 필터링 하기
- 그룹의 결과물에 대한 조건을 부여할 때 where가 아닌 having 을 사용함.
- having count(*) > 200
- with절 사용

-- 4. order by
-- 의사가 많은 순서대로 병원 이름과 의사 수 출력하기
SELECT hptl_nm, doc_num FROM hptl_mast ORDER BY DOC_NUM DESC;

-- 실습과제 4
-- 가장 오래된 전통있는 병원부터 병원이름, 오픈일, 주소를 출력하시오.
-- 오픈일자가 같으면 병원명 오름차순으로 정렬
-- 오픈일자가 없는 병원은 '99991231'로 셋팅

SELECT hptl_nm, CASE WHEN open_date = '' THEN '9999-12-31'
WHEN open_date = '1900-01-01' THEN '9999-12-31' ELSE open_date END AS open_dt,addr
FROM hptl_mast 
ORDER BY open_dt, hptl_nm;

-- 5. group by
-- 시도별, 병원타입별, 병원 수, 의사 수
SELECT sido_cd_nm, typ_cd_nm, count(*) AS "병원 수", sum(doc_num) AS "의사 수" FROM hptl_mast 
GROUP BY sido_cd_nm, typ_cd_nm
ORDER BY typ_cd_nm;

-- 6. having
WITH cntbysido AS (
	SELECT sido_cd_nm, count(*) AS cnt FROM hptl_mast 
	GROUP BY sido_cd_nm
), manyhospitals AS (
	SELECT * FROM cntbysido WHERE cnt > 3000
)
SELECT * FROM manyhospitals;

SELECT sido_cd_nm, count(*) AS cnt FROM hptl_mast 
GROUP BY sido_cd_nm 
HAVING cnt > 3000 
ORDER BY cnt desc;

요약

select 컬럼명들... from 테이블명 where 조건식 group by 컬럼명들 having 조건식 order by 컬럼명들

* 특이사항: with절 존재

* select에서 가장 중요한 부분은 where 조건에서 가능한 많은 범위를 줄여줘야 함.

🐣 해당 게시글은 입문 개발자가 요약/정리한 글이므로 틀린 내용이나 오타가 있을 수 있습니다.

저작자표시 비영리 변경금지 (새창열림)