mysql踩坑之count distinct多列问题怎么解决

发布时间：2023-03-23 10:18:31 作者：iii
来源：亿速云阅读：207

MySQL踩坑之COUNT DISTINCT多列问题怎么解决

在MySQL中，COUNT(DISTINCT column) 是一个常用的聚合函数，用于统计某一列中不同值的数量。然而，当我们需要统计多列的组合中不同值的数量时，直接使用 COUNT(DISTINCT column1, column2) 会导致语法错误。本文将深入探讨这个问题，并提供几种解决方案。

1. 问题描述

假设我们有一个表 orders，其中包含以下字段：

order_id: 订单ID
customer_id: 客户ID
product_id: 产品ID
order_date: 订单日期

我们想要统计每个客户购买的不同产品的数量。也就是说，我们需要统计 customer_id 和 product_id 组合中不同值的数量。

1.1 错误示例

如果我们尝试直接使用 COUNT(DISTINCT customer_id, product_id)，MySQL会报错：

SELECT customer_id, COUNT(DISTINCT customer_id, product_id) AS unique_products
FROM orders
GROUP BY customer_id;

错误信息如下：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'product_id) AS unique_products FROM orders GROUP BY customer_id' at line 1

1.2 原因分析

MySQL的 COUNT(DISTINCT) 函数只支持单列，不支持多列。因此，直接使用 COUNT(DISTINCT column1, column2) 会导致语法错误。

2. 解决方案

2.1 使用子查询

一种常见的解决方案是使用子查询。我们可以在子查询中先对 customer_id 和 product_id 进行去重，然后在外部查询中进行统计。

SELECT customer_id, COUNT(*) AS unique_products
FROM (
    SELECT DISTINCT customer_id, product_id
    FROM orders
) AS subquery
GROUP BY customer_id;

2.1.1 解释

子查询 SELECT DISTINCT customer_id, product_id FROM orders 会返回所有 customer_id 和 product_id 的唯一组合。
外部查询 SELECT customer_id, COUNT(*) AS unique_products FROM subquery GROUP BY customer_id 会对每个 customer_id 统计其对应的唯一 product_id 数量。

2.2 使用 `GROUP_CONCAT` 和 `FIND_IN_SET`

另一种解决方案是使用 GROUP_CONCAT 和 FIND_IN_SET 函数。这种方法适用于数据量较小的情况。

SELECT customer_id, 
       (LENGTH(GROUP_CONCAT(DISTINCT product_id)) - LENGTH(REPLACE(GROUP_CONCAT(DISTINCT product_id), ',', '')) + 1) AS unique_products
FROM orders
GROUP BY customer_id;

2.2.1 解释

GROUP_CONCAT(DISTINCT product_id) 会将每个 customer_id 对应的 product_id 拼接成一个字符串，并用逗号分隔。
LENGTH(GROUP_CONCAT(DISTINCT product_id)) 计算拼接后的字符串长度。
LENGTH(REPLACE(GROUP_CONCAT(DISTINCT product_id), ',', '')) 计算去掉逗号后的字符串长度。
通过计算两者的差值加1，可以得到不同 product_id 的数量。

2.3 使用 `CONCAT` 和 `COUNT(DISTINCT)`

我们还可以使用 CONCAT 函数将多列拼接成一个字符串，然后使用 COUNT(DISTINCT) 进行统计。

SELECT customer_id, COUNT(DISTINCT CONCAT(customer_id, '-', product_id)) AS unique_products
FROM orders
GROUP BY customer_id;

2.3.1 解释

CONCAT(customer_id, '-', product_id) 将 customer_id 和 product_id 拼接成一个字符串。
COUNT(DISTINCT CONCAT(customer_id, '-', product_id)) 统计拼接后的字符串中不同值的数量。

2.4 使用 `ROW_NUMBER()` 窗口函数（MySQL 8.0+）

在MySQL 8.0及以上版本中，我们可以使用窗口函数 ROW_NUMBER() 来实现多列去重统计。

WITH ranked_orders AS (
    SELECT customer_id, product_id,
           ROW_NUMBER() OVER (PARTITION BY customer_id, product_id ORDER BY order_date) AS rn
    FROM orders
)
SELECT customer_id, COUNT(*) AS unique_products
FROM ranked_orders
WHERE rn = 1
GROUP BY customer_id;

2.4.1 解释

ROW_NUMBER() OVER (PARTITION BY customer_id, product_id ORDER BY order_date) 为每个 customer_id 和 product_id 组合生成一个行号。
WHERE rn = 1 只保留每个组合的第一行，实现去重。
外部查询 SELECT customer_id, COUNT(*) AS unique_products FROM ranked_orders WHERE rn = 1 GROUP BY customer_id 统计每个 customer_id 对应的唯一 product_id 数量。

3. 性能比较

不同的解决方案在性能上可能会有所差异，具体取决于数据量、索引情况以及MySQL版本。

子查询：适用于大多数情况，性能较好，尤其是在有适当索引的情况下。
GROUP_CONCAT 和 FIND_IN_SET：适用于数据量较小的情况，性能较差，尤其是在数据量较大时。
CONCAT 和 COUNT(DISTINCT)：性能较好，但需要注意拼接后的字符串长度，避免超出MySQL的限制。
ROW_NUMBER() 窗口函数：适用于MySQL 8.0及以上版本，性能较好，尤其是在有适当索引的情况下。

4. 总结

在MySQL中，COUNT(DISTINCT) 不支持多列统计，但我们可以通过子查询、GROUP_CONCAT、CONCAT 或窗口函数等方法来实现多列去重统计。选择哪种方法取决于具体的业务需求、数据量以及MySQL版本。在实际应用中，建议根据具体情况选择最合适的解决方案，并进行性能测试以确保查询效率。

希望本文能帮助你解决MySQL中 COUNT DISTINCT 多列统计的问题，避免在实际开发中踩坑。