기초적인 SQL 코드 및 정리

데이터 분석가라면 직접 DB에서 원하는 데이터를 뽑아내서 사용을 할 줄 알아야 할 때가 있다.

이러한 DB중 SQL을 많이 사용을 한다. SQL을 통해 데이터 적재나 구조를 직접 짤 필요는 없지만 언제든지 내가 원하는 데이터를 가져다 쓸 수 있게 하기 위해서는 SQL에 대한 기초 코드는 파악해두는 것이 좋을 것이다.

 

● SQL이란?

Structured Query Language(구조화된 질의 언어)의 약자로, 관계형 데이터베이스 관리 시스템(RDBMS)에서 데이터를 관리하고 처리하기 위해 사용되는 표준화된 언어이다. SQL은 데이터베이스에서 데이터를 조회, 삽입, 수정, 삭제 등 다양한 작업을 수행하는데 사용된다.

 

 

●DBMS의 특성

1. 실시간 접근성(Real-timepricessing): 컴퓨터가 접근할 수 있는 저장 장치에서 관리되는 데이터베이스는 지속적이고 비정형적인 질의에 대하여 실시간 처리가 가능해야한다.

2. 계속적인 변화(Continuous evolution): 데이터베이스의 상태는 동적이며, 기존의 데이터베이스가 존재한다면 그 데이터베이스에 새로운 데이터 삽입, 기존 데이터 삭제, 갱신 등의 변화를 주어 정확한 데이터를 유지해야 한다.

3. 동시 공용(Concurrent sharing): 데이터베이스는 다수의 사용자가 동시에 각자 원하는 데이터에 접근하여 이용할 수 있어야 한다.

4. 내용에 의한 참조(Contents reference): 데이터베이스 환경에서 데이터의 참조는 레코드의 주소나 위치에 의해서가 아니라 사용자가 요구하는 데이터의 내용, 즉 데이터가 가지고 있는 값에 따라 참조된다.

 

 

● SQL의 종류

1. DDL(Data Definition Language): 데이터와 그 구조를 정의하는 언어

- 1.1 CREATE: 데이터베이스 테이블 생성

- 1.2 DROP: 데이터베이스 테이블 삭제

- 1.3 ALTER: 기존 데이터베이스 테이블 재정의

DDL코드 구문

# CREATE 구문
CREATE TABLE 테이블명 (
	열1 데이터_유형,
    열2 데이터_유형,
    .....
);

CREATE TABLE employees (
	employee_id INT,
    employee_name VARCHAR(50)
);   							# CREATE 예시

# DROP 구문
DROP TABLE 테이블명;

DROP TABLE employees;  # DROP 예시

# ALTER 구문
ALTER TABLE 테이블명
ADD COLUMN 새로운_열 데이터_유형;

ALTER TABLE employees
ADD COLUMN hire_date DATE;   # ALTER 예시

 

2. DML(Data Manipulation Language): 데이터 검색과 수정 등의 처리를 위한 언어

- 2.1 INSERT: 테이블에 데이터 삽입/입력

- 2.2 DELETE: 테이블의 데이터 삭제

- 2.3 UPDATE: 기존 테이블 안의 데이터 수정

- 2.4 SELECT: 테이블 내 데이터 검색

DML코드 구문

# INSERT 구문
INSERT INTO 테이블명 (열1, 열2, ...)
VALUES (값1, 값2, ...);

INSERT INTO employees (employee_name, salary)
VALUES ('John Doe', 50000);    # INSERT 예시

# DELETE 구문
DELETE FROM 테이블명
WHERE 조건;

DELETE FROM employees
WHERE employee_name = 'John Doe';  # DELETE 예시

# UPDATE 구문
UPDATE 테이블명
SET 열1 = 값1, 열2 = 값2, ...
WHERE 조건;

UPDATE employees
SET salary = 55000
WHERE employee_name = 'John Doe';  # UPDATE 예시

# SELECT 구문
SELECT 열1, 열2, ...
FROM 테이블명
WHERE 조건;

SELECT employee_name. salary
FROM employees
WHERE salary > 50000;  # SELECT 예시

 

