Obtenir des données pour un histogramme

Question

Existe-t-il un moyen de spécifier la taille des emplacements dans MySQL ? Pour l'instant, j'essaie la requête SQL suivante :

select total, count(total) from faults GROUP BY total;

Les données générées sont suffisamment bonnes, mais il y a tout simplement trop de lignes. Ce dont j'ai besoin, c'est d'un moyen de regrouper les données dans des bacs prédéfinis. Je peux le faire à partir d'un langage de script, mais existe-t-il un moyen de le faire directement en SQL ?

Exemple :

+-------+--------------+
| total | count(total) |
+-------+--------------+
|    30 |            1 | 
|    31 |            2 | 
|    33 |            1 | 
|    34 |            3 | 
|    35 |            2 | 
|    36 |            6 | 
|    37 |            3 | 
|    38 |            2 | 
|    41 |            1 | 
|    42 |            5 | 
|    43 |            1 | 
|    44 |            7 | 
|    45 |            4 | 
|    46 |            3 | 
|    47 |            2 | 
|    49 |            3 | 
|    50 |            2 | 
|    51 |            3 | 
|    52 |            4 | 
|    53 |            2 | 
|    54 |            1 | 
|    55 |            3 | 
|    56 |            4 | 
|    57 |            4 | 
|    58 |            2 | 
|    59 |            2 | 
|    60 |            4 | 
|    61 |            1 | 
|    63 |            2 | 
|    64 |            5 | 
|    65 |            2 | 
|    66 |            3 | 
|    67 |            5 | 
|    68 |            5 | 
------------------------

Ce que je recherche :

+------------+---------------+
| total      | count(total)  |
+------------+---------------+
|    30 - 40 |            23 | 
|    40 - 50 |            15 | 
|    50 - 60 |            51 | 
|    60 - 70 |            45 | 
------------------------------

Je suppose que cela ne peut pas être réalisé de manière directe, mais une référence à une procédure stockée connexe serait également acceptable.

Answer 1

Cet article traite d'une méthode rapide et simple pour créer un histogramme dans MySQL pour des valeurs numériques.

Il existe de nombreuses autres façons de créer des histogrammes qui sont meilleures et plus flexibles. plus flexibles, utilisant des instructions CASE et d'autres types de logique complexe. Cette méthode me séduit à chaque fois, car elle est si facile à mettre en œuvre. facile à modifier pour chaque cas d'utilisation, et si courte et concise. Voici comment le faire :

SELECT ROUND(numeric_value, -2)    AS bucket,
       COUNT(*)                    AS COUNT,
       RPAD('', LN(COUNT(*)), '*') AS bar
FROM   my_table
GROUP  BY bucket;

Il suffit de remplacer numeric_value par la colonne de votre choix, de modifier l'attribut l'incrément d'arrondi, et c'est tout. J'ai fait en sorte que les barres soient dans échelle logarithmique, afin qu'elles ne grossissent pas trop lorsque vous avez de grandes valeurs.

numeric_value doit être décalée dans l'opération d'arrondi, en fonction de l'incrément d'arrondi, afin de s'assurer que le premier godet contient autant d'éléments que les godets suivants.

Par exemple, avec ROUND(numeric_value,-1), la valeur numérique dans la plage [0,4] (5 éléments) sera placée dans le premier seau, tandis que [5,14] (10 éléments) dans le deuxième, [15,24] dans le troisième, à moins que la valeur numérique ne soit décalée de manière appropriée via ROUND(numeric_value - 5, -1).

Voici un exemple d'une telle requête sur des données aléatoires qui ont l'air très intéressantes. agréable. C'est suffisant pour une évaluation rapide des données.

+--------+----------+-----------------+
| bucket | count    | bar             |
+--------+----------+-----------------+
|   -500 |        1 |                 |
|   -400 |        2 | *               |
|   -300 |        2 | *               |
|   -200 |        9 | **              |
|   -100 |       52 | ****            |
|      0 |  5310766 | *************** |
|    100 |    20779 | **********      |
|    200 |     1865 | ********        |
|    300 |      527 | ******          |
|    400 |      170 | *****           |
|    500 |       79 | ****            |
|    600 |       63 | ****            |
|    700 |       35 | ****            |
|    800 |       14 | ***             |
|    900 |       15 | ***             |
|   1000 |        6 | **              |
|   1100 |        7 | **              |
|   1200 |        8 | **              |
|   1300 |        5 | **              |
|   1400 |        2 | *               |
|   1500 |        4 | *               |
+--------+----------+-----------------+

Quelques notes : Les plages qui n'ont pas de correspondance n'apparaîtront pas dans le décompte - vous n'aurez pas de zéro dans la colonne de comptage. De plus, j'utilise la fonction ROUND ici. Vous pouvez tout aussi bien la remplacer par TRUNCATE. si vous pensez que cela a plus de sens pour vous.

Je l'ai trouvé ici http://blog.shlomoid.com/2011/08/how-to-quickly-create-histogram-in.html

Answer 2

La réponse de Mike DelGaudio est la façon dont je le fais, mais avec un léger changement :

select floor(mycol/10)*10 as bin_floor, count(*)
from mytable
group by 1
order by 1

L'avantage ? Vous pouvez faire des bacs aussi grands ou aussi petits que vous le souhaitez. Des bacs de taille 100 ? floor(mycol/100)*100 . Bacs de taille 5 ? floor(mycol/5)*5 .

Bernardo.

Answer 3

SELECT b.*,count(*) as total FROM bins b 
left outer join table1 a on a.value between b.min_value and b.max_value 
group by b.min_value

La table bins contient les colonnes min_value et max_value qui définissent les bins. Notez que l'opérateur "join... on x BETWEEN y and z" est inclusif.

table1 est le nom de la table de données

Answer 4

La réponse d'Ofri Raviv est très proche mais incorrecte. Le site count(*) será 1 même s'il y a zéro résultat dans un intervalle d'histogramme. La requête doit être modifiée afin d'utiliser une méthode conditionnelle. sum :

SELECT b.*, SUM(a.value IS NOT NULL) AS total FROM bins b
  LEFT JOIN a ON a.value BETWEEN b.min_value AND b.max_value
GROUP BY b.min_value;

Answer 5

select "30-34" as TotalRange,count(total) as Count from table_name
   where total between 30 and 34
union (
select "35-39" as TotalRange,count(total) as Count from table_name 
   where total between 35 and 39)
union (
select "40-44" as TotalRange,count(total) as Count from table_name
   where total between 40 and 44)
union (
select "45-49" as TotalRange,count(total) as Count from table_name
   where total between 45 and 49)
etc ....

Tant qu'il n'y a pas trop d'intervalles, c'est une assez bonne solution.