mysqlで個別の複数の列をカウントする問題を解決する方法

再現されたテスト データベースは次のとおりです:

CREATE TABLE `test_distinct` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `b` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

テーブル内のテスト データは次のとおりです。ここで、これら 3 つの列の重複排除後の列の数をカウントする必要があります。

#問題分析

友人は、問題を特定するために 4 つのクエリ ステートメントを教えてくれました。

SELECT COUNT(*) AS cnt FROM test_distinct;
SELECT COUNT(DISTINCT id, a, b) as cnt FROM test_distinct;
SELECT id, a, b, COUNT(*) AS cnt FROM test_distinct GROUP BY id, a, b HAVING cnt > 1;
SELECT 
	l.id AS l_id,
	l.a AS l_a,
	l.b AS l_b,
	r.id AS r_id,
	r.a AS r_a,
	r.b AS r_b
FROM test_distinct l LEFT JOIN test_distinct r
ON l.id = r.id AND l.a = r.a AND l.b = r.b
WHERE r.id is NULL or r.id = 'null';

クエリの結果は次のとおりです。 ## ＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃知らせ！ ！ ！テストデータからどこに問題があるのか​​はすぐに推測できますが、テーブルには 30,000 件以上のデータがあり、肉眼でデータを確認することは不可能であることがわかりました。

上記のクエリ結果には直観に反した点が 2 つあります。

重複排除統計の後に 2 番目のデータが欠落していますが、3 番目のデータの結果には次のことが示されています。同一のデータは存在しません。

同じテーブルを使用して左外部接続を行う場合、駆動テーブルにはデータがありますが、駆動テーブルは空です。

まず 2 番目の質問を見てみましょう。公式ドキュメントには次の説明があります:

ON 句を使用する場合、それに含まれる条件式は WHERE 句で使用されるものと同じです。一般的な状況は、ON 句を使用してテーブルの結合条件を指定し、WHERE 句を使用して結果セットに含まれる行を制限することです。

• LEFT JOIN の ON または USING 部分の条件に一致する行が右側のテーブルにない場合、右側のテーブルは NULL に設定されたすべての列を使用します。

• 算術比較演算子 (=、 など) を使用して NULL を比較することはできません。

SELECT NULL = NULL;
SELECT NULL IS NULL;

• したがって、2 番目の問題は、NULL=NULL の結果が常に False になることです。その結果、 2 つの行は元々等しいデータの結果は等しくありません。
  しかし、これでは最初の問題、つまり重複排除後にデータの一部が消えた理由は解決されません。ただし、欠落しているデータはおそらく NULL 値に関連していると推測できます。
• カウントと個別の 2 つの操作を分離します。

  SELECT COUNT(*) as cnt FROM (SELECT  DISTINCT id, a, b FROM test_distinct) as tmp;

え?結果は正しいです。つまり、count(distinct expr)

によって生成されたクエリ プランは、想像したものと異なる可能性があります。最初に重複を削除してからカウントするわけではありません。クエリ プランを分析するには、 Explain を使用します。

表からわかるように、mysql 実行エンジンは

を直接カウントします。 count(distinct expr)

クエリとして、公式ドキュメントを確認してください:

解決策

問題は最終的に明らかになりました。この問題を解決するには 2 つの方法があります。1 つ目は、最初に重複を削除してからカウントすることです。2 つ目は、IFNULL()

関数を使用することです:

SELECT COUNT(DISTINCT id, a, IFNULL(b, '0')) as cnt FROM test_distinct;

さらに、count( )使用方法:

SELECT id, a, b, COUNT(*) FROM test_distinct GROUP BY id, a, b;
SELECT id, a, b, COUNT(b) FROM test_distinct GROUP BY id, a, b;

知識ポイント

算術比較演算子 (次のような) は使用できません。 =、) を使用して null 値を比較します。

count(distinct expr) は、expr 列内の空でない個別の行の数を返します。

COUNT() には 2 つの異なる用途があります。列内の値の数をカウントするために使用でき、もう 1 つは行の数をカウントするために使用できます。列の値をカウントする場合、列の値は空でない必要があります (NULL はカウントされません)。 COUNT() 関数のかっこ内に列または式が指定されている場合、関数は式に値を持つ結果の数をカウントします。 COUNT() のもう 1 つの機能は、結果セット内の行数をカウントすることです。 MySQL は、括弧内の式の値を空にすることができないことを確認すると、実際には行数をカウントします。最も単純なことは、COUNT() を使用する場合です。この場合、ワイルドカードは予想したようにすべての列に展開されません。実際、すべての列が無視され、すべての行が直接カウントされます - "ハイパフォーマンス MySQL";

InnoDB では、SELECT COUNT(*) と SELECT COUNT(1) は同じ方法で処理され、パフォーマンスに違いはありません。

以上がmysqlで個別の複数の列をカウントする問題を解決する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。