Today I saw a problem with my brother,
Probably as follows:
Query the Minimum Wage userid of the Department.
Table Structure:
D. Wage Department
Userid salary Dept
1 2000 1
2 1000 1
3 500 2
4 1000 2
An expert gave an answer:
Select min (salary) over (partition by dept) Salary, Dept
From SS
After running:
1000 1
1000 1
500 2
500 2
The old man thought it was very advanced. It's really a great man ~
I think this guy is really tall.
But I did not have a deep understanding of partition by usage. The problem of the landlord is not solved.
Please refer to the modified statement
Select userid, salary, DEPT, min (salary) over (partition by dept) Salary
From SS
Result After running:
Userid salary dept min (salary) over (partition by dept)
1 2000 1 1000
2 1000 1 1000
3 500 2 500
4 1000 2 500
Let's see what's going on.
Advanced may not be suitable.
Here is my answer:
Select * from SS
Inner join (select Min (salary) as salary, DEPT from SS group by dept) ss2
Using (salary, Dept)
Result After running:
Salary dept userid
1000 1 2
500 2 3
So I want to summarize the usage of group by and partition.
Group by is a simple grouping of the reserved rows of the search results. Generally, the total love and Aggregate functions are used together, such as AVG (), count (), max (), and main.
Although partition by has the grouping function, it also has other functions.
It is an oracle analysis function.
Use the data of a diligent person to explain:
Sum () over (partition by...) is an analysis function. The result of his execution is different from the ordinary sum... group by.... It calculates the sum of expressions in the group, instead of the sum.
Table A has the following content:
B c d
02 02 1
02 03 2
02 04 3
02 05 4
02 01 5
02 06 6
02 07 7
02 03 5
02 02 12
02 01 2
02 01 23
Select B, c, sum (d) e from a group by B, c
Get:
B c e
02 01 30
02 02 13
02 03 7
02 04 3
02 05 4
02 06 6
02 07 7
The result of using the analysis function is:
Select B, c, d, sum (d) over (partition by B, C order by D) E from
B c e
02 01 2
02 01 7
02 01 30
02 02 1
02 02 13
02 03 2
02 03 7
02 04 3
02 05 4
02 06 6
02 07 7
The results are different. We can see the content of d more clearly:
Select B, c, d, sum (d) over (partition by B, C order by D) E from
B c d e
02 01 2 2 D = 2, sum (d) = 2
02 01 5 7 d = 5, sum (d) = 7
02 01 23 30 d = 23, sum (d) = 30
02 02 1 1 C value is different, re-accumulate
02 02 12 13
02 03 2 2
02 03 5 7
02 04 3 3 3
02 05 4 4
02 06 6 6
02 07 7 7