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
