Learning: Oracle SQL learning path (4): Analyzing function statistics (2)

Source: Internet
Author: User
How to Learn: Oracle SQL learning path (4): Analyzing function statistics (2) Preface

I have written several blog posts about analysis functions.

However, I have always been not satisfied with what I wrote, and I cannot tell where the problem is. The biggest problem is that I am not satisfied, but completely confused, and where I am not satisfied, what should I say clearly? Or the content is too thin.

MAX and MIN know what it means.

SQL:

  1. Select distinct manager_id,
  2. Max (salary) over (partition by manager_id) as max_salary
  3. From employees order by manager_id;

Execution result (excerpt ):

MANAGER_ID

MAX_SALARY

100

17000

101

12008

102

9000

103

6000

108

9000

114

3100

120

3200

121

4200

122

3800

123

4000

124

3500

Who determines the number of statistical function lines?

This problem is actually confusing for beginners to a large extent, because in theory, we always think there is only one MAX. Why is there so much? Especially when no group exists.

Let's rewrite the preceding SQL statement.

  1. Select manager_id,
  2. Max (salary) over (partition by manager_id) as max_salary
  3. From employees
  4. Where manager_id = '20140901'
  5. ;

Result:

MANAGER_ID

MAX_SALARY

100

17000

100

17000

100

17000

100

17000

100

17000

100

17000

100

17000

100

17000

100

17000

100

17000

100

17000

100

17000

100

17000

100

17000

Okay. I will rewrite the SQL.

  1. Select manager_id
  2. From employees
  3. Where manager_id = '20140901'
  4. ;

Then the result set is displayed:

MANAGER_ID

100

100

100

100

100

100

100

100

100

100

100

100

100

To count the number of rows.

You can fully regard the analysis function as a hacker who forcibly adds an SQL statement. The data of this hacker must comply with the original data.

That is to say, the number of rows is determined by the SQL statement after the analytic function is removed.

Syntax

MAX

MIN

FIRST_VALUE

LAST_VALUE

It is easy to use.

In terms of function, I personally think MAX is a subset of FIRST_VALUE.

 

 

 

 

 

 

 

 

Related Article

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.