Raw data is usually bulky, so we like to aggregate it into smaller chunks to make it easier to digest, but there are a lot of subtle problems with aggregating data. We like to aggregate data in such a way that when we put the small chunks back together, we get the original set. In mathematics, this is called a partitioning of a set. When you can do it; it\u2019s very, very, nice. But it\u2019s not always possible.<\/p>\n
Your first thought is that you\u2019re going to partition it by using some kind of The puzzle asks the false question; \u201cwhere did the leprechaun go?\u201d The problem was that the \u201cleprechaun\u201d was not well-defined. If we were to talk about using the same flour, eggs, butter and sugar to bake a dozen cookies or to make a small cake, you would not look at the cake and ask where the individual cookies are inside the cake, nor where the cake is inside the individual cookies. You would realize the two options with the original materials were aggregated differently. The cake is simply different from the cookies, and vice versa.<\/p>\n As an example of a well-defined aggregation, consider the children\u2019s game Cootie (for an explanation, see the Wikipedia page<\/a>). The object of the game is to be the first player to build a “cootie” piece by piece from various plastic<\/a> body parts that include a beehive-like body, a head, antennae, eyes, a coiled proboscis, and six legs. Body parts are acquired by rolling a die. The winner is the first player to completely assemble a cootie. The number of each body part required to make a valid cootie is very well-defined; if he\u2019s missing a part, he\u2019s not a cootie.<\/p>\n The moral to the story is that the set of aggregations is not always the same as the original set.<\/p>\n In set theory, we have equivalence classes and equivalence relations. They partition the set based on some relationship that is well-defined. The two that come to mind in SQL are equality and grouping. The difference is that simple equality (=) doesn\u2019t work with NULLs, but only known values. We also have the Grouping (as in the By using the We could depict the median, or a whole bunch of other measures of central tendency whose names involve a famous statistician, or Greek letter. They have a common characteristic, however; they depend on numeric values upon which we can do computations. In short, they\u2019re not very good for discrete or descriptive values. In fact, they don\u2019t make sense for such things. What is the average of the colors of shoes when you group by a city? If both yellow and red are equally represented, is the average color orange? The question is absurd. This is a classic issue of continuous versus discrete variables when you are doing statistics. Did you notice that the value returned does not have to be a member of the set from which it was created? That\u2019s important. It means that it\u2019s perfectly logical for the families in the population to have an average of 2.3 children. But in practice, it\u2019s very embarrassing if the police find you with a 0.3 child in your house.<\/p>\n SQL did give you some aggregate functions that apply to discrete variables. The extrema functions ( A major problem with the mode, and other discrete statistical functions, is that they behave differently for the whole population than in subsets of the population. The best-known example of this is Simpson\u2019s paradox (see my article: Data is crazier than you think<\/a>).<\/p>\n Simpson\u2019s paradox has nothing to do with Homer Simpson or O. J. Simpson; it is named after the British statistician Edward Hugh Simpson who wrote about it in the 1950s. Simpson\u2019s paradox happens when a trend that appears in separate groups of data disappears when these groups are combined, and the reverse trend appears for the aggregate data. It tends to happen when you partition your data into subsets where each of the subsets is a little skewed and of odd sizes, and illustrates the dangers of errors in population sampling.<\/p>\nGROUP BY<\/code> clause. But back up a step. Many years ago there was a popular puzzle that originally appeared in a Martin Gardner Scientific American column called The Vanishing Leprechaun (See the Youtube demonstration here<\/a>). The puzzle consists of three parts, like a jigsaw puzzle, which can be rearranged one of two ways. Depending how the pieces are arranged, one of the leprechauns seems to disappear.<\/p>\nEquivalence Classes and Relations<\/h2>\n
mod (n)<\/code> function, which creates (n)<\/code> equivalence classes; the simplest example is that of breaking integers into odd and even classes.<\/p>\n GROUP BY<\/code> clause, PARTITION<\/code> and some other places in SQL) treats NULL<\/code>s as falling in the same equivalence class.<\/p>\nAVG()<\/code> and SUM()<\/code> math functions with GROUP BY<\/code>, you reduce a group attribute to a value. These are the ones we picked in the early days of SQL, because they were very easy and very well understood. The problem is that they depend on the numeric values that we can compute.<\/p>\nMIN ()<\/code> and MAX ()<\/code>) extract a single value from the class. These values have be attributes of at least one element in that set. The obvious discrete statistical function that is missing is the MODE(),<\/code> or most frequently occurring value in the set. It\u2019s a terrible measure of central tendency in the set and there can be several of them in the case of ties, but it has the advantage of representing majority rule voting when it is unique.<\/p>\nGerrymandering<\/h2>\n