How to calculate the value in a row, relative to the previous row?

Question

MySQL

table example.
** id, month, quantity of goods **
and I want to add a column - dynamics, that is, this is the ratio of the current quantity to the previous one so that you can see that the previous quantity was 5, and now 10 - so the indicator should be 2
How to write SQL in this case?

Answer 1

Read this article about window functions . In your case lag.
Here is an example from an article on your topic:

SELECT
    id,
    section,
    header,
    score,
    row_number() OVER w        AS rating,
    lag(score) OVER w - score  AS score_lag
FROM news
WINDOW w AS (ORDER BY score DESC)
ORDER BY score desc;

id | section |  header   | score | rating | score_lag 
----+---------+-----------+-------+--------+-----------
  6 |       2 | Заголовок |    95 |      1 |          
  3 |       4 | Заголовок |    79 |      2 |        16
  8 |       3 | Заголовок |    36 |      3 |        43
  4 |       3 | Заголовок |    36 |      4 |         0
  5 |       2 | Заголовок |    34 |      5 |         2
  7 |       4 | Заголовок |    26 |      6 |         8
  1 |       2 | Заголовок |    23 |      7 |         3
  2 |       1 | Заголовок |     6 |      8 |        17

Answer 2

try to take the last two fields, then print the difference " product quantity "