Bagaimana saya bisa mendapatkan AVG
dari kolom yang mengabaikan NULL
dan nilai nol?
Saya memiliki tiga kolom untuk mendapatkan rata-ratanya, saya mencoba menggunakan skrip berikut:
SELECT distinct
AVG(cast(ISNULL(a.SecurityW,0) as bigint)) as Average1
,AVG(cast(ISNULL(a.TransferW,0) as bigint)) as Average2
,AVG(cast(ISNULL(a.StaffW,0) as bigint)) as Average3
FROM Table1 a, Table2 b
WHERE a.SecurityW <> 0 AND a.SecurityW IS NOT NULL
AND a.TransferW<> 0 AND a.TransferWIS NOT NULL
AND a.StaffW<> 0 AND a.StaffWIS NOT NULL
AND MONTH(a.ActualTime) = 4
AND YEAR(a.ActualTime) = 2013
Saya tidak mendapatkan hasil apa pun, namun ketiga kolom tersebut memiliki nilai termasuk NULL dan nol!
Apakah ada cara untuk mengecualikan nilai nol sebelum mendapatkan rata-rata?
contoh: AVERAGE(NOTNULL(SecurityW))
NULL
sudah diabaikan, jadi Anda bisa menggunakan NULLIF
untuk mengubah 0
menjadi NULL
. Anda juga tidak memerlukan DISTINCT
dan WHERE
Anda pada ActualTime
tidak dapat di-sargable.
SELECT AVG(cast(NULLIF(a.SecurityW, 0) AS BIGINT)) AS Average1,
AVG(cast(NULLIF(a.TransferW, 0) AS BIGINT)) AS Average2,
AVG(cast(NULLIF(a.StaffW, 0) AS BIGINT)) AS Average3
FROM Table1 a
WHERE a.ActualTime >= '20130401'
AND a.ActualTime < '20130501'
PS Saya tidak tahu untuk apa Table2 b
dalam kueri asli karena tidak ada kondisi gabungan untuk itu sehingga telah menghilangkannya dari jawaban saya.
Anda sudah mencoba menyaring nilai NULL
dengan NOT NULL
. Saya telah mengubahnya menjadi IS NOT NULL
di klausa WHERE
sehingga akan dieksekusi. Kita bisa melakukan refaktorisasi dengan menghapus fungsi ISNULL
di metode AVG
. Juga, saya ragu Anda benar-benar membutuhkan bigint
sehingga kita bisa menghapus cast.
SELECT distinct
AVG(a.SecurityW) as Average1
,AVG(a.TransferW) as Average2
,AVG(a.StaffW) as Average3
FROM Table1 a, Table2 b
WHERE a.SecurityW <> 0 AND a.SecurityW IS NOT NULL
AND a.TransferW<> 0 AND a.TransferWIS IS NOT NULL
AND a.StaffW<> 0 AND a.StaffWIS IS NOT NULL
AND MONTH(a.ActualTime) = 4
AND YEAR(a.ActualTime) = 2013