如何获取保险产品所有组合套餐及对应客户数？现有SQL仅支持双产品组合

• 数据样本：customers表包含两列，customer_id（客户ID）和insurance_product（保险产品），一个客户可对应多个不同的保险产品。
• 目标：编写SQL查询，返回所有保险产品组合套餐（包含单个产品、2个及以上产品的组合），以及每个套餐对应的客户数量。
• 当前问题：现有查询仅能返回包含两个产品的套餐，无法获取多产品组合。

现有查询代码：

select concat(insuranct_product1,insuranct_product2) as bundle_package,
count(distinct customer_id) as customer_count
from(
select t1.customer_id, t1.insuranct_product,t2.insuranct_product
from customers t1
join 
customers t2
on t1.customer_id=t2.customer_id
where t1.insuranct_product<t2.insuranct_product
) group by concat(insuranct_product1,insuranct_product2)

核心思路是先为每个客户生成专属的产品组合字符串（固定产品排序避免重复统计），再按组合字符串分组统计客户数。以下是不同SQL方言的实现：

MySQL 版本 #

SELECT 
  bundle_package,
  COUNT(customer_id) AS customer_count
FROM (
  SELECT 
    customer_id,
    GROUP_CONCAT(insurance_product ORDER BY insurance_product SEPARATOR '+') AS bundle_package
  FROM customers
  GROUP BY customer_id
) AS customer_bundles
GROUP BY bundle_package;

PostgreSQL 版本 #

SELECT 
  bundle_package,
  COUNT(customer_id) AS customer_count
FROM (
  SELECT 
    customer_id,
    STRING_AGG(insurance_product, '+' ORDER BY insurance_product) AS bundle_package
  FROM customers
  GROUP BY customer_id
) AS customer_bundles
GROUP BY bundle_package;

SQL Server 版本（2017及以上） #

SELECT 
  bundle_package,
  COUNT(customer_id) AS customer_count
FROM (
  SELECT 
    customer_id,
    STRING_AGG(insurance_product, '+' WITHIN GROUP (ORDER BY insurance_product)) AS bundle_package
  FROM customers
  GROUP BY customer_id
) AS customer_bundles
GROUP BY bundle_package;

说明 #

• 内层查询通过聚合函数按客户ID分组，将该客户的所有保险产品按字母顺序拼接成套餐字符串，确保同一产品组合的不同排序会被识别为同一个套餐。
• 外层查询将生成的套餐字符串分组，统计每个套餐对应的客户数量。

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