[프로그래머스] 2. SUM, MAX, MIN (+ 누적합계)

분석함수([칼럼]) OVER(PARTITION BY 칼럼1, 칼럼2... [ORDER BY 절] [WINDOWING 절])

-> 분석함수 사용시 OVER 절 사용 가능

 

-> PARTITION 사용시엔 해당 칼럼으로 그룹화

-> PARTITION 사용하지 않으면, 전체

 

여러 칼럼을 이용해 그룹화

SELECT empno
     , ename
     , job
     , deptno
     , sal
     , SUM(sal) OVER(PARTITION BY job, deptno)
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')
 ORDER BY job

---

누적합계 구하기

-> EMP번호 순

SELECT empno
     , ename
     , job
     , sal
     , SUM(sal) OVER(ORDER BY empno) AS sal_sum
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')

 

-> SAL순

SELECT empno
     , ename
     , job
     , sal
     , SUM(sal) OVER(ORDER BY sal DESC) AS sal_sum
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')

만약, ORDER BY에서 sal순으로 한다면, sal_sum의 합계가 동일한 경우가 존재하면, 그룹화를 통해 사라진다.

-> 유일하게 존재하도록 변경

-> ORDER BY sal DESC, empno [모든 행을 순차적으로 누적 합계 표시]

---

그룹별 누적 합계 구하기

-> OVER절 내부에 PARTITION BY절을 사용해 그룹으로 누적 합계 표시

SELECT empno
     , ename
     , job
     , sal
     , SUM(sal) OVER(PARTITION BY job ORDER BY sal DESC, empno) AS sal_sum
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')

-> OVER내의 정렬 후 밖에 ORDER BY로 정렬을 수행할 경우 원하지 않는 대로 정렬 가능

-> OVER내의 ORDER BY절 실행 후, SELECT절의 ORDER BY절이 실행하므로

---

총합계 -> OVER절 내부에 ORDER BY 절을 선언하지 않으면 전체 합계가 표시

SELECT empno
     , ename
     , job
     , sal
     , SUM(sal) OVER(ORDER BY empno) AS sal_sum
     , SUM(sal) OVER() AS sal_total_sum
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')

 

 

이전 누적합계 -> 조회된 값의 행 순서를 지정해 값 집계

SELECT empno
     , ename
     , job
     , sal
     , SUM(sal) OVER(ORDER BY empno) AS sal_sum
     , SUM(sal) OVER(ORDER BY empno 
                      ROWS BETWEEN UNBOUNDED PRECEDING 
                               AND 1 PRECEDING) AS sal_pre_sum     
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')

 

ROWS와 RANGE

 

키워드	설명
ROWS	물리적인 행 단위
RANGE	논리적인 행 집합
CURRENT ROW	현재 행
UNBOUNDED PRECEDING	첫 번째 행
UNBOUNDED FOLLOWING	마지막 행
[위치] PRECEDING	[위치] 이전 행
[위치] FOLLOWING	[위치] 다음 행

---

ROWS와 RANGE

: ROWS는 물리적 위치로 구분해 모든 행이 1개의 행으로 인식

: RANGE는 집합으로 묶인 그룹이 1개의 행 [원하는 칼럼기준]

 

 

이전 행의 모든 값과 현재 행의 값이 순차적으로 합산(SUM)

 SUM(sales_amt) OVER(ORDER BY sales_quarter 
                            ROWS BETWEEN UNBOUNDED PRECEDING 
                                     AND CURRENT ROW) AS sum_rows

 

 

행 집합 단위

SUM(sales_amt) OVER(ORDER BY sales_quarter 
                           RANGE BETWEEN UNBOUNDED PRECEDING 
                                     AND CURRENT ROW) AS sum_range

 

---

 직전행과 현재행을 합산 

SUM(sales_amt) OVER(ORDER BY sales_quarter
					ROWS BETWEEN 1 PRECEDING
                    		AND CURRENT ROW) AS sum-rows

 

QUARTER로 구분해 직전 쿼터와 현재 쿼터를 합산

SUM(sales_amt) OVER(ORDER BY sales_quarter 
                           RANGE BETWEEN 1 PRECEDING 
                                     AND CURRENT ROW) AS sum_range

 

매출월을 행집합으로 이전 월과 현재 월을 합산

SUM(sales_amt) OVER(ORDER BY TO_NUMBER(sales_month)
						RANGE BETWEEN 1 PRECEDING
                        	AND CURRENT ROW) AS sum_range

 

파티션으로 범위 제한 -> 파티션[쿼터별] 누적합계

SUM(sales_amt) OVER(PARTITION BY sales_quarter ORDER BY sales_month
				ROWS BETWEEN UNBOUNDED PRECEDING
                                    	AND CURRENT ROW) AS sum_rows

 

---

중복 제거 방법

-> DISTINCT, GROUP BY

---

칼럼기준 하나의 행만 가져오기 / 최상위 하나의 행만 조회하기

SELECT empno
     , ename
     , job
     , hiredate
  FROM (
         SELECT empno
              , ename
              , job
              , hiredate
              , ROW_NUMBER() OVER(PARTITION BY job ORDER BY hiredate DESC) AS rn
           FROM emp
       )
 WHERE rn = 1 
 ORDER BY job

 

그룹별로 최댓값만 가져오기

SELECT a.empno
     , a.ename
     , a.job
     , a.hiredate
  FROM emp a
 WHERE a.hiredate = (SELECT MAX(aa.hiredate)
                       FROM emp aa
                      WHERE aa.job = a.job)
 ORDER BY a.job

---

SUM(1) = COUNT(*)

-> 데이터가 존재하지 않을때, SUM(1) = NULL, COUNT(*) = 0

---

1. 최댓값 구하기

-- 코드를 입력하세요
SELECT DATETIME
FROM ANIMAL_INS
WHERE DATETIME = (SELECT MAX(DATETIME) FROM ANIMAL_INS)

 

2. 최솟값 구하기

-- 코드를 입력하세요
SELECT DATETIME
FROM ANIMAL_INS
WHERE DATETIME = (SELECT MIN(DATETIME) FROM ANIMAL_INS)

 

3. 동물 수 구하기

-- 코드를 입력하세요
SELECT COUNT(*)
FROM ANIMAL_INS

 

4. 중복 제거하기

-- 코드를 입력하세요
SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS
WHERE NAME IS NOT NULL;

 

#활용 (누적합계 만들기)

-> 팀별, 직종별, 직급별 누적합계 만들고, 그 중 가장 큰 값 도출