如何改写GROUP BY统计SQL实现按设备数分组统计客户数且无需新表

解决方案 #

完全不需要创建新表，直接嵌套一层聚合查询即可实现需求，两种常见写法如下：

写法1：普通子查询（兼容所有支持标准SQL的数据库） #

SELECT 
  count AS devices,
  COUNT(customer_id) AS customer
FROM (
  SELECT customer_id, COUNT(*) AS count
  FROM devices 
  GROUP BY customer_id
) AS t
GROUP BY count
ORDER BY count DESC;

写法2：CTE写法（支持MySQL 8.0+、PostgreSQL、SQL Server等主流新数据库，可读性更高） #

WITH customer_device_cnt AS (
  SELECT customer_id, COUNT(*) AS count
  FROM devices 
  GROUP BY customer_id
)
SELECT 
  count AS devices,
  COUNT(customer_id) AS customer
FROM customer_device_cnt
GROUP BY count
ORDER BY count DESC;

实现逻辑 #

内层查询先统计出每个客户对应的设备持有量，外层直接基于这个临时计算的结果集，按设备数量分组统计对应分组的客户总数即可，整个过程仅在数据库内存中完成临时计算，无需用户手动创建任何表。

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