选择给定日期范围内的所有月份，包括0值的月份 [英] Select all months within given date span, including the ones with 0 values

问题描述

我正在尝试写一个MySQL查询，以获得每个月的平均值，在给定日期之间的所有月份。我的想法是这样的：

查询，像

  SELECT AVG （value1）as avg_value_1，
 AVG（value2）as avg_value_2，
 MONTH（save_date）as month，
 YEAR（save_date）as year 
 FROM myTable 
 WHERE save_date 2009-01-01和2009-07-01
 GROUP BY YEAR（save_date），MONTH（save_date）
 
 avg_value_1 | avg_value_2 |月|年
 5 | 4 | 1 | 2009 
 2 | 1 | 2 | 2009 
 7 | 5 | 3 | 2009 
 0 | 0 | 4 | 2009< --- 
 6 | 5 | 5 | 2009 
 3 | 6 | 6 | 2009 
  

你看，2009年4月没有输入任何值，但我希望它显示为0，0输出值。关于如何实现这一点的任何想法？可以在MySQL内完成吗？

解决方案

我同意Lieven的回答创建一个包含您可能需要的所有月份的表格，将其用于LEFT JOIN到您的结果表。记住，这是一个非常小的表，每年只有365（ish）行数据...你可以很容易地编写一些代码来填充这个表格

我们在这里做，这给了很多好处，例如，想象一下每月的数据表，其中包含以下字段（以及您可以想到的任何其他内容！）在给定范围内的所有月份填充;

• 日期（例如2009-04-01）


• 日（例如1）


• 星期几（例如星期三）


• 月（例如4）


• 年（例如2009）


• 财政年度（例如2009/10）


• 财政季度（例如2009Q1）


• 日历季度（例如2009Q2）

然后将它与上面的查询组合，如下所示：

 
 SELECT`DT`.`myYear`，`DT`.`myMonth`，
 AVG（`myTable`.`value1`）作为avg_value_1，
 AVG（`myTable`.`value2`）作为avg_value_2 
 
 FROM`dateTable`作为DT 
 LEFT JOIN`myTable` 
 ON`dateTable`.`myDate` =`myTable`.`save_date` 
 
 WHERE`dateTable`.`myDate` BETWEEN'2009-01-01'AND' 2009-07-01'
 
 GROUP BY`DT`.`myYear`，`DT`.`myMonth` 
 

我的SQL代码中可能会出现一些错误，因为我无法创建测试表，但希望您可以获得校长和更改以满足您的需要！

使用这一点，您可以将GROUP BY子句更改为dateTable表中的任何内容，从而可以轻松地按财务季度，月，日，星期几等报告。 / p>

希望有帮助！

I'm trying to write a MySQL query to get an average value per month, for all months between to given dates. My idea is this:

Query, something like

SELECT AVG(value1) as avg_value_1, 
AVG(value2) as avg_value_2, 
MONTH(save_date) as month, 
YEAR(save_date) as year
FROM myTable
WHERE save_date BETWEEN '2009-01-01' AND '2009-07-01'
GROUP BY YEAR(save_date), MONTH(save_date)

avg_value_1 | avg_value_2 | month | year
     5      |      4      |   1   | 2009
     2      |      1      |   2   | 2009
     7      |      5      |   3   | 2009
     0      |      0      |   4   | 2009 <---
     6      |      5      |   5   | 2009
     3      |      6      |   6   | 2009

You see, no values were entered during April 2009, yet i want it to show up as a 0, 0 value in output. Any ideas on how to achieve this? Can it be done within MySQL?

解决方案

I agree with Lieven's answer create a table containing all the months you could ever require, and use that to "LEFT JOIN" to your results table. Remember, this is a really tiny table, only 365(ish) rows per year of data you have... And you can easily write some code to populate this table initially

We do this here, and it gives lots of benefits, for example, imagine a monthly data table with the following fields (and any others you can think of!) fully populated for all the months in a given range;

• Date (E.g. 2009-04-01)
• Day (E.g. 1)
• Day of Week (E.g. Wednesday)
• Month (E.g. 4)
• Year (E.g. 2009)
• Financial Year (E.g. 2009/10)
• Financial Quarter (E.g. 2009Q1)
• Calendar Quarter (E.g. 2009Q2)

Then combining this with your query above, as follows;

SELECT `DT`.`myYear`, `DT`.`myMonth`, 
           AVG(`myTable`.`value1`) as avg_value_1, 
           AVG(`myTable`.`value2`) as avg_value_2

FROM `dateTable` as DT
LEFT JOIN `myTable`
    ON `dateTable`.`myDate` = `myTable`.`save_date`

WHERE `dateTable`.`myDate` BETWEEN '2009-01-01' AND '2009-07-01'

GROUP BY `DT`.`myYear`, `DT`.`myMonth`

There may be some errors in my SQL code as I haven't been able to create the test tables, but hopefully you'll get the principal and alter to suit your needs!

Using this, you can change your "GROUP BY" clause to whatever you have in the "dateTable" table, which can allow you to easily report by Financial Quarter, Month, Day, Day of Week, etc.

Hope that helps!

这篇关于选择给定日期范围内的所有月份，包括0值的月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！