Hay una manera de especificar los tamaños de bin de MySQL? Ahora, estoy tratando de la siguiente consulta SQL:

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

Los datos que se genera no es lo suficientemente bueno, pero hay demasiadas filas. Lo que necesito es una forma de agrupar los datos en predefinida papeleras. Puedo hacer esto desde un lenguaje de secuencias de comandos, pero hay una 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 en una recta hacia adelante de forma, pero una referencia a cualquier procedimiento almacenado estaría bien así.

  • no estoy exactamente seguro de lo que están pidiendo. ejemplo de salida podría ser de ayuda.
  • Lo siento! Acaba de actualizar mi post con un ejemplo.
InformationsquelleAutor Legend | 2009-11-19

10 Comentarios

  1. 142

    Este es un post sobre una super rápido y sucio para crear un histograma
    en MySQL para valores numéricos.

    Hay varias otras maneras de crear histogramas que son mejores y
    más flexible, utilizando el CASO de declaraciones y otros tipos de lógica compleja.
    Este método me gana con el tiempo y la hora de nuevo, ya que es tan fácil
    para modificar para cada caso de uso, y de modo breve y conciso. Esta es la forma en que
    hacerlo:

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

    Acaba de cambiar numeric_value a lo que su columna es, cambiar el
    redondeo de incremento, y eso es todo. He hecho los bares para estar en
    escala logarítmica, por lo que no crecen demasiado cuando usted tiene
    grandes valores.

    numeric_value debe ser compensado en el Redondeo de la operación, basado en el incremento de redondeo, en orden a asegurar el primer cubo contiene tantos elementos como los siguientes cubos.

    por ejemplo, con la RONDA(numeric_value,-1), numeric_value en el intervalo [0,4] (5 elementos) se coloca en primer cubo, mientras que [5,14] (10 elementos) en segundo lugar, [15,24] en tercer lugar, a menos que numeric_value se compensa adecuadamente a través de RONDA(numeric_value – 5, -1).

    Este es un ejemplo de este tipo de consulta sobre algunos datos aleatorios que se ve bastante
    dulce. Lo suficientemente bueno para hacer 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: Rangos que no tienen partido no aparecerá en la cuenta
    usted no tendrá un cero en la columna de recuento. También, estoy usando el
    La función ROUND aquí. Usted puede apenas como fácilmente reemplazarlo con TRUNCATE
    si usted siente que tiene más sentido para usted.

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

  2. 26

    Mike DelGaudio la respuesta es la manera 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? Usted puede hacer que los contenedores que tan grande o tan pequeño como usted desea. Bandejas de tamaño 100? floor(mycol/100)*100. Bandejas de tamaño 5? floor(mycol/5)*5.

    Bernardo.

    • grupo 1 de la orden de 1 de
    • como carillonator dijo que su grupo & orden de mejor debe ser bin_floor o 1 – Enfermo upvote si correcto, esta es la mejor respuesta para mí
    • Justo lo suficiente, @b-m. Cambiado según lo sugerido por carillonator.
    • Bandejas con count == 0 no se muestran.
    • y si quieres un mejor nombre de la columna que usted puede hacer concat(floor(mycol/5)*5," to ",floor(mycol/5)*5+5)
  3. 16
    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 tabla de contenedores contiene columnas min_value y max_value que definen las papeleras.
    tenga en cuenta que el operador «unirse a… en x ENTRE y y z» es inclusivo.

    tabla1 es el nombre de la tabla de datos

    • ¿Por qué es el coloreado de la sintaxis de SQL es tan mala? ¿Cómo puedo mejorar esto? Tal vez debería publicar en meta 😉
    • Raviv Sí, usted debe!
    • En este caso es necesaria una plantilla de tabla para definir min un máx. Sólo con SQL no es posible.
    • SQL Gurú! Exactamente lo que yo quería. Supongo que debe tener cuidado a la hora de crear los cubos de basura de la tabla. De lo contrario, todo funciona a la perfección. 🙂 Gracias. Acabo de terminar de escribir una secuencia de comandos de python pero esto es justo lo que necesitaba…
    • En realidad, soy bastante n00b cuando se trata de SQL. pero este era un interesante y útil a la pregunta, así que me gustó el ejercicio…
    • Gracias… además, también voy a publicar un directo de la secuencia de comandos de python que utiliza su consulta sql para generar los datos para decir, gnuplot
    • Es importante ver a @David West respuesta (que debería haber sido un comentario, aquí) acerca de cómo el COUNT(*) produce 1 cuando cuando se debe producir cero. Que puede no ser un gran problema para usted, pero puede sesgar los datos estadísticos y hacer que se vea un poco tonto si alguien se da cuenta 🙂

  4. 11

    Ofri Raviv la respuesta es muy cerca, pero incorrecta. El count(*) será 1 incluso si hay cero resultados en un histograma de intervalo. La consulta tiene que ser modificado para usar un condicional 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;
  5. 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 hay demasiados intervalos, esta es una solución bastante buena.

    • +1 Esta es la única solución que permite a los contenedores sean de diferente tamaño
    • bien sin necesidad de tablas adicionales
  6. 3

    Hice un procedimiento que puede ser utilizado para generar automáticamente una tabla temporal para contenedores de acuerdo a un determinado número o tamaño, para su uso posterior con Ofri Raviv la solución.

    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

    Que esto va a generar el histograma contar sólo para los recipientes que se llenan. David West debe ser justo en su corrección, pero por alguna razón, despoblada de contenedores no aparecen en el resultado para mí (a pesar de que el uso de un LEFT JOIN — no entiendo por qué).

  7. 3

    Que deben trabajar. No 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

  8. 2
    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 
  9. 0

    Igual anchura binning, en un recuento de las papeleras:

    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á ahí para asegurarse de que los registros con el valor igual a max(col) no la hace el propio bin por sí mismo. También, la constante aditiva está ahí para asegurarse de que la consulta no fallar en la división por cero cuando todos los valores de la columna son idénticos.

    También tenga en cuenta que el recuento de las bandejas (10 en el ejemplo) debe ser escrito con un separador decimal para evitar la división de enteros (sin corregir la bin_width puede ser decimal).

Dejar respuesta

Please enter your comment!
Please enter your name here