Pobieranie danych do wykresu histogramu
Czy jest sposób na określenie wielkości bin w MySQL? W tej chwili próbuję wykonać następujące zapytanie SQL:
select total, count(total) from faults GROUP BY total;
Dane, które są generowane, są wystarczająco dobre, ale jest zbyt wiele wierszy. Potrzebuję sposobu na pogrupowanie danych w predefiniowane pojemniki. Mogę to zrobić z języka skryptowego, ale czy istnieje sposób, aby zrobić to 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 w porządku byłoby również odniesienie do każdej powiązanej procedury składowanej.
10 answers
To jest post o super szybkim i brudnym sposobie tworzenia histogramu w MySQL dla wartości liczbowych.
Istnieje wiele innych sposobów tworzenia histogramów, które są lepsze i bardziej elastyczny, przy użyciu instrukcji CASE i innych typów złożonej logiki. Ta metoda wygrywa mnie raz po raz, ponieważ jest to po prostu tak proste aby zmodyfikować każdy przypadek użycia, i tak krótki i zwięzły. This is how you zrób to:
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 cokolwiek kolumna jest, zmień zaokrąglanie przyrostu i tyle. Zrobiłem kraty, by być w skali logarytmicznej, aby nie rosły zbytnio, gdy masz duże wartości.
Numeric_value powinno być przesunięte w operacji zaokrąglania, na podstawie przyrostu zaokrąglania, aby upewnić się, że pierwsze wiadro zawiera tyle elementów,ile kolejnych wiaderek.
Np. z ROUND(numeric_value, -1), numeric_value w zakresie [0,4] (5 elementów) zostanie umieszczony w pierwszym wiadrze, podczas gdy [5,14] (10 elementów) w drugim, [15,24] w trzecim, chyba że numeric_value jest przesunięty odpowiednio przez okrągły (numeric_value - 5, -1).
To jest przykład takiego zapytania na niektórych losowych danych, które wyglądają ładnie słodko. 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 | * | +--------+----------+-----------------+
Niektóre uwagi: zakresy, które nie mają dopasowania, nie pojawią się w liczniku - nie będziesz mieć zera w kolumnie count. Ponadto, używam Funkcja okrągła tutaj. Równie łatwo można go zastąpić TRUNCATE jeśli uważasz, że to ma dla Ciebie więcej sensu.
Znalazłem go tutaj http://blog.shlomoid.com/2011/08/how-to-quickly-create-histogram-in.html
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-12-03 16:53:27
Odpowiedź Mike ' a DelGaudio jest taka jak ja, ale z drobną zmianą:
select floor(mycol/10)*10 as bin_floor, count(*)
from mytable
group by 1
order by 1
Przewaga? Możesz zrobić pojemniki tak duże lub tak małe, jak chcesz. Kosze wielkości 100? floor(mycol/100)*100
. Kosze wielkości 5? floor(mycol/5)*5
.
Bernardo.Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-06-03 14:57:37
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
Pojemniki tabeli zawierają kolumny min_value i max_value, które definiują pojemniki. zauważ, że operator " join... na X między y i z " JEST WŁĄCZNIE.
Table1 jest nazwą tabeli danych
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-11-19 17:38:30
Odpowiedź Ofri Raviva jest bardzo bliska, ale błędna. {[1] } będzie 1
, nawet jeśli nie ma żadnych wyników w przedziale histogramu. Zapytanie musi zostać zmodyfikowane, aby użyć warunkowego 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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-03-01 22:14:11
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 ....
Dopóki nie ma zbyt wielu interwałów, jest to całkiem dobre rozwiązanie.Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-12-10 23:04:53
Wykonałem procedurę, która może być wykorzystana do automatycznego generowania tabeli tymczasowej dla pojemników według określonej liczby lub wielkości, 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 wypełnionych pojemników. David West powinien mieć rację w swojej poprawce, ale z jakiegoś powodu nie pojawiają się w wyniku dla mnie (mimo użycia lewego łącznika - nie rozumiem dlaczego).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-08-10 15:40:10
To powinno zadziałać. Nie tak elegancko ale i tak:
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-02-17 14:36:46
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-03-10 06:12:51
Oprócz świetnej odpowiedzi https://stackoverflow.com/a/10363145/916682 , możesz użyć narzędzia do Wykresów phpmyadmin, aby uzyskać ładny wynik:
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:18:01
Równa szerokość binning do danej 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;
Zauważ, że 0.0000001 jest tam, aby upewnić się, że rekordy z wartością równą max (col) nie tworzą własnego bin tylko sam. Ponadto stała addytywna jest tam, aby upewnić się, że zapytanie nie zawiedzie przy podziale przez zero, gdy wszystkie wartości w kolumnie są identyczne.
Zauważ również, że liczba koszy (10 W przykładzie) powinna być zapisana ze znakiem dziesiętnym, aby uniknąć podziału liczby całkowitej (niezrównana bin_width może być dziesiętne).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-11-18 14:26:55