pl使用PL/SQL分析计数子字符串?

Question

我应该如何写查询返回一个计数3以K开头的农场

?为什么(partition by id,substr(farm,1))计算为1

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(*) over (partition by id,substr(farm,1))
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

Answer 1

这里有一个解决方案，可以解决您的问题，并且应该比您当前的方法更快(更有效)。注意，这里两个解析函数只被id分割;条件计数在count()调用本身中单独处理。同样，与K或K的比较都不区分大小写;在您尝试的查询中，有一个比较不是。我还避免使用正则表达式(速度较慢)，这里不需要。

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 lower(farm) like 'k%' then
count(case when lower(farm) like 'k%' 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 K001          4          3
1 PINE K003          4          3
1 PINE K002          4          3
1 PINE 0003          4          0