2021 02 16 - CASE, CASE, CASE

집계와 조건분기

-UNION을 사용한 방법

SELECT prefecture, SUM(pop_men) AS pop_men, SUM(pop_won) AS pop_wom
FROM( SELECT prefecture, pop AS pop_men, null AS pop_wom
	  FROM Population
      WHERE gender = '1' --남성
      UNION
      SELECT prefecture, pop AS pop_wom, null AS pop_men
	  FROM Population
      WHERE gender = '2') TMP--여성
GROUP BY prefecture;

-CASE를 사용한 방법

CASE 식을 집계함수 안에 사용하면 필터를 만들 수 있다.

또한 Seq San으로 테이블 풀스캔이 1회로 감소한다.

SELECT prefecture
	SUM(CASE WHEN gender = '1' THEN pop ELSE 0 END) AS pop_men,
    SUM(CASE WHEN gender = '0' THEN pop ELSE 0 END) AS pop_won
FROM Population
GROUP BY prefecture;

2)

UNION을 사용한 방법

SELECT emp_name
	MAX(team) AS team
FROM Employees
GROUP BY emp_name
HAVING COUNT(*) = 1
UNION
SELECT emp_name
	'2개를 겸무' AS team
FROM Employees
GROUP BY emp_name
HAVING COUNT(*) = 2
UNION
SELECT emp_name
	'3개를 겸무' AS team
FROM Employees
GROUP BY emp_name
HAVING COUNT(*) = 3

CASE를 사용한 방법

SELECT emp_name
	CASE WHEN COUNT(*) = 1 THEN MAX(team)	--왜 MAX를쓸까 ㅜ 이유: groupby로 집약했기때문.
    	 WHEN COUNT(*) = 2 THEN '2개를겸무'
         WHEN COUNT(*) >=3 THEN '3개를겸무'
    END AS team
FROM Employees
GROUP BY emp_name;

 

절차 지향형과 선언형

 

집약

GROUP BY 구로 집약했을때 SELECT 구에 입력할 수 있는것은 세가지이다.

- 상수

- GROUP BY 구에서 사용한 집약 키

- 집약함수

 

GROUP BY
집약할때 따라서는 정렬을 사용하기도 한다.(SORT GROUP BY - 오라클의 경우)
GROUP BY 구에 지정되어 있는 필드를 해시 함수를 사용해서 해시키로 변환하고, 같은 해시키를 가진 그룹을 모아 집약하는 방법이다.
해시의 성질상 GROUP BY의 유일성이 높으면 더 효율적으로 작동한다.

자르기

GROUP BY로 집약 말고도 한가지  기능이 있다. 바로 자르기.

모집합인 테이블을 작은 부분 집합들로 분리하는 것.

 

// 첫 문자 알파벳마다 몇명의 사람이 존재하는지 확인.

SELECT SUBSTRING(name, 1, 1) AS label,
		COUNT(*)
FROM Persons
GROUP BY SUBSTRING(name, 1, 1);

 

//파티션

이렇게 GROUP BY 구로 잘라 만든 하나하나의 부분 집합을 수학적으로는 파티션이라고 부른다.

SELECT CASE WHEN age < 20 THEN '어린이'
            WHEN age BETWEEN 20 AND 69 THEN '성인'
            WHEN age >= 70 THEN '노인'
            ELSE NULL END AS age_class,
       COUNT(*)
FROM Persons
GROUP BY CASE WHEN age < 20 THEN '어린이'
            WHEN age BETWEEN 20 AND 69 THEN '성인'
            WHEN age >= 70 THEN '노인'
            ELSE NULL END

        

반복문

SQL은 내부적으로는 반복문을 사용한다.

특정 레코드를 전체의 레코드와 비교하는 로직은 전형적인 '한번에 한 레코드' 사고방식이다.

 

SQL에서는 반복을 어떻게 표현할까?

SQL에서 반복문을 대신하는 수단은 CASE식과 윈도우 함수이다. -> IF-THEN-ELSE 구문에 대응하는 기능이다.

 

SELECT company,
		year,
        sale,
        CASE SIGN(sale - MAX(sale)	--OVER 쓰려고 MAX 쓴듯.
        					OVER( PARTITION BY company
                            		ORDER BY year
                                    ROWS BETWEEN 1 PRECEDING	--현재 레코드에서 직전레코드 1개 지정
                                    		AND 1 PRECEDING
                            	))
        WHEN 0 THEN '='
        WHEN 1 THEN '+'
        WHEN -1 THEN '-'
        ELSE NULL END AS var
FROM Sales;

SIGN 함수는 숫자 자료형을 매개변수로 받아, 음수 -1 양수 1 0이라면 0을 리턴한다.

실행계획 풀스캔 -> 윈도우 함수를 정렬로 실행