MySQL中如何计算总去重用户数减去各门店分组去重购买用户数

实现方案 #

你可以直接通过标量子查询获取全表去重总用户数，再和分组统计的单店购买用户数做差即可，写法简洁且适配所有MySQL版本：

SELECT
  storeId,
  COUNT(DISTINCT userName) AS purchased,
  (SELECT COUNT(DISTINCT userName) FROM purchases) - COUNT(DISTINCT userName) AS notPurchased
FROM purchases
GROUP BY storeId
ORDER BY storeId;

可选优化写法 #

如果是MySQL 8.0及以上版本，也可以用CTE提前计算总用户数，适合需要多次复用总用户数的场景，性能更优：

WITH total_user_cnt AS (
  SELECT COUNT(DISTINCT userName) AS cnt FROM purchases
)
SELECT
  p.storeId,
  COUNT(DISTINCT p.userName) AS purchased,
  tu.cnt - COUNT(DISTINCT p.userName) AS notPurchased
FROM purchases p
CROSS JOIN total_user_cnt tu
GROUP BY p.storeId, tu.cnt
ORDER BY p.storeId;

如果表数据量较大，也可以提前把总用户数存入会话变量，减少重复计算开销：

-- 先计算总用户数存入变量
SET @total_user = (SELECT COUNT(DISTINCT userName) FROM purchases);
-- 分组统计计算结果
SELECT
  storeId,
  COUNT(DISTINCT userName) AS purchased,
  @total_user - COUNT(DISTINCT userName) AS notPurchased
FROM purchases
GROUP BY storeId
ORDER BY storeId;

以上三种写法执行后的输出都和你给出的期望结果完全一致。

内容的提问来源于stack exchange，提问作者Daniel Wright