“This is the 15th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

Aggregation function

In general, we store single data records in DB tables, but sometimes, we need to aggregate these data for analysis (other bars can be done in the background, so the performance will be poor).

By definition, an aggregate function performs a calculation on a set of values and returns a single value

Mysql provides many aggregation functions, such as AVg, count, sum, Max, and min. All aggregation functions except count ignore NULL

If necessary, we can use the WHERE condition to filter the data before using the aggregate function

avg

Avg is used to calculate the average of a set of data, using the existing table data as an example:

We count the average amount of amount

As we can see from the figure, many records with amount = 1 cause the average to drop, so we ignore the records with amount = 1 and see what the average of the rest of the data is

It can be seen from this that avG operates on the result filtered by the WHERE condition when calculating the average value

count

Count returns the function in the table. The usual uses are count(*) and count(1).

In the same test, count is aggregated on the filtered results of WHERE

Here’s a look at some of the information:

Based on InnoDB engine

Count (*), count(primary key ID), and count(1) all indicate the total number of rows that return a result set that meets the criteria

Count (field) : indicates the total number of data rows in which the parameter field is not NULL.

Count (field)<count(primary key ID)<count(1)≈count(*)

sum

Represents the sum over the data of a field. We usually use this to sum over numeric data

max

Select the set of records with the largest field value

In regular table design, we use auto-increment primary keys. We can locate the latest data record of the current table in the following way

select max(id) from table
Copy the code

min

Select the set of records with the smallest field value

In their own use, aggregate functions return the result of a single numeric type

However, we can cooperate with group query and joint table query to play a more powerful power, for example, count + group by directly locate repeated records, these knowledge points will be made up in the subsequent article