3. DCL(Data Control Language): 데이터베이스 사용자의 권한 제어를 위해 사용되는 언어

- 3.1 GRANT: 테이블에 권한 부여

- 3.2 REVOKE: 부여한 권한 취소/회수

DCL 코드 구문

# GRANT 코드 구문
GRANT 권한 ON 객체 TO 사용자 또는 역할;
GRANT SELECT, INSERT ON employees TO user1;  # GRANT 예시

# REBOKE
REVOKE 권한 ON 객체 FROM 사용자 또는 역할;
REVOKE INSERT ON employees FROM user1;  # REVOKE 예시

 

4. TCL(Transaction Control Language): 트랜잭션을 제어하기 위한 언어

- 4.1 COMMIT: 트랜잭션의 작업 확정하고, 데이터베이스에 변경사항을 영구적으로 반영

- 4.2 ROLLBACK: 트랜잭션의 작업 취소하고, 이전 상태로 되돌림

TCL 코드 구문

# COMMIT 명령어 구문 & 예시
COMMIT;
# ROLLBACK 명령어 구문 & 예시
ROLLBACK;

 

모든 SQL 코드의 끝은 ' ; ' 표시로 끝내야한다.

 

 

위에 간단한 SQL의 종류 별 예시코드를 확인해보았다. 이제 본격적으로 SQL기초 코드에 대해서 알아본다.

 

 

● SQL 기초

- 데이터 가져오기

" SELECT 열 이름 FROM 테이블명; " 이런식으로 테이블에 적재된 데이터 중, 지정된 열 이름의 데이터 전부를 들고오는 것이다. 만약 여러 열을 들고올려면  " SELECT 열 이름1, 열 이름2,... FROM 테이블명; " 처럼 열을 여러개 지정하면 된다. 만약 테이블에 존재하는 모든 열을 들고올려면 애스터리스트 문자인 * 를 사용하면 된다. " SELECT * FROM 테이블명; " 을 수행하면 테이블에 존재하는 모든 데이터를 들고올 수 있다.

아래는 간단한 예시 코드이다.

# CLERK 테이블에 id, staff_nm, dep_nm, gender의 데이터 열 전체를 불러오는 SQL 코드
SELECT id, staff_nm, dep_nm, gender FROM CLERK;

 

- 데이터 정렬하기

 가져온 데이터는 무작위 정렬이 되는데, 만약 특정 열 기준으로 오름차순, 내림차순으로 정렬을 하고 싶다면 ORDER BY절을 사용하면 된다. 이러한 ORDER BY절은 다음에 배울 다른 조건들보다 맨 마지막에 사용되어야 한다, 이는 이후 SQL구문을 사용할 때 알아보겠다.

아래는 ORDER BY절의 예시구문이다.

# id, staff_nm, dep_nm, gender를 CLERK 테이블에서 뽑고 id를 기준으로 오름차순 정렬
SELECT id, staff_nm, dep_nm, gender FROM CLERK
ORDER BY id;
#id 말고 위 코드에 지정한 열 위치를 기준으로도 사용할 수 있다.
SELECT id, staff_nm, dep_nm, gender FROM CLERK
ORDER BY 1;  # id를 기준으로 오름차순 정렬, 3을 사용하면 dep_nm 기준 오름차순 정렬 수행

ORDER BY절은 ASC와 DESC가 존재하는데, 기본값으로 ASC인 오름차순을 수행하며, 내림차순을 수행할려면 " ORDER BY id DESC; " 처럼 직접 입력을 해야한다. 또한 정렬 기준을 하나만 사용할 수 있는 것이 아닌 여러개를 정렬 기준으로 사용할 수 있다.

 

 

● SELECT문에 추가적으로 필요한 키워드

- DISTINCT 키워드: 중복이 있는 데이터를 중복없이 출력

# DISTINCT 구문 , 열에 대한 데이터의 중복을 없애고 출력함
SELECT DISTINCT 열 이름 FROM 테이블명;

