MySQL - 按月计数(包括丢失的记录) [英] MySQL - count by month (including missing records)

问题描述

我有这个选择:

SELECT
  DATE_FORMAT(`created`, '%Y-%m') as byMonth,
  COUNT(*) AS Total 
FROM 
  `qualitaet`
WHERE
  `created` >= MAKEDATE(year(now()-interval 1 year),1) + interval 5 month
AND
  `status`=1
GROUP BY 
  YEAR(`created`), MONTH(`created`)
ORDER BY 
  YEAR(`created`) ASC

并得到这个结果:

| byMonth | Total |
| 2015-06 |   2   |
| 2015-09 |  12   |
| 2015-10 |   3   |
| 2015-12 |   8   |
| 2016-01 |   1   |

请参阅SQL-Fiddle此处

WHERE 子句很重要，因为在我的示例中，我需要它作为从 6 月 1 日开始的当前财政年度.

The WHERE clause is important because i need it as current fiscal year starting on June, 1 in my example.

如您所见，我没有 7 月、8 月和 11 月的记录.但我需要这些记录总数为零.

As you can see, i have no records for Jul, Aug and Nov. But i need this records with zero in Total.

所以我的结果应该是这样的:

| byMonth | Total |
| 2015-06 |   2   |
| 2015-07 |   0   |
| 2015-08 |   0   |
| 2015-09 |  12   |
| 2015-10 |   3   |
| 2015-11 |   0   |
| 2015-12 |   8   |
| 2016-01 |   1   |

有没有办法得到这个结果?

is there a way to get this result?

推荐答案

您需要生成所有想要的日期，然后将您的数据左连接到这些日期.另请注意，将一些谓词放在左连接的 ON 子句中很重要，而将其他谓词放在 WHERE 子句中:

You need to generate all the wanted dates, and then left join your data to the dates. Note also that it is important to put some predicates in the left join's ON clause, and others in the WHERE clause:

SELECT
  CONCAT(y, '-', LPAD(m, 2, '0')) as byMonth,
  COUNT(`created`) AS Total 
FROM (
  SELECT year(now())     AS y UNION ALL
  SELECT year(now()) - 1 AS y 
) `years`
CROSS JOIN (
  SELECT  1 AS m UNION ALL
  SELECT  2 AS m UNION ALL
  SELECT  3 AS m UNION ALL
  SELECT  4 AS m UNION ALL
  SELECT  5 AS m UNION ALL
  SELECT  6 AS m UNION ALL
  SELECT  7 AS m UNION ALL
  SELECT  8 AS m UNION ALL
  SELECT  9 AS m UNION ALL
  SELECT 10 AS m UNION ALL
  SELECT 11 AS m UNION ALL
  SELECT 12 AS m
) `months`
LEFT JOIN `qualitaet` q
ON YEAR(`created`) = y 
  AND MONTH(`created`) = m
  AND `status` = 1
WHERE STR_TO_DATE(CONCAT(y, '-', m, '-01'), '%Y-%m-%d') 
    >= MAKEDATE(year(now()-interval 1 year),1) + interval 5 month
  AND STR_TO_DATE(CONCAT(y, '-', m, '-01'), '%Y-%m-%d') 
    <= now()
GROUP BY y, m
ORDER BY y, m

以上是如何工作的?

• CROSS JOIN 在所有可用年份和所有可用月份.这就是您想要的，您希望所有年月组合都没有间隙.
• LEFT JOIN 将所有 qualitaet 记录添加到结果中(如果它们存在)，并将它们连接到之前的年-月笛卡尔积.将诸如 status = 1 谓词之类的谓词放在这里很重要.
• COUNT(created) 只计算 created 的非 NULL 值，即当 LEFT JOIN 在任何给定年份不产生任何行时 -月，我们想要 0 作为结果，而不是 1，即我们不想计算 NULL 值.

How does the above work?

• CROSS JOIN creates a cartesian product between all available years and all available months. This is what you want, you want all year-month combinations with no gaps.
• LEFT JOIN adds all the qualitaet records to the result (if they exist) and joins them to the year-month cartesian product from before. It is important to put prediactes like the status = 1 predicate here.
• COUNT(created) counts only non-NULL values of created, i.e. when the LEFT JOIN produces no rows for any given year-month, we want 0 as a result, not 1, i.e. we don't want to count the NULL value.

以上在您的 ON 和 WHERE 谓词中大量使用了字符串操作和日期时间算法.这不会对大量数据执行.在这种情况下，您最好在 qualitaet 表中预先截断和索引您的年月，并且只对这些值进行操作.

The above makes heavy use of string operations and date time arithmetic in your ON and WHERE predicates. This isn't going to perform for lots of data. In that case, you should better pre-truncate and index your year-months in the qualitaet table, and operate only on those values.

这篇关于MySQL - 按月计数(包括丢失的记录)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！