mysql - guia - Obtener datos para la gráfica del histograma

qgis manual

Question

¿Hay alguna forma de especificar tamaños de contenedor en MySQL? En este momento, estoy intentando la siguiente consulta SQL:

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

Los datos que se generan son suficientes, pero hay demasiadas filas. Lo que necesito es una forma de agrupar los datos en contenedores predefinidos. Puedo hacer esto desde un lenguaje de scripts, pero ¿hay alguna manera de hacerlo directamente en SQL?

Ejemplo:

+-------+--------------+
| 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 | 
------------------------

Lo que estoy buscando:

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

Supongo que esto no se puede lograr de una manera directa, pero una referencia a cualquier procedimiento almacenado relacionado estaría bien también.

Answer 1

Esta es una publicación sobre una forma súper rápida y sucia de crear un histograma en MySQL para valores numéricos.

Hay muchas otras formas de crear histogramas que son mejores y más flexibles, utilizando sentencias CASE y otros tipos de lógica compleja. Este método me gana una y otra vez, ya que es muy fácil de modificar para cada caso de uso, y es breve y conciso. Así es como lo haces:

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

Simplemente cambie numeric_value a cualquiera que sea su columna, cambie el incremento de redondeo, y eso es todo. Hice que las barras estén en escala logarítmica, para que no crezcan demasiado cuando se tienen valores grandes.

numeric_value se debe compensar en la operación REDONDEO, en función del incremento de redondeo, para garantizar que el primer depósito contenga tantos elementos como los siguientes intervalos.

por ejemplo, con ROUND (numeric_value, -1), numeric_value en el rango [0,4] (5 elementos) se colocarán en el primer cubo, mientras que [5,14] (10 elementos) en el segundo, [15,24] en el tercero, a menos que numeric_value se desplace apropiadamente a través de ROUND (numeric_value - 5, -1).

Este es un ejemplo de dicha consulta en algunos datos aleatorios que se ven bastante dulces. Lo suficientemente bueno para una evaluación rápida de los datos.

+--------+----------+-----------------+
| 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 | *               |
+--------+----------+-----------------+

Algunas notas: los rangos que no coinciden no aparecerán en el recuento; no tendrá un cero en la columna de recuento. Además, estoy usando la función REDONDEAR aquí. Puede reemplazarlo fácilmente con TRUNCATE si cree que tiene más sentido para usted.

Lo encontré aquí http://blog.shlomoid.com/2011/08/how-to-quickly-create-histogram-in.html

Answer 2

Además de la excelente respuesta https://.com/a/10363145/916682 , puede usar la herramienta de gráficos phpmyadmin para obtener un buen resultado:

Answer 3

Compartimiento de ancho igual en un conteo de contenedores dado:

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;

Tenga en cuenta que el 0.0000001 está allí para asegurarse de que los registros con el valor igual a max (col) no se conviertan en su propio contenedor por sí mismo. Además, la constante aditiva está ahí para garantizar que la consulta no falle en la división por cero cuando todos los valores en la columna son idénticos.

También tenga en cuenta que el recuento de contenedores (10 en el ejemplo) debe escribirse con una marca decimal para evitar la división de enteros (el bin_width no ajustado puede ser decimal).

Answer 4

Eso debería funcionar. No es tan elegante, pero aún así:

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

a través de Mike DelGaudio

Answer 5

Hice un procedimiento que se puede usar para generar automáticamente una tabla temporal para contenedores de acuerdo con un número o tamaño específico, para su uso posterior con la solución de 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

Esto generará el recuento de histogramas solo para los contenedores que están llenos. David West debería tener razón en su corrección, pero por alguna razón, los contenedores no poblados no aparecen en el resultado para mí (a pesar del uso de un LEFT JOIN, no entiendo por qué).

Answer 6

La respuesta de Mike DelGaudio es la forma en que lo hago, pero con un ligero cambio:

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

¿La ventaja? Puede hacer que los contenedores sean lo más grandes o pequeños que desee. Cubos de tamaño 100? floor(mycol/100)*100 . Cubos de tamaño 5? floor(mycol/5)*5 .

Bernardo.

Answer 7

La respuesta de Ofri Raviv es muy cercana pero incorrecta. El count(*) será 1 incluso si hay cero resultados en un intervalo de histograma. La consulta debe modificarse para usar una sum condicional:

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 8

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

Los contenedores de tabla contienen columnas min_value y max_value que definen los contenedores. tenga en cuenta que el operador "join ... on x BETWEEN y y z" es inclusivo.

table1 es el nombre de la tabla de datos

Answer 9

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 ....

Mientras no haya demasiados intervalos, esta es una solución bastante buena.

Answer 10

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