主页 / dba / 问题 / 7763
Accepted
gbn
Asked: 2011-11-12 02:16:04 +0800 CST

MySQL聚合异常

  • 772

我正在检查某些列对索引的选择性。
这种“忽略我给你的”行为记录在哪里？

这给出了 4,851,908、4,841,060 和 1,000,052

SELECT
     COUNT(*), 
     COUNT(DISTINCT Col1), COUNT(DISTINCT Col2)
FROM Sometable;

根据 MySQL 扩展，这给出了 4,843,634 个唯一对

SELECT COUNT(DISTINCT Col1, Col2) FROM Sometable

以下是错误的：无论任何填充列或表达式顺序如何，单个 COUNT(DISTINCT colx) 都给出 4,843,634 个唯一对计数。

我预计COUNT(DISTINCT Col1) = 4,841,060，和COUNT(DISTINCT Col1) = 1,000,052。

SELECT COUNT(DISTINCT Col1), COUNT(DISTINCT Col2) FROM Sometable

SELECT COUNT(DISTINCT Col2), COUNT(DISTINCT Col1) FROM Sometable

SELECT COUNT(DISTINCT Col1), 1 AS Filler, COUNT(DISTINCT Col2) FROM Sometable

但这又用另一个聚合给出了正确的值（就像COUNT(*)上面一样）

SELECT COUNT(DISTINCT Col1), MAX(col1) AS Filler, COUNT(DISTINCT Col2) FROM Sometable

问题，如果不清楚：

  • 为什么COUNT(DISTINCT Col1), COUNT(DISTINCT Col2)表现得像COUNT(DISTINCT Col1, Col2)
  • 为什么需要另一个聚合才能使其工作？
mysql aggregate

2 个回答

  1. Best Answer

    看起来您遇到了这个回归错误：

    select count(distinct N1), count(distinct N2) from test.AA” 工作不正确
    ......
    “当存在唯一索引时会发生此错误”

    建议的解决方法之一是使用sql_buffer_result

    • 7

  2. 没有看到您的确切结果，我不确定我是否了解问题所在。我在我机器上的随机表上尝试了这个，并得到了我预期的结果。

    mysql> select count(*), count(distinct location_country), count(distinct referer_name) from piwik_log_visit;
+----------+----------------------------------+------------------------------+
| count(*) | count(distinct location_country) | count(distinct referer_name) |
+----------+----------------------------------+------------------------------+
|    44176 |                              109 |                          291 |
+----------+----------------------------------+------------------------------+
1 row in set (0.81 sec)


mysql> select count(distinct location_country,referer_name) from piwik_log_visit;
+-----------------------------------------------+
| count(distinct location_country,referer_name) |
+-----------------------------------------------+
|                                           932 |
+-----------------------------------------------+
1 row in set (0.19 sec)

mysql> select count(distinct location_country), count(distinct referer_name) from piwik_log_visit;
+----------------------------------+------------------------------+
| count(distinct location_country) | count(distinct referer_name) |
+----------------------------------+------------------------------+
|                              109 |                          291 |
+----------------------------------+------------------------------+
1 row in set (0.16 sec)

mysql> select count(distinct referer_name), count(distinct location_country) from piwik_log_visit;
+------------------------------+----------------------------------+
| count(distinct referer_name) | count(distinct location_country) |
+------------------------------+----------------------------------+
|                          291 |                              109 |
+------------------------------+----------------------------------+
1 row in set (0.16 sec)

mysql> select count(distinct location_country), 1 as filler, count(distinct referer_name) from piwik_log_visit;
+----------------------------------+--------+------------------------------+
| count(distinct location_country) | filler | count(distinct referer_name) |
+----------------------------------+--------+------------------------------+
|                              109 |      1 |                          291 |
+----------------------------------+--------+------------------------------+
1 row in set (0.16 sec)

mysql> select count(distinct location_country), max(location_country) as filler, count(distinct referer_name) from piwik_log_visit;
+----------------------------------+--------+------------------------------+
| count(distinct location_country) | filler | count(distinct referer_name) |
+----------------------------------+--------+------------------------------+
|                              109 | zw     |                          291 |
+----------------------------------+--------+------------------------------+
1 row in set (0.25 sec)

    你说

    这些都给了 4,843,634 这不是我要求的

    从某个表中选择计数（DISTINCT Col1），COUNT（DISTINCT Col2）

    从某个表中选择计数（DISTINCT Col2），COUNT（DISTINCT Col1）

    选择 COUNT(DISTINCT Col1), 1 AS Filler, COUNT(DISTINCT Col2) FROM Sometable

    但这没有任何意义。前两个查询应返回两列，最后一个应返回 3。

    您能否在线提供您的实际结果以及您期望看到的结果，也许我们可以弄清楚是否存在实际问题，或者您是否只是误解了某些内容。

    作为参考，我在 Percona Server 5.5.16 上运行了这个

    mysql> select @@version;
+---------------+
| @@version     |
+---------------+
| 5.5.16-55-log |
+---------------+
1 row in set (0.00 sec)

    编辑：我也在另一个有 ~5MM 行的数据集上尝试了这个，并得到了相同的结果......一切都检查过了。这是在 Percona Server 5.1.43 上

    • 0

相关问题