over窗口函数进阶

over窗口函数的基础部分在上一篇文章中已经介绍过（参考文章：https://blog.csdn.net/ck3207/article/details/84329207）。下面来讲一讲over窗口函数的其他灵活的用法。即，统计当前行的前N行及后N行数据。
先来看一下数据的组成：

SELECT name, "ID" FROM CLIENT order by "ID";

结果为：

3	1
ck2	2
ck2	3
ck3	3
ck4	4

ROWS BETWEEN CURRENT ROW AND CURRENT ROW

SELECT name, SUM("ID") over(ORDER BY "ID"  ROWS BETWEEN CURRENT ROW AND CURRENT ROW) FROM CLIENT;

3	1
ck2	2
ck2	3
ck3	3
ck4	4

此案例下，其实与SELECT name, "ID" FROM CLIENT order by "ID";结果一致。因为数据统计行范围为BETWEEN CURRENT ROW AND CURRENT ROW，即当前行。

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

SELECT name, SUM("ID") over(ORDER BY "ID"  ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM CLIENT;

3	1
ck2	3
ck2	5
ck3	6
ck4	7

此案例下，数据会统计当前行及当前行的前一行数据。PRECEDING为在…之前。

ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING

SELECT name, SUM("ID") over(ORDER BY "ID"  ROWS BETWEEN CURRENT ROW AND 1 following) FROM CLIENT;

3	3
ck2	5
ck2	6
ck3	7
ck4	4

此案例下，数据会统计当前行及当前行的后一行数据。FOLLOWING为在…之后。

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

SELECT name, SUM("ID") over(ORDER BY "ID"  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM CLIENT;

3	3
ck2	6
ck2	8
ck3	10
ck4	7

此案例下，数据会统计当前行、当前行的前一行数据及当前行的后一行数据。

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

SELECT name, SUM("ID") over(ORDER BY "ID"  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM CLIENT;

3	1
ck2	3
ck2	6
ck3	9
ck4	13

此案例下，数据会统计当前行之前的所有数据及当前行的数据。注意此SQL执行结果与SQLSELECT name, SUM("ID") over(ORDER BY "ID" ) FROM CLIENT;某些情况下结果是一致的。但当"ID"有重复值时，案例SQL数据到哪行算到哪行，而SQLSELECT name, SUM("ID") over(ORDER BY "ID" ) FROM CLIENT;会统计当前行前的所有数据及与当前行值一样的所有数据。

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

SELECT name, SUM("ID") over(ORDER BY "ID"  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM CLIENT;

3	13
ck2	13
ck2	13
ck3	13
ck4	13

此案例下，会统计表中所有数据。与SQLSELECT name, SUM("ID") over() FROM CLIENT;结果无异，只是结果的排序略有不同。

说了这么多使用方法，可我还是不知道怎么用啊？假设有这么一种情况，数据库中有每月公司经营的盈亏额。老板想让你计算下每个月基于上一月是盈利还是亏损？盈利/亏损值是多少？那么此时用上一个over函数，分分钟搞定。

select month, sum(balance) over(order by month rows between 1 preceding and current row) from month_profit_table ; // 假设balance盈利为正，亏损为负