MySQL practical window function SQL analysis class students' test scores and living expenses

1. Background

Today, the monthly exam results for the third grade class of a fake university are out. Here I would like to announce the exam results of each student to you.

Next, I will announce to you the living expenses of each student.

Next, we use the above test scores and living expenses records to do a simple analysis using MySQL.

Of course, this can be seen from the title of this article. This article will use this data to explain how to use SQL "window functions"?

This will be a very important knowledge point whether you are studying Hive or Oracle database in the future or taking data analysis interviews.

2. Table creation statements and data insertion

Create a table

create table exam_score(
    sname varchar(20),
    age int,
    subject varchar(20),
    score varchar(20)
)charset=utf8;

# ----------------------- #

create table cost_fee(
    sname varchar(20),
    buydate varchar(20),
    buycost int
)charset=utf8;

Inserting Data

insert into exam_score values
('Zhang San', 18, 'Chinese', 90),
('Zhang San', 18, 'Mathematics', 80),
('Zhang San', 18, 'English', 70),
('Li Si', 21, 'Chinese', 88),
('Li Si', 21, 'Mathematics', 78),
('Li Si', 21, 'English', 71),
('Wang Wu', 18, 'Chinese', 95),
('Wang Wu', 18, 'Mathematics', 83),
('Wang Wu', 18, 'English', 71),
('Zhao Liu', 19, 'Chinese', 98),
('Zhao Liu', 19, 'Mathematics', 90),
('Zhao Liu', 19, 'English', 80);
# ----------------------- #
insert into cost_fee values
('Zhang San','2019-01-01',10),
('Zhang San','2019-03-03',23),
('Zhang San','2019-02-05',46),
('Li Si','2019-02-02',15),
('Li Si','2019-01-07',50),
('Li Si','2019-03-04',29),
('Wang Wu','2019-03-08',62),
('Wang Wu','2019-02-09',68),
('Wang Wu','2019-01-11',75),
('Zhao Liu','2019-02-08',55),
('Zhao Liu','2019-03-10',12),
('Zhao Liu','2019-01-12',80);

3. Introduction to Window Function Classification

Before formally discussing the application of "window functions", I will first review the basics of "window functions". We can classify window functions into the following categories:

Aggregate function + over() combination;

Sorting function + over() combination;

ntile() function + over() combination;

Offset function + over() combination;

What are the functions of each category? Observe the mind map below.

There are two commonly used keywords in over() that must be explained. as follows:

partition by + field: You can think of it as the group by keyword, which is the keyword used for " grouping";

order by + field: This is easier to understand, it is the keyword used for "sorting";

4. Window Function Application

We have introduced several commonly used "window functions" above. Here, we will use the data created at the beginning of the article to talk about the application of "window functions".

I hope you can summarize the meaning of each function through each case, so I won’t write it in detail here.

1. Aggregate function + over()

① Calculate each student's score and the average

select 
	sname
    ,subject
    ,score
    ,avg(score) over(partition by sname) as avg_score
from
	exam_score

The results are as follows:

② Calculate each student’s consumption and total consumption from January to March

select
	sname
    ,buydate
    ,buycost
    ,sum(buycost) over(partition by sname) as sum_cost
from
	cost_fee

The results are as follows:

③ Calculate each student’s consumption from January to March and the total cumulative consumption

select
	sname
    ,buydate
    ,buycost
    ,sum(buycost) over(partition by sname order by buydate) as sum_cost
from
	cost_fee

The results are as follows:

Note: Combining ②③, you can find that partition by combined with order by and without order by will produce completely different results. One is to find the total sum of groups (without order by); the other is to find the cumulative sum of groups (with order by).

2. Sorting function + over()

① Calculate the ranking of each subject. The same score has a different ranking, and the order increases in sequence.

select
	sname
	,subject
	,score
    ,row_number() over(partition by subject order by score) rank1
from
	exam_score

The results are as follows:

② Calculate the ranking of each subject. The same score will keep the same ranking, and the remaining rankings will jump up.

select
	sname
	,subject
	,score
    ,rank() over(partition by subject order by score) rank1
from
	exam_score

The results are as follows:

③ Calculate the ranking of each subject. The same scores will be ranked the same, and the remaining scores will be ranked in ascending order.

select
	sname
	,subject
	,score
    ,dense_rank() over(partition by subject order by score) rank1
from
	exam_score

The results are as follows:

3. ntile() function + over() combination

The ntile() function feels a bit out of place, and you don't know which category to put it in. This function is mainly used for " data segmentation". If there is any use for this function, it is that it can also sort the data, similar to the row_number() function mentioned above.

① Split the entire exam_score table

select
	sname
	,subject
	,score
    ,ntile(4) over() rank1
from
	exam_score

The results are as follows:

If you don’t believe me, try it. It seems that no matter which number you write in ntile(), it will work.

② For the exam_score table, split it by subject group

select
	sname
	,subject
    ,score
    ,ntile(4) over(partition by subject) rank1
from
	exam_score

The results are as follows:

Even if you divide by groups, you will find that this is meaningless because the scores are not sorted.

③ For the exam_score table, sort the scores and then group and split them by subject (most useful)

select
	sname
	,subject
    ,score
    ,ntile(4) over(partition by subject order by score) rank1
from
	exam_score

The results are as follows:

Note: If you observe this usage carefully, it is basically equivalent to the row_number() function and the effect is the same.

4. Offset function + over() combination

① Display the “last purchase time” and “next purchase time” of each student

Note: For the first day, it displays "first buy"; for the last day, it displays "last buy " ;

select
	sname
	,buydate
    ,lag(buydate,1,'first day') over(partition by sname order by buydate) as last purchase time,lead(buydate,1,'last day') over(partition by sname order by buydate) as next purchase time from
	cost_fee

The results are as follows:

② As of the current date, each student’s “first purchase time” and “last purchase time”

select
	sname
	,buydate
    ,first_value(buydate) over(partition by sname order by buydate) as first purchase date,last_value(buydate) over(partition by sname order by buydate) as last purchase date from
	cost_fee

The results are as follows:

③ Display each student’s “first purchase time” and “last purchase time”

Note: It does not say "as of the current date" here, please pay attention to the difference between ②③. Different needs lead to different results.

select
	sname
	,buydate
    ,first_value(buydate) over(partition by sname order by buydate) as first purchase date,last_value(buydate) over(partition by sname ) as last purchase date from
	cost_fee

The results are as follows:

The above is the details of using MySQL practical window function SQL to analyze the test scores and living expenses of class students. For more information about SQL window function analysis of scores and consumption, please pay attention to other related articles on 123WORDPRESS.COM!