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屋!