Oh, and I should've said "Thanks, Raul!" too. Your SQL solved my problem.
-Dan -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Dan Bron Sent: Thursday, April 21, 2011 8:59 AM To: 'Chat forum' Subject: Re: [Jchat] Help with SQL Thanks Devon. I took a pass at time arithmetic in SQL and gave up in frustration. Found it easier to add a new column to the table, GregorianDay, and have the app insert it. -Dan -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Devon McCormick Sent: Wednesday, April 20, 2011 12:28 AM To: Chat forum Subject: Re: [Jchat] Help with SQL Here's a snippet of Oracle timestamp cruft that's handy - I have a good pocket reference at work: CREATE TABLE date_table (CORE_ID NUMBER(18), END_DT TIMESTAMP, ST_DT TIMESTAMP) insert into DATE_TABLE select CORE_ID, TO_TIMESTAMP(TO_CHAR(PROCESS_END_TIME, 'MM/DD/YYYY HH24:MI:SS'), 'MM/DD/YYYY HH24:MI:SS')... On Tue, Apr 19, 2011 at 3:00 PM, Dan Bron <[email protected]> wrote: > 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 > -- Devon McCormick, CFA ^me^ at acm. org is my preferred e-mail ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm
