Я хочу рассчитать среднее значение за 6 месяцев до данного месяца в MySQL
образец кода:
SELECT Avg(dt.number_of_employees)
FROM (SELECT number_of_employees
FROM my_table
ORDER BY date DESC
LIMIT 1, 6) dt;
но не сработало. Как я могу рассчитать с php?
Решение
Вам не нужен PHP для расчета, MySQL сделает это просто отлично:
SELECT emp.date, emp.number_of_employees, six_mo_avg.avg_emp, ROUND(six_mo_avg.avg_emp) avg_emp_rounded
FROM my_table emp
LEFT JOIN
(SELECT e.date,
(SELECT avg(avgemp.number_of_employees) avg_emp
FROM my_table avgemp
WHERE avgemp.date BETWEEN e.date - INTERVAL 6 MONTH AND e.date
GROUP BY 1=1) avg_emp
FROM my_table e) six_mo_avg ON six_mo_avg.date = emp.date
ORDER BY emp.date DESC
Другие решения
Это работает, как показано в этой демонстрации: http://sqlfiddle.com/#!9/b17749/1.
set @rownum := 0;
set @rownum2 := 0;
set @lastrownum := (select count(*) from my_table);
select date,
number_of_employees,
sum(number_of_employees)/(maxrownum-rnum+1) as avg_per_6months,
round(sum(number_of_employees)/(maxrownum-rnum+1),0) as avg_rounded
from (
select t.date,
t.number_of_employees,
s.rnum,
s.maxrownum,
t.rnum2
from (
select date,
(@rownum := @rownum + 1) as rnum,
(case when @lastrownum <= @rownum + 5 then @lastrownum
else (@rownum + 5) end) as maxrownum
from my_table
order by date desc) s
cross join (
select date, number_of_employees,
(@rownum2 := @rownum2 + 1) as rnum2
from my_table
order by date desc) t ) v
where rnum2 between rnum and maxrownum
group by rnum
order by rnum, rnum2
detector