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

From: <tonkuma_at_fiberbit.net>
Date: Sat, 26 Oct 2013 03:29:25 -0700 (PDT)

On Wednesday, October 23, 2013 11:35:52 AM UTC+9, 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 can't access Oracle.
So, I tried on DB2.
{code}
WITH
sample_data
( stock , timestamp_sec , quantity ) AS ( VALUES
```  ( 'GOOG' , 12459.6 , -100 )
, ( 'GOOG' , 12464.8 , -100 )
, ( 'GOOG' , 12568.3 , -300 )
, ( 'GOOG' , 12634.0 ,  100 )
, ( 'GOOG' , 12636.2 ,  200 )
, ( 'GOOG' , 12678.0 ,  200 )
```

)
SELECT t.*

, LAG(cumulative , 1 , 0)

```          OVER( PARTITION BY sell_buy
ORDER BY cumulative
) AS lag_cumul
FROM  (SELECT t.*
, SUM( ABS(quantity) )
OVER( PARTITION BY SIGN(quantity)
ORDER BY timestamp_sec
) AS cumulative
, CASE SIGN(quantity)
WHEN -1 THEN 'sell'
FROM  sample_data AS t
) AS t
```

)
, cum_range AS (
SELECT cumulative

, LAG(cumulative , 1 , 0)

OVER( ORDER BY cumulative ) AS lag_cumul  FROM (SELECT DISTINCT

```               cumulative
)
)
SELECT s.timestamp_sec                     AS time

, SIGN(1 - GROUPING(c.cumulative) * 2)

* SUM(c.lag_cumul - c.cumulative)   AS qnt

, - c.cumulative                      AS cumul
, b.timestamp_sec - s.timestamp_sec   AS hold_time

, DEC( SUM(  (b.timestamp_sec - s.timestamp_sec)

* (c.cumulative - c.lag_cumul)
)
, 15 , 1 ) AS weighted_hold_time

, CASE GROUPING(c.cumulative)

WHEN 1 THEN
- DEC( SUM(  (b.timestamp_sec - s.timestamp_sec)
* (c.cumulative - c.lag_cumul)
)
, 31 , 2 )
/ SUM(c.lag_cumul - c.cumulative)
END  AS weighted_avg_hold_time
```

FROM cum_range AS c
INNER JOIN
```  ON   s.sell_buy    = 'sell'
AND s.cumulative >= c.cumulative
AND s.lag_cumul  <= c.lag_cumul
INNER JOIN
```

AND b.cumulative >= c.cumulative
AND b.lag_cumul <= c.lag_cumul
GROUP BY
```       ROLLUP
( (  c.cumulative
, c.lag_cumul
, s.timestamp_sec
, b.timestamp_sec
) )
ORDER BY
time

, cumul DESC

```

;

TIME QNT CUMUL HOLD_TIME WEIGHTED_HOLD_TIME WEIGHTED_AVG_HOLD_TIME

```-------- ----------- ----------- --------- ------------------ ---------------------------------
12459.6        -100        -100     174.4            17440.0                                 -
12464.8        -100        -200     171.4            17140.0                                 -
12568.3        -100        -300      67.9             6790.0                                 -
12568.3        -200        -500     109.7            21940.0                                 -
-         500           -         -            63310.0                            126.62

```

5 record(s) selected.
{code} Received on Sat Oct 26 2013 - 12:29:25 CEST

Original text of this message