Recipe 8.12. Categorizing Noncategorical Data
ProblemYou need to summarize a set of values that are not naturally categorical. SolutionUse an expression to group the values into categories. DiscussionSection 8.11 showed how to group rows by expression results. One important application for doing so is to provide categories for values that are not particularly categorical. This is useful because GROUP BY works best for columns with repetitive values. For example, you might attempt to perform a population analysis by grouping rows in the states table using values in the pop column. As it happens, that does not work very well due to the high number of distinct values in the column. In fact, they're all distinct, as the following query shows: mysql> SELECT COUNT(pop), COUNT(DISTINCT pop) FROM states; +------------+---------------------+ | COUNT(pop) | COUNT(DISTINCT pop) | +------------+---------------------+ | 50 | 50 | +------------+---------------------+ In situations like this, where values do not group nicely into a small number of sets, you can use a transformation that forces them into categories. Begin by determining the range of population values: mysql> SELECT MIN(pop), MAX(pop) FROM states; +----------+----------+ | MIN(pop) | MAX(pop) | +----------+----------+ | 506529 | 35893799 | +----------+----------+ You can see from that result that if you divide the pop values by five million, they'll group into six categoriesa reasonable number. (The category ranges will be 1 to 5,000,000, 5,000,001 to 10,000,000, and so forth.) To put each population value in the proper category, divide by five million, and use the integer result: mysql> SELECT FLOOR(pop/5000000) AS 'max population (millions)', -> COUNT(*) AS 'number of states' -> FROM states GROUP BY 'max population (millions)'; +---------------------------+------------------+ | max population (millions) | number of states | +---------------------------+------------------+ | 0 | 29 | | 1 | 13 | | 2 | 4 | | 3 | 2 | | 4 | 1 | | 7 | 1 | +---------------------------+------------------+ Hmm. That's not quite right. The expression groups the population values into a small number of categories, all right, but doesn't report the category values properly. Let's try multiplying the FLOOR( ) results by five: mysql> SELECT FLOOR(pop/5000000)*5 AS 'max population (millions)', -> COUNT(*) AS 'number of states' -> FROM states GROUP BY 'max population (millions)'; +---------------------------+------------------+ | max population (millions) | number of states | +---------------------------+------------------+ | 0 | 29 | | 5 | 13 | | 10 | 4 | | 15 | 2 | | 20 | 1 | | 35 | 1 | +---------------------------+------------------+ That still isn't correct. The maximum state population was 35,893,799, which should go into a category for 40 million, not one for 35 million. The problem here is that the category-generating expression groups values toward the lower bound of each category. To group values toward the upper bound instead, use the following technique. For categories of size n, you can place a value x into the proper category using this expression: FLOOR((x+(n-1))/n) So the final form of our query looks like this: mysql> SELECT FLOOR((pop+4999999)/5000000)*5 AS 'max population (millions)', -> COUNT(*) AS 'number of states' -> FROM states GROUP BY 'max population (millions)'; +---------------------------+------------------+ | max population (millions) | number of states | +---------------------------+------------------+ | 5 | 29 | | 10 | 13 | | 15 | 4 | | 20 | 2 | | 25 | 1 | | 40 | 1 | +---------------------------+------------------+ The result shows clearly that the majority of U.S. states have a population of five million or less. This technique works for all kinds of numeric values. For example, you can group mail table rows into categories of 100,000 bytes as follows: mysql> SELECT FLOOR((size+99999)/100000) AS 'size (100KB)', -> COUNT(*) AS 'number of messages' -> FROM mail GROUP BY 'size (100KB)'; +--------------+--------------------+ | size (100KB) | number of messages | +--------------+--------------------+ | 1 | 13 | | 2 | 1 | | 10 | 1 | | 24 | 1 | +--------------+--------------------+ In some instances, it may be more appropriate to categorize groups on a logarithmic scale. For example, the state population values can be treated that way as follows: mysql> SELECT FLOOR(LOG10(pop)) AS 'log10(population)', -> COUNT(*) AS 'number of states' -> FROM states GROUP BY 'log10(population)'; +-------------------+------------------+ | log10(population) | number of states | +-------------------+------------------+ | 5 | 7 | | 6 | 35 | | 7 | 8 | +-------------------+------------------+ The query shows the number of states that have populations measured in hundreds of thousands, millions, and tens of millions, respectively.
|
