Chap 5. SQL 연산 및 CASE문

Date: 2022.10.14 Updated: 2022.10.14

카테고리: SQL

수치 연산

SQL을 통해 간단한 수식 혹은 함수를 이용할 수 있다.

사칙 연산

다른 프로그래밍 언어처럼 사칙연산을 수행하면 된다. 연산자를 이용하면 된다. 

SELECT 구로 연산

SELECT 구는 열을 지정하는 구문이다. 하지만 단순히 테이블에 있는 열만 지정해서 출력하지 않는다. 테이블에 있는 열을 이용하여 계산한 식을 표시하거나 함수를 통한 결과를 표시해준다. 즉, SELECT을 통해 출력 표를 디자인한다 생각하면된다.

SELECT 열 1, 열 2, 열 1 + 열 2, 함수(열1, 열2, ...) FROM 테이블명;

위와 같이 코드를 입력하면 출력 테이블은 SELECT 문에 지정된 열과 계산 열 순서로 표시된다. 덤으로 각 행은 그에 맞는 계산을 수행하여 출력된다.

열 별명

SELECT 문으로 열을 계산하여 출력하면 테이블에 수식이 그대로 노출된다. 지저분하여 생산성을 저해하니 AS 구문으로 수식 열에 맞는 열 이름을 생성해준다.

SELECT *, 열 1 + 열 2 AS 별명 1 FROM 테이블명;

이처럼 해당 열 뒤에 AS 구문을 사용하면 된다. 이때 ASCII 문자 (영어, 숫자 등 1개 1 bite)가 아닌 것들은 더블쿼트(““)로 둘러싸서 지정한다. 만일 ASCII여도 예약어라면 더블쿼트로 둘러싸야한다. (하지만 예약어는 다른 객체 이름으로 간주하지 못하는게 일반적이니 지양한다.)

더블쿼트로 둘러싸면 명령구문 상 DB 객체 이름이라 간주. 싱글쿼트로 둘러싸는건 문자열 상수.

별명은 ‘alias’라 불리며 숫자도 들어갈 수 있지만, 숫자가 별명의 시작일 수 없으면 숫자만으로 구성되면 안된다.

WHERE 구에서 연산

SELECT *, 열 1 + 열 2 AS 별명 1 FROM 테이블명
    WHERE 열 1 + 열 2 >= 30;

이처럼 자연스럽게 조건식 내에 필요한 열의 수식을 조합하여 필터링 할 수 있다.

⭐ 이때 WHERE 구문에서 alias를 사용해도 되는가? 아니다 ‘ 별명 1 >= ‘이런식으로 조건식을 쓰면 안된다. 이유는 연산처리에 있어 WHERE 문이 SELECT 보다 먼저기 때문에 WHERE 문을 처리할 때는 별명 1이라는 것이 없기 때문이다.

NULL 값 연산

NULL 값 연산은 간단하다.

NULL값은 어떤 걸 계산해도 NULL 이다.

다른 언어에서는 NULL이 0으로 처리하는 경우가 있어 연산이 가능하지만 SQL은 처리하지 못한다. NULL을 0으로 만들고 싶다면 CASE문 혹은 치환을 통해서 바꿔야 한다.

ORDER BY 구에서 연산

ORDER BY 구문에서도 연산은 가능하다. 필요한 열을 통해 계산 수식을 만들고 이를 정렬 기준으로 삼으면 된다.

SELECT *, 열 1 + 열 2 AS 별명 1 FROM 테이블명
    WHERE 열 1 + 열 2 >= 30
    ORDER BY 열 1 + 열 2 DESC;

신기한건! ORDER BY 구에서는 SELECT 문에서 사용한 alias가 사용가능하다는 것이다.

WHERE구 $\to$ SELECT 구 $\to$ ORDER BY 구

함수

함수명 (인수1, 인수2, 인수3 ,,,)

굳이 많은것을 설명하지 않겠다. 필요한 함수는 다른 프로그래밍 언어처럼 SELECT문, WHERE문, ORDER BY문에 사용하면 된다.

