| Query |
with part1 as
(
select distinct country,annexe,A.reg_area,reg_gear_cod,species,year_d,rectangle,english_name,reg_gear,sum(A.landings) as landings,sum(discards)/(sum(discards)+sum(B.landings)) as tx_discards
from landing_rect_sd A inner join discard_landings_sd B
using(country,annexe,reg_area,reg_gear_cod,species,year_d,vessel_length,reg_gear,specon) inner join def_stocks_area using (species,rectangle) left join asfis on (species=a_code)
where fishstock like 'bss.27.4bc7ad–h' and year_d=2015-1
and discards>0 group by reg_gear,country,annexe,A.reg_area,reg_gear_cod,species,year_d,rectangle,english_name order by year_d,sum(A.landings)
),
part2 as
(
select country,annexe,reg_gear_cod,species,year_d,rectangle,english_name,landings,reg_area,case when tx_discards=1 then 0.99 else tx_discards end as tx_discards from part1
)
,part4 as
(
select distinct rectangle as LIEU,sum(((1/(1-tx_discards))*landings)-landings) as TOTAL from part2 left join countries using(country)
group by LIEU
)
select distinct lieu,100*TOTAL/TOT as VAL from part4, (select sum(TOTAL) as TOT from part4) T1
where 100*TOTAL/TOT>0.05
|
Retour à la fiche 