SQL get change in values based on consecutive dates
I want to get the change in Price from day to day. What SQL query will accomplish this?
Original Table
Date Company Price --------------------------- 1/4/2012 Apple 458 1/3/2012 Apple 462 1/2/2012 Apple 451 1/1/2012 Apple 450
Desired Table
Date Company Price Day_Change ------------------------------------- 1/4/2012 Apple 458 -4 1/3/2012 Apple 462 9 1/2/2012 Apple 451 1 1/1/2012 Apple 450 NULL
Answers
Join the table to itself to get yesterday's price for the company, then subtract it from today's price
select
t1.date,
t1.company,
t1.price,
t1.price - t2.price as day_change
from price_table t1
left join price_table t2
on t2.date = subdate(t1.date, 1)
and t2.company = t1.company
After this you can add a normal where clause, eg where t1.date > subdate(current_date(), 7) to get the last seven day's prices
FYI day_change will be NULL if there isn't a row for yesterday's price
Another approach, will work even in non-contiguous dates:
Source data:
CREATE TABLE fluctuate
(Date datetime, Company varchar(10), Price int);
INSERT INTO fluctuate
(Date, Company, Price)
VALUES
('2012-01-04 00:00:00', 'Apple', 458),
('2012-01-03 00:00:00', 'Apple', 462),
('2012-01-02 00:00:00', 'Apple', 451),
('2012-01-01 00:00:00', 'Apple', 450),
('2012-01-01 00:00:00', 'Microsoft', 1),
('2012-01-03 00:00:00', 'Microsoft', 7),
('2012-01-05 00:00:00', 'Microsoft', 5),
('2012-01-07 00:00:00', 'Microsoft', 8),
('2012-01-08 00:00:00', 'Microsoft', 12);
Output:
DATE COMPANY PRICE DAY_CHANGE January, 04 2012 Apple 458 -4 January, 03 2012 Apple 462 11 January, 02 2012 Apple 451 1 January, 01 2012 Apple 450 NULL January, 08 2012 Microsoft 12 4 January, 07 2012 Microsoft 8 3 January, 05 2012 Microsoft 5 -2 January, 03 2012 Microsoft 7 6 January, 01 2012 Microsoft 1 NULL
Query:
select
date,
company,
price,
day_change
from
(
select
case when company <> @original_company then
-- new company detected,
-- reset the original price based on the new company
@original_price := null
end,
f.*,
price - @original_price as day_change,
(@original_price := price),
(@original_company := company)
from fluctuate f
cross join
(
select
@original_price := null,
@original_company := company
from fluctuate
order by company, date limit 1
)
as zzz
order by company, date
) as yyy
order by company, date desc
Source: http://www.sqlfiddle.com/#!2/56de3/3
@Bohemian's answer is correct and will return the price difference since the day before, but practically I suspect you will actually want the price difference since the previous day's trading (which may span weekends, public holidays, etc.).
To accomplish this, one must first use a subquery to determine the last day on which each company traded (individual companies can be suspended, or might be trading in different markets subject to different holidays); then use that (date,company) pair to lookup the last price...
SELECT current.*, current.Price - previous.Price AS Day_Change
FROM (
SELECT yourtable.*, MAX(before.Date) AS prevDate
FROM
yourtable
JOIN yourtable AS before
ON before.Date < yourtable.Date
AND before.Company = yourtable.Company
GROUP BY yourtable.Date, yourtable.Company
) AS current
JOIN yourtable AS previous
ON previous.Date = current.prevDate
AND previous.Company= current.Company