On Mon, 31 Mar 2014 16:19:20 +0200 Mathieu Doray <mathieu.doray@ifremer.fr> wrote: Salut,
re,
ok je regarde ça demain matin.
j'ai extrait les données qu'il faut pour les exports de captures par chalut du site des indicateurs, mais je me galère pour les mettre en forme en SQL comme dans les fichiers de sortie du site des indicateurs. Peux tu arranger ça?
ci-dessous les requêtes pour les captures par chalut à exporter du site des indicateurs, avec des trucs à faire en plus que je ne sais pas faire en SQL.
1. Fichier "reftax_Species" :
SELECT "taxoncode" AS "C_Perm", "taxonsystematicorder" AS "NumSys", "taxonsystematiclevel" AS "NivSys", "baracoudacode" AS "C_VALIDE", "genusspecies" AS "L_VALIDE", "authorreference" AS "AA_VALIDE", "taxonfathermemocode" AS "C_TxPère" FROM "public"."species" AS "species"
dans cette requête, il faut en plus : - enlever le '_' dans le baracoudacode Il n'y a pas de colonne "taxa"
2. Fichier "capture"
SELECT "voyage"."name" AS "Campagne", "species"."baracoudacode" AS "Espece", "operation"."id" AS "Trait", "sample"."numbersampled" AS "Nombre", "sample"."sampleweight" AS "Poids" FROM "public"."transit" AS "transit", "public"."voyage" AS "voyage", "public"."transect" AS "transect", "public"."operation" AS "operation", "public"."sample" AS "sample", "public"."speciescategory" AS "speciescategory", "public"."species" AS "species" WHERE "transit"."voyage" = "voyage"."topiaid" AND "transect"."transit" = "transit"."topiaid" AND "operation"."transect" = "transect"."topiaid" AND "sample"."operation" = "operation"."topiaid" AND "sample"."speciescategory" = "speciescategory"."topiaid" AND "speciescategory"."species" = "species"."topiaid"
dans cette requête, il faut en plus : - enlever le '_' dans le baracoudacode - créer un champ "Annee" avec l'année du voyage - faire une somme des 'Nombre" et "Poids" en groupant sur les champs "Campagne","trait", "Espece" et "Annee", mais j'y arrive pas...
3. Fichier "tailles"
SELECT "voyage"."name" AS "Campagne", "operation"."id" AS "Trait", "species"."baracoudacode" AS "Espece", "sexcategory"."name" AS "Sexe", "sampledata"."datalabel", "sampledata"."datavalue", "sampledatatype"."name" FROM "public"."voyage" AS "voyage", "public"."mission" AS "mission", "public"."transit" AS "transit", "public"."transect" AS "transect", "public"."operation" AS "operation", "public"."sample" AS "sample", "public"."sampledata" AS "sampledata", "public"."sampledatatype" AS "sampledatatype", "public"."sampletype" AS "sampletype", "public"."speciescategory" AS "speciescategory", "public"."species" AS "species", "public"."sizecategory" AS "sizecategory", "public"."sexcategory" AS "sexcategory" WHERE "voyage"."mission" = "mission"."topiaid" AND "transit"."voyage" = "voyage"."topiaid" AND "transect"."transit" = "transit"."topiaid" AND "operation"."transect" = "transect"."topiaid" AND "sample"."operation" = "operation"."topiaid" AND "sampledata"."sample" = "sample"."topiaid" AND "sampledata"."sampledatatype" = "sampledatatype"."topiaid" AND "sample"."sampletype" = "sampletype"."topiaid" AND "sample"."speciescategory" = "speciescategory"."topiaid" AND "speciescategory"."species" = "species"."topiaid" AND "speciescategory"."sizecategory" = "sizecategory"."topiaid" AND "speciescategory"."sexcategory" = "sexcategory"."topiaid" AND "sampletype"."name" = 'Subsample'
dans cette requête, il faut en plus : - enlever le '_' dans le baracoudacode - créer un champ "Annee" avec l'année du voyage - mettre en colonne les champs "LTcm1" et "WeightAtLengthkg" - faire une somme des datavalues des 'LTcm1" et "WeightAtLengthkg" respectivement en groupant sur les champs "Campagne","trait", "Espece" et "Annee", mais j'y arrive pas... - ajouter une colonne "maturite" avec des NA - convertir les "N" de la colonne "sexe" en NA
4. Fichier "strates"
SELECT "voyage"."name" AS "Campagne", "cell"."name" AS "Strate", "datametadata"."name", "data"."datavalue" AS "Surface" FROM "public"."voyage" AS "voyage", "public"."mission" AS "mission", "public"."cell" AS "cell", "public"."celltype" AS "celltype", "public"."data" AS "data", "public"."datametadata" AS "datametadata" WHERE "voyage"."mission" = "mission"."topiaid" AND "cell"."voyage" = "voyage"."topiaid" AND "cell"."celltype" = "celltype"."topiaid" AND "data"."cell" = "cell"."topiaid" AND "data"."datametadata" = "datametadata"."topiaid" AND "celltype"."id" = 'Region' AND "datametadata"."name" = 'Surface'
là c'est bon
5. Fichier "traits"
j'ai pas fini, c'est plus compliqué, faut qu'on en cause...
M
-- Tony Chemit -------------------- tél: +33 (0) 2 40 50 29 28 http://www.codelutin.com email: chemit@codelutin.com twitter: https://twitter.com/tchemit