On 09 Apr 2015, at 18:45, Caroptions Caroptions <caroptions@hotmail.com> wrote:

Let me answer my own question. 

The preliminary solution would be:

WITH open(sdate, open, rnk) as (
select sdate, open, row_number() over (partition by sdate order by qtime asc) as rnk from marketdata.quotes where sym='SPY'
),
close as (
select sdate, close, row_number() over (partition by sdate order by qtime desc) as rnk from marketdata.quotes where sym='SPY'
),
hilw(sdate,high,low) as (
select sdate, max(high),min(low) from marketdata.quotes where sym='SPY' group by sdate
)
select open.sdate, open.open, hilw.high, hilw.low, close.close from open,close, hilw where open.rnk=1 and close.rnk=1 and open.sdate=close.sdate and open.sdate=hilw.sdate;

But i suspect this SQL is not very optimal, since I query the same data partition many times. If there is any optimization possible, please let me know.

Windowing functions are indeed what you can use for this case. You can just try how far you can go before the performance will become a problem.

Would it be possible to assign each partition an ID so that you can group by that id and select min/max row_number as open and close?

Regards,
Jennie


Thanks,
John


From: caroptions@hotmail.com
To: users-list@monetdb.org
Subject: first_value/last_value solution in MonetDB
Date: Thu, 9 Apr 2015 04:28:07 +0000

Hi,

Till I am trying to resolve my business problem I discovered the segfault bug which I have reported in MonetDB bug tracking system. 

I believe Monetdb is great product and team is moving in right direction utilizing latests advances in technology.

Nevertheless my "business" problem is to build OHLC Time Series for longer time frame from shorter ohlc time series.

I need partition by interval (5 minutes, 30 minutes, 1 hour, 1 day) and get first record of open, max of high, min of low and last of close from let's say 1 minute ohlc series.  In postgresql there are 2 functions:

first_value and last_value, which allows to do exactly this. 

Are there any function/approach/techniques which could be utilized to have the same results in MonetDB, I could do this aggregation on client side but this way I will not utilize DB potential and mainly the network traffic will be prohibitive for the solution.

I am sure this question is already addressed somewhere, but I couldn't find, the solution should be trivial and pretty standard but I no luck on my side, if you push me in right directly I will really appreciate.

Thank you in advance!
John
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list