Penggabungan Untuk Mencari Nilai Minimum dan Maksimum Group

Selanjutnya disini kita akan belajar untuk mencari rekaman pada tiap grup didalam sebuah tabel yang memuat nilai maksimun atau minimum pda kolom tertentu.

Lihat daftar isi : Menggunakan Multi Tabel - MySQL Dasar #72

Dimisalkan Anda ingin mengetahui lukisan paling mahal untuk setiap seniman Maka solusinya adalah dengan membuat sebuah tabel temporer untuk menampung nilai maksimun atau nilai minimun tiap grup, kemudian menggabungkan tabel temporer tersebut dengan tabel awal untuk mencari rekaman yang cocok untuk tiap grup.

Sebagai contoh, Anda dapat menggunakan MAX(populasi) untuk mencari populasi propinsi terbesar yang ada pada tabel propinsi, tetapi Anda juga ingin mengetahui propinsi mana yang memiliki populasi sebesar itu. Seperti yang ditunjukan, salah satu cara untuk menyelesaikan permasalahan ini adalah dengan menggunakan variabel SQL

MariaDB [root93]> SELECT @maks := MAX(populasi) FROM propinsi;
+------------------------+
| @maks := MAX(populasi) |
+------------------------+
|              107383746 |
+------------------------+
1 row in set (0.001 sec)

MariaDB [root93]> SELECT * FROM propinsi WHERE populasi = @maks;
+----------------+-----------+------------+-----------+
| nama           | singkatan | pendirian  | populasi  |
+----------------+-----------+------------+-----------+
| Sumatera Barat | SUMBAR    | 1719-05-25 | 107383746 |
+----------------+-----------+------------+-----------+
1 row in set (0.001 sec)

Cara lain untuk menjawab pertanyaan ini adalah dengan menggunakan penggabungan. Pertama, untuk memilih nilai populasi maksimum ke dalam sebuah tabel temporer adalah sebagai berikut :

MariaDB [root93]> CREATE TABLE tmp SELECT MAX(populasi) AS popmaks
    -> FROM propinsi;
