Skip to Main Content

How to do an average on time series data?

TomGarOct 22 2013 — edited Oct 27 2013

I need to generate average hold times for various stock of companies as follows:

The data looks like:

stock        timestamp (sec)            quantity

-----        ----------------          ---------

GOOG          12459.6                    -100        <-- SALE

GOOG          12634.0                    +100        <-- PURCHASE

GOOG          12636.2                    +200

GOOG          12464.8                    -100

GOOG          12568.3                    -300

GOOG          12678.0                    +200



The rules are

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.

Post Details
Added on Oct 22 2013