Obtenir des données pour le tracé d’histogramme

Existe-t-il un moyen de spécifier les tailles de bacs dans MySQL? En ce moment, j’essaie la requête SQL suivante:

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

Les données générées sont suffisantes, mais il y a trop de lignes. Ce dont j’ai besoin est un moyen de regrouper les données dans des bacs prédéfinis. Je peux le faire depuis un langage de script, mais y a-t-il un moyen de le faire directement dans 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 à toute procédure stockée associée serait également appropriée.

Ceci est un post sur un moyen super rapide de créer un histogramme dans MySQL pour les valeurs numériques.

Il existe de nombreuses autres façons de créer des histogrammes plus efficaces et plus flexibles, en utilisant des instructions CASE et d’autres types de logique complexe. Cette méthode me gagne encore et encore car elle est tellement simple à modifier pour chaque cas d’utilisation, et donc courte et concise. Voici comment vous le faites:

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

Changez simplement numeric_value en n’importe quelle colonne, changez l’incrément d’arrondi et c’est tout. J’ai fait en sorte que les barres soient en échelle logarithmique, de sorte qu’elles ne grossissent pas trop lorsque vous avez de grandes valeurs.

numeric_value doit être décalé dans l’opération ROUNDing, en fonction de l’incrément d’arrondi, afin de garantir que le premier compartiment contienne autant d’éléments que les compartiments suivants.

par exemple avec ROUND (numeric_value, -1), numeric_value dans l’intervalle [0,4] (5 éléments) sera placé dans le premier seau, tandis que [5,14] (10 éléments) dans le second, [15,24] dans le troisième, sauf si numeric_value est correctement compensé via ROUND (numeric_value – 5, -1).

Ceci est un exemple d’une telle requête sur certaines données aléatoires qui ont l’air assez douce. Assez bon 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 sans correspondance n’apparaîtront pas dans le compte – vous n’aurez pas de zéro dans la colonne count. De plus, j’utilise la fonction ROUND ici. Vous pouvez aussi facilement le remplacer par TRUNCATE si cela vous semble plus logique.

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

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 rendre les bacs aussi grands ou aussi petits que vous le souhaitez. Bacs de taille 100? floor(mycol/100)*100 . Bacs de taille 5? floor(mycol/5)*5 .

Bernardo.

 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 cases. Notez que l’opérateur “join … on x BETWEEN y et z” est inclusif.

table1 est le nom de la table de données

La réponse d’Ofri Raviv est très proche mais incorrecte. Le count(*) sera 1 même s’il n’y a aucun résultat dans un intervalle d’histogramme. La requête doit être modifiée pour utiliser une sum conditionnelle:

 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; 
 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 très bonne solution.

J’ai effectué une procédure qui peut être utilisée pour générer automatiquement une table temporaire pour les bacs en fonction d’un nombre ou d’une taille spécifique, pour une utilisation ultérieure avec la solution d’Ofri Raviv.

 CREATE PROCEDURE makebins(numbins INT, binsize FLOAT) # binsize may be NULL for auto-size BEGIN SELECT FLOOR(MIN(colval)) INTO @binmin FROM yourtable; SELECT CEIL(MAX(colval)) INTO @binmax FROM yourtable; IF binsize IS NULL THEN SET binsize = CEIL((@binmax-@binmin)/numbins); # CEIL here may prevent the potential creation a very small extra bin due to rounding errors, but no good where floats are needed. END IF; SET @currlim = @binmin; WHILE @currlim + binsize < @binmax DO INSERT INTO bins VALUES (@currlim, @currlim+binsize); SET @currlim = @currlim + binsize; END WHILE; INSERT INTO bins VALUES (@currlim, @maxbin); END; DROP TABLE IF EXISTS bins; # be careful if you have a bins table of your own. CREATE TEMPORARY TABLE bins ( minval INT, maxval INT, # or FLOAT, if needed KEY (minval), KEY (maxval) );# keys could perhaps help if using a lot of bins; normally negligible CALL makebins(20, NULL); # Using 20 bins of automatic size here. SELECT bins.*, count(*) AS total FROM bins LEFT JOIN yourtable ON yourtable.value BETWEEN bins.minval AND bins.maxval GROUP BY bins.minval 

Cela générera le nombre d'histogrammes uniquement pour les bacs remplis. David West devrait avoir raison dans sa correction, mais pour une raison quelconque, les bacs non peuplés n'apparaissent pas dans le résultat pour moi (malgré l'utilisation d'un LEFT JOIN - je ne comprends pas pourquoi).

Cela devrait fonctionner. Pas si élégant mais quand même:

 select count(mycol - (mycol mod 10)) as freq, mycol - (mycol mod 10) as label from mytable group by mycol - (mycol mod 10) order by mycol - (mycol mod 10) ASC 

via Mike DelGaudio

 select case when total >= 30 and total <= 40 THEN "30-40" else when total >= 40 and total <= 50 then "40-50" else "50-60" END as Total , count(total) group by Total 

En plus d’une excellente réponse https://stackoverflow.com/a/10363145/916682 , vous pouvez utiliser l’outil graphique phpmyadmin pour un résultat agréable:

entrer la description de l'image ici

entrer la description de l'image ici

Largeur égale dans un nombre donné de bacs:

 WITH bins AS( SELECT min(col) AS min_value , ((max(col)-min(col)) / 10.0) + 0.0000001 AS bin_width FROM cars ) SELECT tab.*, floor((col-bins.min_value) / bins.bin_width ) AS bin FROM tab, bins; 

Notez que le 0.0000001 est là pour vous assurer que les enregistrements avec la valeur égale à max (col) ne font pas leur propre casier tout seul. En outre, la constante additive est là pour garantir que la requête n’échoue pas lors de la division par zéro lorsque toutes les valeurs de la colonne sont identiques.

Notez également que le nombre de cases (10 dans l’exemple) doit être écrit avec une marque décimale pour éviter la division entière (la bin_width non ajustée peut être décimale).