Getting data for histogram plot

by

This is a post about a super quick-and-dirty way to create a histogram
in MySQL for numeric values.

There are multiple other ways to create histograms that are better and
more flexible, using CASE statements and other types of complex logic.
This method wins me over time and time again since it’s just so easy
to modify for each use case, and so short and concise. This is how you
do it:

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

Just change numeric_value to whatever your column is, change the
rounding increment, and that’s it. I’ve made the bars to be in
logarithmic scale, so that they don’t grow too much when you have
large values.

numeric_value should be offset in the ROUNDing operation, based on the rounding increment, in order to ensure the first bucket contains as many elements as the following buckets.

e.g. with ROUND(numeric_value,-1), numeric_value in range [0,4] (5 elements) will be placed in first bucket, while [5,14] (10 elements) in second, [15,24] in third, unless numeric_value is offset appropriately via ROUND(numeric_value – 5, -1).

This is an example of such query on some random data that looks pretty
sweet. Good enough for a quick evaluation of the data.

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

Some notes: Ranges that have no match will not appear in the count –
you will not have a zero in the count column. Also, I’m using the
ROUND function here. You can just as easily replace it with TRUNCATE
if you feel it makes more sense to you.

I found it here http://blog.shlomoid.com/2011/08/how-to-quickly-create-histogram-in.html

More Related Contents:

  1. i have an error which i dont understand. can you help me? [closed]
  2. How do you use the “WITH” clause in MySQL?
  3. mysql error 1364 Field doesn’t have a default values
  4. SQL: What is the default Order By of queries?
  5. How to group by week in MySQL?
  6. Can I use a function for a default value in MySql?
  7. Access Denied for User ‘root’@’localhost’ (using password: YES) – No Privileges?
  8. Mysql order by specific ID values
  9. How do I check if a column is empty or null in MySQL?
  10. MySQL versus PDO [duplicate]
  11. Mysql string split [duplicate]
  12. SELECT * WHERE NOT EXISTS
  13. Increment a database field by 1
  14. Measuring actual MySQL query time
  15. Am I Using JDBC Connection Pooling?
  16. MySQL VARCHAR Lengths and UTF-8
  17. Row number per group in mysql
  18. Find and replace entire MySQL database
  19. Resetting ROOT password in MySQL 5.6
  20. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  21. Field ‘id’ doesn’t have a default value?
  22. How do I refer to capture groups in a MySQL regex?
  23. What is the default MySQL JOIN behaviour, INNER or OUTER?
  24. How to get the mysql table columns data type?
  25. how to use a like with a join in sql?
  26. Escaping the colon character ‘:’ in JPA queries
  27. How to export / dump a MySql table into a text file including the field names (aka headers or column names)
  28. How can I stop a MySQL query if it takes too long?
  29. “General error: 1005 Can’t create table” Using Laravel Schema Build and Foreign Keys
  30. django cannot connect mysql in docker-compose

Leave a Comment