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

Another revised example.
(1) Remove a nested subquery in add_cum cte. (2) Remove some columns in GROUP BY and ORDER BY clause.

WITH
 add_cum AS (
SELECT t.*

, SUM(quantity)

          OVER( PARTITION BY position
                           , direction
                    ORDER BY time
              ) AS cumulative


     , NVL(

       SUM(quantity)
          OVER( PARTITION BY position
                           , direction
                    ORDER BY time
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND 1         PRECEDING
              )
       , 0) AS lag_cumul

, SUM(cumul - lag_cumul) AS qnt
, cumul
, hold_time
, SUM( hold_time * (cumul - lag_cumul) )

                                 AS weighted_hold_time


     , NULLIF(GROUPING(cumul) , 0)


       * SUM( hold_time * (cumul - lag_cumul) )
       / SUM(cumul - lag_cumul)  AS weighted_avg_hold_time
 FROM  (SELECT b.time
             , MAX(s.lag_cumul  , b.lag_cumul)  AS lag_cumul
             , MIN(s.cumulative , b.cumulative) AS cumul
             , s.time - b.time                  AS hold_time
         FROM  add_cum AS s
         INNER JOIN
               add_cum AS b
          ON   s.direction = 'S'
           AND b.direction = 'B'
           AND s.cumulative > b.lag_cumul
           AND b.cumulative > s.lag_cumul
       )
 GROUP BY
       ROLLUP
       ( (  time
          , cumul 
          , hold_time
       ) )
 ORDER BY
       cumul