Need some tips on query generating averages on time series data

stock        timestamp (sec)            quantity
-----        ----------------          ---------
GOOG          12459.6                    -100        <-- SELL
GOOG          12634.0                    +100        <-- BUY
GOOG          12636.2                    +200
GOOG          12464.8                    -100
GOOG          12568.3                    -300
GOOG          12678.0                    +200

1. begin and end day with balance 0
2. can short sell, i.e. can sell shares even if balance is currently 0
3. hold time is defined as number of seconds stock was held before it was sold
4. first stock purchased are sold first
 

I need to generate the average hold times seconds per share. I'd prefer to do this using SQL and NOT a procedure.  

Any tips on how to go about calculating this? I have looked at various analytic functions, but still not sure.  

Thank you. Received on Wed Oct 23 2013 - 04:35:52 CEST