Query |
with tmp1 as
(
select distinct year_d,rectangle as LIEU,country_name as GROUPE,sum(landings) as VAL from
landing_rect_sd inner join def_stocks_area using (species,rectangle) left join countries using(country)
where fishstock like 'had.27.7a' 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) as VAL from regroupement where year_d=2015 group by LIEU,GROUPE
order by LIEU,GROUPE
|