अलग-अलग डेटा granularities के साथ aggregating
PostgreSQL में क्वेरी प्रदर्शन सुधारना
Amy McCarty
Instructor
डेटा granularity - विवरण का स्तर
- एक row को unique बनाता है
- 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 |
अलग granularities को join करना
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
अलग granularities को join करना
आउटपुट
| 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 |
Granularity बदलने की तैयारी
Game_Platforms
| game_id | platform | year |
|---|---|---|
| 234 | FCDS | 1986 |
| 234 | GameCube | 2003 |
| 234 | Wii | 2006 |
Granularity बदलने की तैयारी
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 |
CTEs पर फिर नज़र
- अस्थायी results set वाली standalone क्वेरी
- WITH स्टेटमेंट
WITH cte AS
( SELECT * FROM tableA )
SELECT * FROM cte
- join से पहले aggregation करने देता है
CTEs मददगार साबित होते हैं
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
CTEs मददगार साबित होते हैं
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 |
Join करते समय data granularity मिलाएँ
- कोई repeat या duplicate नहीं
- न्यूनतम जरूरी results
- double counting नहीं
अभ्यास करते हैं!
PostgreSQL में क्वेरी प्रदर्शन सुधारना
