1. 분석 함수(Analytic Function)

• 분석 함수란?

  • 테이블의 행을 특정 그룹별로 그루핑하여 편리하게 함수를 적용하는 것

• Sample 데이터 생성 SQL

-- 1. PRODUCT_GROUP 테이블 생성
CREATE TABLE PRODUCT_GROUP (
GROUP_ID SERIAL PRIMARY KEY,
GROUP_NAME VARCHAR (255) NOT NULL
);

-- 1.1 PRODUCT_GROUP 데이터 삽입
INSERT INTO PRODUCT_GROUP (GROUP_NAME)
VALUES
('Smartphone')
, ('Laptop')
, ('Tablet');

SELECT * FROM PRODUCT_GROUP

-- 2. PRODUCT 테이블 생성
CREATE TABLE PRODUCT (
PRODUCT_ID SERIAL PRIMARY KEY
, PRODUCT_NAME VARCHAR (255) NOT NULL
, PRICE DECIMAL (11, 2)
, GROUP_ID INT NOT NULL
, FOREIGN KEY (GROUP_ID)
REFERENCES PRODUCT_GROUP (GROUP_ID)
);

-- 2.1 PRODUCT 데이터 삽입
INSERT INTO PRODUCT (PRODUCT_NAME,
GROUP_ID,PRICE)
VALUES
('Microsoft Lumia', 1, 200)
, ('HTC One', 1, 400)
, ('Nexus', 1, 500)
, ('iPhone', 1, 900)
, ('HP Elite', 2, 1200)
, ('Lenovo Thinkpad', 2, 700)
, ('Sony VAIO', 2, 700)
, ('Dell Vostro', 2, 800)
, ('iPad', 3, 700)
, ('Kindle Fire', 3, 150)
, ('Samsung Galaxy Tab', 3, 200);

SELECT * FROM PRODUCT

2. OVER절

• OVER절?
  • GROUP BY의 한계를 개선하기 위해 나온 함수
  • 집계 함수의 결과를 테이블에 바로 보여줌

SELECT *
      , COUNT(*) OVER()
FROM PRODUCT

3. OVER ... PARTITION BY 절

• PARTITION BY 절?
  • GROUP BY와 비슷한 기능으로 OVER절, 분석함수와 같이 사용
  • 특정 칼럼으로 그룹핑 하여 앞의 집계 함수를 그룹별로 적용

SELECT
    A.PRODUCT_NAME
    , A.PRICE
    , B.GROUP_NAME
    , AVG (A.PRICE) OVER (PARTITION BY B.GROUP_NAME)
FROM
PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

4. ROW _NUMBER, RANK, DENSE_RANK 함수

• 그룹에서 특정 컬럼의 순위를 구하는 함수

  함수명	내용
  ROW_NUMBER	- 같은 순위면 중복 상관 없이 순서대로 순위 구함 - EX) 10,20,20,30 일 때 -> 순위 : 1,2,3,4
  RANK	- 같은 순위면 다음 순위로 건너뜀 - EX) 10,20,20,30 일 때 -> 순위 : 1,2,2,4
  DENSE_RANK	- 같은 순위면 다음 순위로 건너뛰지 않음 -EX) 10,20,20,30 일 때 -> 순위 : 1,1,2,3

• ROW_NUMBER

SELECT
    A.PRODUCT_NAME
    , B.GROUP_NAME
    , A.PRICE
    , ROW_NUMBER () OVER ( PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

• RANK

SELECT
    A.PRODUCT_NAME
    , B.GROUP_NAME
    , A.PRICE
    , RANK () OVER ( PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

• DENSE_RANK

SELECT
    A.PRODUCT_NAME
    , B.GROUP_NAME
    , A.PRICE
    , DENSE_RANK () OVER ( PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

5. FIRST _VALUE , LAST_VALUE 함수

• 그룹안에서 특정컬럼의 첫 번째 값 또는 마지막 값을 구하는 함수

• FIRST_VALUE(컬럼명)

  • 그룹안에서 컬럼명의 첫 번째 값을 구함

SELECT
  A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE
  , FIRST_VALUE (A.PRICE) over (
      PARTITION BY B.GROUP_NAME ORDER BY A.PRICE
      ) AS LOWEST_PRICE_PER_GROUP
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

• LAST_VALUE(컬럼명)

  • 그룹안에서 컬럼명의 마지막 값을 구함

  • 범위 지정 옵션 : 옵션 범위 기준 마지막값을 구함

    옵션	내용
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	첫 번째 로우부터 마지막 로우
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	첫 번째 로우부터 현재 로우

SELECT
  A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE
  , LAST_VALUE (A.PRICE) OVER(
    PARTITION BY B.GROUP_NAME ORDER BY A.PRICE
    RANGE BETWEEN UNBOUNDED PRECEDING
    AND UNBOUNDED FOLLOWING
  ) AS HIGHEST_PRICE_PER_GROUP
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

6. LAG, LEAD 함수

• 그룹안에서 특정 컬럼의 이전 행 또는 다음 행 값을 구함

• LAG

SELECT A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE
,             LAG (A.PRICE, 1) OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE ) AS PREV_PRICE
, A.PRICE - LAG (A.PRICE, 1) OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE ) AS CUR_PREV_DIFF
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

• LEAD

SELECT A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE
,             LEAD (A.PRICE, 1) OVER ( PARTITION BY B.GROUP_NAME ORDER BY A.PRICE ) AS NEXT_PRICE
, A.PRICE - LEAD (A.PRICE, 1) OVER ( PARTITION BY B.GROUP_NAME ORDER BY A.PRICE )AS CUR_NEXT_DIFF
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

# 참고

• 모두를 위한 SQL/DB 올인원 패키지 Online(패스트 캠퍼스)