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

Oracle Lead/Last function

1. Syntax

Purpose

FIRST and LAST are very similar functions.Both are aggregate and analytic functions that operate on a set of values froma set of rows that rank as the FIRST or LAST withrespect to a given sorting specification. If only one row ranks as FIRSTor LAST, then the aggregate operates on the set with only one element.

If you omit the OVERclause, then the FIRST and LAST functions are treated as aggregate functions. You can use thesefunctions as analytic functions by specifying the OVER clause. Thequery_partition_clause is the only part of the OVER clause valid with thesefunctions. If you include the OVER clause but omit thequery_partition_clause, then the function is treated as an analytic function, but the window defined for analysis is theentire table.

English description: omitting the over clause, Fisrt/Last is used as an aggregate function, see example 1; with the keyword over but not query_partition_clause,Fisrt/Last is used as an analysis function, the analysis window is the entire table, see example 2.

These functions take as an argument anynumeric data type or any nonnumeric data type that can be implicitly convertedto a numeric data type. The function returns the same data type as the numericdata type of the argument.

When you need a value from the first orlast row of a sorted group, but the needed value is not the sort key, the FIRSTand LAST functions eliminate the need for self-joins or views and enable betterperformance.

The aggregate_functionargument is any one of the MIN, MAX, SUM, AVG, COUNT, VARIANCE, or STDDEVfunctions. It operates on values from the rows thatrank either FIRST or LAST. If only one row ranks as FIRST or LAST, then theaggregate operates ona singleton (nonaggregate) set.

The KEEP keyword is for semantic clarity.It qualifies aggregate_function, indicating that only the FIRST or LAST valuesof aggregate_function will be returned.

DENSE_RANK FIRST or DENSE_RANK LASTindicates that Oracle Database will aggregate over only those rows with theminimum (FIRST) or the maximum (LAST) dense rank (also called olympic rank)

two。 Explain min (job_id) keep (dense_rank first order bycount (job_id) desc) over (partition by department_id)

Semantics: find the type of work with the largest number of jobs by each department.

Min: for example, in a department, there are two jobs with the largest occupancy, such as 3 for An and 3 for B, and the value returned with min is A, and the corresponding value returned with max is B. If you want to use a function like AVG, you will get an error, invalid number. In fact, the function is to prevent the return of two values, not online, completely meaningless (max and min results are not the same).

Keep: keyword.

Dense_rank: sort operation. Try row_number instead, and throw an exception directly.

Over: that is, the window of analysis function analysis. If over and its following statements are omitted, the whole result will be aggregate.

3. Example 1. Examples 1select max (e.job_id) keep (dense_rank lastorder by count (job_id) desc), min (e.job_id) keep (dense_rank lastorder by count (job_id) desc), max (e.job_id) keep (dense_rank first order by count (job_id) desc), min (e.job_id) keep (dense_rank first order by count (job_id) desc) from employees e group by e.department_id, e.job_id

The returned result set is as follows

SA_REP AC_ACCOUNT SA_REP SA_REP

It is found that the aggregation of the entire table also verifies that the results of max and min are sometimes inconsistent.

two。 Example 2select distinct department_id,-- count (job_id), min (job_id) keep (dense_rank first order by count (job_id) desc) over (partition by department_id) job_id from employees group by department_id, job_id order by 1

Analysis window: grouping by department

The returned result set is as follows

1 10 AD_ASST

2 20 MK_MAN

3 30 PU_CLERK

4 40 HR_REP

5 50 SH_CLERK

6 60 IT_PROG

7 70 PR_REP

8 80 SA_REP

9 90 AD_VP

10 100 FI_ACCOUNT

11 110 AC_ACCOUNT

12 SA_REP

Return to the type of work with the largest number of jobs in each department, and note that the department ID is also returned if it is empty. This is boss.

3. Comparison of several writing methods 1. Method 1with t as (selectdepartment_id, job_id, count (job_id) cnt from employees group by department_id, job_id) selectdepartment_id, max (job_id)-- reaggregate from t where (department_id, cnt) in (selectdepartment_id, max (cnt) from t group by department_id) group by department_id order by 1

1 10 AD_ASST

2 20 MK_REP

3 30 PU_CLERK

4 40 HR_REP

5 50 ST_CLERK

6 60 IT_PROG

7 70 PR_REP

8 80 SA_REP

9 90 AD_VP

10 100 FI_ACCOUNT

11 110 AC_MGR

Summary: 1. Boss this department, that is, the department is empty, did not return

two。 Those with the largest number of jobs in a certain department have two types of work, which have to be aggregated again.

3. The code is cumbersome.

Method 2select department_id, job_id from (select e.department_id, e.job_id, count (e.job_id), row_number () over (partition bydepartment_id order by count (job_id) desc) rk from employees e group by e.department_id, e.job_id) where rk = 1

1 10 AD_ASST

2 20 MK_MAN

3 30 PU_CLERK

4 40 HR_REP

5 50 ST_CLERK

6 60 IT_PROG

7 70 PR_REP

8 80 SA_REP

9 90 AD_VP

10 100 FI_ACCOUNT

11 110 AC_ACCOUNT

12 SA_REP

Summary: 1. Sort with row_number, and then filter objects with row_number 1 using an external query

The result contained by 2.boss is returned.

3. Method 3select / * distinct*/ department_id, count (job_id), min (job_id) keep (dense_rank first order by count (job_id) desc) over (partition by department_id) job_id from employees group by department_id, job_id order by 1

1 10 1 AD_ASST

2 20 1 MK_MAN

3 20 1 MK_MAN

4 30 5 PU_CLERK

5 30 1 PU_CLERK

6 40 1 HR_REP

7 50 20 SH_CLERK

8 50 20 SH_CLERK

9 50 5 SH_CLERK

10 60 5 IT_PROG

11 70 1 PR_REP

12 80 5 SA_REP

13 80 29 SA_REP

14 90 1 AD_VP

15 90 2 AD_VP

16 100 5 FI_ACCOUNT

17 100 1 FI_ACCOUNT

18 110 1 AC_ACCOUNT

19 110 1 AC_ACCOUNT

20 1 SA_REP

Here, we are grouped by department_id and job_id. We only care about department_id and job_id,SQL to make adjustments.

TuneSQL

Select distinct department_id,-- count (job_id), min (job_id) keep (dense_rank first order by count (job_id) desc) over (partition by department_id) job_id from employees group by department_id, job_id order by 1

1 10 AD_ASST

2 20 MK_MAN

3 30 PU_CLERK

4 40 HR_REP

5 50 SH_CLERK

6 60 IT_PROG

7 70 PR_REP

8 80 SA_REP

9 90 AD_VP

10 100 FI_ACCOUNT

11 110 AC_ACCOUNT

12 SA_REP

Summary: 1.boss this department returns

two。 No subqueries are involved, and the code is concise.

3. By careful comparison, the results of method2 and method3 are still slightly different.

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.