Bagaimana untuk Mengira Nilai Purata untuk Pas Berbeza Menggunakan SQL dalam MySQL?

Mengira Nilai Purata untuk Pas Berbeza Menggunakan SQL

Dalam MySQL, mendapatkan nilai purata dengan mengumpulkan data boleh dicapai dengan AVG dan GROUP BY fungsi. Walau bagaimanapun, apabila berurusan dengan berbilang kriteria, seperti mengira purata untuk nilai berbeza lajur tertentu, tugasan menjadi lebih kompleks sedikit.

Dalam kes ini, matlamatnya adalah untuk mengekstrak purata lajur 'val' untuk setiap 'id' unik untuk nilai 'lulus' yang berbeza-beza, yang mungkin berjulat dari 1 hingga 7.

Penyelesaian Subkueri

Satu pendekatan ialah menggunakan subkueri untuk setiap nilai 'lulus' dalam fungsi AVG. Ini akan mengembalikan satu set nilai untuk setiap gabungan 'id' dan 'pass'.

<code class="sql">SELECT d1.id,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2
     WHERE d2.id = d1.id AND pass = 1) as val_1,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2
     WHERE d2.id = d1.id AND pass = 2) as val_2,
    [...]
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2
     WHERE d2.id = d1.id AND pass = 7) as val_7
from data_r1 d1
GROUP BY d1.id</code>

Pertanyaan ini mula-mula mengambil nilai 'id' unik daripada jadual 'data_r1'. Kemudian, untuk setiap 'id', ia melaksanakan tujuh subkueri untuk mengira nilai purata bagi setiap nilai kemungkinan 'lulus' dan menyimpannya sebagai lajur 'val_n' yang berasingan. Fungsi IFNULL digunakan untuk mengendalikan mana-mana kes di mana subquery mengembalikan nilai NULL, memastikan bahawa hasilnya adalah nombor atau sifar.

Kelebihan pendekatan ini ialah ia mengembalikan satu baris untuk setiap 'id', menjadikannya lebih mudah untuk bekerja dengan hasilnya. Walau bagaimanapun, ia boleh menjadi agak bertele-tele, terutamanya jika terdapat banyak kemungkinan nilai untuk 'lulus'.

Pendekatan Ganti

Jika anda lebih suka kaedah yang lebih ringkas, anda boleh gunakan gabungan penyata GROUP BY dan CASE untuk mencapai hasil yang serupa:

<code class="sql">SELECT id,
       SUM(CASE WHEN pass = 1 THEN val END) / COUNT(DISTINCT CASE WHEN pass = 1 THEN id END) AS val_1,
       SUM(CASE WHEN pass = 2 THEN val END) / COUNT(DISTINCT CASE WHEN pass = 2 THEN id END) AS val_2,
       [...]
       SUM(CASE WHEN pass = 7 THEN val END) / COUNT(DISTINCT CASE WHEN pass = 7 THEN id END) AS val_7
FROM data_r1
GROUP BY id</code>

Pertanyaan ini menggunakan berbilang penyata CASE untuk menilai nilai 'lulus' dan hanya memasukkan nilai 'val' yang sepadan dalam pengiraan. Ungkapan COUNT(DISTINCT ...) dalam penyebut memastikan bahawa purata dikira dengan betul, walaupun terdapat berbilang nilai 'val' untuk gabungan 'id' dan 'pass' tertentu.

Sekali lagi, fungsi IFNULL boleh digunakan untuk mengendalikan sebarang kes di mana pembahagian menghasilkan NULL untuk memastikan output ialah nombor atau sifar.

Mana-mana pendekatan yang anda pilih, pertanyaan ini akan mengekstrak nilai 'val' purata dengan berkesan untuk setiap 'id' unik untuk nilai 'lulus' yang berbeza-beza daripada jadual 'data_r1'.

Atas ialah kandungan terperinci Bagaimana untuk Mengira Nilai Purata untuk Pas Berbeza Menggunakan SQL dalam MySQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!