Agregando com diferentes granularidades de dados

Melhorando o Desempenho de Consultas no PostgreSQL

Amy McCarty

Instructor

Granularidade dos dados - nível de detalhe

 

  • Torna a linha única
  • 1+ colunas

 

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
Melhorando o Desempenho de Consultas no PostgreSQL

Juntando diferentes granularidades

O registro de The Legend of Zelda na tabela Video_Games se liga aos 3 registros na tabela Games_Platforms.

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
Melhorando o Desempenho de Consultas no PostgreSQL

Juntando diferentes granularidades

 

Saída

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
Melhorando o Desempenho de Consultas no PostgreSQL

Preparando uma mudança de granularidade

 

Game_Platforms

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

 

Melhorando o Desempenho de Consultas no PostgreSQL

Preparando uma mudança de granularidade

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

Saída

game_id platform year no_platforms last_platform_yr
234 FCDS 1986 3 2006
234 GameCube 2003 3 2006
234 Wii 2006 3 2006
Melhorando o Desempenho de Consultas no PostgreSQL

Revisitando CTEs

  • Consulta independente com conjunto de resultados temporário
  • Declaração WITH
WITH cte AS
  ( SELECT * FROM tableA )

SELECT * FROM cte
  • Permite agregar antes de fazer JOIN
Melhorando o Desempenho de Consultas no PostgreSQL

CTEs para resolver

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
Melhorando o Desempenho de Consultas no PostgreSQL

CTEs para resolver

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

Saída

id game no_platforms last_platform
234 Legend of Zelda 3 Wii
Melhorando o Desempenho de Consultas no PostgreSQL

Ajustando a granularidade ao fazer JOIN

 

  • Sem repetições nem duplicatas
  • Só o necessário
  • Sem dupla contagem
Melhorando o Desempenho de Consultas no PostgreSQL

Vamos praticar!

Melhorando o Desempenho de Consultas no PostgreSQL

Preparing Video For Download...