Forum Programmation.autre [MySQL] un GROUP BY un peu spécial

Posté par mouling le 08 juin 2005 à 09:38.

Étiquettes : aucune

juin

2005

Bonjour,

J'ai sous MySQL des données représentant des actions horodatées attribuées à des utilisateurs, ce qui donne quelque chose comme ceci :



______________________________________


|        Date           |  Individu   | 


|  2005-06-02 12:05:00  |  Pierre     |


|  2005-06-02 12:05:10  |  Pierre     |


|  2005-06-02 12:06:00  |  Paul        |


|  2005-06-02 12:10:00  |  Jacques     |


|  2005-06-02 12:11:30  |  Jacques     |


|  2005-06-02 12:20:00  |  Pierre     |


|  2005-06-02 12:30:00  |  Jacques     |


|  ....                 |  ....       |


______________________________________

Je souhaiterais faire une sorte de GROUP BY qui s'appliquerait aux entrées consécutives, quelque chose comme un "SELECT MIN(Date), Individu FROM table GROUP BY CONSECUTIVE Individu" qui me renverrait pour chaque individu le début de son activité jusqu'à ce qu'un autre individu prenne la main :



______________________________________


|      Min(Date)        |  Individu   | 


|  2005-06-02 12:05:00  |  Pierre     |


|  2005-06-02 12:06:00  |  Paul        |


|  2005-06-02 12:10:00  |  Jacques     |


|  2005-06-02 12:20:00  |  Pierre     |


|  2005-06-02 12:30:00  |  Jacques     |


|  ....                 |  ....       |


______________________________________

Après fouille en règle de la doc MySQL et du Vaste Web, je ne trouve ni l'improbable commande (j'y croyais peu...) ni une astuce permettant de ruser finement pour obtenir ce résultat.
Un linuxfriens la connaîtrait-il, cette astuce ?

Un grand merci d'avance pour vos tuyaux !

# T'as droit à un langage de programmation pour faire ça ? :)

Posté par mac le 08 juin 2005 à 10:35. Évalué à 1.

Au moins un petit Perl ?
- [^] # Re: T'as droit à un langage de programmation pour faire ça ? :)
  
  Posté par JJD le 08 juin 2005 à 11:01. Évalué à 2.
  
  Salut,
  
  Je ne sais pas du tout comment faire ça avec une requête SQL, mais si tu exportes ta table dans un fichier (ou la sortie standard), c'est finalement assez simple à réaliser dans n'importe quel langage.
  
  Un petit exemple : disons que tu as tes données triées par date dans un fichier "toto", un coup de
  uniq -s27 toto
  
  te donneras :
  ______________________________________ | Date | Individu | | 2005-06-02 12:05:00 | Pierre | | 2005-06-02 12:06:00 | Paul | | 2005-06-02 12:10:00 | Jacques | | 2005-06-02 12:20:00 | Pierre | | 2005-06-02 12:30:00 | Jacques |
  
  A+
  
  JJD
- [^] # Re: T'as droit à un langage de programmation pour faire ça ? :)
  
  Posté par mouling le 08 juin 2005 à 11:55. Évalué à 1.
  
  oui, j'y ait droit, c'est d'ailleurs comme ça que je fais pour l'instant ; mais c'est un peu fastidieux, si je pouvais déléguer ça à MySQL, ça m'arrangerait bien...
# Distinct ?

Posté par wax le 08 juin 2005 à 12:03. Évalué à 1.

J'vais ptetre dire une connerie, mais pourquoi pas faire un DISTINCT sur le nom d user ?
- [^] # Re: Distinct ?
  
  Posté par mouling le 08 juin 2005 à 12:54. Évalué à 1.
  
  Parce que si je fais cela, j'aurais une seule entrée pour Pierre, une seule entrée pour Paul, une seule entrée pour Jacques, etc... alors que chaque individu peut avoir plusieurs entrées.
  Je cherche à regrouper les entrées "qui se suivent" suivant un critère de tri quelconque (dans mon exemple, une date), tandis que le DISTINCT truc (ou le GROUP BY truc) me regroupe tous les "truc" de la table.
