2024-03-28 [Floor / Truncate 활용 그룹 분류하기]

CASE

어느 의류 쇼핑몰에서 판매중인 상품들의 정보를 담은 PRODUCT 테이블입니다.

PRODUCT 테이블은 아래와 같은 구조로 되어 있으며

PRODUCT_ID, PRODUCT_CODE, PRICE는 각각 상품 ID, 상품코드, 판매가를 의미합니다.

(상품 별로 중복되지 않는 8자리 상품코드 값을 가지며 앞 2자리는 카테고리 코드를 나타냅니다.)

 

테이블 구조

1. PRODUCT 테이블에서 만원 단위의 가격대 별로 상품 개수를 출력하는 SQL 문을 작성

2. 컬럼명은 각각 컬럼명은 PRICE_GROUP, PRODUCTS로 지정

3. 가격대 정보는 각 구간의 최소금액(10,000원 이상 ~ 20,000 미만인 구간인 경우 10,000)으로 표시

4. 결과는 가격대를 기준으로 오름차순 정렬

 

주춤한 포인트는?

내가 가장 먼저 떠올린 건 'CASE WHEN' 활용 가격대를 구간 별로 분류하는 것이었고, 비효율적인 쿼리지만 하단에 순서대로 설명드리겠습니다.

 

일차적으로 가격대의 전구간을 확인하기 위해 다음과 같은 쿼리를 작성해보았고,

SELECT *
from PRODUCT

가격대가 0원 ~ 90,000원 사이로 확인했다. 여기서부터 문제라고 생각한 것이

1. 10,000원대로 구간을 나눈다면 쿼리가 길어진다는 것

2. 만약 로우가 많은, 규모가 큰 테이블인 경우 다음과 같이 하나하나 가격대를 확인 불가

하다는 점이었고, 일단 다른 사람들이 작성한 쿼리를 보기 전, 문제를 풀고 싶긴해서 다음과 같이 작성해보았다.

 

SELECT 
CASE
    WHEN PRICE < 10000 THEN '0'
    WHEN PRICE <= 19999 THEN '10000'
    WHEN PRICE <= 29999 THEN '20000'
    WHEN PRICE <= 39999 THEN '30000'
    WHEN PRICE <= 49999 THEN '40000'
    WHEN PRICE <= 59999 THEN '50000'
    WHEN PRICE <= 69999 THEN '60000'
    WHEN PRICE <= 79999 THEN '70000'
    WHEN PRICE <= 89999 THEN '80000'
    END PRICE_GROUP,
    COUNT(*) PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP

 

원하는 방향으로 출력은 되었으니, 다른 효율적인 방법을 찾아보았고 여러가지 풀이들을 확인해 본 결과 아래와 같이 총 두가지의 효율적인 쿼리작성 방법과 관련 함수들을 확인할 수 있었다.

 

FLOOR 함수 활용 쿼리 작성

• FLOOR 함수는 언제 활용할까?

FLOOR 함수는 소수점 첫째 자리에서 버림하는 함수로, 주어진 숫자와 가장 근접한 작은 정수를 출력한다.

다음 함수를 활용하여 아래와 같은 쿼리를 작성할 수 있다.

SELECT FLOOR(PRICE/10000)*10000 PRICE_GROUP,
       COUNT(*) PRODUCTS
FROM PRODUCT
GROUP BY 1
ORDER BY 1

예시를 들어서 설명하자면 가격이 17,900원 상품이 10,000원 구간 내 포함되어야한다. PRICE에 17,900원을 대입하면 1.79가 나오고 FLOOR함수를 통해 소수점 자리가 버려지니 1이된다. 여기서 10000을 곱하면 10000원이 되니 1만원대 상품은 모두 10000원으로 변환된다는 것이다.

 

• FLOOR 함수 관련 주의가 필요한 부분은?

인자 값이 음수일 때 FLOOR 함수 활용 소수점을 내림할 경우 음수 값이 커지게 되므로 해당 부분은 반드시 주의가 필요하다.

EX. FLOOR(-22.3)을 입력할 경우 -23이 출력된다.

 

TRUNCATE 함수 활용 쿼리 작성

• TRUNCATE 함수는?

TRUNCATE는 SQL에서 테이블 내 로우(ROW)를 제거할 때 활용하는데, 테이블 구조와 제약 조건들을 유지시키면서 데이터만 삭제하는 경우 다음 함수를 활용한다.

여기서는 자릿수를 지정하여 아래로 버리는 용도로 활용할 예정이며 아래와 같은 구조로 작성한다.

TRUNCATE(숫자,버릴 자릿수)

 

SELECT TRUNCATE(PRICE/10000, 0)*10000 PRICE_GROUP, 
       COUNT(*) PRODUCTS
FROM PRODUCT
GROUP BY 1
ORDER BY 1

결과적으로 위 TRUNCATE 구조를 참고하여 다음과 같이 작성할 수 있습니다.