개발 참고자료

select id, no, min(decode(match, 'YES', 3, no)) over (partition by id) from (
select id, no,
min(decode(no, 3, 'YES', NULL))
over (partition by id order by no rows between unbounded preceding and unbounded following) match
from(
select 'RI6490077' id, NULL no from dual union all
select 'RI6490078' id, '7' no from dual union all
select 'RI6490078' id, '1' no from dual union all
select 'RI6490079' id, '6' no from dual union all
select 'RI6490079' id, '5' no from dual union all
select 'RI6490079' id, '4' no from dual union all
select 'RI6490079' id, '3' no from dual union all
select 'RI6490079' id, '1' no from dual )
) ;



select id, no,
min(decode(no, 3, 'YES', NULL))
over (partition by id order by no rows between unbounded preceding and unbounded following) match
from(
select 'RI6490077' id, NULL no from dual union all
select 'RI6490078' id, '7' no from dual union all
select 'RI6490078' id, '1' no from dual union all
select 'RI6490079' id, '6' no from dual union all
select 'RI6490079' id, '5' no from dual union all
select 'RI6490079' id, '4' no from dual union all
select 'RI6490079' id, '3' no from dual union all
select 'RI6490079' id, '1' no from dual);








SELECT #OZParam.tmonth# as 검정월,
left(D.DO, 2) as 도,
count(s.nfarm) as 농가수,
sum(count(s.nfarm))
over ( order by d.do rows between unbounded preceding and unbounded following) farm_cnt,
sum(s.tmdcno) as 경산우,
sum(s.tmcno) as 착유우,
round(avg(decode(sign(s.age), 1, s.age))) as 월령,
round(avg(decode(sign(s.pari), 1, s.pari)), 1) as 산차,
round(avg(decode(sign(s.fpmsum), 1, s.fpmsum))) as 초산월령,
round(avg(decode(sign(s.cumday), 1, s.cumday))) as 착유일수,
round(avg(decode(sign(s.milk305), 1, s.milk305))) as 유량305,
round(avg(decode(sign(s.fat305), 1, s.fat305)),2) as 유지량305,
round(avg(decode(sign(s.intday), 1, s.intday))) as 분만간격,
round(avg(decode(sign(s.scc), 1, s.scc))) as 체세포,
round(avg(decode(sign(s.tnpday), 1, s.tnpday))) as 공태일수
FROM cong_ref c, v_summary_farm s, farm_ref f,
(SELECT distinct a.cong_code, a.farm_code, b.do, b.kun
FROM FARM_REF A, ZIP B
WHERE A.FARM_ZIP = B.ZIP) D
WHERE c.cong_code = s.cong
AND s.cong = f.cong_code
AND s.farm = f.farm_code
AND s.cong = d.cong_code
AND s.farm = d.farm_code
AND s.tmonth = #OZParam.tmonth#
GROUP BY D.DO