Mysql8.0窗口函数用法

一.Mysql8.0安装

一、环境准备：

1.目录创建：

mkdir -p /data/{software,mysql}

mkdir -p /data/mysql/{data,log,tmp}

2.用户创建：

useradd mysql

3.安装依赖包：

yum install perl perl-devel perl-Data-Dumper libaio-devel -y

二、安装：

1.下载包文件：

cd /data/software/

wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz

tar -xvf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz

ln -s /data/software/mysql-8.0.34-linux-glibc2.12-x86_64 /usr/local/mysql

2.权限赋值：

chown -R mysql:mysql /usr/local/mysql /data/mysql/*

三、配置文件：

参数可以根据实际需求进行调配

vi /etc/my.cnf

[client]

port = 3306

socket = /data/mysql/tmp/mysql.sock

[mysql]

prompt="\u@db \R:\m:\s [\d]> "

no-auto-rehash

[mysqld]

user = mysql

port = 3306

basedir = /usr/local/mysql

datadir = /data/mysql/data

socket = /data/mysql/tmp/mysql.sock

tmpdir =

pid-file = /data/mysql/tmp/mysql.pid

character-set-server=utf8

collation-server = utf8_general_ci

sql_mode=‘NO_UNSIGNED_SUBTRACTION,NO_ENGINE_SUBSTITUTION’

open_files_limit = 65535

innodb_open_files = 65535

back_log=1024

max_connections = 512

max_connect_errors=1000000

interactive_timeout=300

wait_timeout=300

max_allowed_packet = 1024M

secure_file_priv=’’

log-error=/data/mysql/log/error.log

slow_query_log=ON

slow_query_log_file=/data/mysql/log/slow_mysql.log

long_query_time=1

innodb_flush_log_at_trx_commit=1

innodb_log_file_size =1G

innodb_log_files_in_group=3

innodb_log_group_home_dir=./

sync_binlog = 1

binlog_cache_size = 16M

max_binlog_cache_size = 1G

max_binlog_size=1G

expire_logs_days = 30

log-bin-trust-function-creators=1

log-bin= /data/mysql/log/binlog-mysql

binlog_format=row

binlog_row_image=full

server-id = 1

大小根据实际情况而定

innodb_buffer_pool_size=1G

innodb_buffer_pool_instances=2

四、初始化Mysql

1.初始化操作：

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/data --user=mysql --initialize

2.查看随机密码：可以看到我们的初始密码为 “Eiv*/Dy!I44t”

[root@node1 data]# cat /data/mysql/log/error.log | grep password

2019-02-21T10:45:24.067928Z 1 [Note] A temporary password is generated for root@node1: Eiv*/Dy!I44t

五、启动mysql：

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

六、修改密码，设置环境变量以及自启动

[root@node1 log]# echo ‘export PATH=/usr/local/mysql/bin:$PATH’ >> /etc/profile

[root@node1 ~]# source /etc/profile

[root@node1 ~]# /usr/local/mysql/bin/mysqladmin -uroot -p password

Enter password:

New password:

Confirm new password: Password123

