I have a database that is filled with time series data points on different items. I would like to calculate the difference between values by item for the time series and then store it in a difference column. My table is as follows:

id | Ticker | Date | SO | difference
1 | QQQQ | 2011-01-03 | 200000 | 0
2 | QQQQ | 2011-01-04 | 200100 | 100
3 | QQQQ | 2011-01-05 | 201000 | 900
4 | PWC | 2011-01-03 | 180000 | 0
5 | PWC | 2011-01-04 | 180500 | 500
6 | PWC | 2011-01-05 | 181000 | 500


I would like to calculate the difference column when ever new points are added. How would you recommend to do this?

Recommended Answers

All 5 Replies

Depends on the database type you're using you can add a column to your table that will calculate the values you need on the fly. Essentially, you can tell the database that the value of that column is equal to the difference between the other two and it will do all the work. SQL Server uses "Computed Columns", Oracle has "Virtual Columns", Access has "Calculated Columns". You can find the appropriate type for your database with minimal Googling.

The one downside to this approach is that it may use and reuse resources to perform these calculations, and especially when scalability is concerned may cause a performance hazard. If this is the case, consider computing it upon insert and update and having a static field there (if you want to search on it still, for example).

Hope this helps!

- Anton @ AYConsulting

Use a view which shows your calculated column in real-time instead of calculating it beforehand. If you cannot do this (maybe for performance reasons) use a BEFORE INSERT and BEFORE UPDATE trigger which calculates the difference field.

I know about the options between computing the column on the fly and storing it. I want to store it since this column is called much more frequently than it is updated. The question I have is how to calculate it when the id number is not necessarily sequential. All I know is that I order by `Ticker`, `Date`.

For instance how would I calculate the difference column below?

id | Ticker | Date | SO | difference
100 | QQQQ | 2011-01-03 | 200000 | 0
21 | QQQQ | 2011-01-04 | 200100 | 100
36 | QQQQ | 2011-01-05 | 201000 | 900
34 | PWC | 2011-01-03 | 180000 | 0
51 | PWC | 2011-01-04 | 180500 | 500
6 | PWC | 2011-01-05 | 181000 | 500

I only know that I sort the columns by `Ticker`, `Date`. Basically I need to find a way to get the value of the cell above the current row where the tickers eqal and the last date < the current date.

SELECT a.SO - b.SO as diff
FROM mytable a, mytable b
WHERE a.ticker=b.ticker 
AND a.date = (SELECT min(date) from mytable c where b.ticker=c.ticker and c.date > b.date)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.