On Thu, 2009-03-19 at 18:04 -0700, Yue Sheng wrote:
  

I was wondering if anyone know of other column-based DBs suited to
vase amount of time series data? Tried Googling. It'd be nice to hear
from someone who has any insight on this.
    

Hi,

We are currently demonstrating prototypes of our system, which is a web
based interface similar to igmarkets and CMC markets, that relies on
running time series queries against 3 different backends that we are
currently testing. (monet, lucid and mysql)

The other column based system we are looking at is lucidDB.

The current idea is to go for performance from monet or lucid -
http://www.luciddb.org/ 
or alternatively write some time series custom functions for MySQL.

These guys apparently have the financial time series database market
nailed; http://kx.com/ but they are super expensive to deploy.

I guess I should point out that I don't have a db or CS background, I
work as a tech consultant from a startup incubations perspective, so
forgive me (or indeed correct me where I am wrong...;-)

My understanding is that performance wise, if you are going to run your
analysis of your time series on the app/client side of the database
system, then for technical analysis types of problems, performance is
going to be achieved by getting a time series into a client side data
structure as efficiently/quickly as possible for the app to use. And
monetDB seems to perform very well at that for columns of data.

But that means that a strategy that wants to compare 200day moving
averages across every time series is going to have to get all that data
every time it runs the query, or cache it and summarise back to a store
of aggregate data which has to be written back to a table.

Obviously you can use relevant aggregate SQL queries to improve the
performance, ie SUM(), COUNT(), MAX() etc. 

However ideally, we just want to plug ta-lib or some other technical
analysis lib straight into the aggregate functions of the database. For
example at the moment to do a query like; show me all the stocks that
have declined 40% in 6 months, it would be a join like "select symbol
from stocks s1 join stocks s2 on s1.symbol = s2.symbol where s1.date =
NOW() and s2.date = date_sub(Now(), interval 6 months) and (s1.price <
(.6 * s2.price))
(I just run that one off my head from what i remember about mysql, its
probably wrong)

Anyway, but to do something like compare all stocks against bollinger
bands, or Doji you are going to have to pull down all the data for the
time series you want to compare, or indeed re-implement bollinger or
some technical indicator from ta-lib in SQL.

So my point is, that it would be cool to have a extendible UDF system
that would allow us to plugin a ta-lib library into user defined
functions for example;

"Select symbol from stocks group by symbol having Today(price) < (.6
(DateAgo( interval 6 months, price )  ))"

or even better, 

"Select symbol from stocks group by symbol having
Today(MovingAverage(price)) < (.6 (DateAgo( interval 6 months,
MovingAverage(price,50 days) )  ))"

or even better better;

"Select symbol from stocks group by symbol having
Today(CallTaLib("Chande Momentum Oscillator",price)) < (.6
(DateAgo( interval 6 months, CallTAlib("Chande Momentum
Oscillator",price,50 days) )  ))"

so just have an aggregate function that wraps some time series libraries
into aggregate functions.

Oh, and having the moon on a stick would be nice as well!!!!

Cheers,

Tom













------------------------------------------------------------------------------
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
easily build your RIAs with Flex Builder, the Eclipse(TM)based development
software that enables intelligent coding and step-through debugging.
Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users