基于product_type筛选的多类产品购买客户数统计及前端适配问题

数据集 #

需求 #

根据筛选的product_type统计客户数：

• 选1个品类：统计买过该品类的去重客户数
• 选多个品类：统计同时买过所有选中品类的去重客户数

现有SQL的问题 #

最初的SQL会把买过任意选中品类的客户都算进去，不符合多品类全购买的要求：

select count(distinct customer) from price 
where product_type in ('electronics', 'stationary')

调整后的SQL能得到正确结果，但结果里不带筛选的product_type信息，前端动态切换筛选条件时没法对应上：

select count(distinct customer) 
from price
where product_type in ('electronics', 'stationary')
having count(distinct product_type) = 
  (select count(distinct product_type) 
  from price 
  where product_type in ('electronics', 'stationary'))

解决方案 #

针对前端动态筛选的场景，提供三种可行思路：

1. 在结果中返回筛选的品类标识

把选中的品类拼接成字符串返回，让前端能直接关联筛选条件，同时用CTE统一管理筛选参数，避免重复写条件：

with selected_types as (
  -- 这里替换成前端传入的品类数组
  select unnest(array['electronics', 'stationary']) as product_type
)
select 
  count(distinct p.customer) as customer_count,
  string_agg(st.product_type, ',') as selected_product_types
from price p
inner join selected_types st on p.product_type = st.product_type
group by p.customer
having count(distinct p.product_type) = (select count(*) from selected_types)

2. 前端同步传递筛选品类的数量

前端发起请求时，除了传选中的product_type列表，再传一个列表长度参数（比如type_count）。SQL直接用这个参数判断，省去子查询统计数量的步骤：

-- 假设前端传入参数：type_list = ('electronics', 'stationary'), type_count = 2
select count(distinct customer) as customer_count
from (
  select customer
  from price
  where product_type in (:type_list)
  group by customer
  having count(distinct product_type) = :type_count
) as qualified_customers

3. 返回客户及对应购买品类（适合需要明细的场景）

如果前端需要展示每个符合条件的客户对应的购买品类，可以返回明细数据，再由前端统计总数：

select 
  customer,
  array_agg(distinct product_type) as purchased_types
from price
where product_type in ('electronics', 'stationary')
group by customer
-- 用数组包含操作符判断是否覆盖所有选中品类
having array_agg(distinct product_type) @> array['electronics', 'stationary']

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