[root@node1 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

[root@node1 ~]# chkconfig --add mysqld

[root@node1 ~]# chkconfig mysqld on

七、登陆测试

[root@node1 bin]# mysql -uroot -pPassword123

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (111)

解决：

创建软链接：ln -s /data/mysql/tmp/mysql.sock /tmp/mysql.sock

[root@node1 ~]# mysql -uroot -pPassword123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.34 MySQL Community Server - GPL

Copyright © 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

root@db 02:59: [(none)]>

#赋予远程访问权限

select host, user, authentication_string, plugin from mysql.user;

update mysql.user set host=’%’ where user=‘root’;

grant all privileges on . to root@’%’ with grant option;

mysql> select host, user, authentication_string, plugin from mysql.user;

±----------±--------------±------------------------------------------±----------------------+

| host | user | authentication_string | plugin |

±----------±--------------±------------------------------------------±----------------------+

| node1 | root | *8232A1298A49F710DBEE0B330C42EEC825D4190A | mysql_native_password |

| node1 | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |

| node1 | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |

±----------±--------------±------------------------------------------±----------------------+

3 rows in set (0.00 sec)

mysql> update mysql.user set host=’%’ where user=‘root’;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> grant all privileges on . to root@’%’ with grant option;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

#刷新权限

flush privileges;

#远程登陆测试

mysql -uroot -pPassword123 -h192.168.100.66 -P3306

[root@node1 local]# mysql -uroot -pPassword123 -h192.168.100.66 -P3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 8.0.34 MySQL Community Server - GPL

Copyright © 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

root@db 04:50: [(none)]>

二.创建测试数据

1.测试数据下载地址：https://github.com/datacharmer/test_db

2.Download ZIP包 test_db-master.zip，并上传到服务器中

或者使用命令下载到本地服务器上
git clone https://github.com/datacharmer/test_db.git

3.解压
unzip test_db-master.zip

4.执行导入命令
mysql -uroot -pPassword123 < employees.sql
mysql -uroot -pPassword123 < employees_partitioned.sql
mysql -uroot -pPassword123 -t < test_employees_md5.sql

[root@node1 test_db-master]# mysql -uroot -pPassword123 -t < test_employees_md5.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
±---------------------+
| INFO |
±---------------------+
| TESTING INSTALLATION |
±---------------------+
±-------------±-----------------±---------------------------------+
| table_name | expected_records | expected_crc |
±-------------±-----------------±---------------------------------+
| departments | 9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_emp | 331603 | ccf6fe516f990bdaa49713fc478701b7 |
| dept_manager | 24 | 8720e2f0853ac9096b689c14664f847e |
| employees | 300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| salaries | 2844047 | fd220654e95aea1b169624ffe3fca934 |
| titles | 443308 | bfa016c472df68e70a03facafa1bc0a8 |
±-------------±-----------------±---------------------------------+
±-------------±-----------------±---------------------------------+
| table_name | found_records | found_crc |
±-------------±-----------------±---------------------------------+
| departments | 9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_emp | 331603 | ccf6fe516f990bdaa49713fc478701b7 |
| dept_manager | 24 | 8720e2f0853ac9096b689c14664f847e |
| employees | 300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| salaries | 2844047 | fd220654e95aea1b169624ffe3fca934 |
| titles | 443308 | bfa016c472df68e70a03facafa1bc0a8 |
±-------------±-----------------±---------------------------------+
±-------------±--------------±----------+
| table_name | records_match | crc_match |
±-------------±--------------±----------+
| departments | OK | ok |
| dept_emp | OK | ok |
| dept_manager | OK | ok |
| employees | OK | ok |
| salaries | OK | ok |
| titles | OK | ok |
±-------------±--------------±----------+
±-----------------+
| computation_time |
±-----------------+
| 00:00:48 |
±-----------------+
±--------±-------+
| summary | result |
±--------±-------+
| CRC | OK |
| count | OK |
±--------±-------+

到此，Mysql8.0安装与创建测试数据已经完成

三.窗口函数的用法举例

窗口函数是一种强大的数据分析工具，允许在数据集的特定"窗口"（数据子集）内执行计算，同时保留原始行数据。以下是其核心使用方法和场景总结：

一、窗口函数分类及常用函数
序号函数：

ROW_NUMBER()：生成连续唯一序号（无并列），如1,2,3。
SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM students;

RANK()：允许并列但序号跳跃（如1,1,3）。

DENSE_RANK()：允许并列且序号连续（如1,1,2）。
分布函数：

PERCENT_RANK()：计算百分比排名（范围[0,1]）。
CUME_DIST()：计算累积分布值（如小于等于当前值的行占比）。
前后函数：

LAG(expr, N)：获取当前行前第 N 行的值。
LEAD(expr, N)：获取当前行后第 N 行的值。
SELECT order_date, LAG(amount, 1) OVER (ORDER BY date) AS prev_amount FROM orders;

二、基本语法结构
函数名([参数]) OVER (
[PARTITION BY 分组字段]
[ORDER BY 排序字段]
[ROWS/RANGE 窗口范围]
)
PARTITION BY：按字段分组，窗口函数在组内独立计算（类似GROUP BY，但保留所有行）。
ORDER BY：定义窗口内的排序规则。
窗口范围：
ROWS：基于物理行数（如ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING包含前一行、当前行、后一行）。
RANGE：基于逻辑值范围（如按时间差值动态扩展窗口）。

实验部分：基于员工部门表dept_emp，以及薪水表salaries进行测试

//UNBOUNDED PRECEDING表示边界永远为第一行：基于第一行进行累加
SELECT tmp.emp_no,tmp.dept_no, tmp.salary,
ROW_NUMBER() OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary DESC) AS row_num,
DENSE_RANK() OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary DESC) AS dept_rank,
SUM(tmp.salary) OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary ROWS UNBOUNDED PRECEDING) AS salary_total
FROM (select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10) tmp;