# 복수의 DISTINCT 사용 , 열 이름1, 열 이름2를 집합으로 중복 없애고 출력함
SELECT DISTINCT 열 이름1, 열 이름2 FROM 테이블명;

# DISTINCT 예시: position, grade 두개를 집합으로 중복값을 없애서 출력
SELECT DISTINCT position, grade FROM EMP;

 

- ALIAS(별칭) : AS: 데이터의 열 이름을 바꿔서 출력한다.

AS를 사용하는 방법, 인용부호( " " ) 를 사용하는 방법, 인용부호 ( " " ) + AS 사용하는 방법으로 3가지가 있지만 주로 첫번째 방법인 AS를 사용하는 방법이 제일 편하고 간단하다. 아래는 ALIAS를 사용하는 예시이다.

# ALIAS : AS 예시
# id를 clerk_id로 열 이름을 지정하고 grade를 perf로 열 이름을 변경해서 데이터를 출력한다.
SELECT id AS clerk_id, position, party_nm, grade AS perf
FROM EMP;

 

 

● WHERE 조건절을 활용한 데이터 조건 주기

특정 테이블에서 특정한 조건을 만족하는 데이터를 추출하여 나타낼때 특정한 조건 주는 조건절이 WHERE 조건절이다.

1. 숫자형 데이터 조건 주기: SELECT 열이름1, 열이름2 FROM 테이블명 WHERE 비교할 열 = 숫자;  형식으로 작성하면 된다.

2. 문자/날짜형 데이터 조건 주기: SELECT 열이름1, 열이름2 FROM 테이블명 WHERE 비교할 열 이름 = '문자';  형식으로 작성하면 된다.

아래는 WHERE 조건절 예시 코드이다.

# 숫자형 데이터 조건 주기
SELECT id, cnrt_no, cnrt_amt
FROM INS_INFO
WHERE cnrt_amt >= 1000000;  # INS_INFO에서 id, cnrt_no, cnrt_amt 출력 -> cnrt_amt가 100만 이상만

# 문자형 데이터 조건 주기
# id, cnrt_dt, prdt_nm을 INS_INFO에서 출력 -> prdt_nm이 자동차 보험인 것만 and id기준 오름차순 정렬
SELECT id, cnrt_dt, prdt_nm
FROM INS_INFO
WHERE prdt_nm = '자동차 보험'
ORDER BY id;

# 날짜형 데이터 조건 주기
# id, cnrt_dt, prdt_nm을 INS_INFO에서 출력 -> cnrt_dt가 20130416 이후인 데이터만 출력
SELECT id, cnrt_dt, prdt_nm
FROM INS_INFO
WHERE cnrt_dt >= '20130416';

 

※ 데이터에 NULL값(결측값)이 존재할 경우

- NULL값인 행을 찾는 경우 IS NULL을 사용하고 NULL값이 아닌 행을 찾는 경우는 IS NOT NULL을 사용한다. 또한 NULL값은 오름차순 또는 내림차순으로 정렬 시 가장 큰 값으로 분류된다.(오름차순 정렬 시 NULL값은 맨 아래로 내려감)

아래는 IS NULL과 IS NOT NULL에 대한 예시 코드이다.

# IS NULL 예시(WHERE 조건절은 불러오지 않지만 INS_INFO내에 있는 열을 조건에 쓸 수 있다.)
SELECT id, cnrt_no, cnrt_amt
FROM INS_INFO
WHERE cncl_dt IS NULL;

# IS NOT NULL 예시
SELECT id, cnrt_no, cnrt_amt
FROM INS_INFO
WHERE cncl_dt IS NOT NULL;

 

- NULL값을 처리하는 문법 COALESCE로 처리를 할 수 있다. COALESCE문은 SELECT로 열을 불러올 때 같이 사용한다.

아래는 COALESCE에 대한 예시 코드이다.

# COLESCE 예시 -> COLESCE(cnrt_amt, 0)은 cnrt_amt가 결측값이면 0을 넣어준다.
SELECT id, cnrt_no, COLESCE(cnrt_amt, 0)
FROM INS_INFO
WHERE cnrt_no > 700000;

