SQL 데이터베이스에 대해 제대로 알자(4)

SQL 데이터베이스에 대해 제대로 알자(4) - 수치연산, CASE

BackEnd

지난포스팅 목록

1편

2편
3편에 이어서 4편에서는 수치 연산부터 계속해서 SQL 데이터베이스에 대해 정리하고 배워보자!

<수치연산>

사칙연산

SQL은 데이터베이스를 조작하는 언어이지만 컴퓨터를 조작하는 언어이기도 한 만큼 기본적으로 계산기능을 포함하고 있다.

여기에서는 계산하는 방법, 특히 수치의 연산 방법에 대해 배워볼 예정이다.

어떤 계산을 할지는 연산자를 이용해 지정한다. WHERE 구에서 조건을 지정할 때 사용했던 = 역시 연산자의 하나이다.

산술연산은 다른 프로그래밍 언어에서도 사용하는 기본 개념이므로 잘 익혀둬야 한다.

산술 연산이라고 해도 그리 어렵지는 않다. 덧셈, 뺄셈, 곱셈, 나눗셈의 사칙 연산과 나눗셈의 나머지, 크게 이 두가지를 기본적으로 익혀두면 된다.

연산자는 5가지로 정리할 수 있다.

1. 덧셈 : +

2. 뺄셈 : -

3. 곱셈 : *

4. 나눗셈 : /

5. 나머지 : %

여기까지 이미 보고있는 사람이라면 1번부터 5번까지의 의미를 완벽하게는 아니더라도 어렴풋 알고있을 것이라고 판단된다.

수학도 아닌 산수에 관한 내용으로 대부분 금방 금방 이해가 될 부분이지만 그래도 정리는 해두도록 하자!

계산을 할 때에는 우선순위가 있다. 산술 연산자의 경우는 다음과 같다.

우선순위 1번은 */%

우선순위 2번은 +-

곱셈, 나눗셈, 나머지 그룹과 덧셈, 뺄셈 그룹으로 나뉜다.

같은 그룹 내 연산자의 우선 순위는 동일하다. 계산 순서는 연산자에 따라 관계없는 경우도 있지만

기본적으로는 왼쪽에서 오른쪽으로 진행된다. 우선 순위가 같은 연산자들끼리 연산하는 경우는 문제가 되지 않지만

우선순위가 다른 연산자들이 섞여있는 경우는 우선순위가 높은 쪽이 먼저 계산된다.

우선순위가 같은 경우 -> 우선순위가 같다면 왼쪽에서 오른쪽으로 계산 : 1 - 2 + 3

우선순위가 다른 경우 -> 1 + 2 * 3 (+보다 *의 우선순위가 높다. 2* 3 먼저 계산 -> 그 후 결괏값이 최종 값 6 + 1 = 7)

예제를 통해 설명해보자

SELECT 구로 연산하기

SELECT 구에는 열명을 지정한다 라고 지난 편에서 다뤘었다. 이 외에도 여러 가지 식을 기술할 수 있다.

이 식은 열명, 연산자, 상수로 구성된다.

SELECT 식1, 식2 ... FROM 테이블명

식을 기술할 수 있다는 것은 명령이 실행될 때 연산을 할 수 있다는 것을 의미한다. 구체적인 예를 들어 설명하기 위해 테이블을 살펴보자

SELECT * FROM testsql;

예를 들어 가격이 100원인 상품을 10개 주문하면 주문금액은 1000원이 된다.

이는 '단가*개수' 라는 연산식을 사용할 수 있다.

현재 테이블에서는 price가 가격을, quatity가 수량을 의미하고 있다.

따라서 금액을 계산하는 식은 price*quantity가 되며 SELECT 구로 지정해서 계산할 수 있다.

이때 *의 앞뒤로 스페이스를 넣을 필요는 없다.

그냥 붙여서 작성해주면된다.

열의 별명

SELECT *, price * quantity From testsql;

만약 이렇게 연산을 처리하게 되면 곱셈의 결과물이 나오게 되는데

결괏값이 너무 길어서 보기가 불편하다면 별명을 지어서 볼 수 있다.

SELECT *, price * quantity AS amount From testsql;

이렇게 amount라는 별명을 부여해서 다시 출력해보자

그럼 이렇게 훨씬 보기에 편하게 나타낼 수도 있다.

별명은 AS라는 예약어를 사용해서 지정한다. SELECT 구에서는 콤마, 로 구분해서 복수의 식을 지정할 수 있으며

각각의 식에 별명을 붙일 수 있다.

MySQL에서는 별명을 중복해서 지정해도 에러는 발생하지 않지만 프로그래밍 언어에서 결괏값의 처리 방식에 따라

문제가 발생할 수 있으므로, 기본적으로는 중복되지 않도록 지정하는 것이 좋다.

키워드 AS같은 경우에는 생략도 가능하다.

SELECT *, price * quantity amount From testsql;

이렇게 작성해도 무방하다. 동일한 결과가 출력된다.

엘리어스(alias) 라고도 불리는 별명은 영어, 숫자, 한글 등으로 지정 할 수 있다.

