Diff between values in different rows

user_5011497 0
Viewed 59

MariaDB version 10.4.10.

I have a stock scraper script that fetches stock data every hour and inserts it into a MySQL database. The database structure is similar to this:

stocks( time_fetched DATETIME, fetch_id INT, name VARCHAR, price INT )

And some example data:

**time_fetched          fetch_id        name        price**
2020-03-10 09:00:00     1               stock1      10
2020-03-10 09:00:00     1               stock2      15
2020-03-10 10:00:00     2               stock1      12
2020-03-10 10:00:00     2               stock2      20
2020-03-10 11:00:00     3               stock1      8
2020-03-10 11:00:00     3               stock2      18

I want a way to get price change for each stock between, say, stocks fetched at 09:00 and 10:00, or between 09:00 and 11:00. Something like this (pseudo code):

SELECT *, DIFF( current_price, price_one_hour_ago) AS change_1h, DIFF( current_price, price_two_hours_ago) AS change_2h

Is it possible to do this directly in MySQL? I am using a PHP script to display the data, so I might have to resort to do it in PHP instead.

window-functions mysql sql
1 Answers

You can use a CTE to generate prices delayed by 1 or 2 hours, and then compute the changes using those values (potentially using COALESCE to make NULL values into 0):

WITH prices AS (
  SELECT time_fetched, name, price,
         LAG(price, 1) OVER(PARTITION BY name ORDER BY time_fetched) AS price_1h,
         LAG(price, 2) OVER(PARTITION BY name ORDER BY time_fetched) AS price_2h
  FROM stocks
)
SELECT time_fetched, name, price,
       COALESCE(price - price_1h, 0) AS change_1h,
       COALESCE(price - price_2h, 0) AS change_2h
FROM prices

Output:

time_fetched            name    price   change_1h   change_2h
2020-03-10 09:00:00     stock1  10      0           0
2020-03-10 10:00:00     stock1  12      2           0
2020-03-10 11:00:00     stock1  8       -4          -2
2020-03-10 09:00:00     stock2  15      0           0
2020-03-10 10:00:00     stock2  20      5           0
2020-03-10 11:00:00     stock2  18      -2          3

Demo on dbfiddle also showing query results without COALESCE.

user_94737640
Related