如何在SQL中筛选各客户维度下最高产品计数的统计结果
按客户维度筛选最高产品出现次数记录的SQL实现
核心逻辑
你当前的查询已经完成「统计每个客户-产品维度的购买次数」的基础计算,后续筛选无需改动原有分组统计逻辑,只需要针对每个客户的分组集合,保留集合内CUSTOMER_PRODUCT_COUNT值最大的条目即可。
实现方案1:窗口函数取组内排名(主流现代数据库通用)
适配Oracle、MySQL 8.0+、PostgreSQL等支持窗口函数的数据库,无需多层复杂关联,执行效率更高:
SELECT customer_id, product_id, CUSTOMER_PRODUCT_COUNT FROM ( SELECT customer_id, product_id, COUNT(product_id) CUSTOMER_PRODUCT_COUNT, -- 按客户分区,将同客户下的产品按购买次数降序排名,数值相同的条目会获得相同排名 RANK() OVER (PARTITION BY customer_id ORDER BY COUNT(product_id) DESC) AS row_rank FROM Order_Details GROUP BY customer_id, product_id ) base -- 仅保留每个客户组内排名第1的最高计数条目 WHERE row_rank = 1 ORDER BY customer_id;
执行后输出结果和你给出的预期完全匹配:
- 自动保留C1组内计数最高的P3(计数2),筛除计数为1的P4
- 自动保留C3组内计数最高的P2(计数4),筛除计数为1的P3、P4
- 对于仅存在1条记录的C2、C4、C5,该记录本身就是组内最高值,会被正常保留
补充说明:如果出现同客户下两个产品计数相同、并列最高的场景,RANK()会把两条记录都保留;如果业务上强制每个客户只输出1条最高记录,可以把RANK()替换为ROW_NUMBER(),数据库会自动按指定排序规则选1条返回。
实现方案2:关联匹配客户最高值(适配低版本老旧数据库)
针对不支持窗口函数的低版本数据库(比如MySQL 5.x),可以先计算每个客户的最高计数阈值,再关联原分组结果匹配条目:
-- 预先计算每个客户对应的最高购买计数 WITH customer_max_threshold AS ( SELECT customer_id, MAX(CUSTOMER_PRODUCT_COUNT) AS max_purchase_cnt FROM ( SELECT customer_id, product_id, COUNT(product_id) CUSTOMER_PRODUCT_COUNT FROM Order_Details GROUP BY customer_id, product_id ) group_stat GROUP BY customer_id ) -- 关联匹配计数等于客户最高阈值的记录 SELECT stat.customer_id, stat.product_id, stat.CUSTOMER_PRODUCT_COUNT FROM ( SELECT customer_id, product_id, COUNT(product_id) CUSTOMER_PRODUCT_COUNT FROM Order_Details GROUP BY customer_id, product_id ) stat INNER JOIN customer_max_threshold max_t ON stat.customer_id = max_t.customer_id AND stat.CUSTOMER_PRODUCT_COUNT = max_t.max_purchase_cnt ORDER BY stat.customer_id;
自定义调整说明:如果业务上需要省略C2、C4、C5这类仅单条计数为1的冗余条目,只需要在最外层查询的筛选条件中追加AND stat.CUSTOMER_PRODUCT_COUNT > 1即可,不会影响核心筛选规则。
内容的提问来源于stack exchange,提问作者Samit Paudel
相关产品推荐
相关产品推荐