# 심화 코드
'''
CARD_TRAM_201311에 있는 cmf, party_nm과 pif_amt, inst_amt, cash_amt 가 null이면 0을 넣고
다 더해서 tot_amt라는 새로운 열을 만든다. 그 후 tot_amt를 내림차순으로 정렬한다.
'''
SELECT cmf, party_nm, (COALESCE(pif_amt, 0)
	+ COALESCE(inst_amt, 0)
    + COALESCE(cash_amt, 0)) AS tot_amt
FROM CARD_TRAN_201311
ORDER BY tot_amt DESC;

이렇게 데이터 중 NULL값이 존재할 시 처리하는 방법에 대해서 알아보았다, 이 이외에도 NULL값을 처리할 수 있는 함수들이 존재한다. 이러한 함수는 따로 다루지 않고 어떤 함수인지만 알아본다.

1. ZEROIFNULL(열 이름): 해당 열에 NULL값 포함되면 숫자 0으로 바꾸는 함수

2. NVL2(열 이름, 표현식1, 표현식2): 해당 열이 NULL이면 표현식 2의 값을 나타내고, NULL이 아니면 표현식1의 값을 나타냄

 

 

●논리연산자를 활용한 데이터 조건 주기 - WHERE 조건절에 두 개 이상의 조건을 주는 방법

1. 논리연산자 AND

논리연산자 AND는 특정한 조건들을 모두 만족하는 데이터를 추출하기위해 사용하는 연산자이며, 이러한 논리연산자 AND는 WHERE 조건절 뒤에 사용한다.

AND연산자는 수학의 집합 개념 중 하나인 교집합과 같은 역할을 수행한다.

아래는 AND 논리연산자에 대한 예시 코드이다.

# BRUNCH_INFO에서 brunch_no, brunch_nm, brunch_num, brunch_perf를 불러오는데
# brunch_num이 10 이상인 데이터와 brunch_perf가 C인 것만 들고 온다.
SELECT brunch_no, brunch_nm, brunch_num, brunch_perf
FROM BRUNCH_INFO
WHERE brunch_num >= 10 AND brunch_perf = 'C';

AND 논리연산자는 1개만 사용가능한 것이 아닌 2개 이상 여러개의 조건에도 사용할 수 있으며, AND를 조건 사이에 계속 이어서 사용하면 된다.

 

2. 논리연산자 OR

논리연산자 OR은 적어도 하나의 특정한 조건을 만족하는 데이터를 추출하기위해 사용하는 연산자이며, 이러한 논리연산자 OR은 AND와 마찬가지로 WHERE 조건절 뒤에 사용한다.

OR연산자는 수학의 집합 개념 중 하나인 합집합과 같은 역할을 수행한다.

아래는 OR 논리연산자에 대한 예시 코드이다.

# BRUNCH_INFO에서 brunch_no, brunch_nm, brunch_num, brunch_perf를 들고오는데,
# brunch_num이 8이상인 값 또는 brunch_perf가 A인 값을 불러온다.
SELECT brunch_no, brunch_nm, brunch_num, brunch_perf
FROM BRUNCH_INFO
WHERE brunch_num >= 8 OR brunch_perf = 'A';

OR 논리연산자는 AND와 마찬가지로 2개 이상 여러 조건에도 사용할 수 있다.

 

※ AND 논리연산자와 OR 논리연산자의 우선순위

우선순위로는 OR연산자보다 AND연산자를 우선적으로 처리한다. 만약 AND연산자보다 OR연산자를 먼저 처리하고 싶으면 해당 연산을 괄호로 묶어서 우선처리를 시켜주면 된다.

아래는 AND, OR 논리 연산자 우선순위에 대한 예시 코드이다.

# BRUNCH_INFO에서 brunch_no, brunch_nm, brunch_num, brunch_perf, close_dt를 들고오는데,
# brunch_num이 8이 넘는것 또는 brunch_perf가 A인 것을 먼저 뽑은 후 close_dt가 NULL값이 아닌 것 출력
SELECT brunch_no, brunch_nm, brunch_num, brunch_perf, close_dt
FROM BRUNCH_INFO
WHERE (brunch_num >= 8 OR brunch_perf = 'A') AND close_dt IS NOT NULL;

