选择给定日期范围内的所有月份，包括值为 0 的月份

发布于 2024-09-06 10:50:16 字数 699 浏览 2 评论 0原文

我正在尝试编写一个 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 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

你看，2009 年 4 月期间没有输入任何值，但我希望它在输出中显示为 0, 0 值。关于如何实现这一目标有什么想法吗？可以在MySQL中完成吗？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

江心雾 2024-09-13 10:50:16

我同意 Lieven 的回答，创建一个包含您可能需要的所有月份的表，并使用它“左连接”到您的结果表。请记住，这是一个非常小的表，您每年只有 365 行数据...并且您可以轻松地编写一些代码来最初填充该表

我们在这里这样做，它带来了很多好处，例如，想象一个每月数据表，其中包含以下字段（以及您能想到的任何其他字段！），并且完全填充了给定范围内的所有月份；

日期（例如 2009-04-01）
日（例如 1）
星期几（例如星期三）
月份（例如 4）
年（例如 2009）
财政年度（例如 2009/10）
财务季度（例如 2009Q1）
日历季度（例如 2009Q2 ） )

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

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`

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

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

希望有帮助！

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 原文