SUM (SAL) over (partition by Deptno)--a continuous summation of departmental employees ' wages, grouped by department
Partition by is a grouping function
Second, row_number over
Row_number () over ([partition by col1] ORDER by col2) as Alias
Represents a grouping based on col1, sorted by col2 within a group
The value of this "alias" indicates the sequential number after each group's internal sort (a sequential unique within the group), [partition by col1] can be omitted
Row_number () Over is grouped according to Partion by, then sorted by open_date within each group to return the order number of the internal sort
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 ') >= ' 2011-03 ';
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 ') >= ' 2011-03 ';
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 ') >= ' 2011-03 ';
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 ') >= ' 2011-03 ';
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 ') >= ' 2011-03 ';
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 ') >= ' 2011-03 ';
Rank's analytic function syntax:
RANK () over ([Query_partition_clause] order_by_clause)
Rank's total function syntax:
RANK (expr [, expr] ...) WITHIN GROUP
(Order BY
expr [DESC | ASC] [NULLS {i | Last}]
[, expr [DESC | ASC] [NULLS {i | Last}]] ...
)
For analysis functions,
Select A.*,rank () over (PARTITION by col2 Order by col1) ' RANK ' from Table A;
Sorted by col2 grouped col1, rank is equivalent to Dense_rank usage, the difference is as follows:
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 '%0% '
And if you are using Dense_rank:
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 '%0% '
From the above, are the data grouping, the difference is in a group, b sort, when the dence_rank in a parallel relationship, the relevant level does not skip. Rank is skipped (often in rank).
The Over Analytic function series is very similar.
For aggregate functionality:
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
The feeling is that the query locates the record's position in the table
Five, max\min over
Function: Generate 2 columns to obtain field maximum, minimum value
For example:
Check employee information with the highest wage minimum employee select Ename,sal,max (SAL) over (), Min (sal) over () from EMP;
Six, Lag\lead over
Function:
To get data on a row or on the next line of the current row
Example:
Require an SQL statement to count the order amount for each month and month and next month
Request list Output as
Monthly Order amount for month of month order quantity
Select To_char (d.update_time, ' yyyymm '),
Count (code),
Lag (count (code) over) last month's order quantity,
Number of orders under lead (count (code) over (code))
From V_nwrz_dwxx_test2 D
Group BY To_char (D.update_time, ' yyyymm ');
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
