Mencari Nilai Tertinggi Pada Setiap Grup

Pada artikel ini kita hanya akan membahas satu solusi yaitu menggunakan self joinsederhana dan tidak menjabarkan alternatif lain. Database server yang digunakan untuk contoh adalah MariaDB/MySQL. Tetapi harusnya dapat diaplikasikan ke semua RDBMS karena menggunakan standard SQL. 

Contoh Masalah untuk Group-wise Maximum

"Tampilkan daftar pencetak gol terbanyak untuk sebuah klub, urutkan berdasarkan jumlah gol terbanyak dan kelompokkan berdasarkan nama klub."


Berikut ini adalah data top skor Liga Inggris musim 2015–2016. Nama tabel yang digunakan adalah top_scores.

shell> SELECT * FROM top_scores;
+----+------------------+-------------------+-----------+-------+
| id | player           | club              | season    | goals |
+----+------------------+-------------------+-----------+-------+
|  1 | Alexis Sanchez   | Arsenal           | 2015-2016 |    13 |
|  2 | Troy Deeney      | Watford           | 2015-2016 |    13 |
|  3 | Romelu Lukaku    | Everton           | 2015-2016 |    18 |
|  4 | Harry Kane       | Tottenham Hotspur | 2015-2016 |    25 |
|  5 | Oliver Giroud    | Arsenal           | 2015-2016 |    16 |
|  6 | Anthony Martial  | Manchester United | 2015-2016 |    11 |
|  7 | Riyad Mahrez     | Leicester City    | 2015-2016 |    17 |
|  8 | Gylfi Sigurdsson | Swansea City      | 2015-2016 |    11 |
|  9 | Jamie Vardy      | Leicester City    | 2015-2016 |    24 |
| 10 | Odion Ighalo     | Watford           | 2015-2016 |    15 |
| 11 | André Ayew       | Swansea City      | 2015-2016 |    12 |
| 12 | Diego Costa      | Chelsea           | 2015-2016 |    12 |

+----+------------------+-------------------+-----------+-------+


Percobaan Query yang Gagal

Sepertinya mudah untuk mendapatkan hasil yang diharapkan dengan hanya memanfaatkan group by dan order by. Mari kita coba.

SELECT id, player, club, season, MAX(goals) AS goals
FROM top_scores GROUP BY club ORDER BY goals DESC;

Hasilnya adalah sebagai berikut.

+----+------------------+-------------------+-----------+-------+
| id | player           | club              | season    | goals |
+----+------------------+-------------------+-----------+-------+
|  4 | Harry Kane       | Tottenham Hotspur | 2015-2016 |    25 |
|  7 | Riyad Mahrez     | Leicester City    | 2015-2016 |    24 |
|  3 | Romelu Lukaku    | Everton           | 2015-2016 |    18 |
|  1 | Alexis Sanchez   | Arsenal           | 2015-2016 |    16 |
|  2 | Troy Deeney      | Watford           | 2015-2016 |    15 |
|  8 | Gylfi Sigurdsson | Swansea City      | 2015-2016 |    12 |
| 12 | Diego Costa      | Chelsea           | 2015-2016 |    12 |
|  6 | Anthony Martial  | Manchester United | 2015-2016 |    11 |
+----+------------------+-------------------+-----------+-------+
8 rows in set (0.00 sec)

Sepertinya sesuai yang diharapkan. Tunggu dulu! Coba lihat baris Riyad Mahrez (Leicester City), Alexis Sanchez (Arsenal) dan Gylfi Sigurdsson (Swansea City) mereka harusnya tidak masuk ke dalam hasil. Karena yang kita inginkan hanyalah pencetak gol terbanyak. Harusnya yang masuk adalah nama berikut Jamie Vardy, Oliver Giroud dan André Ayew.

Solusi untuk Group-wise Maximum

Pada hampir setiap masalah cara yang menurut saya efektif adalah dengan memecahnya menjadi bagian-bagian yang sederhana. Untuk kasus ini langkah pertama adalah kita akan mendapatkan goal terbanyak dikelompokkan berdasarkan nama klub terlebih dahulu.

SELECT club, MAX(goals) AS max_goals FROM top_scores GROUP BY club;

Pada query diatas kita tidak memperdulikan urutan, hasil dari query diatas adalah seperti dibawah.

+-------------------+-----------+
| club              | max_goals |
+-------------------+-----------+
| Arsenal           |        16 |
| Chelsea           |        12 |
| Everton           |        18 |
| Leicester City    |        24 |
| Manchester United |        11 |
| Swansea City      |        12 |
| Tottenham Hotspur |        25 |
| Watford           |        15 |
+-------------------+-----------+
8 rows in set (0.00 sec)

Langkah berikutnya adalah kita akan melakukan self join tabel top_scores dengan hasil query sebelumnya. Tujuannya adalah kita hanya mencocokkan baris yang mempunyai nama klub dan jumlah gol yang sama.

SELECT ts1.* FROM top_scores AS ts1
INNER JOIN (
  SELECT club, MAX(goals) AS max_goals FROM top_scores
  GROUP BY club
) AS ts2 ON ts2.club=ts1.club AND ts1.goals=ts2.max_goals
ORDER BY ts1.goals DESC;

Hasil dari query diatas harusnya telah sama dengan yang diharapkan.

+----+-----------------+-------------------+-----------+-------+
| id | player          | club              | season    | goals |
+----+-----------------+-------------------+-----------+-------+
|  4 | Harry Kane      | Tottenham Hotspur | 2015-2016 |    25 |
|  9 | Jamie Vardy     | Leicester City    | 2015-2016 |    24 |
|  3 | Romelu Lukaku   | Everton           | 2015-2016 |    18 |
|  5 | Oliver Giroud   | Arsenal           | 2015-2016 |    16 |
| 10 | Odion Ighalo    | Watford           | 2015-2016 |    15 |
| 11 | André Ayew      | Swansea City      | 2015-2016 |    12 |
| 12 | Diego Costa     | Chelsea           | 2015-2016 |    12 |
|  6 | Anthony Martial | Manchester United | 2015-2016 |    11 |

+----+-----------------+-------------------+-----------+-------+ 

Komentar

Posting Komentar