如何基于数据聚合逻辑筛选并输出未聚合的原始明细结果

现有如下结构的数据表：

需要筛选出数据集中持有超过1条banana记录的person，本示例中符合条件的是person B。已知可通过数据聚合实现该筛选逻辑，但如果希望输出结果为未聚合的明细形式，效果如下，最佳实现方案是什么？

预期输出结果：

回答 #

这类需求的核心逻辑是「先按人员维度统计banana记录的数量，筛选出满足阈值的人员，再保留这些人员对应的所有原始banana记录」，不要直接用普通GROUP BY聚合，否则会把多条明细折叠成单条结果，无法保留原始行粒度。根据你使用的数据库版本，可以选择以下两种主流方案：

方案1：窗口函数实现（优先推荐） #

所有支持SQL:2003标准的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle、Spark SQL、ClickHouse等）都支持该写法，仅需一次表扫描即可完成统计和明细关联，性能最优，写法简洁：

SELECT person, fruit, date
FROM (
    SELECT
        *,
        COUNT(CASE WHEN fruit = 'banana' THEN 1 END) OVER (PARTITION BY person) AS banana_count
    FROM 你的实际表名
) t
WHERE banana_count > 1
  AND fruit = 'banana';

逻辑说明：

• 通过COUNT() OVER(PARTITION BY person)窗口函数，按人员维度分组统计每个人的banana记录总数，统计值会附加到每一条原始记录上，不会改变原始数据的行粒度
• 外层直接筛选banana记录数大于1、且水果类型为banana的行，直接输出原始明细即可。

方案2：聚合子查询关联（兼容老版本数据库） #

如果使用不支持窗口函数的老版本数据库（比如MySQL 5.x），可以先聚合算出符合条件的人员名单，再通过关联原表过滤明细：

SELECT a.*
FROM 你的实际表名 a
INNER JOIN (
    SELECT person
    FROM 你的实际表名
    WHERE fruit = 'banana'
    GROUP BY person
    HAVING COUNT(*) > 1
) b ON a.person = b.person
WHERE a.fruit = 'banana';

逻辑说明：

• 内层子查询先做聚合，筛选出持有超过1条banana记录的人员清单
• 通过内连接把人员清单和原表关联，过滤出这些人员对应的所有banana原始记录，输出结果就是非聚合的明细数据。

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