Sorry, I meant "position" in the sense of "inventory of a security or other
tradeable instrument" (and such inventories can be negative).
So in the simplest sense, I have a SQL table with 2 columns: timestamp and
value. What I'm looking for is something like:
(daynumber TIMESTAMPS) (+/ % #)/. VALUES
though that's mocked-up J I haven't tested (daynumber is a monadic verb
which converts a float in seconds-since-epoch into an integer representing a
specific day).
You approach looks reasonable and much simpler than the examples I've dug up
on the web, eg
http://download.oracle.com/docs/cd/B28359_01/server.111/b28314/tdpdw_sql.htm
#CIHFCAEA
I'll give it a shot. Thank you.
-Dan
-----Original Message-----
From: [email protected] [mailto:[email protected]] On
Behalf Of Raul Miller
Sent: Tuesday, April 19, 2011 2:43 PM
To: Chat forum
Subject: Re: [Jchat] Help with SQL
On Tue, Apr 19, 2011 at 1:12 PM, Dan Bron <[email protected]> wrote:
> I have some historical data based on positions and PnL stored in an
Oracle
> XE database. I need a little help with a SQL query.
>
> Positions are captured every 15 minutes along with an associated timestamp
> (stored as a single number, seconds-since-Epoch). How can I calculate the
> average position for each day? I know it's a basic question, but I'm not
a
> SQL guy.
What is a position?
And, what is an average position?
For example, let us say that Position was an x and y coordinate. And
that by average, you meant "average x" and "average y".n
And let's also suppose your table is named T.
Then:
select sum(x)/count(*) as averageX, sum(y)/count(*) as average Y
from T
group by TO_DATE(timestamp, 'YYYY-MM-DD')
That said, note that I am not sure of exactly how to convert oracle
timestamps to oracle dates. The above is based on a quick search but
I might have gotten something wrong.
Also, if oracle balks at that group by statement, you will need to get
a bit more verbose:
select sum(x)/count(*) as averageX, sum(y)/count(*) as average Y
from (select x, y, TO_DATE(timestamp, 'YYYY-MM-DD') as date from T) as
whatever
group by date
If you run into reserved word conflicts (like if 'date' is a reserved
word in oracle) that would also need to be fixed.
If you meant something else by "average position" (like if positions
were in latitude and longitude and you want to do great circle
calculations to find the point with the minimum total distance from
all positions within a given day) then you would need to change the
calculation part of it.
I am not sure if oracle is happier with count(*) or sum(1) (but they
mean the same thing).
Anyways, this might help you get started...
--
Raul
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
