MySQL按月计算包括一组在内的其他两个计算总和的百分比 [英] MySQL calculate percentage of two other calculated sums including a group by month

问题描述

假设我有 3 张桌子，模型是这样的

Say i have 3 tables with a model like this

我想要的结果现在看起来像这样

The result i want to have now looks like this

我想计算所有员工每月的营业额和利润，并将其与去年同月进行比较，并计算利润百分比的差异.它应该包括过去 12 个月的 INTERVAL 函数.

I want to calculate turnovers and profits made by all employees per month and compare it to the last years SAME month and calculate the difference in percentage of the profits. It should include the last 12 months with the INTERVAL function.

select
  bookings.b_emp_id as "Employee",
  MONTH(bookings.b_date) as Month,
  @turnover1 := sum(bookings.b_turnover) as '2017-turnover',
  @turnover2 := (select sum(lx.b_turnover) 
                 from bookings as lx 
                 where lx.b_date = date_add(bookings.b_date, INTERVAL -1 YEAR)
                 GROUP BY 
                   MONTH(bookings.b_date),
                   YEAR(bookings.b_date), 
                   bookings.b_emp_id
                ) as '2016-turnover',
  sum(b_profit) as '2017-profit',
  @profit1 := (select sum(lx.umsatz_fees) 
               from bookings as lx 
               where lx.b_date = date_add(bookings.b_date,INTERVAL -1 YEAR)
               GROUP BY 
                 MONTH(bookings.b_date),
                 YEAR(bookings.b_date),
                 bookings.b_emp_id
              ) as '2016-profit'
from bookings 
where bookings.b_date > '2017-01-01'
  and bookings.b_emp_id = ´SA´ 
GROUP BY MONTH(bookings.b_date)
order by bookings.b_date desc

推荐答案

使用条件聚合.不清楚您是要查看过去 12/24 个月，还是 2017 年和 2016 年的同一个月.我也不明白您想如何计算百分比.我在下面的查询中将今年的利润除以去年的利润.调整它以满足您的需求.

Use conditional aggregation. It is not clear if you want to look at the last 12 / 24 months, or at the months of 2017 and the same months in 2016. Neither do I understand how you want to calculate a percentage. I divide this year's profits by last year's in below query. Adjust this so it meets your needs.

select
  b_emp_id,
  month,
  turnover_this_year,
  profit_this_year,
  turnover_last_year,
  profit_last_year,
  profit_this_year / profit_last_year * 100 as diff
from
(
  select
    b_emp_id,
    month(b_date) as month,
    sum(case when year(b_date) = year(curdate()) then b_turnover end) as turnover_this_year,
    sum(case when year(b_date) = year(curdate()) then b_profit end) as profit_this_year,
    sum(case when year(b_date) < year(curdate()) then b_turnover end) as turnover_last_year,
    sum(case when year(b_date) < year(curdate()) then b_profit end) as profit_last_year
  from bookings
  where year(b_date) in (year(curdate()), year(curdate()) - 1)
    and month(b_date) <= month(curdate())
  group by b_emp_id, month(b_date)
) figures
order by b_emp_id, month;

这篇关于MySQL按月计算包括一组在内的其他两个计算总和的百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！