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

问题描述

我正在尝试编写一个 MySQL 查询来获取每个月的平均值，对于给定日期之间的所有月份.我的想法是这样的:

查询，类似

SELECT AVG(value1) 作为 avg_value_1，AVG(value2) 作为 avg_value_2，MONTH(save_date) 作为月份，YEAR(save_date) 作为年份从我的表WHERE save_date BETWEEN '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)
• 财务季度(例如 2009 年第一季度)
• 日历季度(例如 2009 年第二季度)

然后将其与上面的查询结合起来，如下所示；

<前>选择`DT`.`myYear`、`DT`.`myMonth`、AVG(`myTable`.`value1`) 作为 avg_value_1，AVG(`myTable`.`value2`) 作为 avg_value_2FROM `dateTable` 作为 DT左加入`myTable`ON `dateTable`.`myDate` = `myTable`.`save_date`'2009-01-01' 和 '2009-07-01' 之间的`dateTable`.`myDate`GROUP BY `DT`.`myYear`, `DT`.`myMonth`

我的 SQL 代码中可能存在一些错误，因为我无法创建测试表，但希望您能获得主体并进行更改以满足您的需求！

使用此功能，您可以将GROUP BY"子句更改为dateTable"表中的任何内容，这样您就可以轻松地按财务季度、月份、日期、星期几等进行报告.

希望有帮助！

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