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

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.

insert image description here

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

insert image description here

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.

insert image description here

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:

insert image description here

② 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:

insert image description here

③ 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:

insert image description here

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:

insert image description here

② 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:

insert image description here

③ 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:

insert image description here

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:

insert image description here

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:

insert image description here

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:

insert image description here

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:

insert image description here

② 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:

insert image description here

③ 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:

insert image description here

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!

You may also be interested in:
  • Very practical MySQL function comprehensive summary detailed example analysis tutorial
  • MySQL Database Basics SQL Window Function Example Analysis Tutorial
  • mysql calculation function details
  • MySQL example to explain single-row functions and character math date process control
  • MySQL essential basics: grouping function, aggregate function, grouping query detailed explanation
  • A brief introduction to MySQL functions
  • MySQL spatial data storage and functions
  • Comprehensive summary of mysql functions

<<:  Vue achieves the top effect through v-show

>>:  CSS3 realizes the glowing border effect

Recommend

How to use Javascript to generate smooth curves

Table of contents Preface Introduction to Bezier ...

How to clear floating example code in css

Overview The framework diagram of this article is...

Linux general java program startup script code example

Although the frequency of starting the shell is v...

Implementing login page based on layui

This article example shares the specific code of ...

Docker deploys Mysql, .Net6, Sqlserver and other containers

Table of contents Install Docker on CentOS 8 1. U...

Sending emails in html is easy with Mailto

Recently, I added a click-to-send email function t...

MySQL partitioning practice through Navicat

MySQL partitioning is helpful for managing very l...

MySql learning day03: connection and query details between data tables

Primary Key: Keyword: primary key Features: canno...

How to detect file system integrity based on AIDE in Linux

1. AIDE AIDE (Advanced Intrusion Detection Enviro...

Analysis of the principles of Mysql dirty page flush and shrinking table space

mysql dirty pages Due to the WAL mechanism, when ...

Thoughts on copy_{to, from}_user() in the Linux kernel

Table of contents 1. What is copy_{to,from}_user(...

Solution to the cross-domain problem of SpringBoot and Vue interaction

Table of contents Browser Same Origin Policy 1. V...