Hàm cửa sổ

Xử lý dữ liệu trong SQL

Mona Khalil

Data Scientist, Greenhouse Software

Làm việc với giá trị tổng hợp

Yêu cầu dùng GROUP BY với tất cả cột không tổng hợp

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

Giới thiệu hàm cửa sổ

Tính toán trên bộ kết quả đã tạo (một cửa sổ)
Phép tính tổng hợp
- Tương tự truy vấn con trong SELECT
- Tổng chạy, xếp hạng, trung bình trượt

Hàm cửa sổ là gì?

Mỗi trận 2011/2012 ghi bao nhiêu bàn và so với trung bình thế nào?

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           |

Hàm cửa sổ là gì?

Mỗi trận 2011/2012 ghi bao nhiêu bàn và so với trung bình thế nào?

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           |

Tạo RANK

Thứ hạng trận đấu theo số bàn thắng?

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     |

Tạo RANK

Thứ hạng trận đấu theo số bàn thắng?

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          |

Tạo RANK

Thứ hạng trận đấu theo số bàn thắng?

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          |

Khác biệt chính

Xử lý sau mọi phần của truy vấn trừ ORDER BY
- Dùng thông tin từ kết quả thay vì từ dữ liệu gốc
Có trong PostgreSQL, Oracle, MySQL, SQL Server...
- ...nhưng KHÔNG có trong SQLite

Laten we oefenen!

Xử lý dữ liệu trong SQL