Le portail francophone de la géomatique

cyrille · Tue 09 August 2011 18:15

Bonsoir,
j'ai un fichier qui ressemble à ca:

VILLE Info
ACHERES 000000000
ACHERES 000000000
ACHERES 000000000
ACHERES 000000000
ACHERES 000000000
ACHERES 000000000
ACHERES 010000000
ACHERES 100000000
ACHERES 100000000
ACHERES 100100100

avec ville par ville des infos, j'ai besoin d'arriver à ca

VILLE Info Results

ACHERES 000000000 60%
ACHERES 010000000 10 %
ACHERES 100000000 20 %
ACHERES 100100100 10%

ce qui se lirait de la maniere suivante, pour la ville de achere le cas de figure 000000000 se retrouve dans 60 % des cas, le cas 010000000 dans 10 % etc...

est ce que vous auriez une idee de l'outil capable de faire ca? je dispose de spatialite, access, excel qgis mais pas oracle.

j'ai pensé aux tableaux croisés dynamiques sous excel mais je n'ai pas une mais une tripotées de villes et j'aimerai garder les 3 cas de figures les plus importants

avez vous une idée? merci!

Dernière modification par cyrille (Wed 10 August 2011 09:13)

Pierre · Tue 09 August 2011 18:41

En sql, quelque chose du genre :

Code:

