# Re: Need some tips on query generating averages on time series data

From: Lennart Jonsson <erik.lennart.jonsson_at_gmail.com>
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 don't have access to an Oracle instance so I ran on a competitor:

with T(stock, ts, qnt) as (

```    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)
```

)

select stock, ts, qnt, sum(qnt) over (partition by stock

```                                      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

Original text of this message