Re: Need some tips on query generating averages on time series data
Date: Wed, 23 Oct 2013 08:12:33 +0200
Message-ID: <l47pch$vmb$1_at_dont-email.me>
On 10/23/2013 04:35 AM, TomGar wrote:
> 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 <-- SELL
> GOOG 12634.0 +100 <-- BUY
> 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.
>
I'm not entirely sure how you define hold time for a stock, is it per
share? Otherwise why is rule 4 necessary? Can you provide the expected
result for the sample data above?
I don't have access to an Oracle instance so I ran on a competitor:
with T(stock, ts, qnt) as (
select stock, ts, qnt, sum(qnt) over (partition by stock
STOCK TS QNT CUMULATIVE_SUM
values ('GOOG', 12459.6, -100)
, ('GOOG', 12634.0, 100)
, ('GOOG', 12636.2, 200)
, ('GOOG', 12464.8, -100)
, ('GOOG', 12568.3, -300)
, ('GOOG', 12678.0, 200)
)
order by ts) as cumulative_sum
from t;
----- -------- ----------- -------------- GOOG 12459.6 -100 -100 GOOG 12464.8 -100 -200 GOOG 12568.3 -300 -500 GOOG 12634.0 100 -400 GOOG 12636.2 200 -200 GOOG 12678.0 200 0
What is the average hold times above?
/Lennart Received on Wed Oct 23 2013 - 08:12:33 CEST