위 코드를 보면 괄호로 OR 논리연산자를 최우선으로 조건을 처리하고 그 다음으로 AND 연산자가 실행되도록 한 코드이다.

 

3. 논리연산자 IN

IN 연산자는 여러 조건들 중 적어도 하나만 만족해도 출력한다. 즉, 여러개의 OR연산자를 하나로 묶은 것이라고 생각하면 된다.

아래는 IN 논리연산자에 대한 예시코드이다.

# BRUNCH_INFO에서 brunch_no, brunch_nm, brunch_num, brunch_perf를 들고오는데,
# brunch_no가 8 또는 10인것과 brunch_perf가 A 또는 B인 것을 들고와라
SELECT brunch_no, brunch_nm, brunch_num, brunch_perf
FROM BRUNCH_INFO
WHERE brunch_no IN (8, 10)
	AND brunch_perf IN ('A', 'B');

위 코드를 보면 IN연산자 내에 있는 숫자나 문자는 OR연산자로 사용하는 것 처럼 8 또는 10, A 또는 B라는 뜻을 가지고 있다. 한마디로 여러개의 OR 논리연산자를 사용하는 것 보다 하나의 IN 연산자를 사용하는 것이 더 효율적으로 사용할 수 있을 것이다.

 

4. 논리연산자 NOT IN

NOT IN 연산자는 IN연산자와 반대로 뒤에 있는 조건을 부정할 때 사용한다.

아래는 NOT IN 논리연산자에 대한 예시코드이다.

# BRUNCH_INFO에서 brunch_no, brunch_nm, brunch_num, brunch_perf를 들고오는데,
# brunch_no가 8 또는 10이 아닌 것과 brunch_perf가 A 또는 B가 아닌 것을 들고와라
SELECT brunch_no, brunch_nm, brunch_num, brunch_perf
FROM BRUNCH_INFO
WHERE brunch_no NOT IN (8, 10)
	AND brunch_perf NOT IN ('A', 'B');

기본적으로 NOT IN 내부의 숫자나 문자는 IN 논리연산자와 같은 OR을 사용하지만, 8 또는 10이 아닌 것을 들고온다는 것이 IN 연산자와 다른 점이라고 볼 수 있다. 즉 걸러 내야할 값들을 걸러내고 출력할 때 NOT IN 연산자를 사용하면 효율적일 것이다.

 

※ IN 논리연산자와 NOT IN 논리연산자 지식

IN, NOT IN 연산자는 OR 연산자보다 처리 속도가 빠르다. 즉, 데이터가 방대할 때 좀 더 빠르게 실행할 수 있다.

IN, NOT IN 연산자 안에 다른 SELECT 문장을 사용할 수 있다. 이 내용은 뒤에 나올 하위 쿼리에서 언급한다.

 

 

● 텍스트를 활용한 데이터 조건 주기

1. LIKE 연산자를 활용한 필터링

※ 와일드 카드 문자( % : 숫자 0 또는 문자들을 대체하기 위해 사용,  _: 한 개의 단어를 대체하기 위해 사용됨)

LIKE 연산자는 조건 중 찾고 싶은 문자가 전부 기억이 나지 않을 때 LIKE 연산자와 와일드 카드 문자를 사용하여 필터링을 수행한다.

아래는 LIKE 연산자에 대한 예시 코드이다.

# 1. 뒤에 나오는 문자들을 알 수 없는 경우
SELECT brunch_no, brunch_nm, brunch_name FROM INS_INFO WHERE brunch_name LIKE 'REA%'

# 2. 앞에 나오는 문자들을 알 수 없는 경우
SELECT brunch_no, brunch_nm, brunch_name FROM INS_INFO WHERE brunch_name LIKE '%REA'

# 3. 앞뒤에 나오는 문자들을 알 수 없는 경우
SELECT brunch_no, brunch_nm, brunch_name FROM INS_INFO WHERE brunch_name LIKE '%REA%'

