Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to use the grouping sorting of PostgreSQL aggregation functions". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Aggregate function

The function used for summarization.

COUNT

COUNT, which calculates the number of rows (records) in the table.

Calculate the number of rows of all data:

SELECT COUNT (*) FROM Product

Number of rows of data outside NULL:

SELECT COUNT (purchase_price) FROM Product

The result is as follows.

For a table with NULL:

Take the column name as a parameter to get the number of rows of data outside of NULL, and an asterisk as a parameter to get the number of rows of all data (including NULL).

SUM 、 AVG

The SUM and AVG functions can only be used on columns of numeric types.

SUM, find the sum of the data of the numeric columns in the table.

SELECT SUM (sale_price) FROM Product

The data in purchase_price includes NULL, the four operations contain NULL, and the result is also NULL, but the result is not NULL.

This is because the aggregate function takes the column name as an argument and excludes NULL data when calculating.

SELECT SUM (sale_price), SUM (purchase_price) FROM Product

AVG, find the average of the data of the numeric column in the table.

SELECT AVG (sale_price) FROM Product

If there is NULL in the data in the column, the NULL will be removed in advance and then calculated. For example, AVG (purchase_price), the denominator is 6 instead of 8.

SELECT AVG (sale_price), AVG (purchase_price) FROM Product

MAX 、 MIN

MAX, find the maximum value of any column data in the table.

MIN, find the minimum value of any column data in the table.

SELECT MAX (sale_price), MIN (purchase_price) FROM Product;SELECT MAX (regist_date), MIN (regist_date) FROM Product

Aggregate function + DISTINCT

Calculate the number of rows of data after deduplication:

The DISTINCT is written in parentheses so that it is deduplicated before counting the rows.

SELECT COUNT (DISTINCT product_type) FROM Product

DISTINCT can be used in the parameters of all aggregate functions.

The following SUM (DISTINCT sale_price), first de-duplicates the data in sale_price, and then summs it.

SELECT SUM (sale_price), SUM (DISTINCT sale_price) FROM Product

GROUP BY

Grouping tables: aggregate functions were used earlier to summarize all the data in the table.

You can also divide the table into several groups before summarizing it.

Format:

SELECT,... FROM GROUP BY,...

Statistics by type of commodity:

With GROUP BY product_type, the table is divided according to the type of goods.

The column specified by GROUP BY, called aggregate key, grouped column.

SELECT product_type, COUNT (*) FROM Product GROUP BY product_type

The table is divided according to the type of goods, and three groups of data are obtained, and then the number of rows of data for each kind of goods is calculated.

If the aggregate bond contains NULL, NULL is also used as a specific set of data.

SELECT purchase_price, COUNT (*) FROM Product GROUP BY purchase_price

If you add the WHERE clause, the format is as follows:

SELECT,... FROM WHEREGROUP BY,...

Filter based on the criteria specified in the WHERE clause, and then summarize the process.

The execution order of the following statements: FROM, WHERE, GROUP BY, SELECT.

SELECT purchase_price, COUNT (*) FROM Product WHERE product_type = 'clothes' GROUP BY purchase_price

Note when using aggregate functions and GROUP BY:

There are only three elements in the 1.SELECT clause: constants, aggregate functions, and column names (aggregate keys) specified in the GROPU BY clause.

When using the GROPU BY clause, column names other than aggregate keys cannot appear in the SELECT clause.

Aliases defined in the SELECT clause cannot be used in the 2.GROUP BY clause.

This is because the SQL statement executes the GROUP BY clause first and then the SELECT clause within the DBMS. When executing the GROUP BY clause, DBMS does not know what the alias represents, because the alias is defined in the SELECT clause.

The order in which the results of execution of the 3.GROUP BY clause are displayed is out of order.

4. Aggregate functions can only be used in SELECT clause, HAVING clause, and ORDER BY clause.

HAVING

Use the GROPU BY clause to get the result of grouping the tables.

Use the HAVING clause to specify the conditions of the grouping and select a specific group from the results of the grouping.

Format:

SELECT,... FROM WHEREGROUP BY,... HAVING

Below, select the group that contains two rows of data.

SELECT product_type, COUNT (*) FROM Product GROUP BY product_typeHAVING COUNT (*) = 2

In the following one, select the group whose average value is > = 2500.

SELECT product_type, AVG (sale_price) FROM Product GROUP BY product_typeHAVING AVG (sale_price) > = 2500

There are three elements that can be used in the HAVING clause: the constant, the aggregate function, and the column name specified in the GROPU BY clause (aggregate key).

Some conditions corresponding to aggregate bonds can be written in either the HAVING clause or the WHERE clause.

The results of the following two pieces of code are the same.

The HAVING clause is used to specify the conditions of the group. The WHERE clause is used to specify the condition of the data row. Some of the conditions corresponding to aggregate bonds are better written in the WHERE clause.

SELECT product_type, COUNT (*) FROM Product GROUP BY product_typeHAVING product_type = 'clothes'; SELECT product_type, COUNT (*) FROM ProductWHERE product_type = 'clothes' GROUP BY product_type

ORDER BY

Using the ORDER BY clause, you can sort query results.

Format:

SELECT,... FROM ORDER BY,...

The ORDER BY clause is written at the end of the SELECT statement.

The column name in the ORDER BY clause is called the sort key.

Use ascending sort, use the ASC keyword, omit this keyword, and the default is ascending sort.

SELECT product_id, product_name, sale_price, purchase_price FROM ProductORDER BY sale_price

The above is in ascending order. If you want to sort in descending order, use the DESC keyword.

SELECT product_id, product_name, sale_price, purchase_price FROM ProductORDER BY sale_price DESC

In the above sort, sale_price=500 has two pieces of data, and the order of the two data is random.

You can add another sort key to sort the two data.

The following is achieved, when the price is the same, sort according to the ascending order of the item number.

When there are multiple sort keys, the key on the left is preferred, the same value exists in the column, and then refer to the key on the right.

SELECT product_id, product_name, sale_price, purchase_price FROM ProductORDER BY sale_price, product_id

If there is data in the sort key, the NULL,NULL will be displayed at the beginning or end of the result.

SELECT product_id, product_name, sale_price, purchase_price FROM ProductORDER BY purchase_price

Aliases defined in the SELECT clause can be used in the ORDER BY clause.

This is determined by the order in which SQL statements are executed within DBMS. The execution order of SELECT clause is before ORDER BY and after GROPU BY.

FROM 、 WHERE 、 GROPU BY 、 HAVING 、 SELECT 、 ORDER BY

SELECT product_id AS id, product_name, sale_price AS sp, purchase_price FROM ProductORDER BY sp, id

The ORDER BY clause can be used in a table, but not in a column in the SELECT clause.

SELECT product_name, sale_price, purchase_price FROM ProductORDER BY product_id

Aggregate functions can be used in the ORDER BY clause.

SELECT product_type, COUNT (*) FROM Product GROUP BY product_typeORDER BY COUNT (*); "how to use grouping sorting for PostgreSQL aggregate functions" ends here. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.