root@db 02:28: [employees]> SELECT tmp.emp_no,tmp.dept_no, tmp.salary,
-> ROW_NUMBER() OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary DESC) AS row_num,
-> DENSE_RANK() OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary DESC) AS dept_rank,
-> SUM(tmp.salary) OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary ROWS UNBOUNDED PRECEDING) AS salary_total
-> FROM (select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10) tmp;
±-------±--------±-------±--------±----------±-------------+
| emp_no | dept_no | salary | row_num | dept_rank | salary_total |
±-------±--------±-------±--------±----------±-------------+
| 10001 | d005 | 60117 | 10 | 10 | 60117 |
| 10001 | d005 | 62102 | 9 | 9 | 122219 |
| 10001 | d005 | 66074 | 8 | 8 | 188293 |
| 10001 | d005 | 66596 | 7 | 7 | 254889 |
| 10001 | d005 | 66961 | 6 | 6 | 321850 |
| 10001 | d005 | 71046 | 5 | 5 | 392896 |
| 10001 | d005 | 74333 | 4 | 4 | 467229 |
| 10001 | d005 | 75286 | 3 | 3 | 542515 |
| 10001 | d005 | 75994 | 2 | 2 | 618509 |
| 10001 | d005 | 76884 | 1 | 1 | 695393 |
±-------±--------±-------±--------±----------±-------------+
10 rows in set (0.00 sec)

//CURRENT ROW表示获取当前行记录，也就是边界是当前行，等值关系
SELECT tmp.emp_no,tmp.dept_no, tmp.salary,
ROW_NUMBER() OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary DESC) AS row_num,
DENSE_RANK() OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary DESC) AS dept_rank,
SUM(tmp.salary) OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary range current row) AS salary_total
FROM (select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10) tmp;

root@db 02:28: [employees]> SELECT tmp.emp_no,tmp.dept_no, tmp.salary,
-> ROW_NUMBER() OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary DESC) AS row_num,
-> DENSE_RANK() OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary DESC) AS dept_rank,
-> SUM(tmp.salary) OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary range current row) AS salary_total
-> FROM (select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10) tmp;
±-------±--------±-------±--------±----------±-------------+
| emp_no | dept_no | salary | row_num | dept_rank | salary_total |
±-------±--------±-------±--------±----------±-------------+
| 10001 | d005 | 60117 | 10 | 10 | 60117 |
| 10001 | d005 | 62102 | 9 | 9 | 62102 |
| 10001 | d005 | 66074 | 8 | 8 | 66074 |
| 10001 | d005 | 66596 | 7 | 7 | 66596 |
| 10001 | d005 | 66961 | 6 | 6 | 66961 |
| 10001 | d005 | 71046 | 5 | 5 | 71046 |
| 10001 | d005 | 74333 | 4 | 4 | 74333 |
| 10001 | d005 | 75286 | 3 | 3 | 75286 |
| 10001 | d005 | 75994 | 2 | 2 | 75994 |
| 10001 | d005 | 76884 | 1 | 1 | 76884 |
±-------±--------±-------±--------±----------±-------------+
10 rows in set (0.01 sec)

//UNBOUNDED FOLLOWING 表示边界永远为最后一行
SELECT tmp.emp_no,tmp.dept_no, tmp.salary,
ROW_NUMBER() OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary DESC) AS row_num,
DENSE_RANK() OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary DESC) AS dept_rank,
SUM(tmp.salary) OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary ROWS BETWEEN UNBOUNDED preceding and UNBOUNDED FOLLOWING) AS salary_total
FROM (select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10) tmp;

