Open jpm-cbna opened 2 years ago
Je me demande si pour des questions de performance, et etant donnée que ce sont des données "stables", il ne serait pas plus simple d'avoir une table de précalcul de la correspondance entre sensibilité, niveau de diffusion. Que l'on pourrait ensuite utiliser dans l'atlas.
CREATE TABLE gn_sensitivity.cor_diffusion_level_sensitivity AS
WITH sens AS (
SELECT id_nomenclature AS id_sens, cd_nomenclature AS cd_sens,
CASE
WHEN cd_nomenclature = '0' THEN '5'
ELSE cd_nomenclature
END AS cor_cd_diff,
cd_nomenclature::int AS order_sens
FROM ref_nomenclatures.t_nomenclatures AS tn
WHERE id_type = ref_nomenclatures.get_id_nomenclature_type('SENSIBILITE')
UNION
SELECT -1 AS id_sens, NULL AS cd_sens, NULL AS cor_cd_diff, 0 AS order_sens
) , diff AS (
SELECT id_nomenclature AS id_diff, cd_nomenclature AS cd_diff,
CASE
WHEN cd_nomenclature IN ('0', '5') THEN 0
ELSE cd_nomenclature::int
END AS order_diff
FROM ref_nomenclatures.t_nomenclatures AS tn
WHERE id_type = ref_nomenclatures.get_id_nomenclature_type('NIV_PRECIS')
UNION
SELECT -1 AS id_diff, NULL AS cd_diff, 0 AS order_diff
), d AS (
SELECT * , GREATEST(sens.order_sens, diff.order_diff) AS order_max,
CASE
WHEN GREATEST(sens.order_sens, diff.order_diff) = 0 THEN '5'
ELSE GREATEST(sens.order_sens, diff.order_diff)::varchar
END AS cd_max
FROM sens, diff
)
SELECT d.*, tn.id_nomenclature AS id_max
FROM d
JOIN ref_nomenclatures.t_nomenclatures AS tn
ON id_type = ref_nomenclatures.get_id_nomenclature_type('NIV_PRECIS') AND cd_nomenclature = cd_max;
SELECT DISTINCT s.id_nomenclature_sensitivity , s.id_nomenclature_diffusion_level , cds.*
FROM gn_synthese.synthese AS s
JOIN gn_sensitivity.cor_diffusion_level_sensitivity cds
ON cds.id_sens = COALESCE(s.id_nomenclature_sensitivity, -1) AND cds.id_diff = COALESCE(s.id_nomenclature_diffusion_level, -1)
See #390.