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.
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.