Membangun kalkulasi kompleks

Pelaporan dengan SQL

Tyler Pernes

Learning & Development Consultant

Pendekatan

Fungsi window
Kalkulasi berlapis

Fungsi window

Merujuk ke baris lain dalam tabel.

Fungsi window

Merujuk ke baris lain dalam tabel.

Fungsi window

Merujuk ke baris lain dalam tabel.

Sintaks fungsi window

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

Sintaks fungsi window

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

Sintaks fungsi window

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

PARTITION BY = ruang lingkup kalkulasi
ORDER BY = urutan baris saat kalkulasi berjalan

Contoh fungsi window

Total medali perunggu

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

Contoh fungsi window

Medali perunggu per negara

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

Jenis fungsi window

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

Jenis fungsi window

LAG() dan LEAD()

Jenis fungsi window

LAG() dan LEAD()

Jenis fungsi window

ROW_NUMBER() dan RANK()

Fungsi window pada agregasi

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

Fungsi window pada agregasi

Kueri akhir

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

Fungsi window pada agregasi

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.

Kalkulasi berlapis

Mengagregasi agregasi yang ada
Memanfaatkan subkueri

Contoh kalkulasi berlapis

Langkah 1: Total medali perunggu per negara

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

Langkah 2: Ubah ke subkueri dan ambil nilai maksimum

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

Merencanakan kalkulasi kompleks

Urutan untuk fungsi window?
Dua agregasi dengan kalkulasi berlapis?

Ayo berlatih!

Pelaporan dengan SQL