root@db 02:39: [employees]> SELECT tmp.emp_no,tmp.dept_no, tmp.salary,
-> ROW_NUMBER() OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary DESC) AS row_num,
-> DENSE_RANK() OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary DESC) AS dept_rank,
-> SUM(tmp.salary) OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary ROWS BETWEEN UNBOUNDED preceding and UNBOUNDED FOLLOWING) AS salary_total
-> FROM (select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10) tmp;
±-------±--------±-------±--------±----------±-------------+
| emp_no | dept_no | salary | row_num | dept_rank | salary_total |
±-------±--------±-------±--------±----------±-------------+
| 10001 | d005 | 60117 | 10 | 10 | 695393 |
| 10001 | d005 | 62102 | 9 | 9 | 695393 |
| 10001 | d005 | 66074 | 8 | 8 | 695393 |
| 10001 | d005 | 66596 | 7 | 7 | 695393 |
| 10001 | d005 | 66961 | 6 | 6 | 695393 |
| 10001 | d005 | 71046 | 5 | 5 | 695393 |
| 10001 | d005 | 74333 | 4 | 4 | 695393 |
| 10001 | d005 | 75286 | 3 | 3 | 695393 |
| 10001 | d005 | 75994 | 2 | 2 | 695393 |
| 10001 | d005 | 76884 | 1 | 1 | 695393 |
±-------±--------±-------±--------±----------±-------------+
10 rows in set (0.00 sec)

//EXPR PRECEDING/FOLLOWING 表示带表达式的边界。

SELECT tmp.emp_no,tmp.dept_no, tmp.salary,
SUM(tmp.salary) OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary ROWS 1 PRECEDING) AS salary_total
FROM (select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10) tmp;

root@db 01:55: [employees]> select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10
-> ;
±-------±--------±-------+
| emp_no | dept_no | salary |
±-------±--------±-------+
| 10001 | d005 | 60117 |
| 10001 | d005 | 62102 |
| 10001 | d005 | 66074 |
| 10001 | d005 | 66596 |
| 10001 | d005 | 66961 |
| 10001 | d005 | 71046 |
| 10001 | d005 | 74333 |
| 10001 | d005 | 75286 |
| 10001 | d005 | 75994 |
| 10001 | d005 | 76884 |
±-------±--------±-------+
10 rows in set (0.01 sec)

root@db 01:55: [employees]> SELECT tmp.emp_no,tmp.dept_no, tmp.salary,
-> SUM(tmp.salary) OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary ROWS 1 PRECEDING) AS salary_total
-> FROM (select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10) tmp;
±-------±--------±-------±-------------+
| emp_no | dept_no | salary | salary_total |
±-------±--------±-------±-------------+
| 10001 | d005 | 60117 | 60117 |
| 10001 | d005 | 62102 | 122219 |
| 10001 | d005 | 66074 | 128176 |
| 10001 | d005 | 66596 | 132670 |
| 10001 | d005 | 66961 | 133557 |
| 10001 | d005 | 71046 | 138007 |
| 10001 | d005 | 74333 | 145379 |
| 10001 | d005 | 75286 | 149619 |
| 10001 | d005 | 75994 | 151280 |
| 10001 | d005 | 76884 | 152878 |
±-------±--------±-------±-------------+
10 rows in set (0.36 sec)

上面的查询表达的意思是基于分组内每行记录和它上一条记录求和，不累加。

//求每行和它上面两行的和：

SELECT tmp.emp_no,tmp.dept_no, tmp.salary,
SUM(tmp.salary) OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary ROWS 2 PRECEDING) AS salary_total
FROM (select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10) tmp;

root@db 01:57: [employees]> SELECT tmp.emp_no,tmp.dept_no, tmp.salary,
-> SUM(tmp.salary) OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary ROWS 2 PRECEDING) AS salary_total
-> FROM (select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10) tmp;
±-------±--------±-------±-------------+
| emp_no | dept_no | salary | salary_total |
±-------±--------±-------±-------------+
| 10001 | d005 | 60117 | 60117 |
| 10001 | d005 | 62102 | 122219 |
| 10001 | d005 | 66074 | 188293 |
| 10001 | d005 | 66596 | 194772 |
| 10001 | d005 | 66961 | 199631 |
| 10001 | d005 | 71046 | 204603 |
| 10001 | d005 | 74333 | 212340 |
| 10001 | d005 | 75286 | 220665 |
| 10001 | d005 | 75994 | 225613 |
| 10001 | d005 | 76884 | 228164 |
±-------±--------±-------±-------------+
10 rows in set (0.00 sec)

//求每行的前两行和后面四行相加的结果

SELECT tmp.emp_no,tmp.dept_no, tmp.salary,
SUM(tmp.salary) OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary ROWS between 2 PRECEDING and 4 FOLLOWING) AS salary_total
FROM (select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10) tmp;

