如何使滑动窗口不包含当前行

显示全部楼层 · 2011-5-26 14:53:25

DB2分析函数中的OVER子句中，如何使其不包含当前行？
比如，我有下面的数据：
#姓名部门工资
# ('张三','市场部',2000)
# ('赵红','技术部',2400)
# ('李四','市场部',3000)
# ('李白','技术部',3200)
# ('王五','市场部',4000)
# ('王蓝','技术部',5000)
我想实现：对于当前行，求前两行工资之和。比如：对于李四来说，求其前两行工资之和：2000+2400=4400(但不包括当前行李四的工资)。
拜托各位了，望各位高手多多指教！

千问 · 2011-5-26 14:53:25

over（ ... RANGE BETWEEN .. and...）

千问 · 2011-5-26 14:53:25

原帖由 zhangzongjun 于 2010-2-8 23:53 发表
over（ ... RANGE BETWEEN .. and...）
不懂，能说的详细些吗？

千问 · 2011-5-26 14:53:25

.-RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING----------------------.
>--+--------------------------------------------------------------------------------+-->
|
.-RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW-. |
'-| window-order-clause |--+---------------------------------------------------+-'

'-| window-aggregation-group-clause |---------------'
window-aggregation-group-clause
|--+-ROWS--+--+-| group-start |---+-----------------------------|
'-RANGE-'+-| group-between |-+

'-| group-end |-----'
group-start
|--+-UNBOUNDED PRECEDING----------+-----------------------------|
+-unsigned-constant--PRECEDING-+
'-CURRENT ROW------------------'
group-between
|--BETWEEN--| group-bound1 |--AND--| group-bound2 |-------------|
group-bound1
|--+-UNBOUNDED PRECEDING----------+-----------------------------|
+-unsigned-constant--PRECEDING-+
+-unsigned-constant--FOLLOWING-+
'-CURRENT ROW------------------'
group-bound2
|--+-UNBOUNDED FOLLOWING----------+-----------------------------|
+-unsigned-constant--PRECEDING-+
+-unsigned-constant--FOLLOWING-+
'-CURRENT ROW------------------'
group-end
|--+-UNBOUNDED FOLLOWING----------+-----------------------------|
'-unsigned-constant--FOLLOWING-'

千问 · 2011-5-26 14:53:25

http://publib.boulder.ibm.com/in ... c/doc/r0023461.html

千问 · 2011-5-26 14:53:25

http://blog.csdn.net/违规广告erds/archive/2009/10/28/4738768.aspx 看看这个文章的例子