# 4. 뒤에 나오는 문자를 알 수 없는 경우(한 글자만 모를 경우)
SELECT brunch_no, brunch_nm, brunch_name FROM INS_INFO WHERE brunch_name LIKE 'BREA_'

# 5. 앞에 나오는 문자를 알 수 없는 경우(한 글자만 모를 경우)
SELECT brunch_no, brunch_nm, brunch_name FROM INS_INFO WHERE brunch_name LIKE '_READ%'

# 6. 시작과 끝 문자만 아는 경우
SELECT brunch_no, brunch_nm, brunch_name FROM INS_INFO WHERE brunch_name LIKE 'B%D'

# 7. 특정 단어를 원하지 않는 경우
SELECT brunch_no, brunch_nm, brunch_name FROM INS_INFO WHERE brunch_name NOT LIKE '%REA%'

위 예시 코드를 통해 LIKE 연산자를 어떻게 사용하는지에 대해서 간단하게 알아보았다.

 

※ 필드 결합하기

필드 결합은 여러 개로 나눠진 필드를 하나로 묶어서 표현하고자 할 경우 사용한다. 이떄 SELECT문에 결합연산자 '||' 또는 '+'를 사용한다. SQL에서 주로 '||' 결합연산자를 사용한다.

아래는 필드 결합에 대한 예시 코드이다.

# '+'를 활용한 필드 결합
# id, name, city와 city값과 country값을 합친 열을 addr로 이름을 지정해 불러온다.
SELECT id, name, city, country, city + '('country')' as addr
FROM CUSTOMER;

# '||'를 활용한 필드 결합 ( + 대신 ||로 바꿔주면 된다.)
SELECT id, name, city, country, city || '('country')' as addr
FROM CUSTOMER;

예시 코드처럼, 새로운 열을 만들기 위해 기존 열의 값을 결합시킬 수 있다는 것을 알 수 있다.

 

2. 공백 제거하기

저장 된 데이터가 깨끗하지 않아 공백이 존재하거나, 데이터 끼리 결합을 해 파생 변수를 만들 때에도 공백이 발생 할 수 있기 때문에 공백을 제거할 줄 알아야 한다.

공백 제거에는 오른쪽 공백을 제거하는 RTRIM, 왼쪽 공백을 제거하는 LTRIM, 양쪽 공백 제거하는 TRIM이 있다.

아래는 TRIM에 대한 예시 코드이다.

# 양쪽 공백 제거
SELECT id, name, city, country, city ||'(' || TRIM(country) || ')' AS addr
FROM CUSTOMER;

# 오른쪽 공백 제거
SELECT id, name, city, LTRIM(country)
FROM CUSTOMER;

# 왼쪽 공백 제거
SELECT id, name, city, RTRIM(country)
FROM CUSTOMER;

이처럼 데이터에 공백이 존재한다면 이러한 LTRIM, RTRIM, TRIM을 통하여 처리를 수행할 수 있다.

 

 

● 기본 함수

1. 문자 함수: 변수를 특정한 형태로 변화시키는 것이다.

대표적인 문자 함수

- LOWER: 모든 문자를 소문자로 변환  /  - UPPER: 모든 문자를 대문자로 변환

- LENGTH: 문자의 길이를 나타냄  /  - SUBSTR: 문자 값 중 원하는 길이만큼만 나타냄

- RTRIM: 문자열 오른쪽 공백 잘라냄  /  - LTRIM: 문자열 왼쪽 공백 잘라냄  /  - TRIM: 문자열 양쪽 공백 잘라냄

- REPLACE: 특정 문자열을 다른 문자열로 대체  /  - COALESCE: 조건에 따라 여러가지 값으로 치환

아래는 문자 함수에 대한 예시 코드이다.

# LOWER : 문자 소문자로 변환
SELECT id, name, LOWER(name) AS name_small
FROM VENDOR_INFO;

# UPPER: 문자 대문자로 변환
SELECT id, name, UPPER(name) AS name_big
FROM VENDOR_INFO;

