Query |
with tmp1 as
(
select distinct year_d,rectangle as LIEU,reg_gear as GROUPE,sum(landings) as VAL from
landing_rect_sd inner join def_stocks_area using (species,rectangle)
where fishstock like 'nep.fu.7' and year_d>=2004 and annexe not like 'MED'
group by LIEU,GROUPE,year_d
)
,limite as
(select 0.05*max(VALLIM) as VALLIM from (select distinct year_d,LIEU,0.05*sum(VAL) as VALLIM from tmp1 group by LIEU,year_d) tmp)
,selection_1 as
(select distinct year_d,LIEU,sum(VAL) from tmp1,limite group by LIEU,VALLIM,year_d having sum(VAL)>VALLIM)
,total as
(select GROUPE,sum(VAL) as tot from tmp1 inner join selection_1 using(LIEU) where tmp1.year_d>2015-3 group by GROUPE order by tot DESC limit 6)
,regroupement as
(select distinct tmp1.year_d,LIEU,case when total.GROUPE is null then 'Other' else GROUPE end as GROUPE,VAL as VAL
from tmp1 inner join selection_1 using(LIEU) left join total using(GROUPE))
select distinct LIEU,GROUPE,sum(VAL)/nb_year as VAL from regroupement,(select count(distinct year_d) as nb_year
from tmp1 where year_d>=2008) tmp group by LIEU,GROUPE,nb_year
order by LIEU,GROUPE
|