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

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.