# LENGTH: 문자의 자릿수를 세준다. - PYTHON의 COUNT함수랑 비슷하다.
SELECT id, name, LENGTH(name) AS name_cnt
FROM VENDOR_INFO;

# SUBSTR: 문자의 특정 부분만 출력 - SUBSTR(열 이름, 시작 위치, 자리수)
SELECT id, name, SUBSTR(name, 2, 3) AS name_str
FROM VENDOR_INFO;  # SUBSTR을 사용한 이름 중 2번쨰 문자부터 3글자를 뽑아냄

 

2. 숫자 함수

대표적인 숫자 함수

- ROUND: 소수점 자릿수를 지정하여 반올림  /  - TRUNC: 해당 소수점 자리에서 잘라냄

- MOD(M, N): M을 N으로 나눈 나머지값 반환  /  - ABS: 값을 절대값으로 변환

- SIGN: 숫자가 양수이면 1, 음수이면 -1, 0이면 0으로 변환  /  - SQRT: 제곱근을 나타냄

- COS: 지정한 값의 COS값을 나타냄  /  - SIN: 지정한 값의 SIN값을 나타냄

- PI: 지정한 값의 파이값을 나타냄  /  - TAN: 지정한 값의 TAN값을 나타냄

아래는 숫자 함수에 대한 예시 코드이다.

# ROUND: 지정한 소수점 자리 기준 반올림 / ROUND(열이름, 나타내고 싶은 소수점 자릿수)
SELECT prod_id, total_sales, ROUND(total_sales, 1) AS sale_rev
FROM PROD_SALES;  # 소수점 두번째 자리에서 반올림 -> 소수점 첫번째 자리까지 표현

# TRUNC(숫자, 소수자리수): 주어진 숫자를 정해진 소수 자릿수까지 자르거나 정수로 변환
SELECT prod_id, total_sales, TRUNC(total_sales, 1) AS sales_trunc
FROM PROD_SALES;  # total_sales를 소수점 첫번쨰 자리까지 자른 후 sales_trunc로 출력

# MOD(분자, 분모): 나머지를 구하는 함수
SELECT prod_id, total_sales, MOD(total_sales, sales_num) AS sales_balance
FROM PROD_SALES;  # total_sales를 sales_num으로 나누고 남은 나머지를 출력

# ABS(열이름): 열이름의 절대값 출력
SELECT prod_id, total_sales, econ_income, ABS(econ_income) AS abs_econ
FROM PROD_SALES;

숫자 함수 중 SIGN, COS, SIN, PI, TAN은 잘 사용되지 않는 숫자 함수이므로 예시 코드는 생략한다.

 

3. 날짜 함수

대표적인 날짜 함수

- ADD_MONTHS: 지정한 날짜에 개월 수를 더한 값을 출력  /  - SYSDATE: 현재 시스템 날짜 데이터 반환

- LAST_DAY: 해당 월의 마지막 날짜 반환  /  - MONTH_BETWEEN: 지정된 두 월 간 월 수 반환

아래는 날짜 함수에 대한 예시 코드이다.

# ADD_MONTHS(날짜 열, 더할 월 숫자)
SELECT id, birth_dt, ADD_MONTHS(birth_dt, 1) AS plus1_birth
FROM CLERK;

# SYSDATE: 현재 날짜와 시간 반환
SELECT id, birth_dt, SYSDATE AS current_date
FROM CLERK;

# MONTH_BETWEEN: 지정된 두 날짜의 월 간의 월 수를 반환
SELECT id, birth_dt, SYSDATE AS current_date, MONTH_BETWEEN(birth_dt, current_date) AS momt_diff
FROM CLERK;

# LAST_DAY: 해당 월의 마지막 날짜 변환
SELECT id, birth_dt, LAST_DAY(birth_dt) AS last_day_birth_month
FROM CLERK;

위 방식처럼 날짜 함수를 사용할 수 있다.

 

 

● 함수 활용하기

1. 숫자 데이터 요약하기

집계 함수 정리

- COUNT: 행의 수를 나타냄 -> NULL값 포함: COUNT(*), NULL값 제외: COUNT(열이름), 중복 제외: COUNT(DISTINCT 열이름)

