การสร้างการคำนวณที่ซับซ้อน

การรายงานผลด้วย SQL

Tyler Pernes

Learning & Development Consultant

แนวทาง

Window functions
การคำนวณแบบซ้อนชั้น

Window functions

อ้างอิงแถวอื่นในตาราง

Window functions

อ้างอิงแถวอื่นในตาราง

Window functions

อ้างอิงแถวอื่นในตาราง

ไวยากรณ์ของ window function

SUM(value) OVER (PARTITION BY field ORDER BY field)

ไวยากรณ์ของ window function

SUM(value) OVER (PARTITION BY field ORDER BY field)

ไวยากรณ์ของ window function

SUM(value) OVER (PARTITION BY field ORDER BY field)

PARTITION BY = ขอบเขตของการคำนวณ
ORDER BY = ลำดับของแถวขณะคำนวณ

ตัวอย่าง window function

เหรียญทองแดงทั้งหมด

SELECT 
    country_id, 
    athlete_id, 
    SUM(bronze) OVER () AS total_bronze
FROM summer_games;

+-------------+-------------+---------------+
| country_id  | athlete_id  | total_bronze  |
|-------------|-------------|---------------|
| 11          | 77505       | 141           |
| 11          | 11673       | 141           |
| 14          | 85554       | 141           |
| 14          | 76433       | 141           |
+-------------+-------------+---------------+

ตัวอย่าง window function

เหรียญทองแดงรายประเทศ

SELECT 
    country_id, 
    athlete_id, 
    SUM(bronze) OVER (PARTITION BY country_id) AS total_bronze
FROM summer_games

+-------------+-------------+---------------+
| country_id  | athlete_id  | total_bronze  |
|-------------|-------------|---------------|
| 11          | 77505       | 12            |
| 11          | 11673       | 12            |
| 14          | 85554       | 5             |
| 14          | 76433       | 5             |
+-------------+-------------+---------------+

ประเภทของ window functions

SUM()
AVG()
MIN()
MAX()

ประเภทของ window functions

LAG() and LEAD()

ประเภทของ window functions

LAG() and LEAD()

ประเภทของ window functions

ROW_NUMBER() and RANK()

Window function กับการ aggregate

original_table
+----------+-----------+---------+ 
| team_id  | player_id | points  |
|----------|-----------|---------|    
| 1        | 4123      | 3       |
| 1        | 5231      | 6       |
| 2        | 8271      | 5       |
+----------+-----------+---------+

desired_report
+----------+-------------+---------------+ 
| team_id  | team_points | league_points |
|----------|-------------|---------------|    
| 1        | 9           | 43            |
| 2        | 12          | 43            |
| 3        | 22          | 43            |
+----------+-------------+---------------+

Window function กับการ aggregate

คิวรีสุดท้าย

SELECT
    team_id,
    SUM(points) AS team_points,
    SUM(SUM(points)) OVER () AS league_points
FROM original_table
GROUP BY team_id;

Window function กับการ aggregate

SELECT
    team_id,
    SUM(points) AS team_points,
    SUM(points) OVER () AS league_points
FROM original_table
GROUP BY team_id;

ERROR: points must be an aggregation or appear in a GROUP BY statement.

การคำนวณแบบซ้อนชั้น

นำ aggregate มา aggregate ซ้อนกัน
ใช้ subquery

ตัวอย่างการคำนวณแบบซ้อนชั้น

ขั้นที่ 1: รวมเหรียญทองแดงแต่ละประเทศ

SELECT country_id, SUM(bronze) as bronze_medals
FROM summer_games
GROUP BY country_id;

ขั้นที่ 2: แปลงเป็น subquery แล้วหาค่าสูงสุด

SELECT MAX(bronze_medals)
FROM
  (SELECT country_id, SUM(bronze) as bronze_medals
  FROM summer_games
  GROUP BY country_id) AS subquery;

การวางแผนการคำนวณที่ซับซ้อน

ต้องการกำหนดลำดับสำหรับ window function หรือไม่?
มีการ aggregate สองชั้นที่ต้องใช้การคำนวณซ้อนกันหรือไม่?

มาฝึกกันเถอะ!

การรายงานผลด้วย SQL