[MySQL] Effectuer une distribution statistique en SQL

[loic_cadiot]

Bonjour,

Je suis confronté à une problématique de "distribution statistique" que j'aimerais résoudre avec une requête SQL.

Je travaille sur des réseaux sociaux, et je souhaite compter le nombre d'amis de chacun de mes utilisateurs.

Ce que je sais faire :
- ramener le nombre d'amis pour chacun de mes utilisateurs
- ramener le nombre d'utilisateurs ayant 1, 2, ... amis
- ramener le nombre total d'utilisateurs ayant des amis

Ce que je souhaite faire :
- ramener le nombre d'amis (en X) et la répartition en % par rapport au total de mes utilisateurs (en Y) => exemple

Ma requête actuelle :

Code :

1
2
3
4
5
6
7
SELECT nb_amis, count(util_id) AS nb_util
FROM (
    SELECT utilisateurs.util_id, count(DISTINCT amis.ami_id) AS nb_amis
    FROM utilisateurs INNER JOIN amis ON utilisateurs.util_id = amis.util_id
    GROUP BY 1
) tab
GROUP BY 1;

Pouvez-vous m'aider ?
Merci par avance ! :o)

[CinePhil]

Comme ça ?

Code :

1
2
3
4
5
6
7
SELECT nb_amis, (100 * count(util_id) AS nb_util) / (SELECT COUNT(*) FROM utilisateurs) AS pourcentage
FROM (
    SELECT utilisateurs.util_id, count(DISTINCT amis.ami_id) AS nb_amis
    FROM utilisateurs INNER JOIN amis ON utilisateurs.util_id = amis.util_id
    GROUP BY utilisateurs.util_id
) tab
GROUP BY nb_amis

[punkoff]

hmm attention Cinéphil, ceci est une requête scalaire (edit : remarque vu la répartition .... ca ne sera pas un problème)

Il vaudrai mieux prendre une option de jointure cartésiène pour ce problème je pense.

Vu que me MySql ne supporte pas le cross join ..

Code :

1
2
3
4
5
6
7
8
9
 
SELECT nb_amis, (100 * count(util_id) AS nb_util) / max(nb_tot) AS pourcentage
FROM (
    SELECT utilisateurs.util_id, count(DISTINCT amis.ami_id) AS nb_amis
    FROM utilisateurs INNER JOIN amis ON utilisateurs.util_id = amis.util_id
    GROUP BY utilisateurs.util_id
) tab,
(SELECT COUNT(*) AS nb_tot FROM utilisateurs) 
GROUP BY nb_amis

[CinePhil]

Euh... il me semble avoir répondu à ça :

ramener le nombre d'amis et la répartition en % par rapport au total de mes utilisateurs

Le fait que ce soit en X et en Y n'est pas du ressort du SGBD il me semble.

Dans ta requête comme dans la mienne :

Code :

SELECT COUNT(*) AS nb_tot FROM utilisateurs

Ne donne qu'un seul nombre : le nombre de lignes de la table utilisateurs, c'est à dire le nombre total d'utilisateurs.

Demander dans ta requête le max(nb_tot) donnera toujours le même nombre !

J'ai donc l'impression que ta requête donne le même résultat que la mienne mais avec une jointure inutile.

[pacmann]

Salut !

D'après ton graphique, tu veux une somme cumulative, donc pour mois il y a deux étapes :
- Grouper le nombre d'utilisateurs par nombre d'amis
- Sommer pour chaque nombre d'amis le pourcentage de gens qui ont autant ou moins.

Sans fonctions analytiques, ça donne un truc du genre :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
SELECT tab4.nb_amis, sum(nb_util) / (SELECT count(*) FROM utilisateurs) pct
FROM 
    (SELECT nb_amis, count(*) AS nb_util
    FROM (
        SELECT count(amis.ami_id) AS nb_amis
        FROM utilisateurs 
            LEFT OUTER JOIN amis ON utilisateurs.util_id = amis.util_id
        GROUP BY utilisateurs.util_id
    ) tab
    GROUP BY nb_amis) tab2
    JOIN
        (SELECT DISTINCT nb_amis
    FROM (
        SELECT count(amis.ami_id) AS nb_amis
        FROM utilisateurs 
            LEFT OUTER JOIN amis ON utilisateurs.util_id = amis.util_id
        GROUP BY utilisateurs.util_id
    ) tab3
    ) tab4
    ON tab4.nb_amis >= tab2.nb_amis
