MySQL - 按月计数（包括缺少的记录） [英] MySQL - count by month (including missing records)

问题描述

我有这个SELECT：

  SELECT 
 DATE_FORMAT（`created`，'％Y-％m' ）as byMonth，
 COUNT（*）AS total 
 FROM 
`qualitaet` 
 WHERE 
`created`> = MAKEDATE 1年），1）+间隔5个月
 AND 
`status` = 1 
 GROUP BY 
 YEAR（`created`），MONTH（`created`）
 ORDER BY 
 YEAR（`created`）ASC 
  

p>

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

请参阅 SQL-Fiddle 此处

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

如你所见，我没有7月，8月和11月的记录。但我需要因此我的结果应如下所示：

pre> | byMonth |总计|
| 2015-06 | 2 |
| 2015-07 | 0 |
| 2015-08 | 0 |
| 2015-09 | 12 |
| 2015-10 | 3 |
| 2015-11 | 0 |
| 2015-12 | 8 |
| 2016-01 | 1 |


有办法获得这个结果吗？

解决方案

您需要生成所有想要的日期，然后将您的数据连接到日期。还要注意，在左连接的 ON 子句中设置一些谓词很重要，而在 WHERE

  SELECT 
 CONCAT（y，' - '，LPAD（m，2，'0'））byMonth ，
 COUNT（`created`）AS总计
 FROM（
 SELECT年（现在（））AS y UNION ALL 
选择年份（现在（）） -  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） code>只计算的非空值，即当 LEFT JOIN 给定年 - 月，我们需要 0 作为结果，而不是 1 ，即我们不想计数 NULL 值。



关于效果的注释

以上在 ON 和 WHERE 谓词。这对大量数据不会执行。在这种情况下，您应该更好地在 qualitaet 表中预截断和索引年月，并仅对这些值进行操作。

I have this SELECT:

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

and get this result:

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

see SQL-Fiddle here

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

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

So my result should look like this:

| 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?

解决方案

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

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.

A note on performance

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