Membandingkan grup

Pelaporan dengan SQL

Tyler Pernes

Learning & Development Consultant

Jenis metrik

Metrik volume
Metrik efisiensi

Metrik volume

Bertambah seiring ukuran

Metrik volume

Bertambah seiring ukuran

Metrik volume

Bertambah seiring ukuran

Perhitungan persentase dari total

tabel basketball_points
+----------+------------+---------+
| team_id  | player_id  | points  |
|----------|------------|---------|
| 1        | 482        | 92      |
| 1        | 165        | 47      |
| 2        | 222        | 64      |
+----------+------------+---------+

SELECT team_id, SUM(points) AS points
FROM basketball_points
GROUP BY team_id;

Perhitungan persentase dari total

tabel basketball_points
+----------+------------+---------+
| team_id  | player_id  | points  |
|----------|------------|---------|
| 1        | 482        | 92      |
| 1        | 165        | 47      |
| 2        | 222        | 64      |
+----------+------------+---------+

+----------+---------+
| team_id  | points  |
|----------|---------|
| 1        | 782     |
| 2        | 625     |
| 3        | 487     |
| 4        | 398     |
+----------+---------+

Perhitungan persentase dari total

Langkah 1: Hitung total

SELECT 
    team_id, 
    SUM(points) AS points
    SUM(points) OVER () AS total_points
FROM basketball_points
GROUP BY team_id;

Langkah 2: Hitung persentase dari total

SELECT 
    team_id, 
    SUM(points) AS points
    SUM(points) / SUM(points) OVER () AS perc_of_total
FROM basketball_points
GROUP BY team_id;

Perhitungan persentase dari total

Hasil:

+----------+---------+---------------+
| team_id  | points  | perc_of_total |
|----------|---------|---------------|
| 1        | 782     | .34           |
| 2        | 625     | .27           |
| 3        | 487     | .21           |
| 4        | 398     | .17           |
+----------+---------+---------------+

Perhitungan persentase dari total

Persentase poin yang dicetak per pemain untuk tiap tim:

SELECT 
    player_id,
    team_id,
    SUM(points) AS points
    SUM(points) / (SUM(points) OVER (PARTITION BY team_id)) AS perc_of_team
FROM basketball_points
GROUP BY player_id, team_id;

Perhitungan persentase dari total

Hasil:

+-----------+---------+---------+--------------+
| player_id | team_id | points  | perc_of_team |
|-----------|---------|---------|--------------|
| 482       | 1       | 92      | .12          |
| 165       | 1       | 47      | .06          |
| 222       | 2       | 64      | .10          |
+-----------+---------+---------+--------------+

Metrik efisiensi

Tidak bergantung pada ukuran
Biasanya berupa rasio

Metrik efisiensi

Tidak bergantung pada ukuran
Biasanya berupa rasio

Metrik efisiensi

Tidak bergantung pada ukuran
Biasanya berupa rasio

Indeks kinerja

Bandingkan kinerja dengan patokan
Patokan biasanya rata-rata atau median

Indeks kinerja

tabel basketball_summary
+----------+--------+---------+
| team_id  | games  | points  |
|----------|--------|---------|
| 1        | 24     | 782     |
| 2        | 20     | 625     |
| 3        | 12     | 487     |
+----------+--------+---------+

Kinerja poin per game?

Indeks kinerja

Langkah 1: poin per game tiap tim

SELECT 
    team_id, 
    points/games AS team_ppg
FROM basketball_summary;

Langkah 2: poin per game liga

SELECT 
    team_id, 
    points/games AS team_ppg,
    SUM(points) OVER () / SUM(games) OVER () AS league_ppg
FROM basketball_summary;

Indeks kinerja

Langkah 3: indeks kinerja

SELECT 
    team_id, 
    points/games AS team_ppg,
    SUM(points) OVER () / SUM(games) OVER () AS league_ppg,
    (points/games) 
    / 
    (SUM(points) OVER () / SUM(games) OVER ()) AS perf_index
FROM basketball_summary;

Indeks kinerja

Langkah 3: indeks kinerja

+----------+----------+------------+-------------+
| team_id  | team_ppg | league_ppg | perf_index  |    
|----------|----------|------------|-------------|
| 1        | 32.6     | 33.8       | 0.96        |
| 2        | 31.3     | 33.8       | 0.92        |
| 3        | 40.6     | 33.8       | 1.20        |
+----------+----------+------------+-------------+

¹ Hasilnya menunjukkan tim tiga mencetak 20% lebih banyak poin daripada rata-rata liga.

Waktunya kueri!

Pelaporan dengan SQL