root@db 02:00: [employees]> SELECT tmp.emp_no,tmp.dept_no, tmp.salary,
-> SUM(tmp.salary) OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary ROWS between 2 PRECEDING and 4 FOLLOWING) AS salary_total
-> FROM (select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10) tmp;
±-------±--------±-------±-------------+
| emp_no | dept_no | salary | salary_total |
±-------±--------±-------±-------------+
| 10001 | d005 | 60117 | 321850 |
| 10001 | d005 | 62102 | 392896 |
| 10001 | d005 | 66074 | 467229 |
| 10001 | d005 | 66596 | 482398 |
| 10001 | d005 | 66961 | 496290 |
| 10001 | d005 | 71046 | 507100 |
| 10001 | d005 | 74333 | 440504 |
| 10001 | d005 | 75286 | 373543 |
| 10001 | d005 | 75994 | 302497 |
| 10001 | d005 | 76884 | 228164 |
±-------±--------±-------±-------------+
10 rows in set (0.00 sec)

RANGE PRECEDING/FOLLOWING
表示当前行值范围内的分组记录。ROWS对应的是行号，RANGE对应的行值。

计算逻辑是：计算当前行salary减去1的值，如果计算的值在结果集中不存在，此时salary_total的值为salary当前值，如果计算的值在结果集中存在，此时salary_total的值为salary当前值加上【salary的值减去1】。

SELECT tmp.emp_no,tmp.dept_no, tmp.salary,
SUM(tmp.salary) OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary RANGE 1 PRECEDING) AS salary_total
FROM (select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10) tmp;

root@db 02:13: [employees]> SELECT tmp.emp_no,tmp.dept_no, tmp.salary, SUM(tmp.salary) OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary RANGE 1 PRECEDING) AS salary_total FROM (select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10) tmp;
±-------±--------±-------±-------------+
| emp_no | dept_no | salary | salary_total |
±-------±--------±-------±-------------+
| 10001 | d005 | 60117 | 60117 |
| 10001 | d005 | 62102 | 62102 |
| 10001 | d005 | 66074 | 66074 |
| 10001 | d005 | 66596 | 66596 |
| 10001 | d005 | 66961 | 66961 |
| 10001 | d005 | 71046 | 71046 |
| 10001 | d005 | 74333 | 74333 |
| 10001 | d005 | 75286 | 75286 |
| 10001 | d005 | 75994 | 75994 |
| 10001 | d005 | 76884 | 76884 |
±-------±--------±-------±-------------+
10 rows in set (0.00 sec)

//RANGE between 1 PRECEDING and 1 FOLLOWING:对每行值-1和+1后对应的值进行求和
计算逻辑：计算当前行值-1和+1后对应的值，如果在结果集中都不存在，则salary_total的值取salary的当前值；
如果在结果集中存在，则salary_total的值取salary的当前值+对应的值。

SELECT tmp.emp_no,tmp.dept_no, tmp.salary,
SUM(tmp.salary) OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary RANGE between 1 PRECEDING and 1 FOLLOWING ) AS salary_total
FROM (select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10) tmp;

root@db 02:13: [employees]> SELECT tmp.emp_no,tmp.dept_no, tmp.salary,
-> SUM(tmp.salary) OVER (PARTITION BY tmp.dept_no ORDER BY tmp.salary RANGE between 1 PRECEDING and 1 FOLLOWING ) AS salary_total
-> FROM (select de.emp_no,de.dept_no,sa.salary from dept_emp de, salaries sa where de.emp_no=sa.emp_no and dept_no=‘d005’ limit 10) tmp;
±-------±--------±-------±-------------+
| emp_no | dept_no | salary | salary_total |
±-------±--------±-------±-------------+
| 10001 | d005 | 60117 | 60117 |
| 10001 | d005 | 62102 | 62102 |
| 10001 | d005 | 66074 | 66074 |
| 10001 | d005 | 66596 | 66596 |
| 10001 | d005 | 66961 | 66961 |
| 10001 | d005 | 71046 | 71046 |
| 10001 | d005 | 74333 | 74333 |
| 10001 | d005 | 75286 | 75286 |
| 10001 | d005 | 75994 | 75994 |
| 10001 | d005 | 76884 | 76884 |
±-------±--------±-------±-------------+
10 rows in set (0.00 sec)