db_biljou_carto on db.silva.inrae.fr

Function: ps_calcul_statistiques

Schema

safran

Owner

postgres

Descriptions

There is no description for function ps_calcul_statistiques

Options

Definition

CREATE OR REPLACE FUNCTION safran.ps_calcul_statistiques (
)
RETURNS void AS
$span$
DECLARE
r0 record; -- Liste des périodes de référence
r1 record; -- Liste ordonnée des grilles, scénarios, combinaisons et indicateur distincts + le nombres d'années (nombre de cartes)
var_sql text; -- requête sql
var_counter integer DEFAULT 0; -- compteur
i integer DEFAULT 0; -- compteur
var_nom_table_stat text; -- nom de la table de données statistiques
var_last_id integer; -- id de la dernière carte créée
var_current_date date; -- date actuelle
var_casind_id integer; -- id d'une carte statistique
var_defmoy integer;
var_durmoy integer;
var_debmed integer;
var_tmp text;
var_table_exist boolean;
var_schema text := 'safran';

BEGIN
FOR r0 IN
SELECT *
FROM public.tr_periode_per
ORDER BY per_duree, per_annee_debut
LOOP
i := i + 1;
RAISE NOTICE '   ...calcul sur période %', i;
FOR r1 IN
SELECT ci.caind_gri_id, ci.caind_scen_id, ci.caind_comb_id, i2.ind_id as indicstat_id, ci.caind_nom_table, count(*) AS nb_annees
FROM public.t_carte_indicateur_caind AS ci
INNER JOIN public.tr_indicateur_ind AS i1 ON ci.caind_ind_id = i1.ind_id
INNER JOIN public.tr_indicateur_ind AS i2 ON i1.ind_id = i2.ind_id_parent
WHERE i1.ind_type = 'indicateur'
AND i2.ind_type = 'statistique'
AND ci.caind_annee BETWEEN r0.per_annee_debut AND r0.per_annee_fin
GROUP BY ci.caind_gri_id, ci.caind_scen_id, ci.caind_comb_id, indicstat_id, ci.caind_nom_table
ORDER BY ci.caind_gri_id, ci.caind_scen_id, ci.caind_comb_id, indicstat_id, ci.caind_nom_table
LOOP
IF r1.nb_annees != r0.per_duree THEN
RAISE EXCEPTION 'Nombre d''années != durée période (grille : %, scénario : %, combinaison : %, indicateur statistique : %, nombre d''années : %, durée attendue : %, période : %-% ) ',
r1.caind_gri_id, r1.caind_scen_id, r1.caind_comb_id, r1.indicstat_id, r1.nb_annees, r0.per_duree, r0.per_annee_debut, r0.per_annee_fin;  
END IF;

var_nom_table_stat := replace(r1.caind_nom_table, 'indic', 'indicstat');

SELECT DATE(NOW()) INTO var_current_date;

-- Requête permettant de vérifier si la carte statistique existe déjà
SELECT casind_id INTO var_casind_id
FROM public.t_carte_statistique_indicateur_casind
WHERE casind_nom_table = var_nom_table_stat
AND casind_annee_debut = r0.per_annee_debut
AND casind_annee_fin = r0.per_annee_fin
AND casind_scen_id = r1.caind_scen_id
AND casind_gri_id = r1.caind_gri_id
AND casind_comb_id = r1.caind_comb_id
AND casind_ind_id = r1.indicstat_id;

-- Si la carte statistique existe déjà lève une erreur
IF var_casind_id IS NOT NULL THEN
RAISE EXCEPTION 'La création d''une carte de statistique a échoué car cette carte existe déjà (id : %). Procédure intérrompue, aucune carte n''a été créée.', var_casind_id;
END IF;

-- Création de la carte statistique
INSERT INTO public.t_carte_statistique_indicateur_casind (casind_nom_table, casind_annee_debut, casind_annee_fin, casind_est_publiable, casind_date_integration, casind_scen_id, casind_gri_id, casind_comb_id, casind_ind_id)
SELECT var_nom_table_stat, r0.per_annee_debut, r0.per_annee_fin, true, var_current_date, r1.caind_scen_id, r1.caind_gri_id, r1.caind_comb_id, r1.indicstat_id;

-- Récupère l'id de la dernière carte créée
SELECT CURRVAL(pg_get_serial_sequence('t_carte_statistique_indicateur_casind','casind_id')) INTO var_last_id;

