mysql分组平均计算 [英] mysql group by with avg calculation

问题描述

id originator revenue date

1  acme             1 2013-09-15
2  acme             0 2013-09-15
3  acme             4 2013-09-14
4  acme             6 2013-09-13
5  acme            -6 2013-09-13
6  hello            1 2013-09-15
7  hello            0 2013-09-14
8  hello            2 2013-09-13
9  hello            5 2013-09-14

我有上表.我想根据发起人基于最近3天的收入产生的收入添加排名列

I have the above table . And I would like to add the ranking column based on the revenue generated by the originator based on the revenue for last 3 days

要显示的字段如下:

originator revenue toprank

hello            8       1
acme             5       2  

2)并且基于以上数据，我想根据以下条件计算平均收入

2) And based on the above data , i would like to calculate the avg revenue generated based on the following criteria

如果同一日期的总收入之和为 0 (零)，则不应在计算平均值时将其计算在内.

If the sum of total revenue for the same date is 0 ( zero) then it should not be counted with calculating the average.

a)发起人最高端的平均价值应为收入/计数的总和(收入为非零值的日期的天数)，因此(4 + 1)/2，即2.5

a) avg value for originator acme should be sum of revenue/count(no of dates where the revenue is non zero value) so (4+1)/2 i.e 2.5

b)发起方问好的平均价值应为收入/计数的总和(收入为非零值的日期的天数)，因此(5 + 2 + 1)/3 即 2.6666

b) avg value for originator hello should be sum of revenue/count(no of dates where the revenue is non zero value) so (5+2+1)/3 i.e 2.6666

originator revenue toprank avg(3 days)

hello            8       1      2.6666
acme             5       2      2.5

推荐答案

您首先需要按日期对记录进行分组，以查找每日总计，并排除总计为零的那些日子.

You first need to group the records by date in order to find the daily totals and exclude those days that have a total sum of zero.

然后将该查询用作子查询，您可以再次从该子查询中进行分组，以获取按创建者分类的总数和平均值.

Then use that query as a subquery from which you group again to get the totals and averages by originator.

最后，执行排名(或者在循环结果集时在应用程序代码中进行排名，或者在MySQL中使用带有用户变量的外部查询来进行排名).

Finally, perform the ranking (either in your application code as you loop over the resultset, or else in MySQL using an outer query with user variables).

SELECT originator, revenue, @r:=@r+1 AS toprank, avg FROM (
  SELECT   originator, SUM(total) AS revenue, AVG(total) AS avg
  FROM (
    SELECT   originator, date, SUM(revenue) AS total
    FROM     my_table
--  WHERE    date BETWEEN CURRENT_DATE - INTERVAL 3 DAY AND CURRENT_DATE
    GROUP BY originator, date
    HAVING   total <> 0
  ) t
  GROUP BY originator
  ORDER BY revenue DESC
) t, (SELECT @r:=0) init

在 sqlfiddle 上查看.

这篇关于mysql分组平均计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！