Difference between over partition by and group

Source: Internet
Author: User
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

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.