Xây dựng phép tính phức tạp

Lập báo cáo trong SQL

Tyler Pernes

Learning & Development Consultant

Cách tiếp cận

Hàm cửa sổ
Phép tính phân lớp

Hàm cửa sổ

Tham chiếu đến các dòng khác trong bảng.

Hàm cửa sổ

Tham chiếu đến các dòng khác trong bảng.

Hàm cửa sổ

Tham chiếu đến các dòng khác trong bảng.

Cú pháp hàm cửa sổ

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

Cú pháp hàm cửa sổ

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

Cú pháp hàm cửa sổ

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

PARTITION BY = phạm vi tính toán
ORDER BY = thứ tự dòng khi chạy phép tính

Ví dụ về hàm cửa sổ

Tổng huy chương đồng

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           |
+-------------+-------------+---------------+

Ví dụ về hàm cửa sổ

Huy chương đồng theo quốc gia

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             |
+-------------+-------------+---------------+

Các loại hàm cửa sổ

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

Các loại hàm cửa sổ

LAG() và LEAD()

Các loại hàm cửa sổ

LAG() và LEAD()

Các loại hàm cửa sổ

ROW_NUMBER() và RANK()

Hàm cửa sổ trên phép tổng hợp

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            |
+----------+-------------+---------------+

Hàm cửa sổ trên phép tổng hợp

Truy vấn cuối

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

Hàm cửa sổ trên phép tổng hợp

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

ERROR: points phải là một phép tổng hợp hoặc xuất hiện trong mệnh đề GROUP BY.

Phép tính phân lớp

Tổng hợp trên một tổng hợp có sẵn
Dùng truy vấn con

Ví dụ phép tính phân lớp

Bước 1: Tổng huy chương đồng theo quốc gia

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

Bước 2: Chuyển thành truy vấn con và lấy giá trị lớn nhất

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

Lập kế hoạch phép tính phức tạp

Có cần sắp xếp cho hàm cửa sổ?
Hai phép tổng hợp với tính toán phân lớp?

Let's practice!

Lập báo cáo trong SQL