Member Avatar for b89smith

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?

  • 3 Contributors
  • 5 Replies
  • 229 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for ayconsulting

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

Edited by Ezzaral because: Snipped 'fake sig' link. Please restrict such links to your site-wide user signature, which can be edited from the user control panel.
Member Avatar for smantscheff

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.

Member Avatar for b89smith

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`.

Member Avatar for b89smith

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.

Member Avatar for smantscheff 
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.