# User Defined Functions

Posté par ttt tt le 08 juin 2005 à 13:18. Évalué à 2.

En SQL pur et dur tu vas avoir du mal, c'est impossible à mon humble avis.

Par contre, tu peux surement te créer ta propre User Defined Function, en codant une bonne fois pour toute la fonction en C (par ex), et en l'intégrant au noyau MySQL, faut avoir accès à la compilation.

Un très bon bouquin sur MySQL t'expliquera comment faire ça.
(Voir MySQL 4 chez Micro Application, bon, ok c'est mon bouquin...)

Sinon, le manuel online explique comment faire, mais c'est pas les rois de la clarté.

L'avantage, c'est que une fois ta fonction implémentée, tu pourras t'en servir dans tes requettes.
- [^] # Re: User Defined Functions
  
  Posté par mouling le 08 juin 2005 à 13:42. Évalué à 1.
  
  En SQL pur et dur tu vas avoir du mal, c'est impossible à mon humble avis.
  
  Hum, c'est bien ce que je craignais...
  
  tu peux surement te créer ta propre User Defined Function, en codant une bonne fois pour toute la fonction en C (par ex), et en l'intégrant au noyau MySQL
  
  Ah, ça c'est une idée qu'elle est bonne et séduisante : tout à fait le genre de truc qui va m'amuser ! Je jetterai un coup d'oeil quand j'aurai un peu de temps, je suppose que ça ne va pas de soi.
  Merci du tuyau !
# Hop

Posté par Pooly (site web personnel) le 08 juin 2005 à 14:08. Évalué à 2.

D'apres ce que j'ai compris, tu veux les grouper seulement si les entrées sont consécutives ? c'est bien ca ?
Avec une table temporaire ou une subquery, ca passe pas ?
au pire poste sur la liste mysql ( mysql @ lists.mysql.com ) qui est tres efficace.
# Plusieurs colonnes dans le GROUP BY

Posté par Obsidian le 08 juin 2005 à 16:03. Évalué à 1.

On peut pas grouper plusieurs colonnes dans MySQL (marche bien sous Sybase en tout cas) ?

SELECT min(Date),individu FROM table GROUP BY Date,Individu

Ca ne marche pas ?
- [^] # Re: Plusieurs colonnes dans le GROUP BY
  
  Posté par Obsidian le 08 juin 2005 à 16:05. Évalué à 2.
  
  Ok, vous pouvez moinsser le commentaire ci-dessus. Je m'aperçois que je viens de dire une ânerie. Désolé.
# Un des moyens,

Posté par Gyro Gearllose le 08 juin 2005 à 16:31. Évalué à 3.
ce serait d'avoir un n° de séquence en plus de ce que tu donnes comme information. Alors soit tu peux modifier la structure de ta table pour en attribuer un, soit tu peux passer par une table temporaire.... Sinon, je ne pense pas que ce soit possible. J'ai fait un test sous postgreSQL, en ajoutant un champ séquentiel qui respecte l'ordre que tu donnes dans ton premier tableau ci-dessus, et ceci : select * from toto t1 where ind not in (select ind from toto where num + 1 = t1.num) order by dat_ind; Me retourne bien ce que tu veux. Allez, pour la forme :
```
david=# select * from toto;
         dat_ind          |    ind    | num
--------------------------+-----------+-----
 Thu 02 Jun 12:05:00 2005 |   Pierre  |   1
 Thu 02 Jun 12:05:10 2005 |   Pierre  |   2
 Thu 02 Jun 12:06:00 2005 |   Paul    |   3
 Thu 02 Jun 12:10:00 2005 |   Jacques |   4
 Thu 02 Jun 12:11:30 2005 |   Jacques |   5
 Thu 02 Jun 12:20:00 2005 |   Pierre  |   6
 Thu 02 Jun 12:30:00 2005 |   Jacques |   7
(7 rows)

david=# select * from toto t1 where ind not in (select ind from toto where num + 1 = t1.num) order by dat_ind;
         dat_ind          |    ind    | num
--------------------------+-----------+-----
 Thu 02 Jun 12:05:00 2005 |   Pierre  |   1
 Thu 02 Jun 12:06:00 2005 |   Paul    |   3
 Thu 02 Jun 12:10:00 2005 |   Jacques |   4
 Thu 02 Jun 12:20:00 2005 |   Pierre  |   6
 Thu 02 Jun 12:30:00 2005 |   Jacques |   7
(5 rows)
```
En espérant que ça aide, comme on dit !
- [^] # Re: Un des moyens,
  
  Posté par mouling le 08 juin 2005 à 16:53. Évalué à 1.
  
  ah oui oui oui, ça aide beaucoup, merci !
  
  Qq tests plus tard, ça marche parfaitement en passant par une table temporaire, mais n'ai pas encore trouvé la requête qui va bien pour faire ça en une seule passe (grmmlml d'auto_increment qui veut pas passer dans un SELECT !). Je la poste dès que j'aurai trouvé...
  - [^] # CA MARCHE !
    
    Posté par Obsidian le 08 juin 2005 à 17:40. Évalué à 2.
    
    Ok, je viens de tatonner et ai trouvé ce qui suit.
    
    Voici une commande qui marche en se passant des numéros d'incrémentation, qui ont ceci d'ennuyeux qu'ils brisent la requête si jamais on supprime une ligne :
    
    select distinct min(b.heure),b.individu from pointage a join pointage b on b.heure > a.heure and a.individu != b.individu group by a.individu,a.heure order by 1
    
    En considérant bien sûr que la table s'appelle « pointage » !
    
    Seul inconvénient : Le premier gugusse à pointer n'apparaît jamais dans le résultat de cette requête ...
    - [^] # Re: CA MARCHE !
      
      Posté par mouling le 08 juin 2005 à 18:40. Évalué à 1.
      Ca marche... mais pas vraiment. Pour une raison qui m'échappe, ça marche si les individus sont déjà triés dans la table. Je pense que dans la table que tu as créée pour faire tes tests, tu as saisi les données dans l'ordre des dates. Par contre, si tu mélanges un peu tes insert, comme ceci :
      create table pointage ( heure datetime, individu varchar(10) ); insert into pointage (heure, individu) values ('2005-06-02 11:00:00', 'Pierre') ; insert into pointage (heure, individu) values ('2005-06-02 10:20:00', 'Pierre') ; insert into pointage (heure, individu) values ('2005-06-02 10:30:00', 'Paul') ; insert into pointage (heure, individu) values ('2005-06-02 10:50:00', 'Jacques') ; insert into pointage (heure, individu) values ('2005-06-02 10:00:00', 'Pierre') ; insert into pointage (heure, individu) values ('2005-06-02 10:40:00', 'Paul') ; insert into pointage (heure, individu) values ('2005-06-02 10:10:00', 'Pierre') ;
      ta requête renvoie alors :
      min(b.heure) individu 2005-06-02 10:30:00 Paul 2005-06-02 10:50:00 Pierre 2005-06-02 11:00:00 Pierre
      au lieu de
      min(b.heure) individu 2005-06-02 10:30:00 Paul 2005-06-02 10:50:00 Jacques 2005-06-02 11:00:00 Pierre
      si on insère les données dans l'ordre de la date. Troublant, n'est-ce pas ? En tout cas, merci d'avoir cherché ! Pour ma part, je n'ai toujours pas trouvé, malgré une fouille intensive de la doc MySQL, comme avoir une variable auto incrémentée dans un SELECT... mal parti, tout ça !
      - [^] # Re: CA MARCHE !
        
        Posté par Gyro Gearllose le 08 juin 2005 à 19:35. Évalué à 2.
        
        MySQL gère-t-il les vues ?
        Si oui, ça peut peut-être te sortir d'affaire !
        Tu utilises ta vue pour générer les tuples dans l'ordre des heures, avec l'auto-incrément, et tu te bases ensuite sur cette vue pour faire ta requête....
        Bon, je ne sais pas trop quoi penser. Apparement, oui, mysql gère les vues (cf. http://dev.mysql.com/doc/mysql/fr/create-view.html).(...)
        A toi de jouer, maintenant !
        Ah, et puis, oui, je suis bien content de t'avoir aidé, car j'avais déjà rencontré un problème similmaire il y a quelques temps, mais à l'époque je n'avais pas réussi à le résoudre. Comme quoi....
        
        [^] # Re: CA MARCHE !
        
        Posté par Obsidian le 08 juin 2005 à 19:48. Évalué à 2.
        
        Attention, comme dit plus haut, l'auto-increment pallie une faiblesse de conception, car la requête s'appuie sur une clé artificielle générée juste pour l'occasion, et pas sur la valeur des données elles-mêmes.
        
        En plus, cela place ta requête en échec si une ligne est supprimée, car le pas de une unité entre chaque ligne n'est plus respecté.
        
        De plus, ce n'est valable que si l'on insère les entrées chronologiquement. Si par exemple l'administrateur insère dans la table des informations provenant de deux sources distinctes et successive (qui a dit des fichiers Excel ?), tu es bon pour te taper une renumérotation manuelle de toutes tes entrées. Au pire, tu extrais le contenu de ta table avec un « SELECT ... ORDER BY heure; » vers un fichier, et tu fais un script qui te numérote tes lignes, mais le problème reste entier : Tu es obligé d'exporter le système de garantie de la cohérence de ta base en dehors du SGBD, tu ne peux plus assurer celle-ci en temps réel et, pire que tout, tu ne peux pas empêcher quelqu'un d'insérer des données corrompues entre deux contrôles.
        
        Pas terrible.
        
        [^] # Re: CA MARCHE !
        
        Posté par Gyro Gearllose le 08 juin 2005 à 19:54. Évalué à 2.
        
        Ce que tu dis est tout à fait juste, mais peut-être pallié grâce à une variable placée judicieusement dans la vue (si toutefois c'est possible).
        Je pense que c'est possible avec postgreSQL en plaçant une variable dans la vue. Comme c'est calculé à chaque fois que tu fais un select sur la vue, ça fonctionne forcément, aux erreurs près de ce que tu indiques dans ton post.
        Quant au fait que ma requête n'est valable que si les entrées sont insérées chronologiquement, ceci peut éventuellement être réglé dans la définition de la vue, avec un order by des familles !
        Sinon, et si c'est effectivement pour une application de pointage, j'en ai développé une à mon boulot, mais elle ne fonctionne pas du tout de cette manière.
        A chacun sa façon de voir les choses !
        
        [^] # Re: CA MARCHE !
        
        Posté par mouling le 08 juin 2005 à 20:03. Évalué à 1.
        
        arf, j'y avais bien pensé, mais en bas de la page de doc sur le CREATE VIEW, on peut lire :
        
        Cette commande a été ajoutée en MySQL 5.0.1.
        
        et tant que la 5 sera en Development Release, pas question que je m'y risque !
        Bon, soyons positifs : plus le temps passe, plus je cette échéance approche, et la résolution du problème itou !
      - [^] # Re: CA MARCHE !
        
        Posté par Obsidian le 08 juin 2005 à 19:39. Évalué à 2.
        
        En effet, visiblement MySQL s'emmêle les pinceaux lorsque l'on fait une recherche sur une colonne non-préalablement triée !
        
        Solution propre pour parer à cette anomalie : Créer un index sur ta colonne « heure », qui sert ici de clé.
        
        create index idx on pointage2 (heure);
        
        En revanche, l'ajout du nom de l'utilisateur par ,b.individu ne peut pas fonctionner. C'est également un « bug » (pour autant que je sache, en tous cas), qui permet l'affichage de ce nom en même temps que la valeur issue d'une fonction d'agrégation.
        
        Il faudrait transformer cette requête en sous-requête, et la passer à un ...
        
        SELECT * FROM pointage WHERE heure in ([...]) ORDER BY heure ASC;
        
        ... pour que ce soit parfait, mais bizarrement, MySQL n'aime pas non plus mes sous-requêtes :-(

Suivre le flux des commentaires

Note : les commentaires appartiennent à celles et ceux qui les ont postés. Nous n’en sommes pas responsables.