Oracle SQL查询优化：验证改写语句正确性并简化子查询

原查询的意图 #

你的需求是：筛选出在profile表中总记录数大于2的master_id，并获取这些master_id对应的所有distinct (master_id, last_name)组合。

原查询虽能实现需求，但存在冗余：

• count_a = count(*) over (partition by master_id order by purchased_date desc) 是按购买日期倒序的累计计数，而count_b = max(count_a) over (partition by master_id) 本质就是该master_id的总记录数——完全可以用count(*) over (partition by master_id)直接得到，无需两层窗口函数嵌套。

你改写的语句的问题 #

你改写的语句存在逻辑错误，无法正确实现需求：

select * 
from profile a
join( select * from (
select 
master_id, 
max (count(*)) over (partition by master_id) count 
from profile)  
where count >2) b
ON a. master_id = b. master_id

• 内层的select master_id, max(count(*)) over (partition by master_id) count from profile 语法逻辑错误：count(*)是聚合函数，未加group by master_id时，它会计算整个表的总行数；再套窗口函数max(...) over (partition by master_id)，每个master_id对应的count值都是整个表的行数，这样筛选count>2会把所有master_id都保留，完全不符合“仅保留总记录数>2的master_id”的要求。
• 即使加上group by master_id，max(count(*)) over (partition by master_id)也毫无意义——分组后每个master_id只有一行，max结果就是count(*)本身，直接用having count(*)>2筛选即可。

高效的正确写法 #

推荐两种更简洁高效的实现方式：

方式1：分组筛选+关联去重 #

先筛选出总记录数>2的master_id，再关联原表获取去重后的last_name：

select distinct a.master_id, a.last_name
from profile a
join (
    select master_id
    from profile
    group by master_id
    having count(*) > 2
) b on a.master_id = b.master_id;

方式2：单窗口函数筛选 #

用一层子查询通过窗口函数直接计算每个master_id的总记录数，再筛选去重：

select distinct master_id, last_name
from (
    select 
        master_id, 
        last_name,
        count(*) over (partition by master_id) as total_count
    from profile
)
where total_count > 2;

这两种写法都减少了不必要的子查询和窗口函数嵌套，执行效率远高于原查询和你改写的错误语句。如果profile表数据量较大，建议给master_id字段建立索引，进一步提升查询速度。

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