[데이터베이스] SQL의 핵심 기능, CASE문과 TOP

CASE 문은 행 단위의 조건 처리를 위한 Transaction SQL 기능 중 하나이다. 

나는 실제 실무에서 데이터 검증을 위해서도 CASE 문을 자주 쓰는 편이다. 

CASE 문은 SQL 이 아닌 다른 언어에서 IF문과 비슷한 기능을 한다고 볼 수 있다. 

 

 

CASE 문을 사용할 때 참고 및 주의 사항은 다음과 같다. 

• 서로 다른 데이터 형을 반환 하는 경우는 명시적으로 변환 하는 것을 권장한다. 
• ELSE 절이 없는 경우에는 NULL을 반환하는데, SUM()과 같은 집계 작업에 활용할 수 있다. 
• CASE 문 평가 전에 내부 식이 먼저 계산되는 경우, 원치 않는 결과가 나올 수 있음. -> 오류 발생 할 수 있다면 해결 코드 추가가 필요하다. 

아래 쿼리는 명시적으로 형 변환이 필요한 이유를 보여주는 쿼리이다. 

DECLARE @now datetime = '2022-7-20'

SELECT
	CASE LEN(DATEPART(month, @now))
    	WHEN 1 THEN '0' + CONVERT (char(1), DATEPART(month, @now))
        WHEN 2 THEN DATEPART(month, @now)
    END; -- 결과는 07이 아닌 7

사실 위와 같은 쿼리는 난 잘 사용하지 않지만,, 명시적 형 변환이 필요함에도 정작 중요하게 생각하지 못할 때 가 있긴 한 것 같다.

 

다음은,  ELSE 문의 유무에 대한 차이를 보여주는 쿼리이다. 

(1)의 경우에는  Qunatity가 50개 이하의 경우 0을 반환하고, (2)의 경우에는 null을 반환한다. 

SELECT 
	ProductID,
    SUM(CASE WHEN Quantity > 50 THEN (Quantity * UnitPrice) ELSE 0 END),  -- (1)
    SUM(CASE WHEN Quantity > 50 THEN (Quantity * UnitPrice) END) -- (2) 자동으로 null 반환
FROM dbo.OrderDetails
GROUP BY ProductID


--산술식
SELECT
	CASE 
    	WHEN value <= 0 THEN 0
    	WHEN value <= 10 THEN 100 / value
    END 
FROM (VALUES (0), (1), (11)) AS d(value); --ELSE 없으면 null

나는 현업에서 다음과 같이 CASE문을 가장 유용하게 사용한다.

state라는 새로운 컬럼을 통해 재고의 현황을 쉽게 파악할 수 있다.

-- CASE를 이용해 재고 현황 파악
SELECT productID, 
       UnitsInStock, 
       State = (CASE
                    WHEN UnitsInStock <= 20 THEN '재고부족'
                    WHEN UnitsInStock < 50 THEN '주문필요'
                    ELSE '재고'
                 END)
FROM dbo.Products

 

MSSQL에서 Pivot 함수를 사용할 수 있지만, Inner join을 통해서 다음과 같이 사용할 수도 있다.

-- Pivoting
SELECT productID, orderDate = YEAR(orderDate),
		Quantity
FROM dbo.OrderDetails As d
INNER JOIN dbo.Orders As o
	ON d.OrderID = o.OrderID
ORDER BY ProductID;

 

TOP

• 상수
• 변수 / (SELECT 쿼리)
• WITH TIES : order by 열 동일 값 기준
• PERCENT: float 값으로 변환
• ORDER BY 절 주의 (동률 처리): 중복 데이터 존재 시 정합성(일관성) 문제

-- 변수

DECRARE @TOP int = 5; 

SELECT TOP(@TOP) *
FROM dbo.OrderDetails
ORDER BY Quantity DESC;

-- 서브쿼리
SELECT TOP(SELECT AVG(Quantity) FROM dbo.OrderDetails) *
FROM dbo.OrderDetails
ORDER BY Quantity DESC;

TOP 을 사용할 때 동률 처리를 어떻게 할 것인가는 데이터의 정합성에 있어서 중요하다. 

-- 동률처리
SELECT TOP(5) * 
FROM dbo.Order
ORDER BY Quantity DESC; -- 똑같은 양이 여러개 있다면?? 어떤 근거로 순위를 맺을 것인가?

SELECT TOP(5) With TIES *
FROM dbo.Order
ORDER BY Quantity DESC; -- 같은 수량이 여러개 있을 경우 다 뽑아줌

SELECT TOP(5) *
FROM dbo.Order
ORDER BY Quantity DESC, Discount ACS; -- 동일한 순위가 있을 때 할인율이 높은 것을 먼저