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盈利为正,亏损为负