Forums PostgreSQL.fr

robinson

Membre

Analyse sur plusieurs champs

Bonjour,

Tout d'abord merci pour vos contributions à mes questionnements précédents, ça permet d'avancer un peu quand on est tout seul.
Je me demandais si il était possible de réaliser une analyse sur plusieurs colonnes (même si ce n'est pas vraiment le but de postgreSQL) :
J'ai une table avec un grand nombre d'enregistrements et des champs de deux types, un pour caractériser une géométrie et d'autres, tous identiques dans leur structure et les valeurs qu'ils prennent, représentant des évolutions temporelles :

géométrie(polygon) type_année_n(varchar) type_année_n+1(varchar) type_annee_n+2(varchar)

Je souhaiterai construire une fonction visant à compter le nombre "d'années" (= de colonnes) séparant deux valeurs identiques des champs "type".
Par exemple, les valeurs prises par les champs "type" pouvant être type_1, type_2, type_3, pour l'exemple de tableau suivant :

id géométrie(polygon) type_année_n(varchar) type_année_n+1(varchar) type_annee_n+2(varchar)
1   geom                     type_1                         type_2                             type_1
2   geom                     type_1                         type_3                             type_1
3   geom                     type_1                         type_1                             type_2

J'aimerai obtenir les champs suivant :

id  différence
1   2                   (deux colonnes de différence)
2   2                   (deux colonnes de différence)
3   1                   (une colonne de différence)

J'ai bien identifié la commande

SELECT COUNT(*) FROM information_schema.COLUMNS WHERE table_name = 'matrice_test'

mais je n'ai pas réussi à introduire des conditions sur les valeurs des colonnes.
J'ai pensé faire un CROSSTABLE pour inverser lignes et colonnes mais le nombre d'enregistrements comme de champs est variable. Pareil pour lister les configurations possibles avec CASE. Il reste les opérateurs de comparaisons mais j'ai du mal à transposer la documentation à mon cas.
Si vous avez une piste, je suis preneur!

Bonne semaine à vous.

Marc Cousin

Membre

Re : Analyse sur plusieurs champs

Vous n'y arriverez pas en SQL pur, ni avec PLPgSQL, si vous avez un nombre dynamique de colonnes. Ou alors il faudra passer par des manipulations qui vont vous ruiner les perfs je pense.

À mon avis c'est beaucoup plus simple si vous utilisez un des langages de procédures stockées un peu plus dynamiques… plpython ou plperl par exemple. Un exemple en PLPerl:

create language plperlu;
create table test (a int, b varchar, c varchar);
insert into test values (1,'a','b');

create or replace function demo (test) returns void language plperlu as
$$
  use Data::Dumper;
  my $args=shift;
  print elog(Dumper(INFO,$args)) ;
$$
;

select demo(test) from test;
INFO:  $VAR1 = {
          'a' => '1',
          'c' => 'b',
          'b' => 'a'
        };

 demo 
------

Quelques notes… j'ai utilisé plperlu (untrusted) simplement parce que je voulais utiliser la fonction Dumper, qui permet d'afficher le hash facilement. Ce n'est évidemment pas nécessaire si je veux manipuler les données et enrichir une colonne avec un trigger. Là mon trigger ne fait rien que montrer qu'il a reçu en paramètre l'enregistrement en question… et que mon code se moque totalement du typage de ce paramètre, il s'y adapte dynamiquement.

Bref, il vous faut un langage qui sache recevoir un type dynamique (l'enregistrement) et en analyser la structure au moment de l'exécution. Par exemple, perl avec un hash, python avec un dict...

---

Marc.

dverite

Membre

Re : Analyse sur plusieurs champs

Le souci avec les années en colonnes, c'est que ça ne respecte pas le modèle relationnel. Dans ce modèle on a des relations (=tables), des attributs (=colonnes) et des valeurs (=contenu des colonnes)

Or "typeannée2016" n'est pas un attribut. Par contre "année" est un attribut, et 2016 est une valeur possible de cet attribut. Et sans trop savoir ce que désigne "type" , ça  a l'air d'être un attribut, et "type_1" est une valeur possible de cet attribut

Concrètement dans le modèle relationnel ces données pourraient être avoir cette forme:

TABLE polygone(
 id int PRIMARY KEY,
 geom geometry
)

TABLE mesure(
  id int REFERENCES polygone(id),
  annee int,
  valeur varchar 
);

avec éventuellement un index unique sur le couple (id,annee) parce que la valeur est fonction de (id,annee).
Cette structure permet d'accueillir de nouvelles années sans changer les colonnes, mais surtout d'être requêtée en SQL parce qu'elle est alignée avec la logique relationnelle.

Ce qui ne veut pas dire que les requêtes vont être forcément simples à élaborer, tout dépend des questions auxquelles elles doivent répondre, mais qu'au moins toutes les fonctionnalités du SQL seront utilisables directement.

Dernière modification par dverite (12/06/2018 14:52:47)

---

@DanielVerite
http://blog-postgresql.verite.pro/

robinson

Membre

Re : Analyse sur plusieurs champs

Bonjour,

Après plusieurs jours de travail, j'ai suis enfin arrivé à mes fins (bien que tout cela soit un peu lourd... et malheureusement pas dynamique!), avec un petit emprunt aux expressions régulières (clin d'oeil à Marc Cousin qui m'a mis sur la bonne piste avec le PERL).

Tout d'abord, il faut retravailler la table de départ afin de remplacer les valeurs par des codes correspondant à des chaine de caractères de taille égale. Ensuite, il faut créer un attribut concaténant tous les champs visés par l'analyse :

CREATE TABLE position_type AS	
SELECT type05 ||' '|| type06 ||' '|| type07||' '|| type08 ||' '|| type09 ||' '|| type10 ||' '|| type11 ||' '|| type12 ||' '|| type13 ||' '|| type14 ||' '|| type15 ||' '|| type16 AS type_vec

On peut dès lors compter le nombre d’occurrences d'une chaine de caractères dans la chaine de caractères ainsi créée. Pour ce faire, on calcule la longueur de string totale avec CHAR_LENGTH, à laquelle on retranche la longueur de la chaine sans le string recherché (ici 'AF', remplacé par des 'blancs' : ''), ce qui nous donne la dimension de la (répétition de) chaine de caractère recherchée. Il reste alors à diviser la valeur obtenue par la longueur de la chaine de caractère recherchée pour avoir le nombre de répétitions :

ALTER TABLE position_type ADD COLUMN nbre_occurrence NUMERIC;
UPDATE position_type 
SET nbre_occurrence = (CHAR_LENGTH(type_vec) - CHAR_LENGTH(REPLACE(type_vec, 'AF', ''))) 
    / CHAR_LENGTH('AF');

Un fois le nombre d’occurrences obtenu, on peut alors définir le nombre d'attributs à créer (=nombre maximum d'occurences) ensuite pour le calcul des positions des strings recherchés dans la chaine de caractère totale.

SELECT nbre_occurrence FROM position_type GROUP BY nbre_occurrence

Pour ma part, j'ai 4 colonnes à créer :

ALTER TABLE position_type ADD COLUMN position_1 VARCHAR(40), ADD COLUMN position_2 VARCHAR(40), ADD COLUMN position_3 VARCHAR(40), ADD COLUMN position_4 VARCHAR(40);

Il faut ensuite calculer les positions des chaines de caractère grâce à la fonction POSITION. Celle-ci ne donne malheureusement que l'emplacement de la première occurrence, et non pas de toutes, ce qui nous oblige à répéter les instructions en boucle avec un CASE renvoyant sur la colonne précédemment remplie :

UPDATE position_type
SET 
position_1 = POSITION('AF' IN type_vec);
UPDATE position_type
SET 
position_2 = (CASE 
WHEN nbre_occurrence >= 2 THEN 2 + position_1::integer + POSITION('AF' IN substring (type_vec from 3 + position_1::integer))
ELSE 0
END);
UPDATE position_type
SET 
position_3 = (CASE 
WHEN nbre_occurrence >= 3 THEN 2 + position_2::integer + POSITION('AF' IN substring (type_vec from 3 + position_2::integer))
ELSE 0
END);
UPDATE position_type
SET 
position_4 = (CASE 
WHEN nbre_occurrence >= 4 THEN 2 + position_3::integer + POSITION('AF' IN substring (type_vec from 3 + position_3::integer))
ELSE 0
END);

Pour finir, on peut compter le nombre d'années (de champs séparant deux valeurs similaires pour le même enregistrement). J'ai ici écrit la fonction pour calculer directement une moyenne. Je ne détaille pas mais il faut bien prendre en compte les espaces dans le comptage des positions :

ALTER TABLE position_type ADD COLUMN jachere_moy NUMERIC;
UPDATE position_type
SET
jachere_moy = ROUND(
(CASE 
WHEN nbre_occurrence = 2 THEN
(((position_2::numeric - position_1::numeric)/3)-1)/(nbre_occurrence::numeric-1)
WHEN nbre_occurrence = 3 THEN
((((position_2::numeric - position_1::numeric)/3)-1) + (((position_3::numeric - position_2::numeric)/3)-1))/(nbre_occurrence::numeric-1)
WHEN nbre_occurrence = 4 THEN
((((position_2::numeric - position_1::numeric)/3)-1) + (((position_3::numeric - position_2::numeric)/3)-1) + (((position_4::numeric - position_3::numeric)/3)-1))/(nbre_occurrence::numeric-1)
ELSE 0
END)::numeric, 3)

On a donc la distance moyenne entre deux occurrences. Tout cela est très lourd, et surtout pas du tout dynamique (besoin de rajouter plusieurs lignes à chaque ajout d'un attribut dans la table de base).

@ Marc Cousin : Merci pour le script, mais je n'ai pas du tout compris et je ne suis pas en mesure d'apprendre un nouveau language de programmation... Bien qu'on m'aie conseillé PLPgSQL avec un travail sur les information_schema.COLUMNS pour rendre des colonnes dynamiques.
Cette expérience de script m'aura convaincu que PostGreSQL n'est pas fait pour faire de la statistique et je vais essayer de travailler en R ou Pl/R.

@ dverite : merci pour vos commentaires, personne ne m'avait encore faire cette remarque le modèle conceptuel de ma base. J'entends qu'un attribut ne doit pas porter en soi une information, mais qu'elle doit rester dans les valeurs. Si j'ai bien compris votre schéma, on a plusieurs fois le même id dans la table mesure, autant que d'années (avec leur type correspondant)? Quelle serait alors la différence entre le modèle que vous proposez et la table suivante, qui synthétise votre modèle en une seule table en dupliquant par la même occasion les géométries? Aurait-on une perte de performance? :

CREATE TABLE matrice_total_test AS
SELECT type05 AS type, '2005' AS annee, geom FROM matrice_test
UNION
SELECT type06 AS type, '2006' AS annee, geom FROM matrice_test
UNION
SELECT type07 AS type, '2007' AS annee, geom FROM matrice_test
UNION
SELECT type08 AS type, '2008' AS annee, geom FROM matrice_test
UNION
SELECT type09 AS type, '2009' AS annee, geom FROM matrice_test
UNION
SELECT type10 AS type, '2010' AS annee, geom FROM matrice_test
UNION
SELECT type11 AS type, '2011' AS annee, geom FROM matrice_test
UNION
SELECT type12 AS type, '2012' AS annee, geom FROM matrice_test
UNION
SELECT type13 AS type, '2013' AS annee, geom FROM matrice_test
UNION
SELECT type14 AS type, '2014' AS annee, geom FROM matrice_test
UNION
SELECT type15 AS type, '2015' AS annee, geom FROM matrice_test
UNION
SELECT type16 AS type, '2016' AS annee, geom FROM matrice_test
ORDER BY annee ASC geom DESC;
ALTER TABLE matrice_total_test ADD COLUMN id SERIAL PRIMARY KEY

Merci beaucoup pour vos contributions!

dverite

Membre

Re : Analyse sur plusieurs champs

La table matrice_total_test est dépivotée ce qui va dans le bon sens, mais la forme de ces données (enfin à supposer que  je comprends ce qu'elles représentent) est telle qu'il y aurait 2 dépendances:

id de polygone=> geom
(id de polygone, année) => valeur

D'un pt de vue un peu théorique, le fait d'avoir ces données dans la même table  matrice_total_test est une violation de la 2eme forme normale.
Les formes normales sont décrites par exemple ici:
https://fr.wikipedia.org/wiki/Forme_nor … ionnelles)

Le problème que geom ne dépend en rien de l'année est similaire structurellement à l'exemple wikipedia avec (Produit, fournisseur, Adresse fournisseur).

---

@DanielVerite
http://blog-postgresql.verite.pro/

robinson

Membre

Re : Analyse sur plusieurs champs

J'ai bien compris, je vais essayer de coller au mieux aux règles normales.
Merci pour les précisions!