SELECT ind_code_fr INTO var_tmp
FROM public.tr_indicateur_ind
WHERE ind_id = r1.indicstat_id;

IF var_tmp = 'DEFMOY' THEN
var_defmoy = var_last_id;
ELSIF var_tmp = 'DEBMED' THEN
var_debmed = var_last_id;
ELSIF var_tmp = 'DURMOY' THEN
var_durmoy = var_last_id;
END IF;

IF var_counter = 2 THEN
-- regarde si la table de données n'existe pas déjà
SELECT EXISTS (
SELECT 1
FROM   information_schema.tables
WHERE  table_schema = var_schema
AND    table_name = var_nom_table_stat
) INTO var_table_exist;

IF var_table_exist = false THEN
var_sql := 'CREATE TABLE '|| var_schema ||'.'|| var_nom_table_stat ||'
(
 id_maille integer NOT NULL,
 annee_debut integer NOT NULL,
 annee_fin integer NOT NULL,
 code_scenario varchar(8) NOT NULL,  
 defmoy numeric(6, 2),
 def_sd numeric(6,2),
 defmoy_cartid integer,
 deb_c10 integer,
 debmed integer,
 deb_c90 integer,
 debmed_cartid integer,
 durmoy integer,
 dur_sd integer,
 durmoy_cartid integer,
 nb_annees_def integer,
 CONSTRAINT '|| var_nom_table_stat ||'_pkey PRIMARY KEY (id_maille, annee_debut, annee_fin, code_scenario),
 CONSTRAINT '|| var_nom_table_stat ||'_defmoy_cartid_fkey FOREIGN KEY (defmoy_cartid)
 REFERENCES t_carte_statistique_indicateur_casind (casind_id) MATCH SIMPLE,
 CONSTRAINT '|| var_nom_table_stat ||'_debmed_cartid_fkey FOREIGN KEY (debmed_cartid)
 REFERENCES t_carte_statistique_indicateur_casind (casind_id) MATCH SIMPLE,
 CONSTRAINT '|| var_nom_table_stat ||'_durmoy_cartid_fkey FOREIGN KEY (durmoy_cartid)
 REFERENCES t_carte_statistique_indicateur_casind (casind_id) MATCH SIMPLE
)';
EXECUTE var_sql; -- TODO rajouter les alter table pour les droits owner
var_sql := 'GRANT ALL ON TABLE '|| var_schema ||'.'|| var_nom_table_stat ||' TO biljoucarto_ecriture';
EXECUTE var_sql;
var_sql := 'GRANT SELECT ON TABLE '|| var_schema ||'.'|| var_nom_table_stat ||' TO biljoucarto_lecture';
EXECUTE var_sql;
END IF;

var_sql := '
INSERT INTO '||var_schema||'.'||var_nom_table_stat||' (id_maille, annee_debut, annee_fin, code_scenario, defmoy, def_sd, defmoy_cartid, deb_c10, debmed, deb_c90, debmed_cartid, durmoy, dur_sd, durmoy_cartid, nb_annees_def)
SELECT
id_maille,
'||r0.per_annee_debut||',
'||r0.per_annee_fin||',
s.scen_code, AVG(def),
STDDEV(def),
'||var_defmoy||',
-- à partir de Postgre 9.4
percentile_cont(0.1) WITHIN GROUP (ORDER BY deb),
percentile_cont(0.5) WITHIN GROUP (ORDER BY deb),  
percentile_cont(0.9) WITHIN GROUP (ORDER BY deb),
-- pour Postgre 9.2
--safran.percentile_cont(array_agg_notnull(deb),0.1),
--safran.percentile_cont(array_agg_notnull(deb),0.5),  
--safran.percentile_cont(array_agg_notnull(deb),0.9),
'||var_debmed||',
AVG(dur),
STDDEV(dur),
'||var_durmoy||',
SUM(CASE WHEN def != 0 THEN 1 ELSE 0 END)
FROM '||var_schema||'.'||r1.caind_nom_table||' AS d
INNER JOIN tr_scenario_scen AS s ON d.code_scenario = s.scen_code
WHERE annee BETWEEN '||r0.per_annee_debut||' AND '||r0.per_annee_fin||'
GROUP BY id_maille, s.scen_code
ORDER BY id_maille';
EXECUTE var_sql;
var_counter := 0;
ELSE
var_counter := var_counter + 1;
END IF;
END LOOP;
END LOOP;
END;
$span$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
PARALLEL UNSAFE
COST 100;

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 07/12/2018 13:23