Question 189449 in Mysql Question posted in
The official MySQL documentation can be found here.

Difference between two rows in Mysql

I need to get the difference of two rows. The issue is that I can get it done but only for the next row, I need the difference for the higher row. So row with id 1686 has 275 purchases and next week account with same id has 277 in 3336 id row, it means that on first week he bought twice to have 277 purchases. Is it possible to this without index column? Because I don’t have rights for this.

Description

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    SELECT id, ak_id, datalaikas, purchases_cnt,
       LEAD(purchases_cnt) OVER (PARTITION BY ak_id ORDER BY id) - purchases_cnt AS diff
FROM ai_clv
WHERE ak_id = 14770
ORDER BY id;

  2. 0

    Using a frame should work:

    SELECT id, ak_id, datalaikas, purchases_cnt,
       NTH_VALUE(purchases_cnt, 2) OVER w - purchases_cnt AS 'diff'
FROM ai_clv
WHERE ak_id = 14770
WINDOW w AS (PARTITION BY ak_id  ORDER BY id 
             ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search