mysql: среднее значение против суммы / количества

Question

У меня есть 4 переменные:

• amdt_adopt
• com_amdt_adopt
• amdt_tabled
• com_amdt_tabled

Я хочу вычислить среднее соотношение:

(amdt_adopt - com_amdt_adopt) / (amdt_tabled - com_amdt_tabled)

Если я использую встроенный avg функция:

select avg((amdt_adopt - com_amdt_adopt) / (amdt_tabled - com_amdt_tabled)) as final_res
from europolix.act_act
where validated=2 and com_amdt_adopt>0 and amdt_adopt>0 and com_amdt_tabled>0 and amdt_tabled>0;

я получил 0.44771942,

Если я использую sum а затем делится на count:

select 
(
    select sum((amdt_adopt - com_amdt_adopt) / (amdt_tabled - com_amdt_tabled))
    from europolix.act_act
    where validated=2 and com_amdt_adopt>0 and amdt_adopt>0 and com_amdt_tabled>0 and amdt_tabled>0
) 
/ 
(
    select count(*) 
    from europolix.act_act
    where validated=2 and com_amdt_adopt>0 and amdt_adopt>0 and com_amdt_tabled>0 and amdt_tabled>0
) as final_res;

я получил 0.20883803,

У меня разные результаты, но эти два запроса должны быть эквивалентны! В чем дело?

РЕДАКТИРОВАТЬ

Приведение к decimal как предложено:

select avg((cast(amdt_adopt - com_amdt_adopt as decimal(8,3)))  / (cast(amdt_tabled - com_amdt_tabled as decimal(8,3)))) as final_res
from europolix.act_act
where validated=2 and com_amdt_adopt>0 and amdt_adopt>0 and com_amdt_tabled>0 and amdt_tabled>0;

select 
(
    select sum((cast(amdt_adopt - com_amdt_adopt as decimal(8,3))) / (cast(amdt_tabled - com_amdt_tabled as decimal(8,3))))
    from europolix.act_act
    where validated=2 and com_amdt_adopt>0 and amdt_adopt>0 and com_amdt_tabled>0 and amdt_tabled>0
) 
/ 
(
    select count(*) 
    from europolix.act_act
    where validated=2 and com_amdt_adopt>0 and amdt_adopt>0 and com_amdt_tabled>0 and amdt_tabled>0
) as final_res;

Все тот же результат:(.

Answer 1

Если у вас есть значения NULL в записях для некоторых из тех столбцов, которые вы используете в avg, результаты avg или sum / count могут легко отличаться.

Например:

create table t (id int, x int, y int);

insert into t (id, x, y) values (1, 1, 1);
insert into t (id, x, y) values (2, 2, 3);
insert into t (id, x, y) values (3, 3, 4);
insert into t (id, x, y) values (4, NULL, NULL);


select avg(x), sum(x), count(x), count(*), sum(x)/count(*) from t;

Дам тебе:

AVG(X)  SUM(X)  COUNT(X)    COUNT(*)    SUM(X)/COUNT(*)
2       6       3           4           1.5

Точно так же AVG не будет учитывать, где происходит деление на ноль.

Например

select avg(x/(y-x)) from t where x>0 and y>0;
2.5

select sum(x/(y-x))/count(*) from t where x>0 and y>0;
1.66666667

Одним из решений может быть использование avg с таким условием:

select avg(if(y-x=0, 0, x/(y-x))) from t where x>0 and y>0;
1.66666667

Пример можно увидеть здесь

[редактировать] обновлен, чтобы рассмотреть возможность деления на ноль

Answer 2

Как объясняется Jack, когда (amdt_tabled - com_amdt_tabled)=0, avg функция не принимает во внимание экземпляр, но count функция учитывает это. Так что результаты разные.

Если вы не хотите принимать во внимание эти случаи и использовать sum а также count функции, добавьте условие в count:

select 
(
    select sum((amdt_adopt - com_amdt_adopt) / (amdt_tabled - com_amdt_tabled))
    from europolix.act_act
    where validated=2 and com_amdt_adopt>0 and amdt_adopt>0 and com_amdt_tabled>0 and amdt_tabled>0 
) 
/ 
(
    select count(*)
    from europolix.act_act
    where validated=2 and com_amdt_adopt>0 and amdt_adopt>0 and com_amdt_tabled>0 and amdt_tabled>0 and (amdt_tabled - com_amdt_tabled)<>0
) as final_res;

Если вы хотите принять во внимание эти случаи (как 0) и использовать avg функция, добавить if состояние в соответствии с предложением lp_:

select avg(if(amdt_tabled - com_amdt_tabled=0, 0, (amdt_adopt - com_amdt_adopt) / (amdt_tabled - com_amdt_tabled))) as final_res
from europolix.act_act
where validated=2 and com_amdt_adopt>0 and amdt_adopt>0 and com_amdt_tabled>0 and amdt_tabled>0;

Решено!