서로 다른 데이터 세분화로 집계하기

PostgreSQL에서 쿼리 성능 개선하기

Amy McCarty

Instructor

데이터 세분화 - 상세 수준

행을 고유하게 만듦
1개 이상의 열

Video_Games

id	game	first_yr
012	Grand Theft Auto	1997
234	Legend of Zelda	1986

Game_Platforms

game_id	platform	year
234	FCDS	1986
234	GameCube	2003
234	Wii	2006

다른 세분화 조인하기

Video_Games의 The Legend of Zelda 레코드가 Games_Platforms의 3개 레코드 모두와 연결됨.

SELECT g.id, g.game, g.first_yr
  , COUNT(p.platform) AS no_platforms
  , MAX(p.year) AS last_platform_yr
  , p.platform
FROM video_games g
INNER JOIN game_platforms p
  ON g.id = p.game_id
GROUP BY g.id, g.game, g.first_yr
, p.platform

다른 세분화 조인하기

출력

id	game	first_yr	no_platforms	last_platform_yr	platform
234	Legend of Zelda	1986	3	2006	FCDS
234	Legend of Zelda	1986	3	2006	GameCube
234	Legend of Zelda	1986	3	2006	Wii

세분화 변경 준비하기

Game_Platforms

game_id	platform	year
234	FCDS	1986
234	GameCube	2003
234	Wii	2006

세분화 변경 준비하기

SELECT game_id, platform, year
  , COUNT(platform) AS no_platforms
  , MAX(year) AS last_platform_yr
FROM game_platforms
GROUP BY game_id, platform, year

출력

game_id	platform	year	no_platforms	last_platform_yr
234	FCDS	1986	3	2006
234	GameCube	2003	3	2006
234	Wii	2006	3	2006

CTE 다시 보기

임시 결과가 있는 독립 쿼리
WITH 문

WITH cte AS
  ( SELECT * FROM tableA )

SELECT * FROM cte

조인 전 집계 가능

CTE 구원투수

WITH platforms_cte AS 
(  SELECT game_id, platform, year
      , COUNT(platform) AS no_platforms
      , MAX(year) AS last_platform_yr
   FROM game_platforms
   GROUP BY game_id, platform, year )

SELECT g.id, g.game, cte.no_platforms
  , cte.platform AS last_platform
FROM video_games g
INNER JOIN platforms_cte cte 
  ON g.id = cte.game_id
  AND cte.last_platform_yr = cte.year

CTE 구원투수

WITH platforms_cte AS 
(  SELECT game_id, platform, year
      , COUNT(platform) AS no_platforms
      , MAX(year) AS last_platform_yr
   FROM game_platforms
   GROUP BY game_id, platform, year )

SELECT g.id, g.game, cte.no_platforms
  , cte.platform AS last_platform
FROM video_games g
INNER JOIN platforms_cte cte 
  ON g.id = cte.game_id
  AND cte.last_platform_yr = cte.year

출력

id	game	no_platforms	last_platform
234	Legend of Zelda	3	Wii

조인 시 세분화 맞추기

반복·중복 없음
최소 필요 결과
이중 집계 방지

연습해 봅시다!

PostgreSQL에서 쿼리 성능 개선하기