Агрегування з різною деталізацією даних
Покращення продуктивності запитів у 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 |
Об'єднання різних гранулярностей
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
