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)
Message-ID: <d562e4cc-c34f-4ef0-887f-93115f243e38_at_googlegroups.com>

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 )

)
, add_cum AS (
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'
               ELSE         'buy'
               END  AS sell_buy
         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
         FROM  add_cum
       )
)
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
       add_cum AS s

  ON   s.sell_buy    = 'sell'
   AND s.cumulative >= c.cumulative
   AND s.lag_cumul  <= c.lag_cumul
 INNER JOIN
       add_cum   AS b
  ON   b.sell_buy    = 'buy'

   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