Блог

Главная - Вопросы по программированию - Игнорировать пустые значения в средней математике, но показывать их как ноль в результате

Игнорировать пустые значения в средней математике, но показывать их как ноль в результате

#mysql #sql #average #aggregate-functions #sql-null

#mysql #sql #среднее #агрегатные функции #sql-null

Вопрос:

Мне нужно вычислить Average значение полей, но должны произойти две вещи:

1 — Пустые значения НЕ должны учитываться для средней математики.
2- Если поле пустое, оно все равно должно быть показано в результате (с avg === 0)

Представьте, что у меня есть этот набор данных:

 -----------------------
Code | valField | Date
-----------------------
  A  |          | 2020-09-08
  B  |  12      | 2020-09-09
  A  |  10      | 2020-09-08
  B  |  15      | 2020-09-09
  B  |          | 2020-09-09
  C  |          | 2020-09-09

Поэтому мне нужно среднее значение day . Как вы можете видеть, у нас есть:
A = { empty, 10 }
B = { 12, 15, пусто }
C = { empty }

Мне нужно сделать среднее значение следующим образом:
среднее значение A = 10
Среднее значение B = (12 15) / 2 (потому что у нас есть 2 непустых значения)
Среднее значение C = 0 (оно не имеет ни одного значения, но мне нужно, чтобы оно отображалось в результате как 0)

До сих пор я мог выполнить оба требования, но не одновременно.

Этот запрос покажет пустые значения, но также будет учитывать пустые поля в average math 

 SELECT AVG(valField) FROM myTable;

Таким образом, среднее значение B будет = (12 15 0)/3 — неправильно!

Теперь это будет игнорировать пустые значения, средняя математика будет правильной, но C НЕ будет отображаться.

 SELECT AVG(valFIeld) FROM myTable WHERE valField <> ''

Как я могу выполнить оба требования?

Комментарии:

1. Какой тип данных есть valField ? Пожалуйста, укажите структуру вашей таблицы.

2. Привет! @SlavaRozhnev valField может быть integer или empty (не null).

Ответ №1:

Из вашего комментария я понял, что вы valField определили как varchar , поэтому вы можете использовать следующий трюк:

 select 
    Code, 
    coalesce(avg(nullif(valField, '')), 0) as avg_value
from tbl 
group by Code;

Протестируйте запрос на SQLize.online

Здесь я использовал функцию NULLIF для преобразования пустых значений в null перед вычислением среднего

Комментарии:

1. Здравствуйте. Спасибо за ваш ответ. Вероятно, это правильно, поскольку оно возвращает мне среднее значение каждого кода (по одному для каждого), но мне нужно среднее значение за каждый день, поэтому у меня будут все коды со средним значением за каждый день в базе данных.

2. Итак, просто добавьте Date group by и выберите разделы запроса

Ответ №2:

Я думаю, вы хотите:

 SELECT code, COALESCE(AVG(valField), 0) FROM myTable GROUP BY code

Предполагается valField , что это числовой тип данных, и что под пустым вы подразумеваете null .

Вот что происходит за капотом:

  • avg() , как и большинство других агрегатных функций, игнорирует null значения

  • если все значения равны null , то avg() возвращается null ; вы можете заменить это с 0 помощью coalesce()

Комментарии:

1. Привет! На самом деле я действительно имел в виду пустой. База данных не равна нулю, но некоторые значения просто пустые ` » . It still counting empty rows on AVG() . If there are 10 rows, but only 5 of them have values, the average should divide the sum only by 5 not all 10s. On the other hand, if a code has ONLY empty values, so it should return 0. Also, forgot to mention, I'm grouping by date` в формате YYYY-MM-DD .

Ответ №3:

Это должно быть легко, просто создайте два запроса, один из которых вычисляет среднее значение, используя ненулевые значения, а другой вычисляет коды, не имеющие значения в данных.

 select round(avg(valField)) as avg, code from new where valField is not null group by Code
union all
select 0 as avg, code from new  group by Code having avg(valField) is null;