Window Functions

การจัดการข้อมูลใน SQL

Mona Khalil

Data Scientist, Greenhouse Software

การทำงานกับค่า aggregate

ต้องใช้ GROUP BY กับคอลัมน์ที่ไม่ใช่ aggregate ทั้งหมด

SELECT
  country_id,
  season,
  date,
  AVG(home_goal) AS avg_home
FROM match
GROUP BY country_id;

ERROR: column "match.season" must appear in the GROUP BY 
clause or be used in an aggregate function

รู้จัก Window Functions!

คำนวณบน result set ที่สร้างขึ้นแล้ว (window)
การคำนวณแบบ aggregate
- คล้ายกับ subquery ใน SELECT
- ผลรวมสะสม, การจัดอันดับ, ค่าเฉลี่ยเคลื่อนที่

Window Function คืออะไร?

แต่ละแมตช์ในฤดูกาล 2011/2012 มีการทำประตูกี่ครั้ง และเทียบกับค่าเฉลี่ยอย่างไร?

SELECT
  date,
  (home_goal + away_goal) AS goals,
  (SELECT AVG(home_goal + away_goal) 
     FROM match
     WHERE season = '2011/2012') AS overall_avg
FROM match
WHERE season = '2011/2012';

| date       | goals | overall_avg       |
|------------|-------|-------------------|
| 2011-07-29 | 3     | 2.71646           |
| 2011-07-30 | 2     | 2.71646           |
| 2011-07-30 | 4     | 2.71646           |
| 2011-07-30 | 1     | 2.71646           |

Window Function คืออะไร?

แต่ละแมตช์ในฤดูกาล 2011/2012 มีการทำประตูกี่ครั้ง และเทียบกับค่าเฉลี่ยอย่างไร?

SELECT
    date,
    (home_goal + away_goal) AS goals,
    AVG(home_goal + away_goal) OVER() AS overall_avg
FROM match
WHERE season = '2011/2012';

| date       | goals | overall_avg       |
|------------|-------|-------------------|
| 2011-07-29 | 3     | 2.71646           |
| 2011-07-30 | 2     | 2.71646           |
| 2011-07-30 | 4     | 2.71646           |
| 2011-07-30 | 1     | 2.71646           |

สร้าง RANK

แมตช์ต่างๆ มีอันดับอย่างไรตามจำนวนประตูที่ทำได้?

SELECT
    date,
    (home_goal + away_goal) AS goals
FROM match
WHERE season = '2011/2012';

| date       | goals |
|------------|-------|
| 2011-07-29 | 3     |
| 2011-07-30 | 2     |
| 2011-07-30 | 4     |
| 2011-07-30 | 1     |

สร้าง RANK

แมตช์ต่างๆ มีอันดับอย่างไรตามจำนวนประตูที่ทำได้?

SELECT
    date,
    (home_goal + away_goal) AS goals,
    RANK() OVER(ORDER BY home_goal + away_goal) AS goals_rank
FROM match
WHERE season = '2011/2012';

| date       | goals | goals_rank |
|------------|-------|------------|
| 2012-04-28 | 0     | 1          |
| 2011-12-26 | 0     | 1          |
| 2011-09-10 | 0     | 1          |
| 2011-08-27 | 0     | 1          |
| 2012-01-07 | 0     | 1          |

สร้าง RANK

แมตช์ต่างๆ มีอันดับอย่างไรตามจำนวนประตูที่ทำได้?

SELECT
    date,
    (home_goal + away_goal) AS goals,
    RANK() OVER(ORDER BY home_goal + away_goal DESC) AS goals_rank
FROM match
WHERE season = '2011/2012';

| date       | goals | goals_rank |
|------------|-------|------------|
| 2011-11-06 | 10    | 1          |
| 2011-08-28 | 10    | 1          |
| 2012-05-12 | 9     | 3          |
| 2012-02-12 | 9     | 3          |
| 2012-03-09 | 9     | 3          |

ความแตกต่างที่สำคัญ

ประมวลผล หลังจาก ทุกส่วนของคิวรียกเว้น ORDER BY
- ใช้ข้อมูลจาก result set ไม่ใช่จากฐานข้อมูลโดยตรง
รองรับใน PostgreSQL, Oracle, MySQL, SQL Server...
- ...แต่ ไม่รองรับ SQLite

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

การจัดการข้อมูลใน SQL