Dapatkan AVG dengan mengabaikan nilai Null atau Zero

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))

Darren
Pertanyaan edit 2 Juli 2013 в 12:03
Pemrograman
sql sql-server tsql sql-server-2008-r2
2 Juli 2013 в 11:57
46 tayangan

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.

Komentar (4)

ini seharusnya bekerja, belum pernah mencoba. ini akan mengecualikan nol. NULL dikecualikan secara default

AVG (CASE WHEN SecurityW  0 THEN SecurityW ELSE NULL END)
Komentar (0)

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
Komentar (0)