SQL 단일 행 내장 함수들

DML 문을 연습할 데이터 베이스는 아래의 사이트에서 스크립트를 다운로드하여 실행해 생성했습니다.

MySQL Sample Database

---

이번 포스트에서는 SQL에서 제공하는 단일 행 내장 함수들에 대해 알아보겠습니다.

SELECT ~ FROM ~ WHERE ~ ; 보다가 내장 함수들에 대해 작성하는 이유는, 이 함수들이 SELECT 문, WHERE 절 그리고 나중에 작성할 ORDER BY 절에서 적용할 수 있기 때문입니다.

단일 행 내장 함수들은 다음과 같은 특징들을 가지고 있습니다.

• 각 행들에 대해 개별적으로 적용해 계산해서 결과를 반환
• 함수의 인자(argument)로 상수, 변수, 표현 식, 함수 가능
• 인자는 1개 이상 가능, 여러 인자들을 줘도 하나의 결과만 반환

2024.02.08 - [개념 정리/데이터베이스] - SELECT문 포스트에서 설명했던 LENGTH, CONCAT 함수가 단일 행 내장 함수에 속합니다. 전의 포스트에서 문자열에 적용하는 함수들을 소개했으므로 아래의 데이터 타입에 사용할 수 있는 함수들 중 극히 일부만 소개하겠습니다.(전체 소개하기에는 양이 너무 많습니다..)

숫자형

날짜형

변환형

NULL 관련

조건절 관련 & CASE

아래 사이트에서 더 많은 함수들에 대한 설명을 확인할 수 있습니다. 이 사이트와 포스트는 MySQL을 기준으로 하고 있으므로 ORACLE 등 다른 DBMS를 사용하시는 분들은 자신에게 맞는 설명을 찾으시길 바랍니다.

MySQL :: MySQL 8.0 Reference Manual :: 14 Functions and Operators

숫자형

ROUND

반올림을 진행하는 함수입니다. 두 번째 인자를 주지 않으면 기본적으로 소수 첫째 자리에서 반올림을 합니다. 두 번째 인자가 주어지면, 인자의 위치까지 반올림을 진행합니다.

SELECT ROUND(3.06);      /* 두 번째 인자 없음 */
SELECT ROUND(3.06, 1);   /* 두 번째 인자 있음 */

왼쪽 사진이 하나의 인자만 전달하고, 오른쪽 사진이 두 개의 인자를 전달한 결과입니다. 왼쪽의 경우, 소수 첫째 자리인 '0'을 기준으로 반올림이 이루어졌습니다. 오른쪽의 경우, 소수 둘째 자리인 '6'을 기준으로 반올림이 이루어졌습니다.

MOD

나눗셈을 하고 생긴 나머지를 반환하는 함수입니다. MOD 함수 대신 % 기호를 사용해 계산할 수 있습니다. 산술 연산(더하기, 빼기, 곱하기, 나누기) 또한 기호를 사용해 계산할 수 있습니다.

SELECT MOD(234, 10);    /* MOD 함수로 나머지 반환 */
SELECT 234 % 10;        /* % 기호를 통해 나머지 얻기 */

왼쪽 사진이 함수를 사용한 결과, 오른쪽 사진이 % 기호를 사용한 결과입니다. 두 결과 모두 동일한 것을 확인할 수 있습니다.

날짜형

SYSDATE

현재 날짜와 시각을 출력하는 함수입니다. 이 함수는 인자를 전달하지 않아도 작동합니다.

SELECT SYSDATE();

EXTRACT

날짜형 데이터에서 연도 / 월 / 일 을 뽑는 함수입니다. FROM을 통해 데이터에서 어떤 것을 추출할지 지정할 수 있습니다.

SELECT DISTINCT EXTRACT(YEAR FROM orderDate) AS '구매 년도' FROM orders;

YEAR 대신에 MONTH | DAY를 사용해 월, 일을 출력할 수 있습니다.

변환형

특정 데이터 타입을 다양한 형식으로 출력하고 싶을 때 사용하는 함수입니다.

변환할 때는 명시적 변환과 암시적 변환이 있는데, 암시적 변환은 에러가 발생하는 경우가 있으므로 명시적 변환을 사용하는 것이 바람직하다고 합니다. 따라서 아래에 설명하는 함수들은 모두 명시적 데이터 유형 변환 함수입니다.

