CODEWITHSUNDEEP
sqlpostgresqltime-seriescurve
blue01
blue01

Reputation: 2095

Detect trends in time series

I have a psql database with time series values for various stocks. I want to find, if the stock value increased by lets say 50% in last 45 days. I want to detect such sudden changes in the curve. Right now, my focus is on increasing values. Mostly quadratic curves, although it would be nice to find linear/logarithmic increases too.

I will get to sudden drops later. Any pointers into how I detect such patterns in the curve? Is it possible to do it in sql? I am also open to learning any mathematical analysis tools or languages.

Upvotes: 4

Views: 2215

Answers (1)

Rob Taylor
Rob Taylor

Reputation: 604

To get that kind of information, there are many approaches that can be taken. If this report is going to be run every once in a while, then you could have a SQL query that will join the table to itself, on an older date. Assuming you have a date, stock_ticker, and price as the fields, a query could look something like this:

         SELECT base.date, 
                base.stock_ticker, 
                ((base.price - old.price) / old.price) as gain 
           FROM stocks as base
LEFT OUTER JOIN stocks as old
             ON base.stock_ticker = old.stock_ticker
            AND base.date = old.date + cast('45 days' as interval)

This will give you a table with the date, the stock ticker, and how much the price has gone up since the price 45 days ago. With this, you can use it as a sub-query, alias it with a WITH clause, or do other work on it.

If you weren't looking for just a ratio over a time period, you can use the same technique to get daily averages, and with that data find a moving average.

Upvotes: 2

Related Questions