PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 11/06/2018 22:24:43

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.

Hors ligne

#2 12/06/2018 09:54:02

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.

Hors ligne

#3 12/06/2018 14:52:15

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)

Hors ligne

Pied de page des forums