<SQL> FLOOR 함수를 통한 그룹화

들어가며.

지원한 기업의 쿼리 테스트를 풀다 처음 접근하는 방식의 문제를 풀어봤습니다.

당시 문제를 풀 때는 방법을 인지하고 있지 못해 아는 함수 안에서 풀어보았지만, 문제는 틀렸을 것으로 생각합니다.

 

문제를 복기하며 풀이 방법을 생각해보았을 때, 새로운 함수를 알게 되어 블로그에 글을 적어보려고 합니다.

---

문제 정의

문제는 노출이 불가하여 직접 테이블을 만들어 설명해보겠습니다.

CREATE TABLE CUSTOMERS (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(50),
    AGE INT
);

INSERT INTO CUSTOMERS (NAME, AGE) VALUES
('Alice', 23),
('Bob', 27),
('Charlie', 34),
('David', 19),
('Eve', 42),
('Frank', 15),
('Grace', 55),
('Heidi', 65),
('Ivan', 72),
('Judy', 8);

당시 문제는 고객의 연령대를 5세 단위로 나눠 그룹화를 진행하는 문제였습니다.

해당 테이블에는 고객의 NAME과 AGE가 나와있습니다.

 

제가 처음 생각한 방법은 5세 미만부터 가장 나이가 많은 72세가 있는 Ivan까지 포함하기 위하여 CASE 문을 사용하여 <=와 같은 등호를 써가며 5세 단위로 나누어 75세 미만까지 5세 단위 그룹화를 진행하고 만약 75세 이상의 값이 있다면 '75세 이상' 행에 모두 들어가도록 쿼리문을 짰습니다.

 

100세 이상의 사람이 고객으로 있기 힘들기에 100세까지 CASE 문을 짤 수 도 있지만, 해당 문제에서 원하는 답은 실제 테이블에 80세, 90세 몇 세까지 있던 해당 쿼리문을 통해 모든 나이대를 5세 단위로 그룹화하는 것을 원했던 것 같습니다. 

실제로 그렇게 하지 못한 저는 쿼리테스트에서 뜨거운🔥합격을 받았습니다.

 

해결 방법

해결 방법을 고민하던 도중 컴활 자격증을 따기 위해 열심히 공부 했을 때, 몫을 통해 그룹화를 진행했던 문제가 생각이 나 FLOOR (내림, 버림 함수)를 통해 문제를 해결하였습니다.

 

AGE를 해당 단위로 나눈 값을 FLOOR 함수를 사용해 준다면, 나머지가 아닌 몫을 구할 수 있을 것이라고 생각하였습니다.

* ROUND 함수는 반올림을 해주기 때문에 부적합하다고 생각하였습니다.

SELECT
    CASE
        WHEN AGE < 5 THEN '5세 미만'
        ELSE CONCAT(FLOOR(AGE / 5) * 5, '세 이상 ~ ', FLOOR(AGE / 5) * 5 + 5, '세 미만')
    END AS AGE_GROUP,
    COUNT(*) AS count
FROM
    CUSTOMERS
GROUP BY
    AGE_GROUP
ORDER BY
    MIN(FLOOR(AGE / 5) * 5);

해당 쿼리문을 예를 들어 설명해보겠습니다.

NAME	AGE
Alice	23
Bob	27

 

두 사람의 나이를 보았을 때, Alice는 '20세 이상 25세 미만' 그룹에 들어가야하고, Bob은 '25세 이상 30세 미만' 그룹에 들어가야합니다.

 

해당 쿼리문에 적용 해보겠습니다.

 

FLOOR(23/5 = 4.6) = 4 , FLOOR(27/5 = 5.4) = 5입니다. 이 몫에 나눈 값만큼 다시 곱해준다면 4 * 5 = 20, 5 * 5 = 25가 되어 원하는대로 해당 나이가 속하는 그룹을 찾을 수 있게 되었습니다.

 

주의점

해당 쿼리문과 같은 경우는 문자와 혼합하여 단위를 나타내는 경우가 많습니다.

CONCAT을 사용하여 숫자와 문자로 결합 시, 해당 열은 '문자열'이 되어 AGE_GROUP으로 ORDER BY를 진행하게 되면 제대로 정렬되지 못하기 때문에, ORDER BY 열을 주의하여 원하는 값을 얻도록 해야합니다!

 

이걸 미리 알았더라면...