mysql windowing function
1. Definitions
- In order to display the data before and after the aggregation function.
- The windowing function operates on a group of values. It does not need the group by clause to group the data. It can return the columns of the basic row and aggregate columns in the same row at the same time
Note: the windowing function is in mysql8 Only after 0
2. Grammar
Function name (column) over (option [the option can be partition by column, order by column])
over(partition by xxx) according to xxx Group all rows over(partition by xxx order by aaa) according to xxx Group by aaa sort
3. Classification of windowing function
3.1 aggregate windowing function
- If the function name is an aggregate function, it becomes an aggregate windowing function
- Syntax: aggregate function (column) over(partition by column, order by row)
- Common aggregate functions: sum(),count(),max(),min()
Calculate the number of passes for each student -- Using aggregate functions select student_id,count(sid) from score where num>=60 group by student_id; -- Use windowing function select sid,student_id,count(sid) over(partition by student_id order by student_id) Number of passes from score where num>=60;
summary
The windowing function will not modify the structure of the source data table, but will add the desired results in the last column of the table. If there are multiple rows of data in the grouping, it will be displayed repeatedly
3.2 sorting windowing function
- row_ Number (line number)
- Generate consecutive sequence numbers, regardless of the same score
- Rank
- The ranking of the same score is the same. The rear ranking is the real serial number, and the ranking is jumping
- For example: 12225, there are 3 in the ranking of 2, and the next ranking is 5, because there are 4 scores in front
- dense_ Rank (dense sort)
- The ranking of the same score is the same, which is a continuous ranking
- For example: 12223, there are 3 ranking 2, and the next ranking is 3
- Ntile (group ranking)
- ntile has the concept of bucket
- ntile(6), which divides the total records into 6 barrels. If there are 12 records, there are 2 records in a barrel
- Ranking: 112233445566
- The number size of the sorting result can only be used between buckets. Although the sequence number inside the bucket is the same, the num is not necessarily the same
-- Look at the code differentiation relationship select s.sid,s1.sname,s1.gender,c.cname,s.num, row_number() over(partition by c.cname order by num desc) as row_number ranking, rank() over(partition by c.cname order by num desc) as rank ranking, dense_rank() over(partition by c.cname order by num desc) as dense_rank ranking, ntile(6) over(partition by c.cname order by num desc) as ntile ranking from score as s join student s1 on s.student_id=s1.sid left join course c on s.course_id=c.cid;
Case: -- Query the performance information of the top three students in each subject select * from( select s.sid,s1.sname,s1.gender,c.cname,s.num, dense_rank() over(partition by c.cname order by num desc) as dense_rank ranking from score as s join student s1 on s.student_id=s1.sid left join course c on s.course_id=c.cid) as e where dense_rank ranking<=3;
3.3 others
-
lag(col,n)
The value of the nth row in the statistics window
-
lead(col,n)
The value of the nth row down in the statistics window
These two functions can be used for data subtraction of adjacent rows in the same column
case
-- For the following data, for the same user( uid)If you log in again within 2 minutes, it will be judged as cheating, count which users have cheated, and calculate the number of cheating
-- Go time difference
SELECT *,
format(Seconds difference / 60, 3) Minutes difference
FROM
( SELECT id,
uid,
login_time,
-- Add a column of time
lead (login_time, 1) over ( PARTITION BY uid ORDER BY login_time ) lead_time,
-- Find the difference between the two columns of time
timestampdiff(
SECOND,
login_time,
( lead (login_time, 1) over ( PARTITION BY uid ORDER BY login_time ))
) Seconds difference
FROM lag_table ) as e;
-- Find the number of cheating
SELECT uid,count(1) Number of cheating
FROM
( SELECT id,
uid,
login_time,
-- Add a column of time
lead (login_time, 1) over ( PARTITION BY uid ORDER BY login_time ) lead_time,
-- Find the difference between the two columns of time
timestampdiff(
SECOND,
login_time,
( lead (login_time, 1) over ( PARTITION BY uid ORDER BY login_time ))
) Seconds difference
FROM lag_table ) as e
where format(Seconds difference / 60, 3)<=2 group by uid;
-
first_value(column)
Get the first value of the current row after sorting in the group
-- Take the maximum score of the subject -- In descending order, the first value is the maximum value select s.sid,s1.sname,s1.gender,c.cname,s.num, first_value(num) over(partition by c.cname order by num desc) as firstvalue from score s join student s1 on s.student_id=s1.sid left join course c on s.course_id=c.cid;
-
last_value(column)
- Get the last value of the current row after sorting in the group
- last_ The default statistical range of value() is rows between unbounded preceding and current row, which is to compare the data of the current row with the data before the current row
Modification scope: stay order by Add after rows between unbounded preceding and unbounded following Namely: order by rows between unbounded preceding and unbounded following It changes to: compare the current grouped data with the data and take the last value unbounded Unlimited preceding The forward offset of the current record of the partition current current following Current record backward offset of the partition
-- Take the minimum score of the subject -- According to the descending order, the last value is the minimum value, and the range should be modified select s.sid,s1.sname,s1.gender,c.cname,s.num, last_value(num) over(partition by c.cname order by num desc rows between unbounded preceding and unbounded following) as lastvalue from score s join student s1 on s.student_id=s1.sid left join course c on s.course_id=c.cid;
https://www.luffycity.com/