How to calculate mysql query, a difference between the two closest rows in the column?


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
27 views
table `nspcl`

----------
id | partnum
----------
1 | 7079626
2 | 7079623
3 | 7079593
4 | 7079552
.....

we need to calculate the difference between the nearest end, that is
7079552 - 7079593 = -41
7079593 - 7079623 = -30
7079623 - 7079626 = -3

is it possible to implement this mysql query?
by | 27 views

5 Answers

0 like 0 dislike
You can use window functions

SELECT partnum, LAG(partnum) OVER w AS 'prev_partnum', partnum - LAG(partnum) OVER w AS 'diff' FROM nspcl WINDOW w AS (ORDER BY id);
by
0 like 0 dislike
If you have id without gaps go, just joinin to the sample id-1. If missing, it is a little more complicated the request is.

So if the ID in order, for example:

SELECT t2.partnum p2, t1.partnum p1, t2.partnum - t1.partnum res FROM nspcl t1 Nspcl INNER JOIN t2 ON t1.id = (t2.id - 1)
by
0 like 0 dislike
What version of MySql ? If 8, then using window functions the task is solved on time
\rhttps://dev.mysql.com/doc/refman/8.0/en/window-fun...

Approximately

SELECT t1.num, t2.num, t2.num is a t1.num FROM ( SELECT num, ROW_NUMBER() OVER(ORDER BY num) AS rn FROM table ) AS t1 LEFT JOIN ( SELECT num, ROW_NUMBER() OVER(ORDER BY num) AS rn FROM table ) AS t2 ON t1.rn=t2.rn-1


I must say that performance is not checked and written in the syntax of MSSQL, just outline the idea
by
0 like 0 dislike
"two ends, two rings, in the middle of the stud" - "points to the boob nailed"


you can count only muscul doesn't know about the ends nothing, so the query need an ordered sequence (well ID which autoincrements) and a three-story expression

with a competent ID and can be shorter
by
0 like 0 dislike
Thanks a lot to all for the help!
by

Related questions

0 like 0 dislike
2 answers
0 like 0 dislike
2 answers
0 like 0 dislike
4 answers
0 like 0 dislike
1 answer
110,608 questions
257,187 answers
0 comments
40,796 users