在同一张表中比较两列

Question

我需要比较表格中的两列，并提供以下3项内容：

• 已检查的行数（被检查的总行数）
• 匹配的行数（其中两个列匹配的行数）
• 不同的行数（其中两个列不同的行数）

我能够使用自身连接来获取匹配的行，但不确定如何同时获取其他行。重要性在于同时获取所有信息，因为这是一个非常活跃的表格，数据变化频繁。

由于表格架构中有很多与此问题无关的数据，因此我无法发布表格架构。所涉及的列均为int（11）unsigned NOT NULL DEFAULT '0'。对于此目的，我将称其为mask和mask_alt。

- Jericon

---

你能否提供更多关于“匹配”、“不同”和“已检查”的信息？ - Bohemian

请问您能否发布您的表模式(schema)？ - John Woo

@johntotetwoo，我不能，但我已经添加了更多关于它的细节。 - Jericon

Answer 1

select
    count(*) as rows_checked,
    sum(col = col2) as rows_matching,
    sum(col != col2) as rows_different
from table

请注意sum(condition)的优雅使用。
这是因为在mysql中，true是1，false是0。对这些进行求和可以计算条件为true的次数。这比case when condition then 1 else 0 end更加优雅，后者相当于编码if (condition) return true else return false;而不是简单地return condition;。

- Bohemian

---

现在尝试这个。我已将它限制为仅过去一天。不幸的是，我必须限制某些列，但这些列并没有索引，所以这会导致大约 300M 行的完整表扫描：S - Jericon

有没有一种方法可以使用索引列最初限制行，然后在其上运行上述操作。例如，如果您确信限制出现在最近一周的数据中，请首先选择最近一周的数据作为内部别名查询，然后查询该结果。 - Bohemian

我收回之前的说法，我限制的一些列是有索引的，但它们的基数不是很高。在这张表里的8亿行中，几乎有4亿需要被扫描。其中，有1.43亿个匹配项。而且这只是过去10周的结果。 - Jericon

Answer 2

假设您想要计算 col1 等于或不等于 col2 的行数，您可以使用聚合函数 SUM() 结合 CASE 语句：

SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN col = col2 THEN 1 ELSE 0 END )AS matching,
  SUM(CASE WHEN col <> col2 THEN 1 ELSE 0 END) AS non_matching
FROM table

如果以上方法不够高效，可能更有效的方式是在子查询中获取COUNT(*)总数，并使用该值减去匹配数以获取非匹配数。

SELECT
  total,
  matching,
  total - matching AS non_matching
FROM
(
  SELECT
    COUNT(*) AS total,
    SUM(CASE WHEN col = col2 THEN 1 ELSE 0 END )AS matching
  FROM table
) sumtbl

- Michael Berkowski