SELECT VILLE, INFO,
CUME_DIST() OVER ( PARTITION BY VILLE ORDER BY
count(distinct(INFO))
AS Results
FROM 
GROUP BY VILLE,INFO

Facile ?

cyrille · Tue 09 August 2011 19:05

Merci ! j'essaie de lancer la requete sous spatialite -conecté a qgis-

et ca ne semble pas passer

j'ai bien adapté la requete

il manque la table derriere le from non?

la fonction CUME_DIST()

ne semble pas etre dispo sous spatialite

OVER ( PARTITION BY VILLE ORDER BY
count(distinct(INFO)) m'echappe completement

Dernière modification par cyrille (Tue 09 August 2011 19:07)

Pierre · Tue 09 August 2011 19:43

Je ne connaissais pas le nom de la table.
Ajoutez Spatialite dans le nom du sujet, sans cela tous les utilisateurs d'Oracle risquent de vous répondre avec des fonctions propres à Oracle. Je ne connais malheureusement pas l'équivalent de cette fonction dans spatialite .

Nicolas Ribot · Wed 10 August 2011 11:50

cyrille a écrit:

Bonsoir,
j'ai un fichier qui ressemble à ca:

VILLE Info
ACHERES 000000000
ACHERES 000000000
ACHERES 000000000
ACHERES 000000000
ACHERES 000000000
ACHERES 000000000
ACHERES 010000000
ACHERES 100000000
ACHERES 100000000
ACHERES 100100100

avec ville par ville des infos, j'ai besoin d'arriver à ca

VILLE Info Results

ACHERES 000000000 60%
ACHERES 010000000 10 %
ACHERES 100000000 20 %
ACHERES 100100100 10%

ce qui se lirait de la maniere suivante, pour la ville de achere le cas de figure 000000000 se retrouve dans 60 % des cas, le cas 010000000 dans 10 % etc...

est ce que vous auriez une idee de l'outil capable de faire ca? je dispose de spatialite, access, excel qgis mais pas oracle.

j'ai pensé aux tableaux croisés dynamiques sous excel mais je n'ai pas une mais une tripotées de villes et j'aimerai garder les 3 cas de figures les plus importants

avez vous une idée? merci!

Bonjour,

quelque chose comme:

Code:

select t.ville, t.info, (cast(count(info) as real) / t2.cnt) * 100
from test t,
(select ville, cast(count(ville) as real) as cnt from test group by ville) as t2
where t2.ville = t.ville
group by info
order by t.ville;

sur une table ressemblant a ca:

Code:

ACHERES        000000000
ACHERES        000000000
ACHERES        000000000
ACHERES        000000000
ACHERES        000000000
ACHERES        000000000
ACHERES        010000000
ACHERES        100000000
ACHERES        100000000
ACHERES        100100100
agen        0000000
agen        0000000
agen        00000011000000011
agen        00000011000000011
agen        00000011000000011
agen        00000011100000011

Donne le resultat suivant:

Code:

ACHERES        000000000                60
ACHERES        010000000                10
ACHERES        100000000                20
ACHERES        100100100                10
agen        0000000                    33.33333333333333
agen        00000011000000011        50
agen        00000011100000011        16.666666666666664

les fonctions dont parlait Pierre, couplées à l'utilisation de la clause OVER (PARTITION BY...) sont des Windowing functions, ou ranking functions (suivant les bases). C'est tres puissant pour faire des stats ou des classements de ses données.

Postgresql supporte ces fonctions depuis la version 8.4.

Un article interessant a ce sujet: http://database-programmer.blogspot.com … ction.html

Nicolas

Dernière modification par Nicolas Ribot (Wed 10 August 2011 12:49)

Nicolas Ribot · Wed 10 August 2011 12:00

(bon, pardon pour la mise en page, je suis nul en indentation )

Dernière modification par Nicolas Ribot (Wed 10 August 2011 12:01)

cyrille · Wed 10 August 2011 14:52

Merci pour votre aide precieuse

J'ai demonté la requête en 2

select ville, cast(count(ville) as real) as cnt from test group by ville

fonctionne bien avec ville un group by ville dans une colonne et le nombre d'enregistrement dans l'autre

ensuite si je lance la requete en entier, je n'obtient qu'un seul enregistrement. je continue a chercher.

ThomasG · Fri 12 August 2011 21:27

Bonsoir,

Même si de nouvelles fonctions spécifiques existent par exemple dans PostgreSQL ou Oracle comme signalé par Pierre et Nicolas, je préfère souvent repartir de la base (a priori vous débutez en SQL)

Les étapes que j'ai suivi sont les suivantes :

-Grouper pour compter par ville et par type de résultat info

Code:

SELECT ville,info,count()
AS resultvi
FROM test
GROUP BY ville, info

-Grouper pour compter par ville seulement

Code:

SELECT ville,count()
AS resultv
FROM test
GROUP BY ville

Maintenant vous avez deux résultats : d'une part, le nombre total de résultats par ville et d'autre part, le nombre de résultat par ville pour chaque type d'info.
Il vous reste à diviser l'un par l'autre en faisant attention au type en faisant un cast qui est la déclaration d'un type pour une colonne considérée (les count renvoient des entiers et il vous faut des nombres à virgule REAL) et en effectuant une jointure
Le résultat est au choix (les deux syntaxes suivantes sont justes)

Code:

select tv.ville, tvi.info, resultvi, resultv, CAST(resultvi AS REAL) / CAST(resultv AS REAL)*100 from (SELECT ville, count() AS resultv FROM test GROUP BY ville) as tv, 
(SELECT ville, info, count() AS resultvi FROM test GROUP BY ville, info) as tvi
where tv.ville = tvi.ville

Code:

select tv.ville, tvi.info, resultvi, resultv, CAST(resultvi AS REAL) / CAST(resultv AS REAL)*100 from (SELECT ville, count() AS resultv FROM test GROUP BY ville) as tv JOIN (SELECT ville, info, count() AS resultvi FROM test GROUP BY ville, info) as tvi ON (tv.ville = tvi.ville)

Vous trouverez en pièce jointe le fichier csv utilisé et à charger dans spatialite en tant que csv/txt. Pour info, le séparateur de texte est le guillemet double, le séparateur de colonne le point virgule, l'encodage n'est pas important car on a pas d'accent dans notre fichier de test, le séparateur de décimal n'a pas nom plus d'importance car on n'importe pas des données décimales.

Cordialement

ThomasG

PS: Si vous ne l'avez pas consulté, allez voir le cookbook spatialite qui est bien fait http://www.gaia-gis.it/spatialite-2.4.0 … okbook-fr/

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

cyrille · Tue 16 August 2011 11:23

Bonjour Thomas, merci pour le temps que tu as passé pour ce petit tuto! ca fonctionne tres bien.
Y t'il moyen de ne garder que les 3 plus gros pourcentage simplement? sinon pas grave cette reponse me va.
Merci!

Dernière modification par cyrille (Tue 16 August 2011 11:24)

Nicolas Ribot · Tue 16 August 2011 11:44

cyrille a écrit:

Bonjour Thomas, merci pour le temps que tu as passé pour ce petit tuto! ca fonctionne tres bien.
Y t'il moyen de ne garder que les 3 plus gros pourcentage simplement? sinon pas grave cette reponse me va.
Merci!

Bonjour,

oui, il y a moyen: SQLite supporte l'opérateur LIMIT:

select * from table limit 3; (par exemple)

Nicolas

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Tue 09 August 2011 18:15

[spatialite] colle SQL

#2 Tue 09 August 2011 18:41

Re: [spatialite] colle SQL

Code:

#3 Tue 09 August 2011 19:05

Re: [spatialite] colle SQL

#4 Tue 09 August 2011 19:43

Re: [spatialite] colle SQL

#5 Wed 10 August 2011 11:50

Re: [spatialite] colle SQL

Code:

Code:

Code:

#6 Wed 10 August 2011 12:00

Re: [spatialite] colle SQL

#7 Wed 10 August 2011 14:52

Re: [spatialite] colle SQL

#8 Fri 12 August 2011 21:27

Re: [spatialite] colle SQL

Code:

Code:

Code:

Code:

#9 Tue 16 August 2011 11:23

Re: [spatialite] colle SQL

#10 Tue 16 August 2011 11:44

Re: [spatialite] colle SQL

Pied de page des forums