- SUM: 행의 합계 나타냄  /  - AVG: 행의 평균 나타냄  /  - MAX: 행의 최대값 나타냄  /  - MIN: 행의 최소값 나ㅏ냄

- STDENV: 행의 표준편차 나타냄  /  - VARIANCE: 행의 분산 나타냄

아래는 집계 함수에 대한 예시 코드이다.

# COUNT: 행의 수 출력
SELECT COUNT(*) AS cnt FROM STUD_SCORE;  # NULL값도 포함시켜 행의 수
SELECT COUNT(math_score) AS math_cnt FROM STUD_SCORE; # NULL값 포함 X 행의 수
SELECT COUNT(DISTINCT math_score) AS math_distinct_cnt FROM STUD_SCORE; # 중복 포함, NULL x 행 수

# SUM: 행의 값 합계 출력
SELECT SUM(math_score) AS sum_math FROM STUD_SCORE;

# AVG: 행의 값 평균 출력
SELECT AVG(music_score) AS avg_music FROM STUD_SCORE;

# MAX: 행의 값 중 최대값 출력
SELECT MAX(music_score) AS maximum_music FROM STUD_SCORE;

# MIN: 행의 값 중 최소값 출력
SELECT MIN(music_score) AS minimum_music FROM STUD_SCORE;

# STDENV: 행의 값 표준편차 출력
SELECT STDENV(math_score) AS std_math FROM STUD_SCORE;

# VARIANCE: 행의 값 분산 출력
SELECT VARIANCE(music_score) AS var_music FROM STUD_SCORE;

 

※집계 함수 유용한 지식

- COUNT함수는 데이터의 검증용으로 많이 사용: 테이블에 NULL값이나 중복값 있는지 눈으로 찾기 어렵기 때문에 COUNT함수를 활용해 쉽게 검증

- 집계 함수 사용하면(COUNT함수 제외) NULL값은 계산에서 무시: NULL값을 포함해 계산하고 싶다면 앞에서 배운 COALESCE함수를 이용해 치환 후 사용하면 된다.

 

2. 조건문 이해

CASE WHEN은 조건에 따른 결과값을 출력할 수 있다.

아래는 CASE WHEN 구조와 예시이다.

# CASE WHEN 문장 구조
SELECT 열이름1,
	CASE WHEN [조건1] THEN [결과값1]
    WHEN [조건2] THEN [결과값2]
    ELSE [결과값3] END AS 새로운 열 이름
FROM 테이블명;

# CASE WHEN 문장 예시
SELECT id, job, current_sal, eng_score,
	CASE WHEN job = 'CLERK' AND eng_score >= 80
    	THEN current_sal * 1.07
    	WHEN job = 'CLERK' AND eng_score < 80
    	THEN current_sal * 1.06
    	WHEN job = 'OFFICER' AND eng_score >= 80
    	THEN current_sal * 1.05
    	WHEN job = 'OFFICER' AND eng_score < 80
    	THEN current_sal * 1.04
    	ELSE current_sal
    END AS next_sal
FROM STAFF_SAL;

위 CASE WHEN 문장 예시는 STAFF_SAL에서 job, current_sal, eng_score를 불러오고 만약 job이 CLERK이고 영어점수가 80점 이상이면 current_sal이 7% 상승, CLERK이고 영어점수가 80점 미만이면 current_sal이 6% 상승, job이 OFFICER이고 영어점수가 80점 이상이면 current_sal 5%증가, OFFICER이고 영어점수가 80점 미만이면 4% 증가, 나머지는 current_sal 그대로 출력을 시키고 이러한 값이 저장된 열 이름을 next_sal로 지정해서 출력한다는 것이다. 이처럼 많은 조건에 따라 변경되는 값을 출력하고 싶을 때 CASE WHEN을 사용하면 좋을 것이다.

 

글이 너무 길어졌기 때문에 여기까지만 정리하고 데이터의 그룹화, 필터링은 다음 글에서 이어서 작성하도록 한다.