如何基于给定Oracle查询按Tree_ID统计各类产品汇总数量？

问题场景 #

现有Oracle查询语句，期望按TREE_ID分组，统计每组的PULP_COUNT（PULP类型数量）、CHIP_COUNT（CHIP类型数量）和TREE_TOTAL（该组总数量），但当前查询无法输出正确结果：

原查询语句 #

with tree_row
as (
select '1111' as tree_id, 'PULP' as tree_product from dual union all
select '1111' as tree_id, 'PULP' as tree_product from dual union all
select '2222' as tree_id, 'PULP' as tree_product from dual union all
select '2222' as tree_id, 'CHIP' as tree_product from dual union all
select '3333' as tree_id, 'PULP' as tree_product from dual union all
select '3333' as tree_id, 'CHIP' as tree_product from dual union all
select '3333' as tree_id, 'CHIP' as tree_product from dual
)
select distinct tree_id, 
       count(*) over (partition by tree_id, tree_product) as pulp_count,
       count(*) over (partition by tree_id, tree_product) as chip_count,
       count(*) over (partition by tree_id)               as tree_total
       from tree_row;

期望结果 #

TREE_ID PULP_COUNT  CHIP_COUNT TREE_TOTAL
    1111     2           0            2
    2222     1           1            2
    3333     1           2            3

问题原因 #

• 原查询用同一个count(*) over (partition by tree_id, tree_product)同时赋值给pulp_count和chip_count，导致两个字段的值都是当前行tree_product类型的计数，无法分别统计两种产品的数量
• distinct关键字无法消除窗口函数带来的重复行，最终输出存在多余记录

修正方案 #

方案一：条件聚合+GROUP BY（推荐，性能更优） #

直接按tree_id分组，通过CASE WHEN分别统计两种产品的数量：

with tree_row
as (
select '1111' as tree_id, 'PULP' as tree_product from dual union all
select '1111' as tree_id, 'PULP' as tree_product from dual union all
select '2222' as tree_id, 'PULP' as tree_product from dual union all
select '2222' as tree_id, 'CHIP' as tree_product from dual union all
select '3333' as tree_id, 'PULP' as tree_product from dual union all
select '3333' as tree_id, 'CHIP' as tree_product from dual union all
select '3333' as tree_id, 'CHIP' as tree_product from dual
)
select 
    tree_id,
    count(case when tree_product = 'PULP' then 1 end) as pulp_count,
    count(case when tree_product = 'CHIP' then 1 end) as chip_count,
    count(*) as tree_total
from tree_row
group by tree_id
order by tree_id;

方案二：窗口函数实现 #

如果需要保留窗口函数的写法，可结合条件计数+DISTINCT去重：

with tree_row
as (
select '1111' as tree_id, 'PULP' as tree_product from dual union all
select '1111' as tree_id, 'PULP' as tree_product from dual union all
select '2222' as tree_id, 'PULP' as tree_product from dual union all
select '2222' as tree_id, 'CHIP' as tree_product from dual union all
select '3333' as tree_id, 'PULP' as tree_product from dual union all
select '3333' as tree_id, 'CHIP' as tree_product from dual union all
select '3333' as tree_id, 'CHIP' as tree_product from dual
)
select distinct
    tree_id,
    count(case when tree_product = 'PULP' then 1 end) over (partition by tree_id) as pulp_count,
    count(case when tree_product = 'CHIP' then 1 end) over (partition by tree_id) as chip_count,
    count(*) over (partition by tree_id) as tree_total
from tree_row
order by tree_id;

说明 #

• 方案一直接分组聚合，避免了窗口函数的重复计算和DISTINCT的去重开销，性能更优
• 两种方案均能准确输出期望的统计结果，可根据实际场景选择

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