Pobieranie danych do wykresu histogramu

Czy istnieje sposób na określenie rozmiarów pojemników w MySQL? W tej chwili próbuję wykonać następujące zapytanie SQL:

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

Generowane dane są wystarczająco dobre, ale jest po prostu zbyt wiele wierszy. Potrzebuję sposobu na pogrupowanie danych w predefiniowane pojemniki. Mogę to zrobić z poziomu języka skryptowego, ale czy istnieje sposób, aby to zrobić bezpośrednio w SQL?

Przykład:

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

Czego szukam:

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

Myślę, że nie można tego osiągnąć w prosty sposób, ale odniesienie do jakiejkolwiek powiązanej procedury składowanej również byłoby w porządku.

To jest post o super szybkim i brudnym sposobie tworzenia histogramu w MySQL dla wartości liczbowych.

Istnieje wiele innych sposobów tworzenia lepszych i bardziej elastycznych histogramów przy użyciu instrukcji CASE i innych typów złożonej logiki. Ta metoda zawsze mnie przekonuje, ponieważ jest tak łatwa do zmodyfikowania dla każdego przypadku użycia, a także tak krótka i zwięzła. Tak to się robi:

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

Po prostu zmień numeric_value na dowolną kolumnę, zmień przyrost zaokrąglenia i to wszystko. Słupki są ustawione w skali logarytmicznej, aby nie rosły zbytnio, gdy masz duże wartości.

Wartość_liczbowa powinna zostać przesunięta w operacji ZAOKRĄGLANIA na podstawie przyrostu zaokrąglania, aby mieć pewność, że pierwszy segment zawiera tyle elementów, ile kolejne.

np. z ROUND (numeric_value, -1), numeric_value w zakresie [0,4] (5 elementów) zostanie umieszczonych w pierwszym koszyku, a [5,14] (10 elementów) w drugim, [15,24] w trzecim, chyba że numeric_value jest odpowiednio przesunięty przez ROUND (numeric_value - 5, -1).

To jest przykład takiego zapytania na niektórych losowych danych, który wygląda całkiem nieźle. Wystarczająco dobre do szybkiej oceny danych.

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

Kilka uwag: Zakresy, które nie pasują, nie pojawią się w zliczeniu - w kolumnie zliczania nie będzie zera. Używam tutaj również funkcji ROUND. Możesz równie łatwo zastąpić go TRUNCATE, jeśli uważasz, że ma to dla Ciebie większy sens.

Znalazłem to tutaj http://blog.shlomoid.com/2011/08/how-to-quickly-create-histogram-in.html

Odpowiedź Mike'a DelGaudio brzmi tak, jak to robię, ale z niewielką zmianą:

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

Przewaga? Możesz zrobić tak duże lub tak małe pojemniki, jak chcesz. Kosze o rozmiarze 100? floor(mycol/100)*100. Pojemniki o rozmiarze 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

Kosze tabeli zawierają kolumny min_value i max_value, które definiują pojemniki. zwróć uwagę, że operator "join ... on x BETWEEN yiz" jest włącznie.

tabela1 to nazwa tabeli danych

Odpowiedź Ofri Raviv jest bardzo bliska, ale błędna. count(*)Będzie 1, nawet jeśli nie są zerowe wyniki w przedziale histogramu. Zapytanie należy zmodyfikować, aby użyło warunku 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;

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

O ile nie ma zbyt wielu interwałów, jest to całkiem dobre rozwiązanie.

Zrobiłem procedurę, za pomocą której można automatycznie wygenerować tymczasową tabelę dla pojemników według określonej liczby lub rozmiaru, do późniejszego wykorzystania z rozwiązaniem 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

Spowoduje to wygenerowanie liczby histogramów tylko dla zapełnionych pojemników. David West powinien mieć rację w swojej poprawce, ale z jakiegoś powodu w wyniku nie pojawiają się dla mnie puste kosze (pomimo użycia LEFT JOIN - nie rozumiem dlaczego).

To powinno działać. Nie tak eleganckie, ale nadal:

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

przez Mike DelGaudio

SELECT
    CASE
        WHEN total <= 30 THEN "0-30"
        WHEN total <= 40 THEN "31-40"       
        WHEN total <= 50 THEN "41-50"
        ELSE "50-"
    END as Total,
    count(*) as count
GROUP BY Total 
ORDER BY Total;

Kategoryzowanie równej szerokości do podanej liczby pojemników:

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;

Zwróć uwagę, że 0,0000001 jest po to, aby upewnić się, że rekordy o wartości równej max (col) nie tworzą własnego pojemnika samodzielnie. Ponadto stała addytywna zapewnia, że ​​zapytanie nie zakończy się niepowodzeniem przy dzieleniu przez zero, gdy wszystkie wartości w kolumnie są identyczne.

Należy również zauważyć, że liczba przedziałów (w tym przykładzie 10) powinna być zapisana ze znakiem dziesiętnym, aby uniknąć dzielenia liczb całkowitych (nieskorygowana szerokość_punktu może być dziesiętna).

Oprócz świetnej odpowiedzi https://stackoverflow.com/a/10363145/916682 , możesz użyć narzędzia wykresu phpmyadmin, aby uzyskać ładny wynik: