การรวมข้อมูลด้วย granularity ที่แตกต่างกัน
การเพิ่มประสิทธิภาพคิวรีใน PostgreSQL
Amy McCarty
Instructor
Data granularity - ระดับความละเอียดของข้อมูล
- ทำให้แต่ละแถวไม่ซ้ำกัน
- ใช้ 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 |
การ JOIN ข้อมูลที่มี granularity ต่างกัน
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
การ JOIN ข้อมูลที่มี granularity ต่างกัน
Output
| 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
Output
| 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
- คิวรีแบบ standalone ที่มีชุดผลลัพธ์ชั่วคราว
- ใช้คำสั่ง WITH
WITH cte AS
( SELECT * FROM tableA )
SELECT * FROM cte
- รวมข้อมูลก่อน JOIN ได้
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
Output
| id | game | no_platforms | last_platform |
|---|---|---|---|
| 234 | Legend of Zelda | 3 | Wii |
การจับคู่ granularity ของข้อมูลเมื่อ JOIN
- ไม่มีข้อมูลซ้ำหรือ duplicate
- ได้ผลลัพธ์เท่าที่จำเป็น
- ไม่นับข้อมูลซ้ำซ้อน
มาฝึกกันเถอะ!
การเพิ่มประสิทธิภาพคิวรีใน PostgreSQL
