...
Därmed är allt klart för Weave.
| Code Block | ||
|---|---|---|
| ||
I create or replace view skfab_climate_24.view_smhi_rutnat_absolute_fd as ( WITH mms AS ( select greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r85_pe3)) as g_fd, least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r85_pe3)) as l_fd, greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r85_pe3)) - least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r85_pe3)) as d_fd from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute ), pro as ( -- calculate the procentage (0-100) from values select r.id, 'Frost dygn per år'as name, fd_r26_pe1 as rpc26_1_abs, fd_r26_pe2 as rpc26_2_abs, fd_r26_pe3 as rpc26_3_abs, fd_r45_pe1 as rpc45_1_abs, fd_r45_pe2 as rpc45_2_abs, fd_r45_pe3 as rpc45_3_abs, fd_r85_pe1 as rpc85_1_abs, fd_r85_pe2 as rpc85_2_abs, fd_r85_pe2pe3 as rpc85_3_abs, fd_r85_pe3, ((fd_r26_pe1-mms.l_fd)*100/mms.d_fd)::integer as rpc26_1_rel, ((fd_r26_pe2-mms.l_fd)*100/mms.d_fd)::integer as rpc26_2_rel, ((fd_r26_pe3-mms.l_fd)*100/mms.d_fd)::integer as rpc26_3_rel, ((fd_r45_pe1-mms.l_fd)*100/mms.d_fd)::integer as rpc45_1_rel, ((fd_r45_pe2-mms.l_fd)*100/mms.d_fd)::integer as rpc45_2_rel, ((fd_r45_pe3-mms.l_fd)*100/mms.d_fd)::integer as rpc45_3_rel, ((fd_r85_pe1-mms.l_fd)*100/mms.d_fd)::integer as rpc85_1_rel, ((fd_r85_pe2-mms.l_fd)*100/mms.d_fd)::integer as rpc85_2_rel, ((fd_r85_pe3-mms.l_fd)*100/mms.d_fd)::integer as rpc85_3_rel from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r , mms ) -- view the result select h.*, r.geom from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r join pro h on h.id=r.id ); create or replace view skfab_climate_24.view_smhi_rutnat_absolute_r20mm as ( WITH mms AS ( select greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r85_pe3)) as g_r20mm, least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r85_pe3)) as l_r20mm, greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r85_pe3)) - least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r85_pe3)) as d_r20mm from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute ), pro as ( -- calculate the procentage (0-100) from values select r.id, 'Extrem nederbord dygn per år'as name, r20mm_r26_pe1 as rpc26_1_abs, r20mm_r26_pe2 as rpc26_2_abs, r20mm_r26_pe3 as rpc26_3_abs, r20mm_r45_pe1 as rpc45_1_abs, r20mm_r45_pe2 as rpc45_2_abs, r20mm_r45_pe3 as rpc45_3_abs, r20mm_r85_pe1 as rpc85_1_abs, r20mm_r85_pe2 as rpc85_2_abs, r20mm_r85_pe2pe3 as rpc85_3_abs, r20mm_r85_pe3, ((r20mm_r26_pe1-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc26_1_rel, ((r20mm_r26_pe2-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc26_2_rel, ((r20mm_r26_pe3-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc26_3_rel, ((r20mm_r45_pe1-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc45_1_rel, ((r20mm_r45_pe2-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc45_2_rel, ((r20mm_r45_pe3-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc45_3_rel, ((r20mm_r85_pe1-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc85_1_rel, ((r20mm_r85_pe2-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc85_2_rel, ((r20mm_r85_pe3-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc85_3_rel from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r , mms ) -- view the result select h.*, r.geom from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r join pro h on h.id=r.id ); create or replace view skfab_climate_24.view_smhi_rutnat_absolute_r10mm as ( WITH mms AS ( select greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r85_pe3)) as g_r10mm, least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r85_pe3)) as l_r10mm, greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r85_pe3)) - least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r85_pe3)) as d_r10mm from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute ), pro as ( -- calculate the procentage (0-100) from values select r.id, 'Kraftig nederbörd dygn per år' as name, r10mm_r26_pe1 as rpc26_1_abs, r10mm_r26_pe2 as rpc26_2_abs, r10mm_r26_pe3 as rpc26_3_abs, r10mm_r45_pe1 as rpc45_1_abs, r10mm_r45_pe2 as rpc45_2_abs, r10mm_r45_pe3 as rpc45_3_abs, r10mm_r85_pe1 as rpc85_1_abs, r10mm_r85_pe2 as rpc85_2_abs, r10mm_r85_pe2pe3 as rpc85_3_abs, r10mm_r85_pe3, ((r10mm_r26_pe1-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc26_1_rel, ((r10mm_r26_pe2-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc26_2_rel, ((r10mm_r26_pe3-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc26_3_rel, ((r10mm_r45_pe1-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc45_1_rel, ((r10mm_r45_pe2-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc45_2_rel, ((r10mm_r45_pe3-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc45_3_rel, ((r10mm_r85_pe1-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc85_1_rel, ((r10mm_r85_pe2-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc85_2_rel, ((r10mm_r85_pe3-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc85_3_rel from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r , mms ) -- view the result select h.*, r.geom from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r join pro h on h.id=r.id ); create or replace view skfab_climate_24.view_smhi_rutnat_absolute_pr as ( WITH mms AS ( select greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe3)) as g_pr, least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe3)) as l_pr, greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe3)) - least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe3)) as d_pr from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute ), pro as ( -- calculate the procentage (0-100) from values select r.id, 'Nederbord dygn per år' as name, pr_r26_pe1 as rpc26_1_abs, pr_r26_pe2 as rpc26_2_abs, pr_r26_pe3 as rpc26_3_abs, pr_r45_pe1 as rpc45_1_abs, pr_r45_pe2 as rpc45_2_abs, pr_r45_pe3 as rpc45_3_abs, pr_r85_pe1 as rpc85_1_abs, pr_r85_pe2 as rpc85_2_abs, pr_r85_pe2pe3 as rpc85_3_abs, pr_r85_pe3, ((pr_r26_pe1-mms.l_pr)*100/mms.d_pr)::integer as rpc26_1_rel, ((pr_r26_pe2-mms.l_pr)*100/mms.d_pr)::integer as rpc26_2_rel, ((pr_r26_pe3-mms.l_pr)*100/mms.d_pr)::integer as rpc26_3_rel, ((pr_r45_pe1-mms.l_pr)*100/mms.d_pr)::integer as rpc45_1_rel, ((pr_r45_pe2-mms.l_pr)*100/mms.d_pr)::integer as rpc45_2_rel, ((pr_r45_pe3-mms.l_pr)*100/mms.d_pr)::integer as rpc45_3_rel, ((pr_r85_pe1-mms.l_pr)*100/mms.d_pr)::integer as rpc85_1_rel, ((pr_r85_pe2-mms.l_pr)*100/mms.d_pr)::integer as rpc85_2_rel, ((pr_r85_pe3-mms.l_pr)*100/mms.d_pr)::integer as rpc85_3_rel from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r , mms ) -- view the result select h.*, r.geom from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r join pro h on h.id=r.id ); create or replace view skfab_climate_24.view_smhi_rutnat_absolute_tas as ( WITH mms AS ( select greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r85_pe3)) as g_tas, least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r85_pe3)) as l_tas, greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r85_pe3)) - least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r85_pe3)) as d_tas from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute ), pro as ( -- calculate the procentage (0-100) from values select r.id, 'Dygnsmedeltemperatur per år' as name, tas_r26_pe1 as rpc26_1_abs, tas_r26_pe2 as rpc26_2_abs, tas_r26_pe3 as rpc26_3_abs, tas_r45_pe1 as rpc45_1_abs, tas_r45_pe2 as rpc45_2_abs, tas_r45_pe3 as rpc45_3_abs, tas_r85_pe1 as rpc85_1_abs, tas_r85_pe2 as rpc85_2_abs, tas_r85_pe2pe3 as rpc85_3_abs, tas_r85_pe3, ((tas_r26_pe1-mms.l_tas)*100/mms.d_tas)::integer as rpc26_1_rel, ((tas_r26_pe2-mms.l_tas)*100/mms.d_tas)::integer as rpc26_2_rel, ((tas_r26_pe3-mms.l_tas)*100/mms.d_tas)::integer as rpc26_3_rel, ((tas_r45_pe1-mms.l_tas)*100/mms.d_tas)::integer as rpc45_1_rel, ((tas_r45_pe2-mms.l_tas)*100/mms.d_tas)::integer as rpc45_2_rel, ((tas_r45_pe3-mms.l_tas)*100/mms.d_tas)::integer as rpc45_3_rel, ((tas_r85_pe1-mms.l_tas)*100/mms.d_tas)::integer as rpc85_1_rel, ((tas_r85_pe2-mms.l_tas)*100/mms.d_tas)::integer as rpc85_2_rel, ((tas_r85_pe3-mms.l_tas)*100/mms.d_tas)::integer as rpc85_3_rel from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r , mms ) -- view the result select h.*, r.geom from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r join pro h on h.id=r.id ); create or replace view skfab_climate_24.view_smhi_rutnat_absolute_dd as ( WITH mms AS ( select greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r85_pe3)) as g_dd, least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r85_pe3)) as l_dd, greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r85_pe3)) - least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r85_pe3)) as d_dd from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute ), pro as ( -- calculate the procentage (0-100) from values select r.id, 'Torra dygn per år' as name, dd_r26_pe1 as rpc26_1_abs, dd_r26_pe2 as rpc26_2_abs, dd_r26_pe3 as rpc26_3_abs, dd_r45_pe1 as rpc45_1_abs, dd_r45_pe2 as rpc45_2_abs, dd_r45_pe3 as rpc45_3_abs, dd_r85_pe1 as rpc85_1_abs, dd_r85_pe2 as rpc85_2_abs, dd_r85_pe2pe3 as rpc85_3_abs, dd_r85_pe3, ((dd_r26_pe1-mms.l_dd)*100/mms.d_dd)::integer as rpc26_1_rel, ((dd_r26_pe2-mms.l_dd)*100/mms.d_dd)::integer as rpc26_2_rel, ((dd_r26_pe3-mms.l_dd)*100/mms.d_dd)::integer as rpc26_3_rel, ((dd_r45_pe1-mms.l_dd)*100/mms.d_dd)::integer as rpc45_1_rel, ((dd_r45_pe2-mms.l_dd)*100/mms.d_dd)::integer as rpc45_2_rel, ((dd_r45_pe3-mms.l_dd)*100/mms.d_dd)::integer as rpc45_3_rel, ((dd_r85_pe1-mms.l_dd)*100/mms.d_dd)::integer as rpc85_1_rel, ((dd_r85_pe2-mms.l_dd)*100/mms.d_dd)::integer as rpc85_2_rel, ((dd_r85_pe3-mms.l_dd)*100/mms.d_dd)::integer as rpc85_3_rel from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r , mms ) -- view the result select h.*, r.geom from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r join pro h on h.id=r.id ); create or replace view skfab_climate_24.view_smhi_rutnat_absolute_gsl as ( WITH mms AS ( select greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r85_pe3)) as g_gsl, least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r85_pe3)) as l_gsl, greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r85_pe3)) - least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r85_pe3)) as d_gsl from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute ), pro as ( -- calculate the procentage (0-100) from values select r.id, 'Vegetationsperioden dygn per år' as name, gsl_r26_pe1 as rpc26_1_abs, gsl_r26_pe2 as rpc26_2_abs, gsl_r26_pe3 as rpc26_3_abs, gsl_r45_pe1 as rpc45_1_abs, gsl_r45_pe2 as rpc45_2_abs, gsl_r45_pe3 as rpc45_3_abs, gsl_r85_pe1 as rpc85_1_abs, gsl_r85_pe2 as rpc85_2_abs, gsl_r85_pe2pe3 as rpc85_3_abs, gsl_r85_pe3, ((gsl_r26_pe1-mms.l_gsl)*100/mms.d_gsl)::integer as rpc26_1_rel, ((gsl_r26_pe2-mms.l_gsl)*100/mms.d_gsl)::integer as rpc26_2_rel, ((gsl_r26_pe3-mms.l_gsl)*100/mms.d_gsl)::integer as rpc26_3_rel, ((gsl_r45_pe1-mms.l_gsl)*100/mms.d_gsl)::integer as rpc45_1_rel, ((gsl_r45_pe2-mms.l_gsl)*100/mms.d_gsl)::integer as rpc45_2_rel, ((gsl_r45_pe3-mms.l_gsl)*100/mms.d_gsl)::integer as rpc45_3_rel, ((gsl_r85_pe1-mms.l_gsl)*100/mms.d_gsl)::integer as rpc85_1_rel, ((gsl_r85_pe2-mms.l_gsl)*100/mms.d_gsl)::integer as rpc85_2_rel, ((gsl_r85_pe3-mms.l_gsl)*100/mms.d_gsl)::integer as rpc85_3_rel from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r , mms ) -- view the result select h.*, r.geom from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r join pro h on h.id=r.id ); create or replace view skfab_climate_24.view_smhi_rutnat_absolute_pr as ( WITH mms AS ( select greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe3)) as g_pr, least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe3)) as l_pr, greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe3)) - least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe3)) as d_pr from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute ), pro as ( -- calculate the procentage (0-100) from values select r.id, 'Medelnederbörd nedbord_ per månad' as name, pr_r26_pe1 as rpc26_1_abs, pr_r26_pe2 as rpc26_2_abs, pr_r26_pe3 as rpc26_3_abs, pr_r45_pe1 as rpc45_1_abs, pr_r45_pe2 as rpc45_2_abs, pr_r45_pe3 as rpc45_3_abs, pr_r85_pe1 as rpc85_1_abs, pr_r85_pe2 as rpc85_2_abs, pr_r85_pe2pe3 as rpc85_3_abs, pr_r85_pe3, ((pr_r26_pe1-mms.l_pr)*100/mms.d_pr)::integer as rpc26_1_rel, ((pr_r26_pe2-mms.l_pr)*100/mms.d_pr)::integer as rpc26_2_rel, ((pr_r26_pe3-mms.l_pr)*100/mms.d_pr)::integer as rpc26_3_rel, ((pr_r45_pe1-mms.l_pr)*100/mms.d_pr)::integer as rpc45_1_rel, ((pr_r45_pe2-mms.l_pr)*100/mms.d_pr)::integer as rpc45_2_rel, ((pr_r45_pe3-mms.l_pr)*100/mms.d_pr)::integer as rpc45_3_rel, ((pr_r85_pe1-mms.l_pr)*100/mms.d_pr)::integer as rpc85_1_rel, ((pr_r85_pe2-mms.l_pr)*100/mms.d_pr)::integer as rpc85_2_rel, ((pr_r85_pe3-mms.l_pr)*100/mms.d_pr)::integer as rpc85_3_rel from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r , mms ) -- view the result select h.*, r.geom from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r join pro h on h.id=r.id ); |