Oracle analysis function over partition by and group by difference ___ Static function
Source: Internet
Author: User
the difference between over partition by and group byToday I saw a problem with a man,
Probably as follows:
Find out the userid number of the lowest wage in the department
Table structure:
Wage Department D
UserID Salary Dept
1 2000 1
2 1000 1
3 500 2
4 1000 2
There is an expert who gives an answer:
SELECT MIN (Salary) over (PARTITION by dept) Salary, Dept
from SS
After running get:
1000 1
1000 1
500 2
500 2
The landlord that man a look feel very advanced. Big sigh is really high man ah ~
I also think this man is really tall.
But I looked into it and found out that the man didn't understand the use of partition by. And did not solve the landlord's problem.
Everyone, please look at my revised statement.
SELECT Userid,salary,dept,min (Salary) over (PARTITION by dept) Salary
from SS
Results after run:
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
We can see the clue.
The advanced may not be suitable.
Here is the answer I gave:
SELECT * from SS
INNER JOIN (SELECT MIN (SALARY) as SALARY, DEPT from SS GROUP by DEPT) SS2
USING (salary,dept)
So I thought of summarizing the usage of group by and partition by
Group BY is a simple grouping of the reserved rows of the retrieved results, with general love and aggregate functions such as AVG (), COUNT (), Max (), Main (), and so on.
Partition by although also has the grouping function, but also has the other function.
It belongs to the analysis function of Oracle.
Use the data from a diligent person to explain:
SUM () over (PARTITION by ...) is an analytic function. The effect he performed with the ordinary sum ... GROUP by ... Instead, it calculates the accumulation of expressions in a group, not the simple and.
Table A, which reads as follows:
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
And the result of using analytic functions is:
SELECT B, C, D, SUM (d) over (PARTITION by b,c order by D) E from a
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 result is not the same, so it is not clear that we also show the content of D is more clear:
SELECT B, C, D,sum (d) over (PARTITION by b,c order through D) E from a
B C D E
2 2 d=2,sum (d) =2
5 7 d=5,sum (d) =7
D=23,sum (d) =30
1 1 C value different, re-cumulative
02 02 12 13
02 03 2 2
02 03 5 7
02 04 3 3
02 05 4 4
02 06 6 6
02 07 7 7
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.