CODEWITHSUNDEEP
sql
Anit Gandhi
Anit Gandhi

Reputation: 125

Calculating Percent Change between two rows

I understand that this has been asked already. However, I am relatively new at SQL and MySQL, so I was confused by the other answers.

Say I have a table of historical financial data, and I have multiple records in place.

Date       | Close Price | % Change
2014-03-25 |    3.58     |   ?
2014-03-24 |    3.57     |   ?
2014-03-21 |    3.61     |   ?

I have the date and close price in the table. I would like to find the % change from day to day. For example, from 2014-03-24 to 2014-03-25 is about +0.28%.

I'm completely stuck on how to do this for all records without running a large number of individual queries. I'm thinking it's some sort of join, but this is where I'm confused as I have not done these before.

Upvotes: 3

Views: 33477

Answers (5)

BigBen_Davy
BigBen_Davy

Reputation: 17

Not enough rep to write a comment, but I believe the percent change formula in user @Kickstart's accepted answer is incorrect.

Using the formula in the hyperlink, the query should be:

SELECT x.Date, x.Close_Price, (((x.Close_Price - y.Close_Price) / x.Close_Price)) * 100) AS '% Change'
FROM 
(
    SELECT a.Date AS aDate, MAX(b.Date) AS aPrevDate
    FROM SomeTable a
    INNER JOIN SomeTable b
    WHERE a.Date > b.Date
    GROUP BY a.Date
) Sub1
INNER JOIN SomeTable x ON Sub1.aDate = x.Date
INNER JOIN SomeTable y ON Sub1.aPrevDate = y.Date
ORDER BY x.Close_Price DESC

Upvotes: 0

Diego Andres M
Diego Andres M

Reputation: 1

SELECT

(("CurrentValueRow" /LAG("CurrentValueRow",1) OVER(ORDER BY "AltRowForOrder"))-1)*100 AS PercentualChange

FROM "YourTable"

Upvotes: 0

joeblog
joeblog

Reputation: 1225

Postgresql has a handy window function 

lag(value anyelement [, offset integer [, default anyelement ]])

which simplifies this problem (as I just discovered myself) to

SELECT 'Date',
       'Close Price',
       (('Close Price'/lag('Close Price', 1) OVER (ORDER BY 'Date')) - 1) * 100 AS percentage_change
FROM treasury
ORDER BY 'Date';

Upvotes: 3

Kickstart
Kickstart

Reputation: 21513

Something like this would do it I think

SELECT x.Date, x.Close_Price, (((x.Close_Price / y.Close_Price) - 1) * 100) AS '% Change'
FROM 
(
    SELECT a.Date AS aDate, MAX(b.Date) AS aPrevDate
    FROM SomeTable a
    INNER JOIN SomeTable b
    WHERE a.Date > b.Date
    GROUP BY a.Date
) Sub1
INNER JOIN SomeTable x ON Sub1.aDate = x.Date
INNER JOIN SomeTable y ON Sub1.aPrevDate = y.Date
ORDER BY x.Close_Price DESC

This will cope when the days are not one after the other (eg, missing records for non processing days).

The sub query gets each date, and the max date that is less than it. This is then joined against the table to get the full records for that date and the previous one, and then the calculation can be done.

EDIT - and update based on this SELECT:-

UPDATE treasury x
INNER JOIN 
(
    SELECT a.Date AS aDate, MAX(b.Date) AS aPrevDate
    FROM treasury a
    INNER JOIN treasury b
    WHERE a.Date > b.Date
    GROUP BY a.Date
) Sub1 ON Sub1.aDate = x.Date
INNER JOIN treasury y ON Sub1.aPrevDate = y.Date
SET x.PercentChange = (((x.Close_Price / y.Close_Price) - 1) * 100)

Upvotes: 7

user3248346
user3248346

Reputation: 

Select S1.Date as Today, (S1.ClosePrice - S2.ClosePrice) / S2.ClosePrice as Change 
FROM Stock as S1 INNER JOIN Stock as S2 
ON S1.Date = (S2.Date+1)

Upvotes: 3

Related Questions