Function topics: Sum, row_number, Count, rank \ dense_rank over

Source: http://blog.csdn.net/bbliutao/article/details/7727320

1. sum over

Sum over is used to accumulate the values of a field step by step.

Select name, salary, sum (salary) over (order by salary, name) subsal
From EMP
Order by salary;

Name, salary, age
A, 900, 20
B, 900, 21
C, 1000, 18
D, 950, 20

Format after execution

Name, salary, subsal
A, 900,900
B, 900,180 0
D, 950,275 0
C, 1000,375 0

Sum (SAL) over (partition by deptno) -- continuously sums the salaries of employees in different departments.
Partition by is used as a group.

Ii. row_number over
Row_number () over ([partition by col1] order by col2) as Alias
Indicates grouping by col1 and sorting by col2 within the group
The value of this "alias" indicates the sequential number after sorting in each group (the sequential and unique in the group). [partition by col1] can be saved.
Omitted

Row_number () over is grouped by partion by, and then sorted by open_date in each group.
Is the sequential number of the internal sorting

Select T. family_id,
T. package_id,
T. Phone,
T. Reason,
T. open_date,
Row_number ()
Over (partition by family_id, package_id, phone
Order by open_date DESC)
Rn
From zj_transaction_log t
Where to_char (T. open_date, 'yyyy-mm')> = '2017-03 ';

Family_id package_id phone reason open_date Rn
19811 100 ********* 861 ADC request [-lite version] restoration processing 14:16:29 1
19811 100 ********* 861 ADC request [-lite version] Suspension of processing 3:00:26 2
19823 100 ******** 667 ADC request [hardcoded version] Suspension of processing 9:47:41 1
19823 100 ********* 667 ADC request [-lite version] restoration processing 19:03:30 2
19823 100 ********* 667 ADC request [-lite version] Suspension of processing 6:51:27 3
19841 100 ******** 328 ADC request [Lite version] restoration processing 9:01:31 1
19841 100 ******** 328 ADC request [hardcoded version] Suspension of processing 0:57:33 2
19869 100 ********* 172 ADC request [-lite version] Suspension of processing 2:55:17 1
19950 100 ******** 631 ADC request [-lite version] restoration processing 14:14:51 1

Select T. family_id,
T. package_id,
T. Phone,
T. Reason,
T. open_date,
Row_number ()
Over (partition by family_id, package_id, phone
Order by package_id DESC)
Rn
From zj_transaction_log t
Where to_char (T. open_date, 'yyyy-mm')> = '2017-03 ';

Family_id package_id phone reason open_date Rn
19811 100 ********* 861 ADC request [-lite version] Suspension of processing 3:00:26 1
19811 100 ********* 861 ADC request [-lite version] restoration processing 14:16:29 2
19823 100 ******** 667 ADC request [hardcoded version] Suspension of processing 9:47:41 1
19823 100 ********* 667 ADC request [-lite version] restoration processing 19:03:30 2
19823 100 ********* 667 ADC request [-lite version] Suspension of processing 6:51:27 3
19841 100 ******** 328 ADC request [hardcoded version] Suspension of processing 0:57:33 1
19841 100 ******** 328 ADC request [Lite version] restoration processing 9:01:31 2
19869 100 ********* 172 ADC request [-lite version] Suspension of processing 2:55:17 1
19950 100 ******** 631 ADC request [-lite version] restoration processing 14:14:51 1

Iii. Count over

Select T. family_id,
T. package_id,
T. Phone,
T. open_date,
T. Reason,
Count (*)
Over (partition by family_id, package_id, phone)
Rn
From zj_transaction_log t
Where to_char (T. open_date, 'yyyy-mm')> = '2017-03 ';

Family_id package_id phone open_date reason Rn
19811 100 ********* 861 2011-3-6 3:00:26 ADC request [-lite version] suspend processing 2
19811 100 ******** 861 2011-3-6 14:16:29 ADC request [-lite version] restoration 2
19823 100 ******** 667 2011-3-12 9:47:41 ADC request [Lite version] suspend processing 3
19823 100 ********* 667 2011-3-7 19:03:30 ADC request [-lite version] restoration 3
19823 100 ********* 667 2011-3-7 6:51:27 ADC request [-lite version] suspend processing 3
19841 100 ********* 328 0:57:33 ADC request [Lite version] suspend processing 2
19841 100 ********* 328 9:01:31 ADC request [Lite version] restoration 2
19869 100 ********* 172 2:55:17 ADC request [-lite version] suspend processing 1
19950 100 ********* 631 2011-3-2 14:14:51 ADC request [-lite version] restoration processing 1

Select T. family_id,
T. package_id,
T. Phone,
T. open_date,
T. Reason,
Count (*)
Over (partition by family_id, package_id, phone
Order by open_date)
Rn
From zj_transaction_log t
Where to_char (T. open_date, 'yyyy-mm')> = '2017-03 ';

Family_id package_id phone open_date reason Rn
19811 100 ********* 861 2011-3-6 3:00:26 ADC request [-lite version] suspend processing 1
19811 100 ******** 861 2011-3-6 14:16:29 ADC request [-lite version] restoration 2
19823 100 ********* 667 2011-3-7 6:51:27 ADC request [-lite version] suspend processing 1
19823 100 ********* 667 2011-3-7 19:03:30 ADC request [-lite version] restoration 2
19823 100 ******** 667 2011-3-12 9:47:41 ADC request [Lite version] suspend processing 3
19841 100 ********* 328 0:57:33 ADC request [Lite version] suspend processing 1
19841 100 ********* 328 9:01:31 ADC request [Lite version] restoration 2
19869 100 ********* 172 2:55:17 ADC request [-lite version] suspend processing 1
19950 100 ********* 631 2011-3-2 14:14:51 ADC request [-lite version] restoration processing 1

