Forum Programmation.SQL calcul sur plusieurs aggregats

Posté par  .
Étiquettes : aucune
4
21
juin
2012

Bonjour tout le monde

Ces temps-ci, je cherche a faire un peu d'analyse de donnees sur mes contenus, et je commence a tomber sur des cas assez recurrents qui me posent probleme.
Admettons que j'ai 2 tables, une table "community" avec 2 colonnes "user_id" et "id", et une table "users" avec disons "id" et "age"

Si je veux le nombre de moins de 30 ans par communaute, pas de soucis

select community.id, count(user_id) from community, users where users.id=user_id and age<30 group by community.id

Par contre, comment les choses se passent si je veux calculer plusieurs aggregats avec des filtres differents ?
Par exemple, dans une meme requete, renvoyer le nombre de moins de 18 ans et de plus de 60 ans ?
J'avais commence quelque chose avec des clauses select imbriquees dans mon from, mais je me suis vite rendu compte que mon extraction commencait apres que ces select aient ete faits en integralite. Si je rajoute un limit 10 a la fin par exemple, je dois quand meme patienter une eternite.
Ainsi,

select q1.id, c1, c2 from
(select id, count(user_id) as "c1" from community group by id) as "q1",
(select community.id as "id", count(user_id) as "c2" from community, users where users.id=user_id and age<30 group by community.id) as "q2"
where q1.id=q2.id

semble vraiment inefficace !
Y a-t'il une meilleure methode pour faire cela ?

  • # CASE

    Posté par  (site web personnel) . Évalué à 2.

    No testé, mais l'idée devrait être la:

    SELECT
    CASE WHEN u.age < 18 THEN 'junior'
         WHEN u.age > 60 THEN 'senior'
         ELSE 'medior'
    END as age_category
  , c.id
  , count(c.user_id)
FROM
    community c
JOIN
    users u ON u.id=c.user_id