GROUP BY tab4.nb_amis

Punkoff, tu penses que MySQL ré-exécute la requête scalaire à chaque fois ?
(Sur Oracle je parierais que non)

[dehorter olivier]

Pour faire une digression (sur le plan statistique), le terme de distribution statistique est incorrect.

La distribution statistique est le représentation des données sous forme graphique pour vérifier quelle suit une loi statistique (gauss, Poisson, etc) - pour faire simple -

[punkoff]

Envoyé par pacmann

Punkoff, tu penses que MySQL ré-exécute la requête scalaire à chaque fois ?
(Sur Oracle je parierais que non)

C'est la question que j'essai d'éllucidé en ce moment même
le trip c'est qu'ici il n'y a qu'un "select count(*) from ma_table" sans référence à l'autre table ... du coup ...


edit: je ne peux pas tester sur MySql, mais sur 2 autres sgbd ca passe sasn problème.

Donc je suppose que la solution de Cinephil est mieux (je parle de la technique utilisée pas de la requête à fournir pour résoudre le problème initiale)

[pacmann]

Envoyé par dehorter olivier

Pour faire une digression (sur le plan statistique), le terme de distribution statistique est incorrect.

La distribution statistique est le représentation des données sous forme graphique pour vérifier quelle suit une loi statistique (gauss, Poisson, etc) - pour faire simple -

Ouah, tu chipottes quand même

La répartition d'une population par modalité, et la version cumulative, c'est la version discrète de ce que tu obtiens en intégrant la loi de probabilité...
En tous cas, le terme de distribution s'applique bien ici. Après, c'est le mot statistique que tu n'aimes pas ?

[dehorter olivier]

Envoyé par pacmann

Ouah, tu chipottes quand même

La répartition d'une population par modalité, et la version cumulative, c'est la version discrète de ce que tu obtiens en intégrant la loi de probabilité...
En tous cas, le terme de distribution s'applique bien ici. Après, c'est le mot statistique que tu n'aimes pas ?

oui, je sais ET tu as raison, la version cumulative est bien une distribution au sens statistique.


Sur le plan SQL, je ne suis pas très au point dessus; mais les CTE pourraient être utile dans ce contexte (si elles sont implémentées dans MySql ?)

[CinePhil]

Eh non ! Pas de récursif chez MySQL !

Je n'ai pas regardé la requête de Pacman en détail mais j'ai l'impression que ce sera plus rapide de récupérer le paquet de données de ma requête puis de traiter la cumulation dans le langage de programmation en même temps que la construction du graphique.

[pacmann]

Oui s'il y a des problèmes de perf, c'est sûrement ce qu'il y a de mieux à faire... ou alors passer par une table temporaire (toujours pas aussi rapide qu'un traitement itératif sur le premier résultset, mais une fois l'aggrégat enregistré, la requête doit être beaucoup plus légère).

Olivier, la CTE aurait été utile uniquement sous Oracle, parce qu'elle permet la matérialisation du résultat (c'est à dire la mise en table temporaire "système").

Mais avant de reprocher ça à MySQL, je reprocherais plutôt l'absence de fonctions analytique qui auraient certainement permis d'écrire le résultat très différemment ! (et plus rapidement bien sûr )

Par exemple sous Oracle :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT nb_amis, sum(nb_util) OVER(ORDER BY nb_amis) / count(*) OVER(ORDER BY NULL)
FROM (
       SELECT nb_amis, count(*) nb_util
       FROM (
            SELECT count(b.amis_id) nb_amis
            FROM utilisateurs a
              LEFT OUTER JOIN amis b ON a.util_id = b.util_id
            GROUP BY a.util_id)
            )
       GROUP BY nb_amis
      )