Use of DENSE_RANK analysis functions

I suddenly found that DENSE_RANK is a good function. I used to think that FIRST_VALUE and LAST_VALUE can be replaced, but it is not. Sometimes it can be used by everyone. DENSE_RANK Function Description: calculates the relative positions of each row returned by the query and other rows based on the value of the expression in the ORDERBY clause. Data in the group is ordered by the ORDERBY clause.

I suddenly found that DENSE_RANK is a good function. I used to think that FIRST_VALUE and LAST_VALUE can be replaced, but it is not. Sometimes it can be used by everyone. DENSE_RANK Function Description: calculates the relative positions of each row returned BY the query and other rows based on the value of the expression in the order by clause. Data in the group is arranged BY the order by clause

I suddenly found that DENSE_RANK is a good function. I used to think that FIRST_VALUE and LAST_VALUE can be replaced, but it is not. Sometimes it can be used by everyone.

DENSE_RANK

Function Description: calculates the relative positions of each row returned BY the query and other rows based on the value of the expression in the order by clause. The data in the group is ordered BY the order by clause, and each row is assigned a number to form a sequence. The sequence starts from 1 and accumulates later. This sequence also increases each time the value of the order by expression changes. Rows with the same value obtain the same number (equivalent when null is considered ). There is no interval for returned dense sequence.

FIRST

Function Description: extract the row with the first value from the set returned by DENSE_RANK (multiple rows may be taken because the values may be equal ), therefore, the complete syntax needs to add a set function at the beginning to retrieve records.

SAMPLE: In the following example, DENSE_RANK is partitioned by department, and then sorted by Commission commission_pct. FIRST, all the rows with the lowest Commission are retrieved, then, the previous MAX function extracts the lowest salary value from this set; the LAST function extracts all the rows with the highest commission, and the previous MIN function extracts the highest salary value from this set.

LAST

Function Description: extract the rows with the last value from the set returned by DENSE_RANK (multiple rows may be taken because the values may be equal ), therefore, the complete syntax needs to add a set function at the beginning to retrieve records.

SAMPLE: In the following example, DENSE_RANK is sorted by the employment date. FIRST, all the rows with the lowest salary are retrieved, and the MAX function above extracts the lowest salary value from this set; LAST Retrieves all rows with the highest employment date, and then the MIN function above extracts the highest salary value from this set.

SELECT

Department_id,

First_name | ''| last_name employee_name,

Hire_date,

Salary,

MIN (salary) KEEP (DENSE_RANK first order by hire_date) OVER (partition by department_id)"Worst ",

MAX (salary) KEEP (DENSE_RANK last order by hire_date) OVER (partition by department_id)"Best"

FROM employees

Next, let's give an example of using dense rank. In some special scenarios, for example, the first employee in the highest salary of the Statistics Department, the first employee in dense rank, the last function is very easy to implement.

The following example shows how to calculate the maximum and minimum values. In fact, it is not fully used in the scenario I just mentioned.

Create table test (V1 VARCHAR2 (20), V2 VARCHAR2 (10), V3 VARCHAR2 (10 ));
Insert into TEST (V1, V2, V3) Values ('1', '1', 'M ');
Insert into TEST (V1, V2, V3) Values ('1', '2', 'F ');
Insert into TEST (V1, V2, V3) Values ('2', '1', 'n ');
Insert into TEST (V1, V2, V3) Values ('2', '2', 'G ');
Insert into TEST (V1, V2, V3) Values ('3', '1', 'B ');
Insert into TEST (V1, V2, V3) Values ('3', '2', 'A ');
Insert into TEST (V1, V2, V3) Values ('1', '3', 'A ');
SQL> SELECT t. *, t. rowid FROM test t order by v1, v2;

V1 V2 V3 ROWID
----------------------------------------------------------
1 m AAASUkAAEAAAAisAAA
1 2 f AAASUkAAEAAAAisAAB
1 3 a AAASUkAAEAAAAisAAG
2 1 n AAASUkAAEAAAAisAAC
2 2g AAASUkAAEAAAAisAAD
3 B AAASUkAAEAAAAisAAE
3 2 a AAASUkAAEAAAAisAAF
How to implement the following results:
V1 V3 V3
----------------------------------------
1 m
2 n g
3 B
Bytes ------------------------------------------------------------------------------------------------------------

Answer:

Select v1
, Max (v3) keep (dense_rank first order by v2)
, Max (v3) keep (dense_rank last order by v2)
From test
Group by v1;

Bytes -------------------------------------------------------------------------------------------------------------

<无>

SELECT  department_id,  first_name||' '||last_name employee_name,  hire_date,  salary,  MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY hire_date) OVER (PARTITION BY department_id) "Worst",  MAX(salary) KEEP (DENSE_RANK LAST ORDER BY hire_date) OVER (PARTITION BY department_id) "Best"FROM employees

CREATE TABLE TEST(  V1  VARCHAR2(20),  V2  VARCHAR2(10),  V3  VARCHAR2(10)) ;Insert into TEST   (V1, V2, V3) Values   ('1', '1', 'm');Insert into TEST   (V1, V2, V3) Values   ('1', '2', 'f');Insert into TEST   (V1, V2, V3) Values   ('2', '1', 'n');Insert into TEST   (V1, V2, V3) Values   ('2', '2', 'g');Insert into TEST   (V1, V2, V3) Values   ('3', '1', 'b');Insert into TEST   (V1, V2, V3) Values   ('3', '2', 'a');Insert into TEST   (V1, V2, V3) Values   ('1', '3', 'a');SQL> SELECT t.* ,t.rowid FROM test t order by v1,v2;

select v1      ,max(v3) keep (dense_rank first order by v2)      ,max(v3) keep (dense_rank last order by v2)  from testgroup by v1;