我试图创建一个新列Diff
,其中包含名为的同一列的不同行之间的差异Rep
,这是一个整数。
我的表如下所示:
------------------------
security_ID | Date | Rep
------------------------
2256 |202001| 0
2257 |202002| 1
2258 |202003| 2
2256 |202002| 3
2256 |202003| 5
对于一个特定的security_ID
我想得到的差异是,Rep
如果Date
它们是整数,则相差1(例如202002-202001 = 1)。例如,我希望输出为:
-------------------------------
security_ID | Date | Rep | Diff
-------------------------------
2256 |202001| 0 | 0
2257 |202002| 1 | 1
2258 |202003| 2 | 2
2256 |202002| 3 | 3
2256 |202003| 5 | 2
最后一行是Diff
2,因为Date
对于security_ID
2256 ,计算将为5-3(分别针对202003和202002)。
编辑:因为Sybase没有,LAG()
我尝试了以下操作:
SELECT security_ID, Date, Rep,
MIN(Rep) OVER (PARTITION BY Date, security_ID rows between current row and 1 following) - Rep as "Diff"
from
my_table
但这并没有给我正确的答案。例如,Diff
根据上述情况,上面的最后一行和倒数第二行的差为0。
谢谢
假设date
列始终按升序排列,我们可以将其left join
与self一起使用,并带入先前的rep
值,然后在外部计算差异。如,
select security_id,dt,rep,(rep-prev_rep) diff
from
(
select t1.security_id,t1.dt,t1.rep,
coalesce(t2.rep,0) prev_rep
from mytable t1
left join mytable t2
on t1.security_id = t2.security_id
and t2.dt = t1.dt - 1
)
order by rep;
编辑:解决OP的查询尝试
如果可以使用显示的窗口功能,则可以按以下方式修改查询,
select security_id
, dt
, rep
, (rep-coalesce(max(rep) over (partition by security_id order by dt rows between unbounded preceding and 1 preceding),0)) diff
from mytable;
order by rep
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句