TO_DATE

문자열을 주어진 형식의 날짜 타입으로 변환합니다. 첫 번째 인자로 날짜가 작성된 문자열, 두 번째 인자로 날짜 형식을 전달합니다.

SELECT STR_TO_DATE('20240417', '%Y%m%d') AS "일자";

CAST

주어진 표현식을 목표로 하는 데이터 유형으로 변환합니다. AS를 통해서 표현식을 어떤 타입으로 변환할지 지정할 수 있습니다.

SELECT CAST(20240517 AS DATE);

NULL 관련

NULL에 대한 특징을 정리하면 아래와 같습니다.

• NULL은 정의되지 않은 값이기 때문에 0(숫자)도 아니고 공백(문자)도 아닙니다.
• 테이블을 정의할 때 NOT NULL 또는 PK로 정의하지 않았으면 NULL 값을 가질 수도 있습니다.
• NULL 값을 포함하는 연산의 결과는 NULL입니다.

ISNULL

열 값이 NULL 이면 1, 아니면 0을 출력하는 함수입니다. 인자로 NULL 판단을 할 열의 이름을 전달합니다.

SELECT customerName, ISNULL(addressLine2) AS "address line 2 null" FROM customers;

 

조건절 관련 & CASE

IF-THEN-ELSE 논리와 유사하게 사용할 수 있습니다. CASE는 사실 SQL 표준의 입장으로 봤을 때는 단일 행 함수보다는 표현식이 정확한 표현입니다. 하지만 Oracle의 Decode 함수와 같은 기능을 하고 있어서 단일 행 내장 함수로 같이 설명하고 있습니다.

SELECT officeCode, city, country,
CASE
    WHEN country = "France" THEN "F"
    WHEN country = "Japan" THEN "J"
    ELSE country
END AS "countryCode"
FROM offices LIMIT 5;

CASE를 통한 월별 데이터 집계

고객별로 월별 payment amount를 계산하려는 상황이 왔을 때 아래와 같은 형식의 SQL 문법을 사용해 집계 보고서 형식으로 만들 수 있습니다.

집계함수(CASE()) ~ GROUP BY

GROUP BY 절에 대해서는 아래의 포스트에 정리했습니다.

GROUP BY, HAVING 절

 

SELECT customerNumber,
  SUM(CASE WHEN MONTH(paymentDate) = 1 THEN amount END) M01, SUM(CASE WHEN MONTH(paymentDate) = 2 THEN amount END) M02,
  SUM(CASE WHEN MONTH(paymentDate) = 3 THEN amount END) M03, SUM(CASE WHEN MONTH(paymentDate) = 4 THEN amount END) M04,
  SUM(CASE WHEN MONTH(paymentDate) = 5 THEN amount END) M05, SUM(CASE WHEN MONTH(paymentDate) = 6 THEN amount END) M06,
  SUM(CASE WHEN MONTH(paymentDate) = 7 THEN amount END) M07, SUM(CASE WHEN MONTH(paymentDate) = 8 THEN amount END) M08,
  SUM(CASE WHEN MONTH(paymentDate) = 9 THEN amount END) M09, SUM(CASE WHEN MONTH(paymentDate) = 10 THEN amount END) M10,
  SUM(CASE WHEN MONTH(paymentDate) = 11 THEN amount END) M11, SUM(CASE WHEN MONTH(paymentDate) = 12 THEN amount END) M12
FROM payments GROUP BY customerNumber LIMIT 5;

위의 코드 길이가 길지만 월별 데이터라 12개의 CASE를 사용해야 하기 때문에 길 뿐이지 복잡한 SQL문은 아닙니다.

고객별로 확인하기 위해 customerNumber로 그룹화를 진행한 후, payment의 월을 추출해 이를 CASE별로 다뤄서 amount의 sum을 구했습니다. 이를 실행하면 아래와 같은 결과를 출력합니다.

월별 amount 집계 실행 결과

처음 CASE 예시와 지금의 예시는 명확한 차이점을 가지고 있습니다. 처음 예시는 CASE 적용 결과가 하나의 열로 출력되지만 이 예시에서는 CASE를 작성한 만큼의 열을 추가하여 출력했습니다. 즉 이 예시처럼 CASE를 사용해 필요한 만큼 열을 생성해 집계 보고서를 생성할 수 있습니다.