SQL如何現(xiàn)相鄰兩行數(shù)據(jù)加減乘除

發(fā)布時(shí)間：2020-10-26 14:31:17 來(lái)源：億速云閱讀：909 作者：Leah 欄目：開(kāi)發(fā)技術(shù)

這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)?lái)有關(guān)SQL如何現(xiàn)相鄰兩行數(shù)據(jù)加減乘除，文章內(nèi)容豐富且以專(zhuān)業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

SQL實(shí)現(xiàn)表里數(shù)據(jù)按一定順序排序后，按某幾個(gè)字段分組后相鄰兩行數(shù)據(jù)實(shí)現(xiàn)加減乘除運(yùn)算。

思路：

1：先把表數(shù)據(jù)分組排序后打上序號(hào)標(biāo)簽

2：根據(jù)需求把標(biāo)簽字段加/減一

上代碼：

select distinct a.phone,from_unixtime(cast(floor(a.ts/1000) as bigint),'yyyyMMdd HH:mm:ss'),cha
from table a
join 
(
 select a.phone,a.ts,abs(a.ts-b.ts)/1000 cha
 from (select phone,ts,row_number() over(partition by phone order by ts ) rank from table) a
 left join
 ( select phone,ts,rank-1 as rank from (select phone,ts,row_number() over(partition by phone order by ts ) rank from table) a ) b
 on a.phone = b.phone and a.rank = b.rank
) b
on a.phone = b.phone and a.ts = b.ts
where a.phone is not null and a.phone<>'';

表數(shù)據(jù)如圖：