[SQL] MySQL window 함수 실습

0. 목표

 - MySQL의 window 함수 사용

1. 실습

 1) 실습용 테이블 생성

CREATE TABLE pbj_db.window_test
(
    year INT NOT NULL,
    country VARCHAR(32) NOT NULL,
    product VARCHAR(32) NOT NULL,
    profit INT NOT NULL
) ENGINE = INNODB;

 2) 데이터 삽입

INSERT INTO pbj_db.window_test(year, country, product, profit) VALUES(2000, 'Finland', 'Computer', 1500);
INSERT INTO pbj_db.window_test(year, country, product, profit) VALUES(2000, 'Finland', 'Phone', 100);
INSERT INTO pbj_db.window_test(year, country, product, profit) VALUES(2001, 'Finland', 'Phone', 10);
INSERT INTO pbj_db.window_test(year, country, product, profit) VALUES(2000, 'India', 'Calculator', 75);
INSERT INTO pbj_db.window_test(year, country, product, profit) VALUES(2000, 'India', 'Computer', 1200);
INSERT INTO pbj_db.window_test(year, country, product, profit) VALUES(2001, 'India', 'Calculator', 75);
INSERT INTO pbj_db.window_test(year, country, product, profit) VALUES(2000, 'USA', 'Calculator', 75);
INSERT INTO pbj_db.window_test(year, country, product, profit) VALUES(2000, 'USA', 'Computer', 1500);
INSERT INTO pbj_db.window_test(year, country, product, profit) VALUES(2001, 'USA', 'Calculator', 50);
INSERT INTO pbj_db.window_test(year, country, product, profit) VALUES(2001, 'USA', 'Computer', 1200);
INSERT INTO pbj_db.window_test(year, country, product, profit) VALUES(2001, 'USA', 'TV', 100);
INSERT INTO pbj_db.window_test(year, country, product, profit) VALUES(2001, 'USA', 'TV', 150);

 3) 데이터 확인

SELECT * FROM pbj_db.window_test;

  - 결과

 4) window 함수 사용

SELECT year, country, product, profit,
	SUM(profit) OVER() AS total_profit,
    SUM(profit) OVER(PARTITION BY country) AS country_profit,
    FIRST_VALUE(profit) OVER(PARTITION BY country) AS country_first,
	LAST_VALUE(profit) OVER(PARTITION BY country) AS country_last
FROM pbj_db.window_test
ORDER BY country, year, product, profit;

  - 결과

   (1) 전체의 합이 total_profit 에 저장
   (2) country 별 합이 country_profit 에 저장

   (3) country의 첫 값이 country_first 에 저장

   (4) country의 마지막 값이 country_last에 저장

 5) 서브쿼리 사용을 통한 비교

  - 네이버 예제 쿼리는 틀린 거 같음

SELECT f.year, f.country, f.product, f.profit,
	(SELECT
		SUM(s1.profit)
	FROM pbj_db.window_test as s1
    ) AS total_profit,
    (SELECT
		SUM(s2.profit)
	FROM pbj_db.window_test as s2
    WHERE s2.country = f.country
    ) AS country_profit
FROM pbj_db.window_test as f
ORDER BY country, year, product, profit;

 - 결과

2. 참고

 - 네이버 클라우드 플랫폼 블로그