Versions Compared

Version Old Version 6 New Version Current
Changes made by

Sven-Ola Svensson

Sven-Ola Svensson

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Se till att Postgres-databasen är öppen. Skapa ett temporärt schema t.ex. SMHIsmhi

Markera alla de nio lagren från temat i QGIS och dra dem upp till schema-namnet i Postgres. Det blir tyst en lång stund men processen pågår att flytta lagren. Efter en lång stund meddelas att det är klart.

...

För att snabba upp arbetet med inddatata indata så skapar vi index för dessa

...

Skapa tabellen smhi_rutor_absolute

Det fins redan en tabell där alla attribut finns för 8 teman som heter smhi_rutor. Kolumnnamnen är avkortade. Strävan är nu att skap en lika tabell men med absoluta värden. Det ny tabellen skapas efter SQL som beskrivs nedan

...

Skapa ett schema, för att lägga in den slutgiltliga tabellem med rutor, där varje ruta innehåller alla attribut.

Code Block
create schema  skfab_climate_24;

Utångstabellen för alla lager och teman är FrostDygn, Scenaro2.6 Period 1 (fd_r26_pe1). Första kolumnen är Id och sista geom. Denna tabell är den enda med from (rad 12). Alla övriga är en spatial join på rutan.

...

Därmed är allt klart för Weave.

Code Block
languagesql
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
);