Shulou(Shulou.com)06/01 Report--

SQL aggregation function is commonly used in what, I believe that many inexperienced people do not know what to do, for this reason this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

1）COUNT

Syntax: COUNT(e1)

Parameter: e1 is an expression and can be any data type.

Return: Returns numerical data

Function: Returns the total number of records whose specified column e1 is not empty

2）SUM,

Syntax: SUM(e1)

Parameter: e1 is an expression whose type is numeric

Return: Returns numerical data

Function: Sum the columns specified by e1

3）MIN, MAX

Syntax: MIN(e1), MAX(e1)

Parameter: e1 is an expression of character, date or numeric type.

If e1 is a character type, the maximum and minimum values are determined according to ASCII codes.

Return: Returns the data of the corresponding type according to the type of e1 parameter.

MIN(e1) Returns the smallest value in the column specified by the e1 expression;

MAX(e1) Returns the largest value in the column specified by the e1 expression;

4）AVG

Syntax: AVG(e1)

Parameter: e1 is an expression of numeric type

Return: Returns a numeric data type

Effect: Average the columns specified by the e1 expression.

5）MEDIAN

Syntax: MEDIAN(e1)

Parameter: e1 is an expression of numeric or date type

Return: Returns data of a numeric or date type

Action: First, sort the values according to the columns specified in the e1 expression;

If the total number of records is odd after sorting, the value in the middle of the sorting queue is returned;

If the total number of records is even after sorting, average the middle two values in the sorting queue and return the average value;

6）RANK

1) Usage 1:RANK OVER

Syntax: RANK( ) OVER ([ PARTITION BY column1 ] ORDER BY column2 [ASC| DESC])

For the parse function, a sequence number is generated for each record and returned.

Parameter: column1 is the column name, specifying which column to classify (grouping)

column2 is the column name, specifying which column to sort by, default is ascending;

Sort each class if a classification clause (PARTITION BY) is specified (sort each class separately)

Return: Returns a numeric data type as the serial number of the record!

Function: For analysis function, classify records by column1, sort each category, and assign a serial number to each record (sort each category separately)

Note: Records with the same sorting field value are assigned the same sequence number. There are cases where serial numbers are discontinuous

Example: student table records the scores of students in each subject, which is required to be sorted by subject, and obtains the records of the top two scores in each subject.

The student table is as follows:

SQL> select * from student order by kemu;

NAME ID KEMU FENSHU

---------- -------------- -------------- ----------------

Li 0113101 Physics 80

Luo 0113011 Physics 80

Wang 0113077 Physics 70

Zhang 0113098 Physics 90

Luo 0113011 High 80

Wang 0113077 High 70

Zhang 0113098 High 80

Li 0113101 High Number 90

8 rows selected

Classified by discipline, sorted by grade (descending order)

SQL> select rank() over(partition by KEMU order by FENSHU desc) as sort,student.* from student;

SORT NAME ID KEMU FENSHU

---------- ---------- ---------------- ------------ ----------

1 Zhang 0113098 Physics 90

2 Li 0113101 Physics 80

2 Luo 0113011 Physics 80

4 Wang 0113077 Physics 70

1 Li 0113101 High Number 90

2 Luo 0113011 High 80

2 Zhang 0113098 High 80

4 Wang 0113077 High 70

From the returned records, you can see that for records with the same value in the sort column, rank assigns the same sequence number (SORT NAME column).

and the sequence numbers of subsequent records are discontinuous.

If you get the top two in each subject, just add two queries to the sorted results.

select * from

(select rank() over(partition by KEMU order by FENSHU desc) as sort_id,student.* from student) st

where st.sort_id select * from employees;

EMP_ID EMP_NAME SALARY

---------- -------------------- ---------------

10001 ZhangSan 500

10002 LiSi 1000

10003 WangWu 1500

10004 MaLiu 2000

10005 NiuQi 2500

SQL> select rank(1500) within group (order by salary) as "rank number" from employees;

rank number

-----------

3

From the results, it can be seen that the employees whose salary is 1500 are sorted in ascending order in the table, and the serial number is 3.

7）FIRST、LAST

Language: agg_function (e1) KEEP (DENSE_RANK FIRST ORDER BY e2 [NULLS {FIRST| LAST}]) [OVER PARTITION BY e3 ]

agg_function（e1） KEEP (DENSE_RANK LAST ORDER BY e2 [NULLS {FIRST|LAST}]) [OVER PARTITION BY e3 ]

Parameter: agg_function is an aggregate function, which can be MIN, MAX, SUM, AVG, COUNT, VARIANCE or STDDEV

e2 specifies which field to sort by;

e3 Specify which field is used as the basis for classification (grouping);

When the OVER PARTITION BY clause is specified, sort separately for each classified class;

DENSE_RANK assigns serial numbers to sorted records, and serial numbers are sequential.

NULLS {FIRST| LAST} Specifies that if the value of the sorting field e1 is empty, the sequence is preceded (NULLS FIRST) or followed (NULLS LAST)

FIRST/LAST after DENSE_RANK determines to select the record with the smallest/largest serial number in the sequence sorted by DENSE_RANK. When the serial number is the same, multiple records are returned.

When multiple records are returned with the same sequence number, agg_function(e1) aggregation continues to aggregate the e1 fields of these multiple records.

Function: If agg_function is min(e1), obtain the minimum value of a field e1 among multiple records of FIRST or LAST after sorting.

This field is not a sort key field e2

Examples:

It is known that the employee table has a salary field and a bonus field. Ask for records of employees with the highest bonuses among the lowest paid employees.

The known table reads as follows:

SQL> select * from employees order by salary;

EMP_ID EMP_NAME SALARY COMMISSION

---------- ---------------------------- ------------ ------------

10001 ZhangSan 500 200

10002 LiSi 500 300

10003 WangWu 500 100

10004 MaLiu 2000 500

10005 NiuQi 2500 200

10006 ShangDuo 2500 300

10007 BaiQi 2500 400

SQL> select max(commission) keep(dense_rank first order by salary asc) as commission from employees;

COMMISSION

----------

300

First, after sorting by salary, obtain the records with the lowest salary, which are employee 10001, 10002 and 10003 respectively.

The aggregation function max(commission) gets the highest bonus for employee 10002 for 3 records, and the bonus is 300.

After reading the above, do you know what the common aggregation functions in SQL are? If you still want to learn more skills or want to know more related content, welcome to pay attention to the industry information channel, thank you for reading!

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.