Select T. family_id,
T. package_id,
T. Phone,
T. open_date,
T. Reason,
Count (*)
Over (partition by family_id, package_id, phone
Order by package_id)
Rn
From zj_transaction_log t
Where to_char (T. open_date, 'yyyy-mm')> = '2017-03 ';

Family_id package_id phone open_date reason Rn
19811 100 ********* 861 2011-3-6 3:00:26 ADC request [-lite version] suspend processing 2
19811 100 ******** 861 2011-3-6 14:16:29 ADC request [-lite version] restoration 2
19823 100 ******** 667 2011-3-12 9:47:41 ADC request [Lite version] suspend processing 3
19823 100 ********* 667 2011-3-7 19:03:30 ADC request [-lite version] restoration 3
19823 100 ********* 667 2011-3-7 6:51:27 ADC request [-lite version] suspend processing 3
19841 100 ********* 328 0:57:33 ADC request [Lite version] suspend processing 2
19841 100 ********* 328 9:01:31 ADC request [Lite version] restoration 2
19869 100 ********* 172 2:55:17 ADC request [-lite version] suspend processing 1
19950 100 ********* 631 2011-3-2 14:14:51 ADC request [-lite version] restoration processing 1

Select T. family_id,
T. package_id,
T. Phone,
T. Reason,
Count (*)
Over (partition by family_id, package_id, phone
Order by open_date DESC
Rows between unbounded preceding and current row)
Rn
From zj_transaction_log t
Where to_char (T. open_date, 'yyyy-mm')> = '2017-03 ';

Family_id package_id phone open_date reason Rn
19811 100 ********* 861 2011-3-6 3:00:26 ADC request [-lite version] suspend processing 1
19811 100 ******** 861 2011-3-6 14:16:29 ADC request [-lite version] restoration 2
19823 100 ********* 667 2011-3-7 6:51:27 ADC request [-lite version] suspend processing 1
19823 100 ********* 667 2011-3-7 19:03:30 ADC request [-lite version] restoration 2
19823 100 ******** 667 2011-3-12 9:47:41 ADC request [Lite version] suspend processing 3
19841 100 ********* 328 0:57:33 ADC request [Lite version] suspend processing 1
19841 100 ********* 328 9:01:31 ADC request [Lite version] restoration 2
19869 100 ********* 172 2:55:17 ADC request [-lite version] suspend processing 1
19950 100 ********* 631 2011-3-2 14:14:51 ADC request [-lite version] restoration processing 1

Select T. family_id,
T. package_id,
T. Phone,
T. Reason,
Count (*)
Over (partition by family_id, package_id, phone
Order by package_id DESC
Rows between unbounded preceding and current row)
Rn
From zj_transaction_log t
Where to_char (T. open_date, 'yyyy-mm')> = '2017-03 ';

Family_id package_id phone reason Rn
19811 100 ******** 861 ADC request [-lite version] suspend processing 1
19811 100 ********* 861 ADC request [-lite version] restoration 2
19823 100 ******** 667 ADC request [hardcoded version] suspend processing 1
19823 100 ********* 667 ADC request [-lite version] restoration 2
19823 100 ********* 667 ADC request [-lite version] Suspension 3
19841 100 ******** 328 ADC request [hardcoded version] suspend processing 1
19841 100 ******** 328 ADC request [Lite version] restoration 2
19869 100 ******** 172 ADC request [-lite version] suspend processing 1
19950 100 ********* 631 ADC request [-lite version] restoration 1


Iv. Rank \ dense_rank over

Rank Analysis Syntax:
Rank () over ([query_partition_clause] order_by_clause)

Syntax of Rank's aggregate function:
Rank (expr [, expr]...) Within group
(Order
Expr [DESC | ASC] [nulls {first | last}]
[, Expr [DESC | ASC] [nulls {first | last}]...
)

For analysis functions,
Select a. *, rank () over (partition by col2 order by col1) "rank" from Table;
Col1 is sorted by col2 groups. Rank and dense_rank are used in the same way, with the following differences:
　　
For example, when rank is:
Select F. Phone, F. student_name,
Rank () over (partition by F. Phone order by F. student_name) CNT
From zs_family F
Where nvl (F. is_test, 0) <> 1
And length (F. Phone) = 11
And F. Phone not like '% 100'
　　
Phone student_name CNT
* ****** 188 Chen Yalin 1
* ****** 188 Chen Yalin 1
* ******* 188 Chen zhenhao 3

If dense_rank is used:
Select F. Phone, F. student_name,
Dense_rank () over (partition by F. Phone order by F. student_name) CNT
From zs_family F
Where nvl (F. is_test, 0) <> 1
And length (F. Phone) = 11
And F. Phone not like '% 100'
　　
Phone student_name CNT
* ****** 188 Chen Yalin 1
* ****** 188 Chen Yalin 1
* ******* 188 Chen zhenhao 2

From the above, we can see that all data is sorted by group. The difference is that when a and B are sorted by group, the correlation level will not be skipped when dence_rank is in the parallel relationship. Rank is skipped (commonly used in rankings ).

The over analysis function series are very similar.

For Aggregate functions:

Select
Rank ('******** 188', 'chen Yalin ') Within group (order by F. Phone, F. student_name) CNT
From zs_family F
Where nvl (F. is_test, 0) <> 1
And length (F. Phone) = 11
And F. Phone not like '% 0% ';

CNT
258

It seems that the query locates the location of the record in the table.