단 별명을 한글로 지정하는 경우에는 여러가지로 오작동하는 경우가 많으므로 더블쿼트(MySQL에서는 백쿼트`)로 둘러싸서 지정한다.

이룰은 데이터베이스 객체의 이름에 ASCII 문자 이외의 것을 사용할 경우에 해당한다.

SELECT price * quantity "금액" FROM testsql;

이런식으로 작성해주게 되면

이렇게 금액 이라는 별명을 통해서 곱셈의 결과물을 뽑아 낼 수 있다.

SELECT price * quantity `금액` FROM testsql;

이렇게 작성해줘도 결과는 똑같이 나온다는 것도 알아두자!

데이터베이스 객체명 
"testsql"
문자열 상수
'ABC'

이렇게 문자열 상수는 싱글쿼트 데이터베이스 객체명은 더블쿼트로 감싸주면 된다.

이전 편에서 설명하면서 예약어 같은 경우는 이름으로 사용할 수 없다고 했다.

SELECT price * quantity SELECT From testsql;

이렇게 작성하면 SELECT는 사용이 불가능하지만

SELECT price * quantity "SELECT" From testsql;

이렇게 더블쿼트로 감싸서 사용하면 별명으로 지어서 사용이 가능해진다.

또한 알아둬야 할 점은 숫자로 시작하는 이름은 사용이 불가능하다는 것이다.

수치형 상수를 명령 안에서 사용할 경우 쿼트로 묶지 않고 숫자만 입력한다.

이때 이름이 숫자로 시작한다는 것은 그것이 수치형 상수를 의미하는 것인지 데이터베이스 객체명을 의미하는 것인지 구별할 수 없다.

물론 이름이 예약어와 겹칠 때와 마찬가지로 더블쿼트로 묶으면 피할 수 있다.

MySQL에서는 숫자로 시작하는 객체명이 허용된다.

다만 숫자만으로 구성되는 객체명은 역시나 허용되지 않는다. 문자도 같이 뒤에 붙여줘야한다.

SELECT price * quantity 1a From testsql;

이렇게 숫자로 시작했지만 뒤에 문자를 붙여줘서 사용이 가능해진 것이다.

한편 Oracle에서는 숫자로 시작하는 이름은 허용되지 않는다. 더블쿼트로 둘러싸면 객체명으로 간주하는 룰은

표준 SQL에서 규정하고 있다는 것도 알아두자!

WHERE 구에서 연산하기

이번에는 SELECT 구에 이어서 WHERE 구에서의 연산방법을 알아보자

가격*수량으로 금액을 계산하여 2000원에 이상에 해당하는 행을 검색한다고 해보자

검색에 앞서 계산해서 값을 뽑아내는 방식에 2가지 차이점이 있는데

SELECT *, price * quantity AS amount From testsql;

이렇게 *, 를 붙여서 뽑아낼 경우에는

이렇게 내가 조건에 사용한 열들이 모두 출력되서 결괏값이랑 한번에 나오지만

SELECT price * quantity AS amount From testsql;

이렇게 *, 을 제외하고 가격과 수량만 넣고 돌리게되면

별칭으로 지정한 amount값만 출력해서 보여주게 된다.

2가지의 차이점에 대해서도 숙지하고 있는 것이 좋다.

이어서 조금 전의 요구사항인 2000원 이상의 값을 뽑아내볼텐데

SELECT *, price * quantity AS amount From testsql
WHERE price * quantity >= 2000;

이렇게 기존에 검색한 쿼리문뒤에 WHERE 구를 붙여서 가격과 수량을 곱한 결괏값이 2000원 보다 크거나 같은 값을 보여줘! 라고 했더니

현재 이렇게 모든 행중에 내가 걸었던 조건에 해당하는 값이 되는

이 값만 뽑아서 보여주게 되는 것이 확인되었다.

현재 WHERE 구의 조건식은 price * quantity이다. 그 조건에 결과에 해당하는 행만 뽑아낸 것이다.

그런데 궁금한 점이 위에서는 AS amount로 별명을 지어줬으니 아래 WHERE 구에서도 별명을 적으면 되는데

왜 굳이 price * quantity라고 적었을까?

SELECT *, price * quantity AS amount From testsql
WHERE amount >= 2000;

그 이유는 이렇게 작성하면 에러가 발생하기 때문인데 왜 저렇게 하면 에러가 발생하는지 알아보자

WHERE 구와 SELECT 구의 내부처리 순서

WHERE 구에서 행 선택, SELECT 구에서 열 선택은 데이터베이스 서버 내부에서 WHERE 구 -> SELECT 구 순서로 처리된다.

서버내부의 처리 순서까지 고려해야 할 필요가 있을까? 라고 의문을 가질 수 있지만 SELECT 명령을 이해하기 위해서는 중요한 부분이므로

서버의 처리순서와 관련이 있다는 것만이라도 알아두도록 하자

표준 SQL에서는 내부처리 순서가 따로 정해져 있지 않다. 하지만 WHERE구 -> SELECT 구의 순서로 내부 처리를 하는 데이터베이스가 많다. 따라서 WHERE 구로 행이 조건에 일치하는지 아닌지를 먼저 확인한 후에 SELECT 구에 지정된 열을 선택해서 결과를 반환하는 식으로 처리가된다.

별명은 SELECT 구문을 내부 처리할 때 비로소 붙여지게 된다.

즉 WHERE 구의 처리는 SELECT 구보다 선행되므로 WHERE 구에서 사용한 별칭은 아직 내부적으로 지정되지 않은 상태가 되어 에러가 발생하는 것이다.

쉽게 생각해서 변수를 지정하지않고 변수를 사용하면 안되는 것 처럼 지정되지 않은 별칭을 사용하는 것과 같은 원리라고 보면 된다.

NULL 값의 연산

NULL 값을 이용해 'NULL +1' 과 같은 연산을 하면 결과가 어떻게 될까?

C나 PHP 언어로 프로그래밍을 했던 경험이 있다면 아마 '1' 이라고 대답할 수도 있다.

프로그래밍 경험이 없는 상태에서는 NULL은 유효한 값이 없는 상태이므로 0으로 간주해 계산 결과가 '1'이 될 것이라고 생각할 수도 있겠다.

C나 PHP 언어에서는 NULL이 0으로도 처리되지만 SQL에서는 NULL 값이 0으로 처리되지 않는다. 즉 'NULL +1'의 결괏값이 1이 아닌 NULL이다. 다음 연산 결과 또한 모두 NULL이 된다.

NULL+1
1+NULL
1+2*NULL
1/NULL

여기서 나눗셈을 보면 NULL이 0으로 처리되지 않는다는 것을 알 수 있다.

통상적인 연산에서는 0으로 1을 나누면 'division by zero' 에러가 발생할 것이다. 하지만 1/NULL을 계산해도 NULL이 0으로 처리되지 않아 에러가 발생하지 않고 결과는 NULL이 된다.

ORDER BY 구에서 연산하기

ORDER BY 구에서 연산할 수 있고 그 결괏값을 정렬할 수 있다.

'가격*수량'으로 금액을 계산해서 값이 큰 순서대로 정렬해보자

SELECT *, price * quantity AS amount FROM testsql;

현재 출력된 테이블 데이터에서 ORDER BY 구에서 금액을 계산하고 내림차순으로 정렬해보자

SELECT *, price * quantity AS amount FROM testsql
ORDER BY price * quantity DESC;

이렇게 값을 내림차순으로 정렬했다.

ORDER BY 구는 서버에서 내부적으로 가장 늦게 처리가 된다. 즉 SELECT 구보다 나중에 처리되기 때문에

SELECT 구에서 지정한 별명을 ORDER BY 구에서 사용이 가능해진다.

SELECT *, price * quantity AS amount FROM testsql
ORDER BY amount DESC;

이렇게 SELECT 구에서 지정한 별명을 ORDER BY 구에서 잘 사용하고 있고 결괏값도 잘 나오고 있다.

다시한번 말하지만 WHERE구에서는 별명을 사용할 수 없다. 내부순서 처리때문이다.

아래 순서를 꼭 기억하도록 하자!

WSO!

WHERE구 -> SELECT구(여기서 별명을 지정) -> ORDER BY 구

함수

연산자 외에 함수를 사용해 연산할 수도 있다. 함수는 다음과 같은 문법으로 표기한다.

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

이전에 설명했던 연산자는 기호에 따라 연산 방법이 결정된다.

예를 들어 + 는 덧셈에 사용되는 것이다.

한편 함수는 함수명에 따라 연산 방법이 결정된다. 연산자는 좌우의 항목이 연산 대상이 된다.

함수는 계산 대상을 인수로 지정하낟. 이때 인수는 함수명 뒤에 괄호로 묶어서 표기한다.

인수의 수나 구분 방법은 함수에 따라 다르다. 대부분의 함수는 1개 이상의 인수를 가진다. 인수는 파라미터 라고도 부른다.

또한 연산자가 그러하듯 함수 역시 결괏값을 반환한다.

이것을 '함수의 반환값' 이라고 부른다.

사실 따지고보면 연산자도 함수도 둘다 같은 것이라고할 수 있다. 다만 표기 방법이 다를 뿐이다.

여기서는 간단하게 나머지를 계산하는 % 연산자와 동일하게 나머지를 계산하는 MOD 함수를 서로 비교해 설명해보겠다.

%연산자는 MySQL 등에서 사용할 수 있는 연산자이다. 10 % 3을 계산하면 10 나누기 3 을 한 나머지는 1이 되므로

결과는 1이된다. MOD함수는 MySQL이나 Oracle 등에서 사용할 수있는 함수이다.

함수명이 MOD이고, 인수로는 나뉘어질 수와 나눌 수의 순서로 2개를 지정해야한다.

MOD함수 역시 나머지값을 반환한다.

다음 예를 보면 아주 비슷하다는 것을 알 수 있다.

10 % 3; -> 1
MOD(10,3) -> 1

함수도 연산자도 표기 방법이 다를 뿐, 같은 것이다!

ROUND 함수

우리는 일상생활에서 부가세를 계산하는 경우가 있다.

이 경우 부가세를 포함한 금액을 계산하려면 부가세가 5%일 경우 1.05로, 8%일 경우 1.08로 곱셉을 하게된다.

경우에 따라서는 부가세를 포함한 금액이 소수점을 가질 수도 있다. 이런 경우 거래되는 단위를 고려해 반올림을 하는데

이때 사용하는 것이 ROUND 함수다.

SELECT ROUND(amount) FROM testsql;

이렇게 조회할 컬럼을 ROUND함수를 붙여서 조회해주면

이렇게 소수점을 짜르고 정수타입으로 보여주게 된다.

ROUND 함수는 기본적으로 소수점 첫째 자리를 기준으로 반올림한 값을 반환한다.

이때 ROUND 함수의 두 번째 인수로 반올림한 자릿수를 지정할 수 있다.

SELECT amount, ROUND(amount, 1) FROM testsql;

이렇게 작성하면 소수점 둘째 자리에서 반올림을 하게 된다.

만약 ,1 이라는 것을 생략하면 인수를 생략한다는 뜻으로 0으로 간주되어서 소수점 첫째 자리에 반올림하는 것이다.

1을 지정하면 소수점 둘째 자리를 반올림한다는 뜻이다.

또한 음수로 저정해 정수부의 반올림할 자릿수를 지정할 수도 있다.

5961.60 이라는 값을 기준으로

-1을 지정하면 1단위 = 1

-2를 지정하면 10단위 = 61에서 6에 해당

-3을 지정하면 100단위 = 961에서 9에 해당

그 밖에도 반올림 외에 버림을 하는 경우도 있는데 이는 TRUNCATE 함수로 계산할 수 있다.

예를 들어 값이

5962

2138

1080

이렇게 있다고 했을 때

SELECT amount, ROUND(amount, -2) FROM testsql;

이렇게 작성해주면 위에서 설명했듯 -2는 10의 자리에 해당하는 값을 기준으로 반올림을 하는 것이다.

그래서 결괏값을 보면

이렇게1번부터 설명하면 60을 올렸으니 앞에 9가 올라가서 6000이 되고

2번에서는 38을올렸으나 3이 반올림이 안되니까 2100이 되고

3번에서는 80을 반올림했으니까 8을 올려서 앞에 0이 1이 되어서 1100이 되는 것이다.

문자열 결합

데이터에는 수치형 외에 문자열형도 있다. 이번에는 문자열 연산에서 문자열 결합에 대해 배워보자

문자열 결합 사례는 아래와 같다.

'ABC' || '1234' -> 'ABC1234'

문자열을 결합하는 연산자에는 데이터베이스 제품마다 방언이 있으며 다음과 같은 차이를 가진다.

SQL Server :

+ 문자열 결합

Oracle, DB2, PostgreSQL :

|| 문자열 결합

MySQL :

CONCAT 문자열 결합

문자열 결합은 '2개의 열 데이터를 모아서 1개의 열로 처리하고 싶은' 경우에 자주 사용한다.

테이블을 이용해서 수량과 수량 단위의 문자열을 결합해보자

SELECT * FROM testsql;

이렇게 기본 테이블을 세팅해두고 조회해봤다.

여기서 quantity(수량)와 unit(단위)를 연결해서 결과를 얻어보자

INTEGER타입의 수령과 VARCHAR 타입의 unit을 연결해볼 생각이다.

SELECT CONCAT(quantity, unit) FROM testsql;

이렇게 두개의 열을 묶어서 하나의 문자열로 결합했다.

이렇게해서 해당 데이터는 문자열형이 된 것이다.

SUBSTRING 함수

SUBSTRING 함수는 문자열의 일부분을 계산해서 반환해주는 함수이다.

데이터베이스에 따라서는 함수명이 SUBSTR인 경우도 있다.

블로그 시스템 등에서는 사용자가 업로드한 데이터를 특정 지을 수 있도록 날짜와 연속된 번호를 사용해

유일한 ID를 지정하는 경우가 많다. 이러한 ID를 하나의 열로 저장하는 경우도 있지만 보통 다루기 쉽도록 날짜와 연속된 번호라는

두개의 열로 나누는 경우가 많다.

또한 연월일을 YYYYMMDD와 같은 형식의 문자열 데이터로 저장하고 싶은 경우도 아주 많다.

해당 날짜 데이터에는 년, 월, 일을 각각 따로 추출해내고 싶은 경우가 있을 것이다.

이런 경우 SUBSTRING 함수를 사용해 간편하게 문자열을 추출해낼 수 있다.

앞 4자리(년) 추출

SUBSTRING('20140125001', 1,4) -> '2014'

첫번째 인자 1은 추출할 문자 첫번째부터 라는 뜻이고 두번째 인자 4는 4번째 순서까지 뽑아줘! 라는 뜻이다.

그래서 2014에서 2가 첫번째 문자니까 2부터뽑아서 4번째 순서인 4까지 뽑아내서 2014가 나오는 것이다.

5째 자리부터 2자리(월) 추출

SUBSTRING('20140125001', 5,2) -> '01'

TRIM 함수

TRIM 함수는 문자열의 앞뒤로 여분의 스페이스가 있을 경우 이를 제거해주는 함수로 문자열 도중에 존재하는 스페이스는 제거되지 않는다.

고정길이 문자열형에 대해 많이 사용하는 함수이다.

앞선 편에서 설명했듯이 CHAR형의 문자열형에서는 문자열의 길이가 고정되며 남은 공간은 스페이스로 채워진다고 했다.

이처럼 빈 공간을 채우기 위해 사용한 스페이스를 제거하는 데 TRIM 함수를 사용할 수 있다. 한편, 인수를 지정하는 것으로 스페이스 이외의 문자를 제거할 수도 있다.

TRIM으로 스페이스 제거하기

TRIM('ABC    ') -> 'ABC'

이렇게 빈공간을 없애주는거다.

CHARACTER_LENGTH 함수

CHARACTER_LENGTH 함수는 문자열의 길이를 계산해 돌려주는 함수이다.

VARCHAR 형의 문자열은 가변 길이이므로 길이가 서로 다르다. CHARACTER_LENGTH 함수를 사용하면

문자열의 길이를 계산할 수 있다.

문자열의 길이는 문자 단위로 계산되어 수치로 반환된다. 또한 함수명은 CHAR_LENGTH로 줄여서 사용할 수 있다.

한편 OCTET_LENGTH 함수는 문자열의 길이를 바이트 단위로 계산해서 돌려주는 함수다.

이것으로 CHAR_LENGTH/ OCTET_LENGTH 함수의 설명을 끝내고 싶지만 조금 까다로울 수 있는 내용을 추가해보려고 한다.

컴퓨터 안에는 이미지 데이터, 음성 데이터, 수치 데이터, 문자열 데이터 등 다양한 종류의 데이터가 저장되어 있다.

하지만 이런 데이터는 '전부 수치'라고 자주 언급되므로 따로 자세한 설명은 하지 않겠다. 다시 말해 문자열 데이터도 결국 수치로 저장된다는 것이다.

데이터 단위로 '바이트'라는 것이있다. SQL의 OCTET_LENGTH 함수를 잘 사용하는 동시에 중요한 것은 문자 하나의 데이터가 몇 바이트의 저장공간을 필요로 하는지 '인코드 방식'에 따라 결정된다는 점이다.

바꿔말하자면 문자를 수치화(인코드) 하는 방식에 따라 필요한 저장공간의 크기가 달라진다는 것이다.

VARCHAR 형의 최대 길이 역시 바이트 단위로 지정한다. 하지만 문자세트(character set)에 따라 길이가 문자 수로 간주되기도 하니

주의할 필요가 있다.

문자세트(character set)

한중일의 인쇄 문화에서는 반각과 전각이라는 용어를 사용해 문자의 폭을 설명하기도 한다.

예를 들어 알파벳의 경우는 반각문자, 한글을 전각문자라고 할 수 있다.

반각과 전각의 차이는 화면상의 표시를 보면 잘 알 수 있다.

반각 문자는 전각문자의 폭의 절반밖에 안 되며 저장용량 또한 전각문자 쪽이 더 크다.

반각의 알파벳이나 숫자, 기호는 'ASCII 문자'라고 부른다.

한글의 경우 'EUC-KR', 'UTF-8'등의 인코드 방식을 주로 사용한다. 인코드 방식은 데이터베이스나 테이블을 정의할 때 변경할 수 있다.

이를 RDBMS에서는 '문자세트' 라고 부른다. 서론이 조금 길었지만 핵심은 '한 문자가 몇 바이트인지는 쓰이는 문자세트에 따라 다르다' 라는 것이다.

CHAR_LENGTH 함수를 사용하는 경우에는 아무런 문제가 되지 않는다. 한글이든 ASCII 문자든 문자 수로 계산되기 때문이다.

하지만 OCTET_LENGTH 함수의 경우는 문자 수가 아닌 바이트 단위로 길이를 계산하므로 주의할 필요가 있다.

'A는 반각, 한은 전각' 이라는 문자열의 문자 수와 바이트 수를 문자세트 별로 조사하면 다음과 같다.

문자세트 문자 수 바이트 수

EUC-KR 12 19

UTF-8 12 26

EUC-KR에서 ASCII 문자는 1바이트, 한글은 2바이트의 용량을 가진다.

한편 UTF-8에서 ASCII 문자는 1바이트, 한글은 3바이트의 용량을 가진다.

이렇게 문자세트에 따라 한 문자의 크기는 달라진다. 문자열 조작 함수로 문자 단위가 아닌 바이트 단위로 지정할 경우에는

문자세트에 주의할 필요가 있다.

날짜 연산

이번에는 날짜 연산에 대해 배워보자

CURRENT_TIMESTAMP CURRENT_DATE INTERVAL

오늘의 시스템 날짜는 CURRENT_DATE이고

내일 시스템 날짜는 CURRENT_DATE +INTERVAL 1 DAY 가 된다.

날짜 및 시간 데이터를 저장하는 방법은 데이터베이스 제품에 따라 크게 달라진다.

날짜와 시간 전부를 저장할 수 있는 자료형을 지원하거나, 혹은 날짜는 DATE형, 시간은 TIME형, 날짜와 시간은 DATETIME 형과 같이

세분화해 지원하는 데이터베이스 제품도 있다.

여기서는 날짜와 시간을 초 단위로 저장할 수 있는 날짜 시간형을 중점으로 설명해보자

실제 시스템에서 날짜 연산은 빈번하게 일어난다. 쇼핑사이트 등에서도 입하, 출하, 주문 등의 많은 부분에 걸쳐 날짜 정보는 유용하게 사용된다.

SQL에서의 날짜

날짜나 시간 데이터는 수치 데이터와 같이 사칙 연산을 할 수 있다.

날짜시간 데이터를 연산하면 결괏값으로 동일한 날짜시간 유형의 데이터를 반환하는 경우도 있으며 기간(간격)의 차를 나태내는

기간형(interval)데이터를 반환하는 경우도 있다.

기간형은 '10일간', '2시간10분'과 같이 시간의 간격을 표현할 수 있다.

시스템 날짜

날짜시간 데이터의 연산을 설명하기 이전에 시스템 날짜를 확인하는 방법에 대해 설명해보자

컴퓨터에는 반드시 시계가 내장되어 있다. 네트워크나 주변기기와 데이터를 통신하기 위해서는 시간을 정확하게 측정할 필요가 있기 때문이다.

'시스템 날짜'란 이 같은 하드웨어 상의 시계로부터 실시간으로 얻을 수 있는 일시적인 데이터를 말한다.

RDBMS에서도 시스템 날짜와 시간을 확인하는 함수를 제공한다.

표준 SQL에서는 'CURRENT_TIMESTAMP'라는 긴 이름의 함수로 실행했을 때를 기준으로 시간을 표시한다.

CURRENT_TIMESTAMP라는 함수임에도 인수를 필요로 하지 않는다.

일반적인 함수와는 달리 인수를 지정할 필요가 없으므로 괄호를 사용하지 않아도 되는 특수한 함수이다.

SELECT CURRENT_TIMESTAMP;

이렇게 작성하고 실행하면 현재시간을 잘 보여주게 된다.

해당 코드에서는 FROM 구를 생략했다. SELECT 구만으로도 SELECT 명령은 실행된다. Oracle과 같은 전통적인 데이터베이스는

FROM구를 생략할 수 없으므로 주의해야한다.

앞에서 언급한 것 처럼 CURRENT_TIMESTAMP 함수는 표준 SQL로 규정되어 있는 함수이다.

Oracle에서는 SYSDATE 함수, SQL Server에서는 GETDATE 함수를 사용해도 시스템 날짜를 확인 할 수 있다.

그러나 이들은 표준화되기 전에 구현된 함수인 만큼 사용하지 않는 편이 낫다.

날짜 서식

날짜 데이터를 데이터베이스에 저장할 경우 CURRENT_TIMESTAMP를 사용해 시스템 상의 날짜를 저장할 수 있다.

다만 임의의 날짜를 저장하고 싶을 경우에는 직접 날짜 데이터를 지정해야 한다.

날짜 서식은 국가별로 다른데 한국과 일본에서는 연월일을 슬래시나 하이픈으로 구분해 표기하는 경우가 많다.

한편 미국에서는 숫자를 대신해 Jan, Feb 등으로 표기하며 일반적으로 일월년의 순으로 표기한다.

- 2014/01/25

- 2014-01-25

- 25 Jan 2014

이처럼 날짜를 표기하는 방식이 다양한 가운데 대부분의 데이터베이스 제품은 날짜 데이터의 서식을 임의로 지정, 변환할 수 있는 함수를 지원한다.

Oracle의 경우에는 TO_DATE 함수를 사용해 문자열 데이터를 날짜형 데이터로 변환할 수도 있으며 서식 또한 별도로 지정할 수 있다.

TO_DATE ('2014/01/25', 'YYYY/MM/DD')

여기서는 'YYYY/MM/DD'가 서식부분이다. YYYY가 년, MM이 월, DD가 날을 의미한다.

반대로 날짜형 데이터를 서식에 맞춰 변환해 문자열 데이터로 출력하는 함수도 존재한다. Oracle의 경우 TO_CHAR 함수가 그에 해당한다.

날짜의 덧셈과 뺄셈

날짜시간형 데이터는 기간형 수치데이터와 덧셈 및 뺄셈을 할 수 있다. 날짜 시간형 데이터에 기간형 수치데이터를 더하거나 빼면 날짜시간형 데이터가 반환된다.

예를 들어 특정이로부터 1일 후를 계산하고 싶다면 a+1DAY 라는 식으로 계산할 수 있다 1일 전이라면 a-1DAY로 하면 된다.

SELECT CURRENT_DATE + INTERVAL 1 DAY;

이렇게 입력해주면 오늘날짜에 1일을 더 한 3월 10일을 데이터로 보여준다.

날짜형 간의 뺄셈

날짜시간형 데이터 간에 뺄셈도 할 수 있다.

예를 들어 Oracle에서는 '2014-02-28' - '2014-01-01'이라고 한다면

두 날짜 사이에 차이가 얼마나 발생하는지 계산할 수 있다.

한편 MySQL에서는 DATEDIFF ('2014-02-28', '2014-01-01')로 계산할 수 있다.

SELECT DATEDIFF ('2014-02-28', '2014-01-01');

이렇게 계산되어진 58일이라는 날짜까 나오게된다.

CASE 문으로 데이터 변환하기

이번에는 CASE 문에 대해 배워보자. CASE 문을 이용해 데이터를 변환할 수 있다.

CASE 문

지금까지 연산자와 함수를 서로 적절히 조합해 사용하면 대부분의 계산을 처리할 수 있다.

하지만 RDBMS에 갖추어져 있는 기존의 연산자나 함수만으로는 처리할 수 없는 것들도 있을 것이다.

예를 들면 NULL 값을 0으로 간주하여 계산하고 싶은 경우가 그렇다. 하지만 NULL 값으로 연산한 결과는 모두 NULL값이 된다.

RDBMS에서는 사용자가 함수를 작성할 수 있다. 방금 설명한 것과 같은 상황 역시 사용자 정의 함수를 작성해서 해결할 수 있을 것이다.

하지만 간단한 처리의 경우에는 사용자 정의 함수를 작성하지 않고도 CASE 문으로 처리할 수 있다.

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

먼저 WHEN 절에는 참과 거짓을 반환하는 조건식을 기술한다. 해당 조건을 만족하여 참이 되는 경우는 THEN 절에 기술한 식이 처리된다.

이때 WHEN과 THEN을 한데 조합해 지정할 수 있다. WHEN 절과 대응하는 THEN 절 식의 처리결과를 CASE문의 결괏값으로 반환한다.

그 어떤 조건식도 만족하지 못한 경우에는 ELSE 절에 기술한 식이 채택된다. ELSE는 생략가능하며 생략했을 경우 ELSE NULL로 간주된다.

그럼 지금부터 NULL값을 0으로 변환하는 CASE식 구현사례를 살펴보자

SELECT * from testsql;

이렇게 값을 입력해서 테이블을 출력해준다.

SELECT a, CASE WHEN a IS NULL THEN 0 ELSE a END "a(null=0)"
FROM testsql;

이렇게 조건식을 걸어주고 데이터를 뽑아내면

이렇게 조건에 맞는 데이터가 출력된다.

해당 조건을 읽어보면 조건식으로 a가 NULL일 경우 THEN을 이용해서 0이라는 값을 뽑아낸다.
그렇지 못한 조건일 경우 a값으로 a를 반환한다.

그 결과로 1과 2는 그대로 1과 2를 뱉어냈지만 NULL값 같은 경우는 조건식이 '참'이 되어 0이라는 값을 뱉어낸다.

여기서 추가적으로

SELECT a, CASE WHEN a IS NULL THEN 0 ELSE a END
FROM testsql;

이렇게만 작성했다면

이렇게 긴 이름으로 복잡한 표로 나타내지만

SELECT a, CASE WHEN a IS NULL THEN 0 ELSE a END "a(null=0)"
FROM testsql;

이렇게 내가 원하는 이름을 적어주면

이렇게 깔끔하게 뽑아준다.

SELECT a, CASE WHEN a IS NULL THEN 0 ELSE a END "결과"
FROM testsql;

이렇게 한글로 정해줄수도 있다.

결과라는 이름으로 데이터를 뽑았다.

COALESCE

사실 NULL값을 변환하는 경우라면 COALESCE 함수를 사용하는 편이 더 쉽다.

앞의 SELECT 명령 예제를 COALESCE 함수를 사용해 구현하면 다음과 같다.

SELECT a, COALESCE(a,0) FROM testsql;

COALESCE 함수는 여러 개의 인수를 지정할 수 있다. 주어진 인수 가운데 NULL이 아닌 값에 대해서는 가장 먼저 지정된 인수의 값을 반환한다. 앞의 예문은 a가 NULL이 아니면 a값을 그대로 출력하고, 그렇지 않으면 a가 NULL이면 0을 출력하는 것이다.

SELECT a, COALESCE(a,'널') FROM testsql;

이렇게 작성해줄 수 있다.

a열에 1과 2는 NULL이 아니라서 1과 2를 그대로 뽑아냈지만 3행에 있는 NULL은 '널'이라는 조건에 걸려서 널 이라고 나오게 된 것이다.

또 하나의 CASE 문

숫자로 이루어진 코드를 알아보기 더 쉽게 문자열로 변환하고 싶은 경우 CASE문을 많이 사용한다.

예를 들어 '1은 남자 2는 여자'라는 코드 체계가 있다면, 이를 모르는 사람에게는 '1/2'라고 표시하는 것보다

'남자/여자'라고 표시하는 편이 알아보기에 훨씬 쉬울 것이다.

덧붙이자면 이와 같이 문자화하는 것을 '디코드'라고 부르고 반대로 수치화하는 것을 '인코드'라고 부른다.

코드 = 수치데이터 정보

1 -> 디코드 남

2 <- 인코드 여

이와 같은 디코드를 CASE문으로도 처리할 수 있다.

WHEN a=1 THEN '남자';
WHEN a=2 THEN '여자';

CASE 문은 '검색 CASE'와 '단순 CASE'의 두 개 구문으로 나눌 수 있다.

검색 CASE는 앞서 설명한 'CASE WHEN 조건식 THEN 식...' 구문이다.

한편 단순 CASE는 'CASE 식 WHEN 식 THEN 식...' 구문이다.

단순 CASE에서는 CASE뒤에 식을 기술하고 WHEN뒤에 (조건식이 아닌) 식을 기술한다.

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

식 1의 값이 WHEN의 식2의 값과 동일한지 비교하고, 값이 같다면 식3의 값이 CASE문 전체의 결괏값이 된다.

즉, 식1의 값과 식4의 값이 같은지를 비교하고 같다면 식5의 값이 CASE문의 결괏값이 되는 것이다.

비교결과 일치하는 WHEN 절이 하나도 없는 경우에는 ELSE절이 적용된다.

그럼 지금부터 성별 문자열을 디코딩을 해보도록 하자. 먼저 검색 CASE의 경우이다.

SELECT a AS "코드",
CASE
    WHEN a=1 THEN'남자'
    WHEN a=2 THEN '여자'
    ELSE '미지정'
END AS "성별" FROM testsql;

조건식을 보면 1이면 남자 2는 여자 그외에는 미지정으로 되도록 해줬기 때문에 정상적으로 결과가 나왔다.

이것이 검색 CASE이다.

다음으로 단순 CASE에 관해 알아보자.

검색CASE의 경우에는 WHEN에 a=1, a=2 식을 상세하게 기술해야 하지만 단순 CASE에서는 CASE문에서 비교할 항목인 'a'를 따로 지정하므로 WHEN에는 1, 2처럼 비교할 값만 기술하면 된다.

SELECT a AS "코드",
CASE a
    WHEN 1 THEN '남자'
    WHEN 2 THEN '여자'
    ELSE '미지정'
END AS "성별" FROM testsql;

차이점은 a뒤에 비교대상을 적는다.

WHEN 뒤에는 값만 적는다는 것에서 검색CASE와 차이가 있다.

CASE를 사용할 경우 주의사항

이제까지 예제에서는 SELECT 구에서 CASE 문을 사용했다. 그러나 CASE 문은 어디에나 사용할 수 있다.

WHERE 구에서 조건식의 일부로 사용될 수도 있고 ORDER BY 구나 SELECT 구에서도 사용할 수 있다.

ELSE 생략

ELSE를 생략하면 ELSE NULL이 되는 것에 주의해야한다. 상정한 것 이외에 데이터가 들어오는 경우도 많다.

대응하는 WHEN이 하나도 없으면 ELSE 절이 사용된다. 이때 ELSE를 생략하면 상정한 것 이외에 데이터가 왔을 때 NULL이 반환된다. 따라서 ELSE를 생략하지 않고 지정하는 편이 낫다.

WHEN에 NULL지정하기

단순 CASE에서는 WHEN 뒤에 1개의 상수값을 지정하는 경우가 많을 것이다.

앞에서 작성한 예제에도 보면 WHEN 1 THEN '남자' WHEN 2 THEN '여자'와 같이 쓸 수 있다.

여기에서 데이터가 NULL인 경우를 고려해 WHEN NULL THEN '데이터 없음'과 같이 지정해도 문법적으로는 문제가 없지만

정상적으로 처리되지 않는다.

CASE a
       
       WHEN 1 THEN '남자'
       WHEN 2 THEN '여자'
       WHEN NULL THEN '데이터 없음'
       ELSE '미지정'
END

이 예제코드는 다음과 같은 순서로 조건식을 처리한다.

1. a = 1

2. a = 3

3. a = NULL

비교 연산자 = 로는 NULL 값과 같은지 아닌지를 비교할 수 없다. 따라서 a열의 값이 NULL이라 해도 a=NULL은 참이 되지 않는다.

즉 '데이터 없음' 대신 '미지정' 이라는 결괏값이 나온다. 단순 CASE 문으로는 NULL을 비교할 수 없다는 문제점이 있다.

이때 NULL 값인지 아닌지를 판정하기 위해서는 IS NULL을 사용해야 한다.

다만 단순 CASE 문은 특성상 = 연산자로 비교하는 만큼, NULL 값인지를 판정하려면 검색 CASE 문을 사용해야 한다.

CASE
    WHEN a=1 THEN'남자'
    WHEN a=2 THEN '여자'
    WHEN a IS NULL THEN '데이터 없음'
    ELSE '미지정'
END

Point -> 단순 CASE 문으로는 NULL 값을 비교할 수 없다!

DECODE NVL

Oracle에는 이 같은 디코드를 수행하는 DECODE 함수가 내장되어 있다. DECODE 함수는 CASE문과 같은 용도로 사용할 수 있다.

다만 DECODE 함수는 Oracle에서만 지원하는 함수인 만큼 다른 데이터베이스 제품에서는 사용할 수 없다. 그에 비해 CASE문은 표준 SQL로 규정된 덕분에 많은 데이터 베이스 제품이 지원한다.

또한 NULL 값을 변환하는 함수도 있는데 Oracle에서는 NVL함수, SQL Server에서는 ISNULL함수가 이에 해당한다.

다만 이 함수들은 특정 데이터베이스에 국한된 함수인 만큼 NULL값을 변환할 때는 표준 SQL로 규정되어 있는 COALESCE함수를 사용한다.

여기까지 작성하고 데이터의 추가, 삭제, 갱신은 다음 편에서 이어서 작성하겠다.

혹시 4편까지 읽은 사람이 있다면 감사의 마음을 표한다.

모든 포스팅에 대한 내용은 도서 SQL첫걸음에서 익히고 배운 것들에 대한 내용을 직접 타이핑 하였음을 밝힙니다.

저작자표시 비영리 변경금지

'BackEnd' 카테고리의 다른 글

SQL 데이터베이스에 대해 제대로 알자(6) - 논리삭제와 물리삭제 (0)	2023.03.09
SQL 데이터베이스에 대해 제대로 알자(5) - 추가, 삭제, 갱신 (1)	2023.03.09
SQL 데이터베이스에 대해 제대로 알자(3) - ORDER BY, LIMIT (0)	2023.03.08
SQL 데이터베이스에 대해 제대로 알자(2) - 조건조합하기,연산자 (0)	2023.03.08
SQL 데이터베이스에 대해 제대로 알자(1) - 데이터베이스란? (0)	2023.03.08

현재글SQL 데이터베이스에 대해 제대로 알자(4) - 수치연산, CASE

Taemin's Devlog