GROUP BY 
    c.id
  , age_category

    Le filtre lui même (dans le CASE) peut être aussi complexe que tu le souhaites.

  • # oupse

    Posté par  . Évalué à 2.

    tu peux faire une double jointure, avec 3 colonne avec pour résultat communauté, moins de 18 ans, plus de 60 ans (et faire des count(moins18),cout(plus60).

    en gros chaque enregistrement sera de la forme comunauté, 5, null ou comunauté, null, 65

    Il ne faut pas décorner les boeufs avant d'avoir semé le vent

  • # Commentaire supprimé

    Posté par  . Évalué à 2.

    Ce commentaire a été supprimé par l’équipe de modération.

    • [^] # Re: Ne pas tout faire en SQL

      Posté par  . Évalué à 1.

      C'est exactement ce que je pense… un chouilla de python et j'aurais ce qu'il me faut !
      Mais, mais, mais… la table community contient 300 millions d'entrees, pour environ 3 millions de communautes differentes.
      A ce rythme la, mon python sera a genoux, enfin je me comprends ! Alors a part ecrire les resultats dans des fichiers, fusionner tout ca, je ne vois pas trop comment m'en sortir.
      J'avais suppose que laisser faire le tout a la base serait un gain de temps et de performances.

  • # Window Functions

    Posté par  (site web personnel) . Évalué à 2.

    Il y a les Window Functions pour ce genre de chose
    http://www.postgresql.org/docs/9.1/static/tutorial-window.html

    • [^] # Re: Window Functions

      Posté par  . Évalué à 1.

      De ce que j'en lis, ces window functions ne renvoient pas d'aggregat et conservent le nombre de lignes.
      Dans mon cas, ca　me donne 300 millions de lignes en sortie, qui contiennent effectivement la bonne info, mais pas tres compact.
      De plus, je ne vois pas vraiment comment integrer une jointure dans ces fonctions (pour le filtrage sur les user), j'ai loupe quelque chose peut-etre ?

      • [^] # Re: Window Functions

        Posté par  (site web personnel) . Évalué à 1.

        oui tu as surement manqué quelque chose
        il y a plein de littérature dessus, regarde bien la syntaxe de OVER où tu définis la partition, son ordre, et éventuellement sa frame
        lire par exemple
        http://blog.ropardo.ro/2010/01/11/windowing-functions-in-postgresql-8-4/
        http://www.pgcon.org/2009/schedule/attachments/98_Windowing%20Functions.pdf
        etc …

        • [^] # Re: Window Functions

          Posté par  . Évalué à 0.

          Je confirme, j'ai vraiment du louper quelque chose > Admettons que j'ai 300 millions d'entrees dans ma table community (en fait, community_users comme ecrit plus bas, j'avais juste voulu condenser un peu l'ecriture), de tous les exemples que j'ai vus, j'aurai egalement 300 millions de lignes en sortie de requete.

          Est-ce qu'il vous serait possible de me montrer un exemple ?
          Typiquement, je voudrais un resultat identique a ce que renvoie la requte du journal. A savoir environ 3 millions de lignes, soit 1 par communaute, de la forme

          com_id, nbusers total, nbusers de moins de 25 ans

          Mais j'ai l'impression que les join plombent beaucoup les performances :(

  • # Une solution

    Posté par  . Évalué à -1.

    Tiens :

    SELECT community.id, COUNT(u.nbmineurs) AS nbmineurs, COUNT(u.nbvieux) AS nbvieux
FROM (SELECT *, age < 18 AS nbmineurs, age > 60 AS nbvieux FROM user) u
JOIN community ON community.user_id = u.id
GROUP BY community.id;

    C'est quel sgbd que tu utilises ?

    • [^] # Re: Une solution

      Posté par  . Évalué à 0.

      Encore plus simple

      SELECT community_id, COUNT(u.mineur) AS nbmineurs, COUNT(u.nbvieux) AS nbvieux FROM
(SELECT users.*, age < 18 AS mineur, age > 60 AS vieux, community.id AS community_id FROM users
JOIN community ON community.iduser = users.id) u
GROUP BY community_id

      • [^] # Re: Une solution

        Posté par  . Évalué à 1.

        Ah mais c'est pas bete du tout ca !
        J'avoue a ma grande honte que je n'avais jamais utilise d'expressions logiques comme ca dans mes requetes. La faute a l'usage d'ORM je suppose, qui me force a penser "objet" alors que la effectivement c'est tout simple ^
        Je testerai ca avec ma table une fois que j'aurai fini de la restaurer !

      • [^] # Re: Une solution

        Posté par  . Évalué à 1.

        Ah, mais non, c'est faux.
        Si tu fais COUNT(u.mineur), tu comptes toutes les lignes, y compres celles où l'âge est supérieur à 18.
        Avec un moteur pour lequel vrai = 1, et faux = 0 (Oracle), il aurait fallu faire la somme :
        SUM (u.mineur)

        Avec PostgreSQL, il faut convertir les booléens en entier

        SELECT
        community_id, SUM(u.mineur::integer) AS nbmineurs,
        SUM(u.vieux::integer) AS nbvieux
        FROM
        (
        SELECT users.*, age 60 AS vieux,
        community.id AS community_id
        FROM users
        JOIN community
        ON community.iduser = users.id
        ) u
        GROUP BY community_id

        Remarque au passage : si tes utilisateurs ne font partie que d'une seule communauté, la table community telle que décrite est inutile (on met l'identifiant de la communauté directement dans users).
        Dans le cas contraire, il y a une relation n-n entre utilisateurs et communautés, donc on appelle habituellement la table avec un nom du style : usercommunity, pour éviter la confusion avec une table "community" qui contiendra elle les informations spécifiques à la communauté.

        • [^] # Re: Une autre solution ? UNION ALL

          Posté par  . Évalué à 0. 

          select id , count(id) from (
select users.* , "mineurs" as StatusAge from users where users.age < 18
union all
select users.* , "Seniors" as StatusAge from users where users.age > 60
union all
select users.* , "Quarantenaires" as StatusAge from users where (users.age > 39 and/or users.age < 50)
) group by StatusAge

          C'est dans l'idée je n'est pas vraiment testé !
          Extraire les infos qui nous intéresse, les assemblés en les catégorisant puis faire les calculs dessus !

        • [^] # Re: Une solution

          Posté par  . Évalué à 0.

          Effectivement dans mon schema d'origine j'ai 3 tables, comme decrit. J'ai juste voulu rendre la chose plus lisible. Sinon, je devrais ecrire un autre join pour mes conditions sur la communaute (categorie, date de creation, peu importe), mais ce n'etait pas vraiment le point que je voulais soulever :)

Suivre le flux des commentaires

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