RDBMS 마다 사용하는 함수는 다르니 꼭 검색을 통해 잘 알아보자 ^^

수식에 필요한 집계 함수 등은 다음에 더 자세히 다루겠다.

문자열 연산

문자열 연산은 수치형 연산보단 연산자가 적다. 함수를 이용하여 결합하거나 슬라이싱, 혹은 길이를 구하는 연산이 주를 이룬다.

문자열 결합 (CONCAT)

RDBMS에 따라 다르지만 MySQL에선 CONCAT 함수로 문자열을 결합한다.

2개 열 데이터를 모아 1개의 열로 처리할 때 사용한다. 이때 수치형과 문자열형을 합칠 수도 있으며 합한 결과는 문자열이 된다.

SELECT CONCAT(수치형 열, 문자열형 열) FROM 테이블명;

결과는 ‘수치형 열문자열 형’이다. 띄어쓰기 없이 붙어서 출력된다.

슬라이싱 (SUBSTRING)

문자열의 일부분을 계산하여 반환해주는 것으로 슬라이싱이라 생각하면 된다.

특정 열에서 몇 번째 열에서 몇 번째 열까지 추출할 때 사용하면 된다.

SUBSTRING('20221014',1,4) -> '2022'

이런 식으로 간편하게 내가 원하는 부분만 출력할 수 있다.

여백 제거 (TRIM)

문자열 데이터 자료형 중 CHAR 형은 문자의 길이가 정해져있다. 최대 길이보다 짧은 길이의 문자의 경우 나머지를 여백으로 채운다. 이렇게 빈 공간이 있는 문자열의 앞, 뒤 여백을 제거해주는 함수가 TRIM이다.

TRIM('ABC    ') -> 'ABC'

문자열 중간의 여백은 제거가 안되는 것을 유념하자.

문자의 길이 출력

CHARACTER_LENGTH 함수

VARCHAR 형 문자열은 가변 길이기 때문에 CHARACTER_LENGTH 함수를 이용하여 문자열의 길이를 계산할 수 있다.

OCTET_LENGTH 함수

위 함수는 문자열의 길이를 바이트 단위로 계산해 돌려주는 함수이다. 어느때 사용하는진 아직 모르지만 컴퓨터 자료구조 상 문자의 길이가 곧 bite는 아니기 때문에 알아둬야 할 것 같다.

⭐ 문자의 길이가 곧 bite를 의미하진 않는다.

위에서도 언급했듯 컴퓨터, 모니터 상 문자, 영상, 소리로 표현이 되어있지만 전산상 모든 것은 숫자로 이루어진 데이터이다. 때문에 연산 처리를 위해선 모든 문자는 숫자화 하는 인코드(수치화)하고 이 방식에 따라 bite가 달라진다.

ASCII 문자 / 영어, 숫자 등 반각 문자, 즉 저장 공간이 1 길이당 1 bite인 문자이다.

한글의 경우 ‘EUC-KR’, ‘UTF-8’등 어디선가 들어본 인코드 방식으로 수치화하며 이에 문자길이가 길이와 일치하지 않고 더 크다.

때문에 OCTET_LENGTH 함수를 통해 길이를 출력하면 종종 길이와 비례하지 않는 수치가 나오기도 한다.

날짜 연산

날짜 * 시간 데이터는 RDBMS에 따라 세분화 (날짜형, 시간형, 날짜시간형)혹은 단순하게 날짜시간형으로 구성되어 있다.

시스템상 날짜 연산은 매우 많이 일어난다.

날짜 연산은 연산을 통해 동일한 날짜시가간형 자료형이 나오거나, 두 날짜의 간격 즉 기간을 나타내는 기간형 데이터를 반환한다.

오늘 날짜 구하기.

CURRENT_TIMESTAMP 함수를 실행하면 컴퓨터 상 날짜를 표시한다. 인수를 지정할 필요가 없는 함수이다.

MySQL에선 YYYY/MM/DD가 기본 서식이다. 즉 오늘로 치면 2022/10/14로 표시가 된다.

