Shulou(Shulou.com)06/01 Report--

This article mainly introduces the example analysis of sorting within the group in SQL, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

Why is Jane talking about it? in fact, the analysis function is also used in a lot of places in the work, but there are many aspects it can be realized. Here we will talk about three aspects: 1. Sort within the group, 2. Aggregation within the group, 3. Calculates the offset between rows and row data. Ps: when you learn to analyze a function, trust me, you will love it! )

Jianghu custom, first take excel surgery, there is such a scene, our school has 8 classes, the name is one, two, three. In the class, there are n students in each class, and now the teacher wants to see the ranking of each student in the class and in the whole grade. This is the general model:

From the left to the right, this transformation process excel should be able to achieve, but I can not ~, students who can play excel can leave a message to the author on the official account, I also learn ~

Back to the point, let's first create a table in the database to store this data:

CREATE TABLE student (xh INT (10), bj CHAR (10), cj INT (10); INSERT INTO student VALUES (20001, Class 1, 510); INSERT INTO student VALUES (20002, Class 2, 630); INSERT INTO student VALUES (20003, Class 2, 200); INSERT INTO student VALUES (20004, Class 3, 500); INSERT INTO student VALUES (20005, Class 1, 500); INSERT INTO student VALUES (20006, Class 2, 150) INSERT INTO student VALUES (20007, Class 3, 140); INSERT INTO student VALUES (20008, Class 3, 140); INSERT INTO student VALUES (20009, Class 3, 120)

Then we run the following query:

Select xh,bj,cj, row_number () over (order by cj desc,xh asc) as njpm, row_number () over (partition by bj order by cj desc,xh asc) as bjpm from student

You can see that the data have been sorted according to our rules, and here we see that the students with student numbers 20008 and 20007 have the same grades. Here, when the scores are the same, they are sorted according to the student numbers from big to small.

Next, let's analyze this function.

Row_number () over (partition by grouping column, order by sorting)

The front row_number () over is unchanged, and the content in the parentheses is the key. Here, we need to sort the scores of each class, so we need to divide each class into a group, that is, the partition by is followed by the grade column. If we are grouped by grade, we have the data of one grade in the whole table here, so we do not write the grouping, that is, we treat all the data as a group. Then follow our student grades. Desc is sorted from big to small, that is, from high to low. There can be multiple sorting fields. For example, when the scores are the same, they can be sorted by student number from small to large, that is, asc. Grouping and sorting are the same, and there can be 0 or more fields.

But some teachers may want to see a fairer result, that is, students with the same grades have the same ranking, but a new question will arise, that is, the ranking of the next 20009 students is second according to the first two, and then it is the third, or it directly crosses the third place and ranks it to the fourth place, which leads to the two sibling functions of row_number (): dense_rank () and rank ():

Rank () over (partition by grouping column, order by sorting)-rank () can rank students of the same rank by leaps and bounds, such as 1224 dense_rank () over (partition by grouping column, order by sorting)-dense_rank () has a function similar to that of rank (), but forms a continuous ranking, such as 1223

Thank you for reading this article carefully. I hope the article "sample Analysis of ranking in groups in SQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.