计算MySQL每天的平均销售额 [英] Compute average sales per day in MySQL

问题描述

在我的数据库中，我有一个包含两列的表.第一列包含日期，第二列是计数变量.我想知道是否有可能根据日期和计数来计算每个工作日的平均计数. 在下面的一个小示例中:

In my database I have a table with two columns. The first column contains dates and the second is a count variable. I was wondering if it is possible to compute the average counts for each weekday based on the dates and counts. In the following a small example:

表格:

Date         Count
02/01/2005     100
02/02/2005     200
02/03/2005     300
...            ...

输出:

 Days       Average
 Monday       120.5         
 Tuesday      200.2                   
 Wednesday    300.5 

推荐答案

您可以进行一系列

You could a series of avg calls on case expression extracting the day's name:

SELECT AVG(CASE DAYOFWEEK(`date`) WHEN 2 THEN `count` ELSE NULL END) AS Monday,
       AVG(CASE DAYOFWEEK(`date`) WHEN 3 THEN `count` ELSE NULL END) AS Tuesday,
       AVG(CASE DAYOFWEEK(`date`) WHEN 4 THEN `count` ELSE NULL END) AS Wednesday,
       AVG(CASE DAYOFWEEK(`date`) WHEN 5 THEN `count` ELSE NULL END) AS Thursday,
       AVG(CASE DAYOFWEEK(`date`) WHEN 6 THEN `count` ELSE NULL END) AS Friday
FROM   mytable

鉴于编辑后的帖子中已更新了预期的输出，因此操作起来更加容易-只需按dayname分组即可:

Given the updated expected output in the edited post, it's much easier to do - just group by the dayname:

SELECT   DAYNAME(`date`), AVG(`count`)
FROM     mytable
WHERE    DAYOFWEEK(`date`) BETWEEN 2 AND 6
GROUP BY DAYNAME(`date`)

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