我應該如何撰寫查詢以回傳以K開頭的 Farms的計數3？
為什么(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
uj5u.com熱心網友回復：
這是解決您的問題的解決方案，并且應該比您當前的方法更快（更有效）。請注意，這里的兩個決議函式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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/327545.html