Query OK, 1 row affected (0.270 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [root93]> SELECT propinsi.* FROM propinsi,tmp
    -> WHERE propinsi.populasi=tmp.popmaks;
+----------------+-----------+------------+-----------+
| nama           | singkatan | pendirian  | populasi  |
+----------------+-----------+------------+-----------+
| Sumatera Barat | SUMBAR    | 1719-05-25 | 107383746 |
+----------------+-----------+------------+-----------+
1 row in set (0.001 sec)

Dengan menerapkan teknik – teknik ini pada tabel seniman dan lukisan, kita dapat menjawab pertanyaan seperti “Lukisan apa yang paling mahal, dan siapa yang melukisnya ?” Untuk menggunakan variabel SQL, Anda menyimpan harga tertinggi di dalamnya, kemudian menggunakan variabel itu untuk mengidentifikasi rekaman yang memuat harga tertinggi tersebut sehingga Anda dpat membaca kolom – kolom lain :

MariaDB [root93]> SELECT @harga_lukisan :=MAX(harga) FROM lukisan;
+-----------------------------+
| @harga_lukisan :=MAX(harga) |
+-----------------------------+
|                          87 |
+-----------------------------+
1 row in set (0.001 sec)

MariaDB [root93]> SELECT seniman.nama, lukisan.judul, lukisan.harga
    -> FROM seniman, lukisan
    -> WHERE lukisan.harga=@harga_lukisan
    -> AND seniman.a_id=lukisan.a_id;
+----------+---------------+-------+
| nama     | judul         | harga |
+----------+---------------+-------+
| Da Vinci | The Mona Lisa |    87 |
+----------+---------------+-------+
1 row in set (0.001 sec)

Teknik yang sama juga bisa kita lakukan dengan cara membuat sebuah tabel temporer untuk menampung setiap harga maksimum, dan kemudian menggabungkannya dengan tabel – tabel lain.

MariaDB [root93]> CREATE TABLE temp SELECT MAX(harga) AS harga_maks
    -> FROM lukisan;
Query OK, 1 row affected (0.275 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [root93]> SELECT seniman.nama, lukisan.judul, lukisan.harga
    -> FROM seniman, lukisan, temp
    -> WHERE lukisan.harga=temp.harga_maks
    -> AND lukisan.a_id=seniman.a_id;
+----------+---------------+-------+
| nama     | judul         | harga |
+----------+---------------+-------+
| Da Vinci | The Mona Lisa |    87 |
+----------+---------------+-------+
1 row in set (0.001 sec)

Penggunaan tabel temporer dan operasi penggabungan merupakan cara lebih kompleks dalam menajawa pertanyaan. Tetapi, teknik ini bisa mengarah ke teknik yang lebih umum dalam menjawab pertanyaan – pertanyaan yang lebih susah.

Query sebelumnya hanya menampilkan informasi untuk lukisan yang paling mahal pada tabel lukisan. Bagaimana misalnya pertanyaanya adalah “Lukisan apa yang paling mahal untuk tiap seniman ?” Kita tidak bisa menggunakan satu variabel SQL untuk menjawab pertanyaan ini, karena jawabannya akan mencari satu harga per seniman, dan sebuah variabel untuk menampung hanya satu nilai pada satu waktu. Teknik menggunakan tabel temporer dapat diterapkan karena tabel dapat menampung banyak nilai dan operasi penggabungan dapat dilakukan untuk mencari semua kecocokan sekaligus.

Untuk menjawab pertanyaan tersebut, Anda perlu memilih id tiap seniman dan harga lukisan maksimum dari tiap seniman ke dalam sebuah tabel temporer. Tabel akan memuat bukan hanya harga lukisan tertinggi, tetapi nilai maksimum untuk tiap grup. Kemudian mencocokan rekaman – rekaman pada tabel lukisan, dan melakukan operasi penggabungan dengan tabel seniman untuk mendapatkan nama – nama seniman terkait

MariaDB [root93]> CREATE TABLE temporer
    -> SELECT a_id, MAX(harga) AS harga_maks
    -> FROM lukisan GROUP by a_id;
Query OK, 3 rows affected (0.299 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [root93]> SELECT seniman.nama, lukisan.judul, lukisan.harga
    -> FROM seniman, lukisan, temporer
    -> WHERE lukisan.a_id=temporer.a_id
    -> AND lukisan.harga=temporer.harga_maks
    -> AND lukisan.a_id=seniman.a_id;
+----------+-------------------+-------+
| nama     | judul             | harga |
+----------+-------------------+-------+
| Da Vinci | The Mona Lisa     |    87 |
| Van Gogh | The Potato Eaters |    67 |
| Renoir   | Les Deux Soeurs   |    64 |
+----------+-------------------+-------+
3 rows in set (0.011 sec)

Teknik yang sama dapat diterapkan pada jenis-jenis lain, seperti nilai temporal. Perhatikan tabel daftar_supir yang memuat daftar supir dan perjalanan yang dilakukannya berikut :

MariaDB [root93]> SELECT nama, tanggal_travel, km
    -> FROM daftar_supir
    -> ORDER by nama, tanggal_travel;
+-------+----------------+------+
| nama  | tanggal_travel | km   |
+-------+----------------+------+
| Ben   | 2014-11-29     |  131 |
| Ben   | 2014-12-02     |  837 |
| Henri | 2014-11-26     |  380 |
| Henri | 2014-11-27     |  367 |
| Henry | 2014-11-26     |  278 |
| Henry | 2014-11-29     |  300 |
| Henry | 2014-11-30     |  574 |
| Henry | 2014-12-01     |  273 |
| Susi  | 2014-11-29     |  391 |
| Susi  | 2014-12-02     |  502 |
+-------+----------------+------+
10 rows in set (0.001 sec)

Salah satu permasalahan maksimum per grup untuk tabel ini adalah menunjukan perjalanan yang paling terakhir untuk tiap supir

MariaDB [root93]> CREATE TABLE tmpl
    -> SELECT nama, MAX(tanggal_travel) AS tanggal_travel
    -> FROM daftar_supir
    -> GROUP by nama;
Query OK, 4 rows affected (0.212 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [root93]> SELECT daftar_supir.nama, daftar_supir.tanggal_travel,
    -> daftar_supir.km
    -> FROM daftar_supir, tmpl
    -> WHERE daftar_supir.nama=tmpl.nama
    -> AND daftar_supir.tanggal_travel=tmpl.tanggal_travel
    -> ORDER by daftar_supir.nama;
+-------+----------------+------+
| nama  | tanggal_travel | km   |
+-------+----------------+------+
| Ben   | 2014-12-02     |  837 |
| Henri | 2014-11-27     |  367 |
| Henry | 2014-12-01     |  273 |
| Susi  | 2014-12-02     |  502 |
+-------+----------------+------+
4 rows in set (0.030 sec)

Ahmad Zaelani

Seorang laki - laki biasa yang senang duduk sendirian di pinggir pantai, pengagum embun pagi dan lembayung senja, suka blogging, coding dan jogging. Moto hidupnya adalah hidup santai dan simple

Penggabungan Untuk Mencari Nilai Minimum dan Maksimum Group – MySQL Dasar #76

0 Response to "Penggabungan Untuk Mencari Nilai Minimum dan Maksimum Group – MySQL Dasar #76"

Post a Comment