집계

• 집계 함수

SUM()
MAX()
MIN()
AVG()
COUNT()

ARRAY_AGG(any) -- 값들을 `{a, b, c, ...}` 꼴로 묶는다.

STRING_AGG(data: text, delimeter: text) or (bytea, bytea) -- 값들을 구분자로 연결하여 하나의 스트링을 만든다.
  - STRING_AGG(name, ', ')
  - STRING_AGG(DISTINCT name, ', ' ORDER BY name) -- 응용 : 중복을 제거하고 정렬한 뒤에 ', '로 연결한다.

• 조건 집계(예시: Pivot)

SELECT
       SUM(PRICE) FILTER ( WHERE year=2001 AND month=1 ) AS P200101
      ,SUM(PRICE) FILTER ( WHERE year=2001 AND month=2 ) AS P200102
      ,SUM(PRICE) FILTER ( WHERE year=2001 AND month=3 ) AS P200103
      ,SUM(PRICE) FILTER ( WHERE year=2002 AND month=1 ) AS P200201
      ,SUM(PRICE) FILTER ( WHERE year=2002 AND month=2 ) AS P200202
      ,SUM(PRICE) FILTER ( WHERE year=2002 AND month=3 ) AS P200203

      ,SUM(CASE WHEN (year=2002 AND month=3) THEN PRICE END)  AS P200203

FROM (VALUES
        (2001, 1, 100) ,(2001, 1, 100)
       ,(2001, 2, 200) ,(2001, 2, 200)
       ,(2001, 3, 300)
       ,(2002, 1, 600)
       ,(2002, 2, 700)
       ,(2002, 3, 800) ,(2002, 3, 800) ,(2002, 3, 800)
     ) AS TAB(Year, Month, Price)

Window

• 종류

ROW_NUMBER  ()     -- 행 번호

RANK        ()     -- 순위(중복 순위 행 개수만큼 다음 순위 합산)       -- OVER (ORDER BY 컬럼) 필수
DENSE_RANK  ()     -- 순위(중복 순위 행 개수 상관 없이 다음 순위는 +1)
PERCENT_RANK()     -- 전체에 대한 현재 순위의 누적 비율

CUME_DIST   ()     -- 전체 행 개수 대비 현재 파티션 내 행 개수의 비율(전체 : 100, 현재 파티션 : 25 => 0.25)

NTILE       (INT)  -- 해당 PARTITION 내에서 지정 개수만큼 분할하여, 분할 범위에 번호를 매긴다.(NTILE(4), 파티션 크기 12이면 3개씩 1, 2, 3, 4)

LAG         (컬럼, INT) -- 해당 파티션 내에서 이전 순서의 지정 컬럼 값을 찾는다. ORDER에 종속되어 동작한다.
LEAD        (컬럼, INT) -- 해당 파티션 내에서 다음 순서의 지정 컬럼 값을 찾는다. ORDER에 종속되어 동작한다.

FIRST_VALUE (컬럼)      -- 해당 PARTITION 내에서 지정 컬럼의 첫 번째 값을 찾는다. ORDER로 정렬할 수 있다.
LAST_VALUE  (컬럼)      -- 해당 PARTITION 내에서 지정 컬럼의  마지막 값을 찾는다.
NTH_VALUE   (컬럼, INT) -- 해당 PARTITION 내에서 지정 컬럼의   N번째 값을 찾는다. 해당 행의 순서가 N보다 앞서면 NULL이 된다.

• 예제

SELECT Name, Value

      ,ROW_NUMBER() OVER (ORDER BY NAME, VALUE)
      ,ROW_NUMBER() OVER (ORDER BY NAME, VALUE DESC)
      ,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY VALUE)

      ,MAX(VALUE) OVER (PARTITION BY NAME)
      ,MIN(VALUE) OVER (PARTITION BY NAME)

      ,SUM(VALUE) FILTER (WHERE VALUE >= 2) OVER ()
      ,SUM(VALUE) FILTER (WHERE VALUE >= 2) OVER (PARTITION BY NAME)

      ,NTILE(2) OVER ()
      ,NTILE(2) OVER (PARTITION BY NAME)

      ,ARRAY_AGG(value) OVER ()
      ,ARRAY_AGG(value) OVER (PARTITION BY NAME)
      ,ARRAY_AGG(value) OVER (PARTITION BY NAME ORDER BY VALUE)

FROM (VALUES
        ('A', 1)  , ('A', 2)  , ('A', 3),
        ('B', 1.1), ('B', 2.2), ('B', 3.3)
     ) AS TAB(Name, Value)

ORDER BY NAME, VALUE

• Window 정의 및 재사용

SELECT user_id
      ,deal_dt
      ,FIRST_VALUE(deal_dt) OVER (W) AS first_deal_dt
      ,LEAD(deal_dt, 1)     OVER (W) AS second_deal_dt
      ,ROW_NUMBER()         OVER (W) AS idx_PART
  FROM (VALUES
           (1, '2022-01-01', 10000)
          ,(1, '2022-03-01', 13000)
          ,(1, '2022-06-01', 15500)
          ,(2, '2022-06-01', 22000)
          ,(2, '2022-03-01', 20000)
          ,(2, '2022-05-01', 24000)
          ,(2, '2022-04-01', 22400)
       ) AS DEAL_RECORD(user_id, deal_dt, price)
WINDOW W AS (PARTITION BY user_id ORDER BY deal_dt)

값

Null인 값 대체 처리

COALESCE(____, ____)

문자열

문자열 대체

REPLACE('ABCABC', 'AB', '__')

날짜

추출