Получить СРЗНАЧ, игнорируя значения Null или Ноль
Как я могу получить среднее значение столбца, игнорируя значения NULL и нулевые значения?
У меня есть три столбца, для которых я хочу вычислить среднее значение. Я пробую использовать следующий скрипт:
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.TransferW IS NOT NULL
AND a.StaffW <> 0 AND a.StaffW IS NOT NULL
AND MONTH(a.ActualTime) = 4
AND YEAR(a.ActualTime) = 2013
Однако я не получаю никаких результатов, хотя в этих трех столбцах есть значения, включая NULL и нули!
Есть ли какой-либо способ исключить значения NULL и нули перед вычислением среднего?
Например, что-то вроде AVERAGE(NOTNULL(SecurityW))
.
5 ответ(ов)
Вам не нужно использовать DISTINCT
, так как NULL
уже игнорируется. Вы можете использовать NULLIF
, чтобы преобразовать 0
в NULL
. Также стоит обратить внимание, что ваш фильтр по ActualTime
не является 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'
Обратите внимание, что я не включал Table2 b
в описание, так как в вашем оригинальном запросе отсутствует условие соединения с этой таблицей.
Этот код должен работать, хотя я сам его не пробовал. Он исключит нули из вычислений. NULL будет исключен по умолчанию.
AVG (CASE WHEN SecurityW <> 0 THEN SecurityW ELSE NULL END)
Если вы хотите игнорировать '0' или 'NULL' при расчете среднего значения, вы можете использовать следующую конструкцию:
AVG(NULLIF(your_column_name, 0))
Такой подход позволяет заменить '0' на 'NULL', что исключает эти значения из вычисления среднего. В результате среднее значение будет рассчитано только для ненулевых значений из вашей колонки.
Этот запрос помог мне решить задачу:
AVG(CASE WHEN SecurityW <> 0 THEN SecurityW ELSE NULL END)
Он вычисляет среднее значение столбца SecurityW
, игнорируя нулевые значения. Если SecurityW
не равно нулю, то оно будет включено в вычисление среднего, в противном случае будет возвращено NULL
, который не учитывается в функции AVG()
.
Вы уже пытаетесь фильтровать значения NULL
с помощью NOT NULL
. Я изменил это на IS NOT NULL
в условии WHERE
, чтобы запрос выполнялся корректно. Мы можем немного упростить запрос, убрав функцию ISNULL
в методе AVG
. Также, у меня есть сомнения, что вам действительно нужен тип bigint
, так что мы также можем убрать явное приведение типа.
Вот исправленный запрос:
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.TransferW IS NOT NULL
AND a.StaffW <> 0 AND a.StaffW IS NOT NULL
AND MONTH(a.ActualTime) = 4
AND YEAR(a.ActualTime) = 2013
Обратите внимание на исправления в условиях фильтрации и устранение лишних привидений типов. Теперь запрос будет более эффективным и легко читаемым.
"Вставка результатов хранимой процедуры в временную таблицу"
Как экранировать одинарную кавычку в SQL Server?
Как выполнить оператор UPDATE с JOIN в SQL Server?
Возможно ли задать условия в Count()?
with(nolock) или (nolock) - есть ли разница?