Oracle analysis function: First

Source: Internet
Author: User
The upstairs example is classic, but two points are added: 1. the FIRST_VALUE function is different between the use of DISTINCT and the use of DISTINCT. The FIRST_VALUE function of DISTINCT is similar to the first () function in SQLServer to obtain the first data in all groups. If the DISTINCT and FIRST_VALUE functions are not used

The upstairs example is classic, but two points are added: 1. the FIRST_VALUE function is different between the use of DISTINCT and the use of DISTINCT. The use of the FIRST_VALUE function of DISTINCT is similar to the first () in SQL Server to obtain the first data in all groups. If the DISTINCT and FIRST_VALUE functions are not used

The upstairs example is classic, but there are two additional points:

1. The FIRST_VALUE function is different when DISTINCT is used and DISTINCT is not used. The FIRST_VALUE function of DISTINCT is similar to the first () function in SQL Server to obtain the first data in all groups.

If the DISTINCT and FIRST_VALUE functions are not used, the first value (first_value) of the group is returned for each NAME, which is grouped by POS based on the example above)

SELECT DISTINCT

FIRST_VALUE (NAME) OVER

(Partition by pos order by age desc)

AS MAXAGE_NAME

, FIRST_VALUE (NAME) OVER

(Partition by pos order by age asc)

AS MINAGE_NAME

, AVG (AGE) OVER

(Partition by pos)

AS AVG_AGE

, POS

FROM EMP

ORDER BY POS

Running result

MAXAGE_NAME MINAGE_NAME AVG_AGE POS

1 E C33.6666666666667 PL

2 E C33.6666666666667 PL

3 E C33.6666666666667 PL

4 a B 22.5 PM

5 a B 22.5 PM

2. Performance,

The use of DISTINCT usually results in a loss of SQL running performance, but the specific loss is not intuitive,

Based on the analysis results of Toad (Tools of Oracle Application Developers), the same SQL statement uses DISTINCT and ROWNUM = 1. The performance loss of DISTINCT is as high as 30%, the evidence is given below.

How to Write DISTINCT

SELECT DISTINCT

FIRST_VALUE (NAME) OVER

(Partition by id order by age desc)

AS MAXAGE_NAME

FROM EMP

ORDER BY POS

ROWNUM = 1

SELECT MAXAGE_NAME

(SELECT

FIRST_VALUE (NAME) OVER

(Partition by id order by age desc)

AS MAXAGE_NAME

FROM EMP

Order by pos) T

Where rownum = 1

Contact Us

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.