over窗口函数进阶

over窗口函数的其他灵活的用法。即，统计当前行的前N行及后N行数据。转自：https://blog.csdn.net/ck3207/article/details/84954511
先来看一下数据的组成：

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

结果为：

1 3      1
2 ck2    2
3 ck2    3
4 ck3    3
5 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

1 SELECT name, SUM("ID") over(ORDER BY "ID"  ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM CLIENT;
2 
3 3      1
4 ck2    3
5 ck2    5
6 ck3    6
7 ck4    7

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

 

ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING
1 SELECT name, SUM("ID") over(ORDER BY "ID"  ROWS BETWEEN CURRENT ROW AND 1 following) FROM CLIENT;
2 
3 3      3
4 ck2    5
5 ck2    6
6 ck3    7
7 ck4    4

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

 

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
1 SELECT name, SUM("ID") over(ORDER BY "ID"  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM CLIENT;
2 
3 3      3
4 ck2    6
5 ck2    8
6 ck3    10
7 ck4    7

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

 

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 1 SELECT name, SUM("ID") over(ORDER BY "ID"  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM CLIENT;
 2 
 3 3      1
 4 ck2    3
 5 ck2    6
 6 ck3    9
 7 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

 1 SELECT name, SUM("ID") over(ORDER BY "ID"  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM CLIENT;
 2 
 3 3      13
 4 ck2    13
 5 ck2    13
 6 ck3    13
 7 ck4    13

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


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

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

 

posted @ 2019-03-16 09:57  Claricre  阅读(458)  评论(0)  编辑  收藏  举报