使用PL/SQL分析函数统计首字母为K的农场计数问题

现有代码计算结果为1的原因 #

你代码中的substr(farm,1)没有指定截取长度，在Oracle语法中，SUBSTR函数省略第三个长度参数时，会从指定起始位置开始截取到字符串末尾，等价于取完整的farm字段值。
所以partition by id,substr(farm,1)实际上是按「id+完整farm值」分组，每个分组仅对应1行数据，count统计的结果自然为1。如果你要取farm的首字母，正确的写法应该是substr(farm,1,1)。

统计首字母为K的农场数量的正确SQL #

你的需求是统计同一id下所有首字母为K的农场总数，不需要按farm的首字母做分区，直接在窗口函数的count中增加条件过滤即可，正确代码如下：

with tree_harvest
as ( 
select 1 as id, 'PINE' as tree, 'K001' as farm from dual union all
select 1 as id, 'PINE' as tree, '0003' as farm from dual union all
select 1 as id, 'PINE' as tree, 'K002' as farm from dual union all
select 1 as id, 'PINE' as tree, 'K003' as farm from dual
)
select id, tree,farm,
       count(*) over (partition by id) as id_count,
       case
       when regexp_like(farm,'^K','i') 
       then count(case when regexp_like(farm,'^K','i') then 1 end) over (partition by id)
       else 0
       end as k_count
       from tree_harvest;

执行后就会得到你期望的结果：

ID TREE FARM   ID_COUNT  K_COUNT  
  1 PINE  0003  4         0
  1 PINE  K001  4         3
  1 PINE  K002  4         3
  1 PINE  K003  4         3

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