对于不存在的日期返回零. [英] returns Zeros for Dates that dont exist MYSQL GROUP BY

问题描述

SELECT AVG(data) AS data, dateReg 
FROM table 
GROUP BY YEAR(dateReg), MONTH(dateReg), DAY(dateReg) ORDER BY dateReg

以上数据返回

1.75, 21 Jan 2010
9.45, 22 Jan 2010
3.96, 23 Jan 2010
2.68, 30 Jan 2010

它缺少从24日到29日的日期，我们可以这样做，所以返回值为:

Its missing dates from the 24th to 29th, Can we do it so the return value is:

1.75, 21 Jan 2010
9.45, 22 Jan 2010
3.96, 23 Jan 2010
0, 24 Jan 2010
0, 25 Jan 2010
0, 26 Jan 2010
0, 27 Jan 2010
0, 28 Jan 2010
0, 28 Jan 2010
2.68, 30 Jan 2010

推荐答案

通常，您可以通过加入 值表 (即表)来实现这种类型的事情其中包含您感兴趣的所有值.

You typically achieve this type of things by JOIN-ing with a value table, i.e. a table which contains all the values you are interested in.

典型值表可能包含例如0到1,000之间的所有整数值，或给定期间的所有日期.通常，值"表包含的值比期望的要多，我们可以通过在WHERE子句中添加过滤器来获得期望的确切输出.

Typical value tables may contain for example all the integer values between 0 and 1,000, or all dates for a given period. Often the Value tables include more values than desired, and we obtain the exact output desired by adding filters in the WHERE clause.

在这种情况下，您将需要一个包含日期的表格.假设此表名为ValTableDates，并且包含2005年1月至2010年12月之间的所有日期，查询将类似于:

In this case you will require such a table which contain dates. Assuming this table is named ValTableDates and that it contains all the dates between January 2005 and December 2010, the query would look like:

SELECT AVG(data) AS data, VT.ValDate
FROM ValTableDates VT
LEFT JOIN  table T ON T.dateReg = VT.ValDate
WHERE VT.ValDate > [Some Start Date] and VT < [Some End Date]
GROUP BY YEAR(dateReg), MONTH(dateReg), DAY(dateReg) 
ORDER BY dateReg

上面的查询可能需要一些调整才能获得零值而不是NULL，但是要点是，值表通常是提供缺失数据点输出记录的最简单方法.
一种替代方法是在子查询中使用产生所需[date]序列的函数/表达式，但这通常效率较低且更容易出错.

The above query may require a bit of tweaking to get a value Zero rather than NULL, but the main point is that the Value Table is typically the simplest way to provide output records for missing data points.
An alternative is to use a function/expression that produces the desired [date] sequence, inside a subquery, but this is generally less efficient and more error prone.

这篇关于对于不存在的日期返回零.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！