3

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.

Anit Gandhi
  • 125
  • 1
  • 2
  • 8
  • Honestly I haven't actually executed anything yet. The only thing I figured I'd be able to do is get the values, use PHP to do the calculation, and insert the result. But that would result in probably 2 queries per row, and I have a lot of rows... – Anit Gandhi Mar 31 '14 at 14:40

5 Answers5

7

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)
Kickstart
  • 21,403
  • 2
  • 21
  • 33
  • Sorry for the dumb question, I'm still starting out with SQL. In my case, if my table name is "treasury", would i just replace "SomeTable" with "treasury"? – Anit Gandhi Mar 31 '14 at 15:12
  • Disregard last comment. I replaced the table name, and the proper column names, and this is what I got, 1 result: 2014-03-27 | 3.51 | -1.126759503952346 That is the correct percent change. However, this did not modify the actual table, since there is no UPDATE called. How would I factor that in? – Anit Gandhi Mar 31 '14 at 15:20
  • You can do an update based on this. However what fields do you want to update? – Kickstart Mar 31 '14 at 15:49
  • I want to update the % change column. Also, how do I do it for all the records? As of right now, the query you suggested returns only the first record. – Anit Gandhi Mar 31 '14 at 15:50
3
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)
  • I saw similar answers on other questions. My main confusion was: what is "S1", "S2", etc? – Anit Gandhi Mar 31 '14 at 14:48
  • What does this do? How does it provide the correct answer to the question? Explain what your doing, and how it works. – Jojodmo Mar 31 '14 at 14:48
  • S1 and S2 are table aliases. The query is a self-join. –  Mar 31 '14 at 14:49
  • And sorry, I forgot to mention that the dates aren't completely consecutive since the markets are closed on weekends. – Anit Gandhi Mar 31 '14 at 15:15
  • in that case this query is not general enough. to write something general is much much more work. for example, you would have to know the schedule for each markets, not just weekends, etc. that would have to be in a table somewhere so it can referenced in the query. –  Mar 31 '14 at 15:18
  • to give you an idea, here is the query to work out weekdays only: http://stackoverflow.com/a/252532/3248346. Good luck ;) –  Mar 31 '14 at 15:20
  • You'll want to modify 'Change' to '% Change' as most adapt SQL programmers would realize that Change is a reserved keyword. – jonincanada May 31 '17 at 16:40
  • This is my personal preference, but as a beginner, ALWAYS define your aliases better than S1/S2. Either use the whole table name or something short but meaningful that would describe that table. Trust me, it will create a lot less confusion. – NonProgrammer Aug 25 '17 at 18:13
3

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';
joeblog
  • 1,101
  • 11
  • 17
0

SELECT

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

FROM "YourTable"

Diego Andres M
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 28 '21 at 23:17
0

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
BigBen_Davy
  • 17
  • 1
  • 10