[Presto SQL] 가격 범위 별 Item 개수 확인

가격 범위별로 아이템 개수를 확인하는 상황이 있습니다.

범위를 5 단위로 설정하는 경우를 예로 들어 두 가지 방법을 설명하겠습니다.

case when을 사용한 첫 번째 방법은 다음과 같습니다.

SELECT 
    CASE 
        WHEN ItemPrice >= 0 AND ItemPrice < 5 THEN '0~5'
        WHEN ItemPrice >= 5 AND ItemPrice < 10 THEN '5~10'
        WHEN ItemPrice >= 10 AND ItemPrice < 15 THEN '10~15'
        WHEN ItemPrice >= 15 AND ItemPrice < 20 THEN '15~20'
        WHEN ItemPrice >= 20 AND ItemPrice < 25 THEN '20~25'
        WHEN ItemPrice >= 25 AND ItemPrice < 30 THEN '25~30'
        WHEN ItemPrice >= 30 AND ItemPrice < 35 THEN '30~35'
        WHEN ItemPrice >= 35 AND ItemPrice < 40 THEN '35~40'
        WHEN ItemPrice >= 40 AND ItemPrice < 45 THEN '40~45'
        WHEN ItemPrice >= 45 AND ItemPrice < 50 THEN '45~50'
        WHEN ItemPrice >= 50 AND ItemPrice < 55 THEN '50~55'
        WHEN ItemPrice >= 55 AND ItemPrice < 60 THEN '55~60'
        WHEN ItemPrice >= 60 AND ItemPrice < 65 THEN '60~65'
        WHEN ItemPrice >= 65 AND ItemPrice < 70 THEN '65~70'
        WHEN ItemPrice >= 70 AND ItemPrice < 75 THEN '70~75'
        WHEN ItemPrice >= 75 AND ItemPrice < 80 THEN '75~80'
        WHEN ItemPrice >= 80 AND ItemPrice < 85 THEN '80~85'
        WHEN ItemPrice >= 85 AND ItemPrice < 90 THEN '85~90'
        WHEN ItemPrice >= 90 AND ItemPrice < 95 THEN '90~95'
        WHEN ItemPrice >= 95 AND ItemPrice < 100 THEN '95~100'
        WHEN ItemPrice >= 100 AND ItemPrice < 150 THEN '100~150'
        WHEN ItemPrice >= 150 AND ItemPrice < 200 THEN '150~200'
        ELSE '>200'
    END AS ItemPriceRange,
    COUNT(*) AS ItemCount,
    CASE 
        WHEN ItemPrice >= 0 AND ItemPrice < 5 THEN 1
        WHEN ItemPrice >= 5 AND ItemPrice < 10 THEN 2
        WHEN ItemPrice >= 10 AND ItemPrice < 15 THEN 3
        WHEN ItemPrice >= 15 AND ItemPrice < 20 THEN 4
        WHEN ItemPrice >= 20 AND ItemPrice < 25 THEN 5
        WHEN ItemPrice >= 25 AND ItemPrice < 30 THEN 6
        WHEN ItemPrice >= 30 AND ItemPrice < 35 THEN 7
        WHEN ItemPrice >= 35 AND ItemPrice < 40 THEN 8
        WHEN ItemPrice >= 40 AND ItemPrice < 45 THEN 9
        WHEN ItemPrice >= 45 AND ItemPrice < 50 THEN 10
        WHEN ItemPrice >= 50 AND ItemPrice < 55 THEN 11
        WHEN ItemPrice >= 55 AND ItemPrice < 60 THEN 12
				WHEN ItemPrice >= 60 AND ItemPrice < 65 THEN 13
				WHEN ItemPrice >= 65 AND ItemPrice < 70 THEN 14
				WHEN ItemPrice >= 70 AND ItemPrice < 75 THEN 15
				WHEN ItemPrice >= 75 AND ItemPrice < 80 THEN 16
				WHEN ItemPrice >= 80 AND ItemPrice < 85 THEN 17
				WHEN ItemPrice >= 85 AND ItemPrice < 90 THEN 18
				WHEN ItemPrice >= 90 AND ItemPrice < 95 THEN 19
				WHEN ItemPrice >= 95 AND ItemPrice < 100 THEN 20
				WHEN ItemPrice >= 100 AND ItemPrice < 150 THEN 21
				WHEN ItemPrice >= 150 AND ItemPrice < 200 THEN 22
				ELSE 23
		END AS itempricerange_num
FROM 
		YourTable
GROUP BY 
		ItemPriceRange, itempricerange_num
ORDER BY 
		itempricerange_num;

하지만 이렇게 작성하게 되면 case when 함수가 길어져서 가독성이 떨어집니다.

ItemPriceRange을 계산하는 과정에서 case when을 사용하지 않고, round 함수를 사용하여 특정 숫자로 나눈 뒤 반올림하는 두 번째 방식으로도 쿼리를 작성할 수 있습니다.

SELECT 
    CONCAT(
        FLOOR(ItemPrice / 5) * 5, 
        '~', 
        FLOOR(ItemPrice / 5) * 5 + 5
    ) AS ItemPriceRange,
    COUNT(*) AS ItemCount,
    FLOOR(ItemPrice / 5) AS itempricerange_num
FROM YourTable
GROUP BY ItemPriceRange, itempricerange_num
ORDER BY itempricerange_num;

ItemPrice를 5로 나눈 뒤 floor 함수를 사용하여 소수점 이하를 버린 값에 5를 곱합니다.

이를 다시 concat 함수를 사용하여 범위로 합쳐서 ItemPriceRange을 계산합니다.

 

이렇게 작업을 하면 case when과 동일하게 lower bound는 포함 되고 upper bound는 포함 되지 않게 됩니다.

 

만약에 범위를 다르게 지정하려면 현재는 5인데 해당 숫자를 변경해 주면 됩니다. 

예를 들어 7이면 6 → 7로 변경해 주면 됩니다.

 

두 쿼리 실행 시 두 번째 쿼리는 가장 높은 range를 지정해 주지 않았습니다. 그래서 200이 넘어 가도 계속 계산이 되었다. 이런 경우에 아래와 같이 if를 사용하면 됩니다.

SELECT 
    IF(ItemPrice >= 200, '>200', CONCAT(
        FLOOR(ItemPrice / 5) * 5, 
        '~', 
        FLOOR(ItemPrice / 5) * 5 + 5
    )) AS ItemPriceRange,
    COUNT(*) AS ItemCount,
    IF(ItemPrice >= 200, 23, FLOOR(ItemPrice / 5)) AS itempricerange_num
FROM 
		YourTable
GROUP BY 
		ItemPriceRange, itempricerange_num
ORDER BY 
		itempricerange_num;

IF 함수를 사용하여 ItemPrice가 200 이상인 경우에는 '>200'으로, 그렇지 않은 경우에는 round 함수를 사용하여 범위를 계산합니다. 

또한, IF 함수를 사용하여 ItemPrice가 200 이상인 경우에는 23으로, 그렇지 않은 경우에는 round 함수를 사용하여 각 범위를 계산합니다.