mysql分组平均,mysql分组平均计算

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

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

the fields to be displayed as below:

originator revenue toprank

hello 8 1

acme 5 2

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

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

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) 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.

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

See it on sqlfiddle.