Search code, repositories, users, issues, pull requests...

The standard SQL provides five most commonly used aggregation operations: SUM/COUNT/AVG/MIN/MAX. Observing these operations, we find that they can all be seen as a function that returns a single value with a set as a parameter. Let's first understand this commonality as the definition of aggregation operation, which involves turning a set to a single value, that is to turn multiple values to one, resulting in aggregation. Therefore, it is called aggregation operation.

Then obviously, aggregation operations can be applied when there is a set, so operations like SUM/COUNT can be implemented on a data table (record set).

The result of grouping operation is a batch of grouped subsets, and aggregation operation can also be applied on each subset, which is the grouping operation of SQL. In fact, the aggregation operation on the entire set can also be understood as on a special group that is only divided into one group (which is also a complete partition). After this understanding, we can consider that the aggregation operation always occurs after the grouping operation (but there may not always be an aggregation operation after the grouping operation, as mentioned earlier). Moreover, it can also be said that as long as it is recognized as an aggregation operation (an operation that meets the above definition), it can definitely be used after grouping. We will see below that this understanding will greatly expand the application scope of grouping plus aggregation operations.

In addition to these five aggregation operations, some databases also provide aggregation functions such as variance and standard deviation, which have similar properties to these five operations and can be called conventional aggregation operations. Let's now study other forms of aggregation operations that are meaningful in business.

Leave a Comment