날짜의 덧셈과 뺄셈

날짜 출력 방법

CURRENT_TIMESTAMP + 1 DAY; # 하루 후 

CURRENT_TIMESTAMP - 1 DAY; # 하루 전 

위와 같이 계산하면 된다.

기간형 날짜 계산

INTERVAL 1 DAY; # 1 기간을 표시

DATEDIFF(앞 날짜, 뒷 날짜); # 두 날짜 사이의 차이를 계산

CASE문

함수를 통해 데이터를 특정 형태로 변환하는 경우도 있지만 간단한 것, 그리고 임의의 조건으로 특별히 변환 처리를 해야 할 것들은 CASE문을 이용하여야 한다.

RDBMS에서는 사용자가 함수를 직접 작성할 수 있지만, 간단한건 CASE문을 처리하는게 더 용이하다.

CASE문은 검색 CASE문, 단순 CASE문 2가지로 나뉜다.

검색 CASE문

CASE WHEN 조건식1 THEN 식1
WHEN 조건식2 THEN 식2 
***
ELSE 식3
END

WHEN 절에는 참, 거짓을 반환하는 조건식을 기술한다. 해당 조건에 참일 시 해당 령의 데이터는 식 1로 전환된다. 만일 거짓일 시 다음 WHEN 조건문으로 계속 넘어간다. 이후 끝까지 조건에 맞지 않다면 ELSE 문의 식으로 전환된다.

ELSE는 생략이 가능하며 생략했을 때 ‘ELSE NULL’로 간주된다.

NULL을 0으로 전환하기.

NULL값은 어떤 연산을 해도 NULL이기 때문에 CASE문을 통해 원하는 수로 전환을 한다. 물론 COALESCE 함수를 이용할 수도 있다.

COALESCE(열명, 0) -> NULL값이 0 으로 전환됨. 

단순 CASE문

CASE 식1
  WHEN 식2 THEN 식3
  WHEN 식4 THEN 식5
  ***
  ELSE 식6
END

CASE 뒤에 사용한 ‘식1’과 WHEN 뒤의 ‘식2’ 값을 비교하여 참일 시 THEN 뒤의 식 3을 변환하고 거짓일 시 그 다음 WHEN 구문과 비교하여 전환한다. 모두가 거짓일 시 ELSE문 식6을 사용한다. 물론 ELSE 생략가능하다.

CASE문 주의사항

CASE문은 SELECT 구, WHERE 구, ORDER BY구 모두 사용이 가능하다.

또한 2가지 CASE문은 상황에 맞게 사용하면 된다. 만일 조건식이 복잡하거나 각 열이 달라질 경우 검색 CASE문을 식이 단순하고 가독성이 중요하다며 단순 CASE문을 사용하는게 좋을 것 같다.

⭐ WHEN에 NULL 지정하기.

밑의 단순 CASE에서는 NULL을 조건식에 입력하기 어렵다.

CASE a
  WHEN 1 THEN 'man'
  WHEN 2 THEN 'women'
  WHEN NULL THEN 'no date'
END

위 코드는 처리가 되지 않는다. WHEN NULL이란건 ‘a = NULL’과 같은 것인데 NULL은 비교가 불가하기 때문에 a가 NULL이라도 참으로 처리 되지 않는다. 이를 행하기 위해서는 ‘IS NULL‘을 사용해야한다. 단순 CASE문은 ‘=’연산자로 비교하기 때문에 이렇게 할 수 없다. 때문에 검색 CASE문을 사용해야한다.

또한, ELSE를 생략하면 ELSE NULL이 된다는 것을 유념하자. 조건식을 촘촘히 짠다고 한들 상정한 것 외에 데이터가 나올 수도 있고 이때마다 NULL값이 반환된다. 데이터에 NULL이 많아지면 분석에 영향이 많이 갈 수 있으니 유념하자.

전문가들은 CASE문에 ELSE는 잘 생략하지 않는다고 한다.

꿈 양봉자🍯