php MySQL AVG(COUNT(*) - Orders By day of week 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2231495/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL AVG(COUNT(*) - Orders By day of week query?
提问by Edy
This query has baffled me... I've searched the web work over a day now and I have tried numerous things.
这个查询让我感到困惑......我已经搜索了一天的网络工作并且我已经尝试了很多事情。
I want to get the avg number of orders for every day of the week from my db. I can pull the total # with COUNT just fine. But I just can't figure out how to get the AVG of COUNT on a GROUP BY. I've tried subqueries... functions... everything... nothing works... maybe someone can throw me a bone.
我想从我的数据库中获取一周中每一天的平均订单数。我可以用 COUNT 拉取总数。但我就是不知道如何在 GROUP BY 上获得 COUNT 的平均值。我试过子查询......函数......一切......没有任何作用......也许有人可以扔给我一块骨头。
Here is the query I started with below. I know AVG(COUNT(*)) won't work but I'll leave it at that because it shows what I want to do.
这是我在下面开始的查询。我知道 AVG(COUNT(*)) 不起作用，但我会保留它，因为它显示了我想要做什么。
SELECT
AVG(COUNT(*)) AS avgorders,
SUM(total) AS ordertotal,
DAYNAME(STR_TO_DATE(order_time,'%m/%d/%Y %H:%i')) AS day
FROM data
GROUP BY day
ORDER BY DAYOFWEEK(STR_TO_DATE(order_time,'%m/%d/%Y %H:%i')) ASC
回答by chrismar035
To get the average you don't need the grand totals for each day, you need multiple daily totals for each day.
要获得平均值，您不需要每天的总计，您每天需要多个每日总计。
Day | Count
__________________
Monday 5
Tuesday 4
Monday 6
Tuesday 3
... ...
Then you can average those numbers. I.e (5+6)/2 for Monday.
Something like this should work:
然后你可以平均这些数字。即星期一 (5+6)/2。
这样的事情应该工作：
SELECT day_of_week, AVG(order_count) average_order FROM
(
SELECT DAYNAME(order_date) day_of_week,
DAYOFWEEK(order_date) day_num,
TO_DAYS(order_date) date,
count(*) order_count
FROM data
GROUP BY date
) temp
GROUP BY day_of_week
ORDER BY day_num
UPDATE: I was originally wrong. Group the inner SELECT by the actual date to get the correct daily totals. For instance, you need to get how many orders happened Monday (2/1/10) and Monday (2/8/10) separately. Then average those totals by the day of the week.
更新：我最初错了。按实际日期对内部 SELECT 进行分组以获得正确的每日总数。例如，您需要分别获取星期一 (2/1/10) 和星期一 (2/8/10) 发生的订单数量。然后按一周中的某天平均这些总数。
回答by Schugs
I know this is old, but i was searching for a similar solution hoping to find something someone else had used. In hopes of not doing a sub query, i came up with the below and would love any feed back!
我知道这很旧，但我正在寻找类似的解决方案，希望能找到其他人使用过的东西。为了不做子查询，我想出了以下内容，并希望得到任何反馈！
SELECT dayofweek(`timestamp`) as 'Day',count(`OrderID`)/count(DISTINCT day(`timestamp`)) as 'Average' FROM `Data` GROUP BY dayofweek(`timestamp`)
The idea is to divide the total orders on a given day of the week, by the total number of "Mondays" or whatever day it is. What this does not account for would be any days that had zero orders would not lower the average. That may or may not be desired depending on the application.
这个想法是将一周中给定日期的订单总数除以“星期一”或任何一天的总数。这没有说明的是，任何订单为零的日子都不会降低平均值。这可能是也可能不是，这取决于应用程序。
回答by dev-null-dweller
This will do, assuming that order_timeis dateor datetimefield ( everyone would be hapier this way ;) ). Of course there is some approximation, because oldest order can be in Friday and newest in Monday, so amount of every day of week isn't equal, but creating separate variable for every day of week will be pain in the ass. Anyway I hope it will be helpful for now.
这会做，假设order_time是date或datetime领域（每个人都会更快乐；））。当然有一些近似值，因为最早的订单可以在周五，最新的订单可以在周一，所以一周中每一天的数量不相等，但是为一周中的每一天创建单独的变量会很麻烦。无论如何，我希望它现在会有所帮助。
SET @total_weeks = (
SELECT
TIMESTAMPDIFF(
WEEK,
MIN(order_time),
MAX(order_time)
)
FROM data
);
SELECT
DAYNAME(order_time) AS day_of_week,
( COUNT(*) / @total_weeks ) AS avgorders,
COUNT(*) AS total_orders
FROM
data
GROUP BY
DAYOFWEEK(order_time)
回答by vicatcu
What you are asking doesn't make sense to me... AVG is an aggregate function and so is COUNT. What's wrong with the query above but just use: COUNT(*) AS avgorders?
你问的对我来说没有意义...... AVG 是一个聚合函数，所以是 COUNT。上面的查询有什么问题，但只是使用：COUNT(*) AS avgorders？
Lets say you had 3 rows for day1, 2 rows for day2, 5 rows for day3, and 9 rows for day4... do you want to get back a single row result that tells you:
假设您第一天有 3 行，第二天有 2 行，第三天有 5 行，第四天有 9 行......你想得到一个单行结果，告诉你：
avgorders = (3 + 2 + 2 + 5 + 9) / 5 = 21 / 5 = 4.2
ordertotal = (3 + 2 + 2 + 5 + 9) = 21
I don't think you can get that in a single query, and you'd be better off doing the second round of aggregation in a server side language like PHP operating on the results of the first aggregation.
我不认为您可以在单个查询中获得它，并且您最好使用服务器端语言（如 PHP）对第一次聚合的结果进行操作进行第二轮聚合。
