mysql - 查找每月缺失的数据

ID因为您只需要具有相关的缺失记录，Month所以这是一个简单的非相关子查询场景。

在 SQL中，可以使用子句中的表达式对非相关子查询进行建模，或者您可以使用 a并且仅包含连接表中列的NULL结果，因为这将指示未找到匹配项的记录NOT EXISTSWHERELEFT OUTER JOIN

此查询仅因您要评估日期的MONTH组成部分而不是明确的日期值这一事实而变得复杂。SQL为此提供了所有必要的工具，我们甚至可以格式化您想要的输出：

SELECT DATE_FORMAT(CAST(CONCAT(m.year,'-',m.month,'-01') as DateTime), '%b-%y') as Data, i.Id as newId
FROM (
  SELECT YEAR(Data) AS Year, MONTH(Data) AS month
  FROM `table`
  GROUP BY YEAR(Data), MONTH(Data)
) m
CROSS JOIN `ID` i
LEFT OUTER JOIN `table` t ON YEAR(t.Data) = m.year AND MONTH(t.Data) = m.month AND t.newId = i.Id
WHERE t.Data IS NULL
ORDER BY m.Year DESC, m.month DESC

看看这个小提琴：http ://sqlfiddle.com/#!9/72af42/2

WHERE NOT EXISTS在AND之间进行选择LEFT OUTER JOIN可能会稍微影响性能，但影响将取决于您的查询、您的 RDBMS 和可用索引。我个人首先使用JOIN语法，因为 IMO 维护起来更简单，但您可以自行决定。

至少在 MS SQL 中有很多讨论NOT EXISTS应该比它更快，JOIN但如果性能是这个特定查询的问题，你应该考虑将year和month列存储为持久值，以便它们可以被索引并减少函数评估.因为它会评估更少的查找。

使用 LEFT JOIN 或 NOT EXISTS 之间的最佳实践

左外连接与不存在的 SQL 性能

MySQL：不存在与 LEFT OUTER JOIN ......是 NULL

作为比较，这是等效的WHERE NOT EXISTS查询：http ://sqlfiddle.com/#!9/72af42/5

SELECT DATE_FORMAT(CAST(CONCAT(m.year,'-',m.month,'-01') as DateTime), '%b-%y') as Data, i.Id as newId
FROM (
  SELECT YEAR(Data) AS Year, MONTH(Data) AS month
  FROM `table`
  GROUP BY YEAR(Data), MONTH(Data)
) m
CROSS JOIN `ID` i
WHERE NOT EXISTS (
  SELECT * 
  FROM `table` t 
  WHERE YEAR(t.Data) = m.year 
    AND MONTH(t.Data) = m.month 
    AND t.newId = i.Id
)
ORDER BY m.Year DESC, m.month DESC

如何使用持久值进行优化？

如果我们预先求值YEAR()并MONTH()直接将结果存储在表中，那么查询速度会提高，但我们也可以添加索引来增强它。

在走到这一步之前，请考虑一下所有的优点和缺点......

您真的需要这种级别的优化吗？

查询多久执行一次？

您能否更改应用程序逻辑以使用更合适的WHERE子句来限制数据范围？

物化视图

对此的一种解决方案是创建和管理物化视图。这是一种 DW 技术，它有效地允许您定义一个视图，但让它定期执行并存储到它自己的表空间中。

物化视图不会优化您的查询，但它允许您执行一次复杂且长时间运行的查询，从而可以像普通表一样直接查询结果，而无需重新评估列表达式。

您的数据和查询类型看起来很适合物化视图，因为它正在查询变化率为零或非常低的历史数据，仅更新新行，而且我们可能不关心当前月份的结果。在这种情况下，如果您最终多次运行查询，并且结果或多或少保持不变，那么为什么不将查询作为一个进程运行，比如说每个月并将结果存储在一个专门构建的表中，那么您的应用程序可以以闪电般的快速结果频繁查询该表。

MySQL 不支持物化视图，但您可以在应用程序逻辑中复制上述概念，其他一些 RDBMS 提供此 OOTB，这是应考虑的概念。

计算列

您可以将额外的列添加到表中并从用户/应用程序逻辑中维护这些列，但这不是很可靠，除非您信任您的应用程序开发人员并且应用程序是唯一将更新此表的进程。

在这种情况下，计算列非常适合可靠性，但如果您可以将值保存到列存储中，它们只会帮助我们提高性能。（计算列的默认状态是表达式将在执行时进行评估，这与当前查询相比几乎没有什么好处）

这也是 MySQL 会让你失望的地方，许多其他 RDBMS 提供了更简单的方法来做到这一点，你需要 MySQL v5.7 才能工作
ALTER TABLE `table` ADD `year` GENERATED ALWAYS AS (YEAR(Data)) STORED;
ALTER TABLE `table` ADD `month` GENERATED ALWAYS AS (MONTH(Data)) STORED;

扳机

您的另一个选择是添加列，然后使用触发器来维护值，MySQL 并不容易，但它可以工作

将列添加到表中：

ALTER TABLE `table` ADD (`year` int NULL);
ALTER TABLE `table` ADD (`month` int NULL);

创建触发器来管理这些列中的值，以便用户无法覆盖它们：

DELIMITER $$

CREATE TRIGGER persist_index_values_insert
  BEFORE INSERT ON `table` FOR EACH ROW
BEGIN
  SET new.year= YEAR(new.Data);
  SET new.month = MONTH(NEW.Data);
END$$

CREATE TRIGGER persist_index_values_update
BEFORE UPDATE ON `table` FOR EACH ROW
BEGIN
  SET NEW.year = YEAR(NEW.Data);
  SET NEW.month = MONTH(NEW.Data);
END$$

分隔符；

更简单的查询：

 SELECT DATE_FORMAT(CAST(CONCAT(m.year,'-',m.month,'-01') as DateTime), '%b-%y') as Data, i.Id as newId
 FROM (
   SELECT `year`, `month`
   FROM `table`
   GROUP BY `year`, `month`
 ) m
 CROSS JOIN `ID` i
 LEFT OUTER JOIN `table` t ON t.year = m.year AND t.month = m.month AND t.newId = i.Id
 WHERE t.Data IS NULL
 ORDER BY m.Year DESC, m.month DESC

现在可以根据需要应用索引，您应该咨询您的查询执行计划以获得指导，但我建议您至少需要一个索引year和month和：newId
```
  CREATE INDEX IX_TABLE_YEAR_MONTH_NEWID ON `table` (`year`,`month`,'newId');
```

mysql - 查找每月缺失的数据

问题描述

解决方案

如何使用持久值进行优化？

物化视图

计算列

扳机

推荐阅读