SQL implements addition, subtraction, multiplication and division operations on two adjacent rows of data

SQL implements addition, subtraction, multiplication and division operations on two adjacent rows of data after sorting the data in a certain order and grouping them by certain fields.

Ideas:

1: First group and sort the table data and then label them with serial numbers

2: Add/subtract one to the tag field as needed

Above code:

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<>'';

The table data is as follows:

The first column is phone, the second column is time ts, and you are required to calculate the time it takes for each two adjacent data of the same phone

The result is as shown below:

The third column is in seconds.

Supplementary knowledge: SQL implements the current row equal to the sum of the previous two rows of data

SQL implements a function similar to the Fibonacci sequence, that is, the current data is equal to the sum of the previous two data. See the example in this article for details.

Original table:

SQL statement (be familiar with the usage of JION ON here)

result

The above SQL to implement addition, subtraction, multiplication and division operations of two adjacent rows of data is all the content that the editor shares with you. I hope it can give you a reference, and I also hope that you will support 123WORDPRESS.COM.