Analytic Functions (short intro) Alex S, http://theparticle.com/ alex@theparticle.com Analytic functions (or windowing functions) are a new class of aggregate functions in SQL that add a summary column to a result set. For example, imagine you have stock trading data of the form: select * from test_trades order by 1,2,3; TDATE | SYMBOL | TIM | PRICE | QTY ------------+--------+--------+-------+------ 2010-01-01 | UVK | 100407 | 41.39 | 200 2010-01-01 | UVK | 102337 | 41.39 | 500 2010-01-01 | UVK | 111715 | 41.36 | 400 2010-01-01 | UVK | 115122 | 41.35 | 400 2010-01-01 | UVK | 121052 | 41.33 | 400 2010-01-01 | UVK | 124952 | 41.27 | 500 2010-01-01 | UVK | 132852 | 41.25 | 500 2010-01-01 | UVK | 141511 | 41.18 | 300 2010-01-01 | UVK | 143707 | 41.14 | 600 2010-01-01 | UVK | 145637 | 41.10 | 400 2010-01-01 | UVK | 155015 | 41.10 | 300 2010-01-01 | UVK | 161211 | 41.09 | 200 2010-01-01 | XOI | 100047 | 21.07 | 500 2010-01-01 | XOI | 101915 | 21.09 | 400 2010-01-01 | XOI | 105409 | 21.10 | 500 2010-01-01 | XOI | 113715 | 21.14 | 200 2010-01-01 | XOI | 124500 | 21.14 | 700 2010-01-01 | XOI | 140300 | 21.14 | 800 2010-01-01 | XOI | 142331 | 21.18 | 600 2010-01-01 | XOI | 143753 | 21.19 | 400 2010-01-01 | XOI | 150028 | 21.10 | 1000 2010-01-01 | XOI | 152303 | 21.15 | 700 Each row represents a transaction at a certain time, price, and quantity. If we wanted to calculate the total (all symbols) daily volume, we could do it via: select sum(qty) from test_trades where tdate='2010-01-01'; SUM ------- 10500 Much more often, we are interested in volume by symbol: select tdate,symbol,sum(qty) from test_trades where tdate='2010-01-01' group by tdate,symbol order by 1,2,3; TDATE | SYMBOL | SUM ------------+--------+------ 2010-01-01 | UVK | 4700 2010-01-01 | XOI | 5800 Now, what percentage of daily volume does each trade represent? For that, we will need to group by tdate,symbol, and join to the trades table. Ie: select a.*,b.totvol,a.qty/b.totvol*100 prcnt from test_trades a natural inner join (select tdate,symbol,sum(qty) totvol from test_trades where tdate='2010-01-01' group by tdate,symbol) b order by 1,2,3; TDATE | SYMBOL | TIM | PRICE | QTY | TOTVOL | PRCNT ------------+--------+--------+-------+------+--------+----------- 2010-01-01 | UVK | 100407 | 41.39 | 200 | 4700 | 4.255300 2010-01-01 | UVK | 102337 | 41.39 | 500 | 4700 | 10.638300 2010-01-01 | UVK | 111715 | 41.36 | 400 | 4700 | 8.510600 2010-01-01 | UVK | 115122 | 41.35 | 400 | 4700 | 8.510600 2010-01-01 | UVK | 121052 | 41.33 | 400 | 4700 | 8.510600 2010-01-01 | UVK | 124952 | 41.27 | 500 | 4700 | 10.638300 2010-01-01 | UVK | 132852 | 41.25 | 500 | 4700 | 10.638300 2010-01-01 | UVK | 141511 | 41.18 | 300 | 4700 | 6.383000 2010-01-01 | UVK | 143707 | 41.14 | 600 | 4700 | 12.766000 2010-01-01 | UVK | 145637 | 41.10 | 400 | 4700 | 8.510600 2010-01-01 | UVK | 155015 | 41.10 | 300 | 4700 | 6.383000 2010-01-01 | UVK | 161211 | 41.09 | 200 | 4700 | 4.255300 2010-01-01 | XOI | 100047 | 21.07 | 500 | 5800 | 8.620700 2010-01-01 | XOI | 101915 | 21.09 | 400 | 5800 | 6.896600 2010-01-01 | XOI | 105409 | 21.10 | 500 | 5800 | 8.620700 2010-01-01 | XOI | 113715 | 21.14 | 200 | 5800 | 3.448300 2010-01-01 | XOI | 124500 | 21.14 | 700 | 5800 | 12.069000 2010-01-01 | XOI | 140300 | 21.14 | 800 | 5800 | 13.793100 2010-01-01 | XOI | 142331 | 21.18 | 600 | 5800 | 10.344800 2010-01-01 | XOI | 143753 | 21.19 | 400 | 5800 | 6.896600 2010-01-01 | XOI | 150028 | 21.10 | 1000 | 5800 | 17.241400 2010-01-01 | XOI | 152303 | 21.15 | 700 | 5800 | 12.069000 An alternative to doing such a join is to use analytical functions: select a.*,qty/(sum(qty) over (partition by tdate,symbol))*100 prcnt from test_trades a where tdate='2010-01-01' order by 1,2,3; TDATE | SYMBOL | TIM | PRICE | QTY | PRCNT ------------+--------+--------+-------+------+----------- 2010-01-01 | UVK | 100407 | 41.39 | 200 | 4.255300 2010-01-01 | UVK | 102337 | 41.39 | 500 | 10.638300 2010-01-01 | UVK | 111715 | 41.36 | 400 | 8.510600 2010-01-01 | UVK | 115122 | 41.35 | 400 | 8.510600 2010-01-01 | UVK | 121052 | 41.33 | 400 | 8.510600 2010-01-01 | UVK | 124952 | 41.27 | 500 | 10.638300 2010-01-01 | UVK | 132852 | 41.25 | 500 | 10.638300 2010-01-01 | UVK | 141511 | 41.18 | 300 | 6.383000 2010-01-01 | UVK | 143707 | 41.14 | 600 | 12.766000 2010-01-01 | UVK | 145637 | 41.10 | 400 | 8.510600 2010-01-01 | UVK | 155015 | 41.10 | 300 | 6.383000 2010-01-01 | UVK | 161211 | 41.09 | 200 | 4.255300 2010-01-01 | XOI | 100047 | 21.07 | 500 | 8.620700 2010-01-01 | XOI | 101915 | 21.09 | 400 | 6.896600 2010-01-01 | XOI | 105409 | 21.10 | 500 | 8.620700 2010-01-01 | XOI | 113715 | 21.14 | 200 | 3.448300 2010-01-01 | XOI | 124500 | 21.14 | 700 | 12.069000 2010-01-01 | XOI | 140300 | 21.14 | 800 | 13.793100 2010-01-01 | XOI | 142331 | 21.18 | 600 | 10.344800 2010-01-01 | XOI | 143753 | 21.19 | 400 | 6.896600 2010-01-01 | XOI | 150028 | 21.10 | 1000 | 17.241400 2010-01-01 | XOI | 152303 | 21.15 | 700 | 12.069000 The "sum(qty) over (partition by tdate,symbol)" above calculates the sum of qty, grouped by tdate, symbol: exactly the same as doing it in a separate query and doing a join. The power of analytical functions lies in specifying the grouping (and order within groups) very specifically. For example, to find min, max for the whole day we can do: select a.*, min(price) over (partition by tdate,symbol), max(price) over (partition by tdate,symbol) from test_trades a where tdate='2010-01-01' order by 1,2,3; TDATE | SYMBOL | TIM | PRICE | QTY | MIN | MAX ------------+--------+--------+-------+------+-------+------- 2010-01-01 | UVK | 100407 | 41.39 | 200 | 41.09 | 41.39 2010-01-01 | UVK | 102337 | 41.39 | 500 | 41.09 | 41.39 2010-01-01 | UVK | 111715 | 41.36 | 400 | 41.09 | 41.39 2010-01-01 | UVK | 115122 | 41.35 | 400 | 41.09 | 41.39 2010-01-01 | UVK | 121052 | 41.33 | 400 | 41.09 | 41.39 2010-01-01 | UVK | 124952 | 41.27 | 500 | 41.09 | 41.39 2010-01-01 | UVK | 132852 | 41.25 | 500 | 41.09 | 41.39 2010-01-01 | UVK | 141511 | 41.18 | 300 | 41.09 | 41.39 2010-01-01 | UVK | 143707 | 41.14 | 600 | 41.09 | 41.39 2010-01-01 | UVK | 145637 | 41.10 | 400 | 41.09 | 41.39 2010-01-01 | UVK | 155015 | 41.10 | 300 | 41.09 | 41.39 2010-01-01 | UVK | 161211 | 41.09 | 200 | 41.09 | 41.39 2010-01-01 | XOI | 100047 | 21.07 | 500 | 21.07 | 21.19 2010-01-01 | XOI | 101915 | 21.09 | 400 | 21.07 | 21.19 2010-01-01 | XOI | 105409 | 21.10 | 500 | 21.07 | 21.19 2010-01-01 | XOI | 113715 | 21.14 | 200 | 21.07 | 21.19 2010-01-01 | XOI | 124500 | 21.14 | 700 | 21.07 | 21.19 2010-01-01 | XOI | 140300 | 21.14 | 800 | 21.07 | 21.19 2010-01-01 | XOI | 142331 | 21.18 | 600 | 21.07 | 21.19 2010-01-01 | XOI | 143753 | 21.19 | 400 | 21.07 | 21.19 2010-01-01 | XOI | 150028 | 21.10 | 1000 | 21.07 | 21.19 2010-01-01 | XOI | 152303 | 21.15 | 700 | 21.07 | 21.19 But if we want to find min/max "so far" (upto that trade), we can do: select a.*, min(price) over (partition by tdate,symbol order by tim rows between unbounded preceding and current row), max(price) over (partition by tdate,symbol order by tim rows between unbounded preceding and current row) from test_trades a where tdate='2010-01-01' order by 1,2,3; TDATE | SYMBOL | TIM | PRICE | QTY | MIN | MAX ------------+--------+--------+-------+------+-------+------- 2010-01-01 | UVK | 100407 | 41.39 | 200 | 41.39 | 41.39 2010-01-01 | UVK | 102337 | 41.39 | 500 | 41.39 | 41.39 2010-01-01 | UVK | 111715 | 41.36 | 400 | 41.36 | 41.39 2010-01-01 | UVK | 115122 | 41.35 | 400 | 41.35 | 41.39 2010-01-01 | UVK | 121052 | 41.33 | 400 | 41.33 | 41.39 2010-01-01 | UVK | 124952 | 41.27 | 500 | 41.27 | 41.39 2010-01-01 | UVK | 132852 | 41.25 | 500 | 41.25 | 41.39 2010-01-01 | UVK | 141511 | 41.18 | 300 | 41.18 | 41.39 2010-01-01 | UVK | 143707 | 41.14 | 600 | 41.14 | 41.39 2010-01-01 | UVK | 145637 | 41.10 | 400 | 41.10 | 41.39 2010-01-01 | UVK | 155015 | 41.10 | 300 | 41.10 | 41.39 2010-01-01 | UVK | 161211 | 41.09 | 200 | 41.09 | 41.39 2010-01-01 | XOI | 100047 | 21.07 | 500 | 21.07 | 21.07 2010-01-01 | XOI | 101915 | 21.09 | 400 | 21.07 | 21.09 2010-01-01 | XOI | 105409 | 21.10 | 500 | 21.07 | 21.10 2010-01-01 | XOI | 113715 | 21.14 | 200 | 21.07 | 21.14 2010-01-01 | XOI | 124500 | 21.14 | 700 | 21.07 | 21.14 2010-01-01 | XOI | 140300 | 21.14 | 800 | 21.07 | 21.14 2010-01-01 | XOI | 142331 | 21.18 | 600 | 21.07 | 21.18 2010-01-01 | XOI | 143753 | 21.19 | 400 | 21.07 | 21.19 2010-01-01 | XOI | 150028 | 21.10 | 1000 | 21.07 | 21.19 2010-01-01 | XOI | 152303 | 21.15 | 700 | 21.07 | 21.19 Notice how for the first transaction, both min/max start out the same, and diverge later in the day. Similarly, we can find the average price for the day: select a.*, avg(price) over (partition by tdate,symbol) from test_trades a where tdate='2010-01-01' order by 1,2,3; TDATE | SYMBOL | TIM | PRICE | QTY | AVG ------------+--------+--------+-------+------+----------- 2010-01-01 | UVK | 100407 | 41.39 | 200 | 41.245833 2010-01-01 | UVK | 102337 | 41.39 | 500 | 41.245833 2010-01-01 | UVK | 111715 | 41.36 | 400 | 41.245833 2010-01-01 | UVK | 115122 | 41.35 | 400 | 41.245833 2010-01-01 | UVK | 121052 | 41.33 | 400 | 41.245833 2010-01-01 | UVK | 124952 | 41.27 | 500 | 41.245833 2010-01-01 | UVK | 132852 | 41.25 | 500 | 41.245833 2010-01-01 | UVK | 141511 | 41.18 | 300 | 41.245833 2010-01-01 | UVK | 143707 | 41.14 | 600 | 41.245833 2010-01-01 | UVK | 145637 | 41.10 | 400 | 41.245833 2010-01-01 | UVK | 155015 | 41.10 | 300 | 41.245833 2010-01-01 | UVK | 161211 | 41.09 | 200 | 41.245833 2010-01-01 | XOI | 100047 | 21.07 | 500 | 21.130000 2010-01-01 | XOI | 101915 | 21.09 | 400 | 21.130000 2010-01-01 | XOI | 105409 | 21.10 | 500 | 21.130000 2010-01-01 | XOI | 113715 | 21.14 | 200 | 21.130000 2010-01-01 | XOI | 124500 | 21.14 | 700 | 21.130000 2010-01-01 | XOI | 140300 | 21.14 | 800 | 21.130000 2010-01-01 | XOI | 142331 | 21.18 | 600 | 21.130000 2010-01-01 | XOI | 143753 | 21.19 | 400 | 21.130000 2010-01-01 | XOI | 150028 | 21.10 | 1000 | 21.130000 2010-01-01 | XOI | 152303 | 21.15 | 700 | 21.130000 Or we can find a 60 minute moving average via: select a.*, floor(mod(floor(tim),100)+mod(floor(tim/100),100)*60+floor(tim/10000)*3600) ticks, avg(price) over (partition by tdate,symbol order by ticks range between 3200 preceding and current row) avg60m from test_trades a order by 1,2,3; TDATE | SYMBOL | TIM | PRICE | QTY | TICKS | AVG60M ------------+--------+--------+-------+------+-------+----------- 2010-01-01 | UVK | 100407 | 41.39 | 200 | 36247 | 41.390000 2010-01-01 | UVK | 102337 | 41.39 | 500 | 37417 | 41.390000 2010-01-01 | UVK | 111715 | 41.36 | 400 | 40635 | 41.360000 2010-01-01 | UVK | 115122 | 41.35 | 400 | 42682 | 41.355000 2010-01-01 | UVK | 121052 | 41.33 | 400 | 43852 | 41.340000 2010-01-01 | UVK | 124952 | 41.27 | 500 | 46192 | 41.300000 2010-01-01 | UVK | 132852 | 41.25 | 500 | 48532 | 41.260000 2010-01-01 | UVK | 141511 | 41.18 | 300 | 51311 | 41.215000 2010-01-01 | UVK | 143707 | 41.14 | 600 | 52627 | 41.160000 2010-01-01 | UVK | 145637 | 41.10 | 400 | 53797 | 41.140000 2010-01-01 | UVK | 155015 | 41.10 | 300 | 57015 | 41.100000 2010-01-01 | UVK | 161211 | 41.09 | 200 | 58331 | 41.095000 2010-01-01 | XOI | 100047 | 21.07 | 500 | 36047 | 21.070000 2010-01-01 | XOI | 101915 | 21.09 | 400 | 37155 | 21.080000 2010-01-01 | XOI | 105409 | 21.10 | 500 | 39249 | 21.095000 2010-01-01 | XOI | 113715 | 21.14 | 200 | 41835 | 21.120000 2010-01-01 | XOI | 124500 | 21.14 | 700 | 45900 | 21.140000 2010-01-01 | XOI | 140300 | 21.14 | 800 | 50580 | 21.140000 2010-01-01 | XOI | 142331 | 21.18 | 600 | 51811 | 21.160000 2010-01-01 | XOI | 143753 | 21.19 | 400 | 52673 | 21.170000 2010-01-01 | XOI | 150028 | 21.10 | 1000 | 54028 | 21.156667 2010-01-01 | XOI | 152303 | 21.15 | 700 | 55383 | 21.146667 Notice how we turn a "tim" attribute into a "ticks" (seconds since midnight) variable, then do a value range on that---3600 seconds is one hour. If you plot the moving average numbers, you will get a much smoother curve than trade-by-trade prices; a better indicator of whether prices are moving up or down. Similarly, you can find the standard deviation: select a.*, stddev(price) over (partition by tdate,symbol) stddev from test_trades a order by 1,2,3; TDATE | SYMBOL | TIM | PRICE | QTY | STDDEV ------------+--------+--------+-------+------+------------------- 2010-01-01 | UVK | 100407 | 41.39 | 200 | 0.11889491158161 2010-01-01 | UVK | 102337 | 41.39 | 500 | 0.11889491158161 2010-01-01 | UVK | 111715 | 41.36 | 400 | 0.11889491158161 2010-01-01 | UVK | 115122 | 41.35 | 400 | 0.11889491158161 2010-01-01 | UVK | 121052 | 41.33 | 400 | 0.11889491158161 2010-01-01 | UVK | 124952 | 41.27 | 500 | 0.11889491158161 2010-01-01 | UVK | 132852 | 41.25 | 500 | 0.11889491158161 2010-01-01 | UVK | 141511 | 41.18 | 300 | 0.11889491158161 2010-01-01 | UVK | 143707 | 41.14 | 600 | 0.11889491158161 2010-01-01 | UVK | 145637 | 41.10 | 400 | 0.11889491158161 2010-01-01 | UVK | 155015 | 41.10 | 300 | 0.11889491158161 2010-01-01 | UVK | 161211 | 41.09 | 200 | 0.11889491158161 2010-01-01 | XOI | 100047 | 21.07 | 500 | 0.039153543900904 2010-01-01 | XOI | 101915 | 21.09 | 400 | 0.039153543900904 2010-01-01 | XOI | 105409 | 21.10 | 500 | 0.039153543900904 2010-01-01 | XOI | 113715 | 21.14 | 200 | 0.039153543900904 2010-01-01 | XOI | 124500 | 21.14 | 700 | 0.039153543900904 2010-01-01 | XOI | 140300 | 21.14 | 800 | 0.039153543900904 2010-01-01 | XOI | 142331 | 21.18 | 600 | 0.039153543900904 2010-01-01 | XOI | 143753 | 21.19 | 400 | 0.039153543900904 2010-01-01 | XOI | 150028 | 21.10 | 1000 | 0.039153543900904 2010-01-01 | XOI | 152303 | 21.15 | 700 | 0.039153543900904 Just as with moving average, you can find moving standard deviation---which tells you whether volatility is increasing or decreasing (standard deviation is square root of variance---large variance indicates high volatility). Many financial triggers depend on price hitting a particular `band'; to find price points that hit one-standard-deviation, you can do: select a.*, floor(mod(floor(tim),100)+mod(floor(tim/100),100)*60+floor(tim/10000)*3600) ticks, avg(price) over (partition by tdate,symbol order by ticks range between 3200 preceding and current row) avg60m, stddev(price) over (partition by tdate,symbol order by ticks range between 3200 preceding and current row) stddev60m, case when price < avg60m-stddev60m or price > avg60m+stddev60m then 'Y' else 'N' end sd1, case when price < avg60m-stddev60m*2.0 or price > avg60m+stddev60m*2.0 then 'Y' else 'N' end sd2 from test_trades a order by 1,2,3; TDATE | SYMBOL | TIM | PRICE | QTY | TICKS | AVG60M | STDDEV60M | SD1 | SD2 ------------+--------+--------+-------+------+-------+-----------+--------------------+-----+----- 2010-01-01 | UVK | 100407 | 41.39 | 200 | 36247 | 41.390000 | 0 | N | N 2010-01-01 | UVK | 102337 | 41.39 | 500 | 37417 | 41.390000 | 0 | N | N 2010-01-01 | UVK | 111715 | 41.36 | 400 | 40635 | 41.360000 | 0 | N | N 2010-01-01 | UVK | 115122 | 41.35 | 400 | 42682 | 41.355000 | 0.0070710678118655 | N | N 2010-01-01 | UVK | 121052 | 41.33 | 400 | 43852 | 41.340000 | 0.014142135623731 | N | N 2010-01-01 | UVK | 124952 | 41.27 | 500 | 46192 | 41.300000 | 0.042426406871193 | N | N 2010-01-01 | UVK | 132852 | 41.25 | 500 | 48532 | 41.260000 | 0.014142135623731 | N | N 2010-01-01 | UVK | 141511 | 41.18 | 300 | 51311 | 41.215000 | 0.049497474683058 | N | N 2010-01-01 | UVK | 143707 | 41.14 | 600 | 52627 | 41.160000 | 0.028284271247462 | N | N 2010-01-01 | UVK | 145637 | 41.10 | 400 | 53797 | 41.140000 | 0.04 | N | N 2010-01-01 | UVK | 155015 | 41.10 | 300 | 57015 | 41.100000 | 0 | N | N 2010-01-01 | UVK | 161211 | 41.09 | 200 | 58331 | 41.095000 | 0.0070710678118655 | N | N 2010-01-01 | XOI | 100047 | 21.07 | 500 | 36047 | 21.070000 | 0 | N | N 2010-01-01 | XOI | 101915 | 21.09 | 400 | 37155 | 21.080000 | 0.014142135623731 | N | N 2010-01-01 | XOI | 105409 | 21.10 | 500 | 39249 | 21.095000 | 0.0070710678118655 | N | N 2010-01-01 | XOI | 113715 | 21.14 | 200 | 41835 | 21.120000 | 0.028284271247462 | N | N 2010-01-01 | XOI | 124500 | 21.14 | 700 | 45900 | 21.140000 | 0 | N | N 2010-01-01 | XOI | 140300 | 21.14 | 800 | 50580 | 21.140000 | 0 | N | N 2010-01-01 | XOI | 142331 | 21.18 | 600 | 51811 | 21.160000 | 0.028284271247462 | N | N 2010-01-01 | XOI | 143753 | 21.19 | 400 | 52673 | 21.170000 | 0.026457513110646 | N | N 2010-01-01 | XOI | 150028 | 21.10 | 1000 | 54028 | 21.156667 | 0.049335585534176 | Y | N 2010-01-01 | XOI | 152303 | 21.15 | 700 | 55383 | 21.146667 | 0.045099889135119 | N | N From the above, we can see that the trade at 150028 is trading ``unusually'' below the 60 minute moving average price. In the industry, most folks use 2*sd, and call it ``Bollinger bands''; a sample strategy might be: When price hits a high bollinger band: sell, when price hits a low bollinger band, buy. Or if you believe momentum plays a major role in price movements, then you would do the reverse (when price hits high bollinger band you buy, since you expect the price to keep going up---this is where price acceleration comes into play). You can also do linear regression---find the line that fits data points. select tdate,symbol,tim,price,qty,slope,intercept from ( select a.*, floor(mod(floor(tim),100)+mod(floor(tim/100),100)*60+floor(tim/10000)*3600) ticks, count(*) over (partition by tdate,symbol order by ticks range between 3200 preceding and current row) N, sum(ticks) over (partition by tdate,symbol order by ticks range between 3200 preceding and current row) sx, sum(ticks*ticks) over (partition by tdate,symbol order by ticks range between 3200 preceding and current row) sxx, sum(price) over (partition by tdate,symbol order by ticks range between 3200 preceding and current row) sy, sum(ticks*price) over (partition by tdate,symbol order by ticks range between 3200 preceding and current row) sxy, (sxx - sx*sx/N)/N as var, (sxy - sx*sy/N)/N as covar, covar / (case when var=0 then 0.000001 else var end) as slope, sy/N - slope * sx/N as intercept from test_trades a ) a order by 1,2,3 TDATE | SYMBOL | TIM | PRICE | QTY | SLOPE | INTERCEPT ------------+--------+--------+-------+------+-----------+----------- 2010-01-01 | UVK | 100407 | 41.39 | 200 | 0.000000 | 41.390000 2010-01-01 | UVK | 102337 | 41.39 | 500 | 0.000000 | 41.390000 2010-01-01 | UVK | 111715 | 41.36 | 400 | 0.000000 | 41.360000 2010-01-01 | UVK | 115122 | 41.35 | 400 | -0.000005 | 41.563293 2010-01-01 | UVK | 121052 | 41.33 | 400 | -0.000017 | 42.075539 2010-01-01 | UVK | 124952 | 41.27 | 500 | -0.000026 | 42.470572 2010-01-01 | UVK | 132852 | 41.25 | 500 | -0.000009 | 41.686258 2010-01-01 | UVK | 141511 | 41.18 | 300 | -0.000025 | 42.463038 2010-01-01 | UVK | 143707 | 41.14 | 600 | -0.000030 | 42.719070 2010-01-01 | UVK | 145637 | 41.10 | 400 | -0.000032 | 42.822507 2010-01-01 | UVK | 155015 | 41.10 | 300 | 0.000000 | 41.100000 2010-01-01 | UVK | 161211 | 41.09 | 200 | -0.000008 | 41.556384 2010-01-01 | XOI | 100047 | 21.07 | 500 | 0.000000 | 21.070000 2010-01-01 | XOI | 101915 | 21.09 | 400 | 0.000018 | 20.421182 2010-01-01 | XOI | 105409 | 21.10 | 500 | 0.000005 | 20.903990 2010-01-01 | XOI | 113715 | 21.14 | 200 | 0.000015 | 20.511870 2010-01-01 | XOI | 124500 | 21.14 | 700 | 0.000000 | 21.140000 2010-01-01 | XOI | 140300 | 21.14 | 800 | 0.000000 | 21.140000 2010-01-01 | XOI | 142331 | 21.18 | 600 | 0.000032 | 19.521744 2010-01-01 | XOI | 143753 | 21.19 | 400 | 0.000024 | 19.929488 2010-01-01 | XOI | 150028 | 21.10 | 1000 | -0.000039 | 23.217323 2010-01-01 | XOI | 152303 | 21.15 | 700 | -0.000015 | 21.957087 Notice that when slope is positive, price is increasing, and when slope is negative, price is decreasing (looking back one hour). Slope is just another word for derivative---so if we calculate the slope of this slope, we will get a second derivative (in other words, we can get an indication of price acceleration, in any particular direction). Note how "var" is calculated: (sxx - sx*sx/N)/N. If we take sqrt(var) we get standard deviation---using the above query, we can compute standard deviation in a single pass---without first computing average. Another useful function is to find previous/next record: select a.*, lag(tim) over (partition by tdate,symbol order by tim) prev_tim, lead(tim) over (partition by tdate,symbol order by tim) next_tim from test_trades a order by 1,2,3; TDATE | SYMBOL | TIM | PRICE | QTY | PREV_TIM | NEXT_TIM ------------+--------+--------+-------+------+----------+---------- 2010-01-01 | UVK | 100407 | 41.39 | 200 | | 102337 2010-01-01 | UVK | 102337 | 41.39 | 500 | 100407 | 111715 2010-01-01 | UVK | 111715 | 41.36 | 400 | 102337 | 115122 2010-01-01 | UVK | 115122 | 41.35 | 400 | 111715 | 121052 2010-01-01 | UVK | 121052 | 41.33 | 400 | 115122 | 124952 2010-01-01 | UVK | 124952 | 41.27 | 500 | 121052 | 132852 2010-01-01 | UVK | 132852 | 41.25 | 500 | 124952 | 141511 2010-01-01 | UVK | 141511 | 41.18 | 300 | 132852 | 143707 2010-01-01 | UVK | 143707 | 41.14 | 600 | 141511 | 145637 2010-01-01 | UVK | 145637 | 41.10 | 400 | 143707 | 155015 2010-01-01 | UVK | 155015 | 41.10 | 300 | 145637 | 161211 2010-01-01 | UVK | 161211 | 41.09 | 200 | 155015 | 2010-01-01 | XOI | 100047 | 21.07 | 500 | | 101915 2010-01-01 | XOI | 101915 | 21.09 | 400 | 100047 | 105409 2010-01-01 | XOI | 105409 | 21.10 | 500 | 101915 | 113715 2010-01-01 | XOI | 113715 | 21.14 | 200 | 105409 | 124500 2010-01-01 | XOI | 124500 | 21.14 | 700 | 113715 | 140300 2010-01-01 | XOI | 140300 | 21.14 | 800 | 124500 | 142331 2010-01-01 | XOI | 142331 | 21.18 | 600 | 140300 | 143753 2010-01-01 | XOI | 143753 | 21.19 | 400 | 142331 | 150028 2010-01-01 | XOI | 150028 | 21.10 | 1000 | 143753 | 152303 2010-01-01 | XOI | 152303 | 21.15 | 700 | 150028 | Notice that the first trade of the day doesn't have "previous" time; similar issue for next_tim. We can use the above created ``range'' (current record till next record) to find all the events that occured in that time window. For example, to get all the quotes that occured in-between trades, we can do: select a.*,b.tim trade_tim from test_quotes a inner join ( select a.*, lag(tim) over (partition by tdate,symbol order by tim) prev_tim from test_trades a ) b on a.tdate=b.tdate and a.symbol=b.symbol and a.tim between b.prev_tim and b.tim order by 1,2,3; TDATE | SYMBOL | TIM | BID | BIDSIZ | OFR | OFRSIZ | TRADE_TIM ------------+--------+--------+-------+--------+-------+--------+----------- 2010-01-01 | UVK | 100407 | 41.30 | 1300 | 41.45 | 900 | 102337 2010-01-01 | UVK | 102337 | 41.32 | 1500 | 41.42 | 1200 | 102337 2010-01-01 | UVK | 102337 | 41.32 | 1500 | 41.42 | 1200 | 111715 2010-01-01 | UVK | 105026 | 41.33 | 400 | 41.36 | 1000 | 111715 2010-01-01 | UVK | 111715 | 41.35 | 1300 | 41.43 | 1000 | 111715 2010-01-01 | UVK | 111715 | 41.35 | 1300 | 41.43 | 1000 | 115122 2010-01-01 | UVK | 113645 | 41.33 | 700 | 41.43 | 1100 | 115122 2010-01-01 | UVK | 115122 | 41.27 | 900 | 41.40 | 1200 | 115122 2010-01-01 | UVK | 115122 | 41.27 | 900 | 41.40 | 1200 | 121052 2010-01-01 | UVK | 121052 | 41.24 | 1100 | 41.32 | 500 | 124952 2010-01-01 | UVK | 121052 | 41.24 | 1100 | 41.32 | 500 | 121052 2010-01-01 | UVK | 124952 | 41.19 | 600 | 41.25 | 700 | 124952 2010-01-01 | UVK | 124952 | 41.19 | 600 | 41.25 | 700 | 132852 2010-01-01 | UVK | 132852 | 41.17 | 1100 | 41.26 | 1100 | 141511 2010-01-01 | UVK | 132852 | 41.17 | 1100 | 41.26 | 1100 | 132852 2010-01-01 | UVK | 140033 | 41.14 | 1000 | 41.25 | 1700 | 141511 2010-01-01 | UVK | 141511 | 41.10 | 800 | 41.15 | 900 | 141511 2010-01-01 | UVK | 141511 | 41.10 | 800 | 41.15 | 900 | 143707 2010-01-01 | UVK | 143707 | 41.09 | 1100 | 41.17 | 700 | 143707 2010-01-01 | UVK | 143707 | 41.09 | 1100 | 41.17 | 700 | 145637 2010-01-01 | UVK | 145637 | 41.01 | 600 | 41.13 | 700 | 155015 2010-01-01 | UVK | 145637 | 41.01 | 600 | 41.13 | 700 | 145637 2010-01-01 | UVK | 152100 | 41.09 | 1100 | 41.19 | 1200 | 155015 2010-01-01 | UVK | 155015 | 41.08 | 900 | 41.19 | 1400 | 155015 2010-01-01 | UVK | 155015 | 41.08 | 900 | 41.19 | 1400 | 161211 2010-01-01 | UVK | 161211 | 41.06 | 800 | 41.10 | 800 | 161211 2010-01-01 | XOI | 100047 | 21.07 | 1200 | 21.11 | 1100 | 101915 2010-01-01 | XOI | 101915 | 21.06 | 1400 | 21.16 | 1100 | 105409 2010-01-01 | XOI | 101915 | 21.06 | 1400 | 21.16 | 1100 | 101915 2010-01-01 | XOI | 105409 | 21.06 | 1000 | 21.14 | 600 | 105409 2010-01-01 | XOI | 105409 | 21.06 | 1000 | 21.14 | 600 | 113715 2010-01-01 | XOI | 111441 | 21.05 | 1300 | 21.18 | 1300 | 113715 2010-01-01 | XOI | 113715 | 21.09 | 1000 | 21.24 | 1100 | 124500 2010-01-01 | XOI | 113715 | 21.09 | 1000 | 21.24 | 1100 | 113715 2010-01-01 | XOI | 120153 | 21.06 | 1200 | 21.15 | 1400 | 124500 2010-01-01 | XOI | 121615 | 21.11 | 600 | 21.14 | 1100 | 124500 2010-01-01 | XOI | 123241 | 21.10 | 1700 | 21.18 | 600 | 124500 2010-01-01 | XOI | 124500 | 21.07 | 1100 | 21.17 | 1400 | 124500 2010-01-01 | XOI | 124500 | 21.07 | 1100 | 21.17 | 1400 | 140300 2010-01-01 | XOI | 130734 | 21.04 | 700 | 21.14 | 1400 | 140300 2010-01-01 | XOI | 133822 | 21.05 | 1100 | 21.18 | 800 | 140300 2010-01-01 | XOI | 140300 | 21.13 | 700 | 21.19 | 600 | 140300 2010-01-01 | XOI | 140300 | 21.13 | 700 | 21.19 | 600 | 142331 2010-01-01 | XOI | 142331 | 21.10 | 1000 | 21.20 | 800 | 143753 2010-01-01 | XOI | 142331 | 21.10 | 1000 | 21.20 | 800 | 142331 2010-01-01 | XOI | 143753 | 21.02 | 700 | 21.18 | 1400 | 143753 2010-01-01 | XOI | 143753 | 21.02 | 700 | 21.18 | 1400 | 150028 2010-01-01 | XOI | 150028 | 21.14 | 1000 | 21.18 | 800 | 150028 2010-01-01 | XOI | 150028 | 21.14 | 1000 | 21.18 | 800 | 152303 2010-01-01 | XOI | 152303 | 21.18 | 1100 | 21.26 | 1300 | 152303 Using slightly different logic, we can find the prevailing quote right before a trade (the last quote that happens before a trade). We start out by creating a "union all" between two separate data sets, both of them having the sequencing value (ie: tim, and o). select * from ( select tdate,symbol,tim,0 o,price,qty qty, null qtim,null bid,null bidsiz, null ofr,null ofrsiz from test_trades union all select tdate,symbol,tim,1 o,null,null, tim qtim,bid,bidsiz,ofr,ofrsiz from test_quotes ) a order by 1,2,3,4; TDATE | SYMBOL | TIM | O | PRICE | QTY | QTIM | BID | BIDSIZ | OFR | OFRSIZ ------------+--------+--------+---+-------+------+--------+-------+--------+-------+-------- 2010-01-01 | UVK | 100407 | 0 | 41.39 | 200 | | | | | 2010-01-01 | UVK | 100407 | 1 | | | 100407 | 41.30 | 1300 | 41.45 | 900 2010-01-01 | UVK | 102337 | 0 | 41.39 | 500 | | | | | 2010-01-01 | UVK | 102337 | 1 | | | 102337 | 41.32 | 1500 | 41.42 | 1200 2010-01-01 | UVK | 105026 | 1 | | | 105026 | 41.33 | 400 | 41.36 | 1000 2010-01-01 | UVK | 111715 | 0 | 41.36 | 400 | | | | | 2010-01-01 | UVK | 111715 | 1 | | | 111715 | 41.35 | 1300 | 41.43 | 1000 2010-01-01 | UVK | 113645 | 1 | | | 113645 | 41.33 | 700 | 41.43 | 1100 2010-01-01 | UVK | 115122 | 0 | 41.35 | 400 | | | | | 2010-01-01 | UVK | 115122 | 1 | | | 115122 | 41.27 | 900 | 41.40 | 1200 2010-01-01 | UVK | 121052 | 0 | 41.33 | 400 | | | | | 2010-01-01 | UVK | 121052 | 1 | | | 121052 | 41.24 | 1100 | 41.32 | 500 2010-01-01 | UVK | 124952 | 0 | 41.27 | 500 | | | | | 2010-01-01 | UVK | 124952 | 1 | | | 124952 | 41.19 | 600 | 41.25 | 700 2010-01-01 | UVK | 132852 | 0 | 41.25 | 500 | | | | | 2010-01-01 | UVK | 132852 | 1 | | | 132852 | 41.17 | 1100 | 41.26 | 1100 2010-01-01 | UVK | 140033 | 1 | | | 140033 | 41.14 | 1000 | 41.25 | 1700 2010-01-01 | UVK | 141511 | 0 | 41.18 | 300 | | | | | 2010-01-01 | UVK | 141511 | 1 | | | 141511 | 41.10 | 800 | 41.15 | 900 2010-01-01 | UVK | 143707 | 0 | 41.14 | 600 | | | | | 2010-01-01 | UVK | 143707 | 1 | | | 143707 | 41.09 | 1100 | 41.17 | 700 2010-01-01 | UVK | 145637 | 0 | 41.10 | 400 | | | | | 2010-01-01 | UVK | 145637 | 1 | | | 145637 | 41.01 | 600 | 41.13 | 700 2010-01-01 | UVK | 152100 | 1 | | | 152100 | 41.09 | 1100 | 41.19 | 1200 2010-01-01 | UVK | 155015 | 0 | 41.10 | 300 | | | | | 2010-01-01 | UVK | 155015 | 1 | | | 155015 | 41.08 | 900 | 41.19 | 1400 2010-01-01 | UVK | 161211 | 0 | 41.09 | 200 | | | | | 2010-01-01 | UVK | 161211 | 1 | | | 161211 | 41.06 | 800 | 41.10 | 800 2010-01-01 | XOI | 94422 | 1 | | | 94422 | 21.02 | 1200 | 21.09 | 900 2010-01-01 | XOI | 100047 | 0 | 21.07 | 500 | | | | | 2010-01-01 | XOI | 100047 | 1 | | | 100047 | 21.07 | 1200 | 21.11 | 1100 2010-01-01 | XOI | 101915 | 0 | 21.09 | 400 | | | | | 2010-01-01 | XOI | 101915 | 1 | | | 101915 | 21.06 | 1400 | 21.16 | 1100 2010-01-01 | XOI | 105409 | 0 | 21.10 | 500 | | | | | 2010-01-01 | XOI | 105409 | 1 | | | 105409 | 21.06 | 1000 | 21.14 | 600 2010-01-01 | XOI | 111441 | 1 | | | 111441 | 21.05 | 1300 | 21.18 | 1300 2010-01-01 | XOI | 113715 | 0 | 21.14 | 200 | | | | | 2010-01-01 | XOI | 113715 | 1 | | | 113715 | 21.09 | 1000 | 21.24 | 1100 2010-01-01 | XOI | 120153 | 1 | | | 120153 | 21.06 | 1200 | 21.15 | 1400 2010-01-01 | XOI | 121615 | 1 | | | 121615 | 21.11 | 600 | 21.14 | 1100 2010-01-01 | XOI | 123241 | 1 | | | 123241 | 21.10 | 1700 | 21.18 | 600 2010-01-01 | XOI | 124500 | 0 | 21.14 | 700 | | | | | 2010-01-01 | XOI | 124500 | 1 | | | 124500 | 21.07 | 1100 | 21.17 | 1400 2010-01-01 | XOI | 130734 | 1 | | | 130734 | 21.04 | 700 | 21.14 | 1400 2010-01-01 | XOI | 133822 | 1 | | | 133822 | 21.05 | 1100 | 21.18 | 800 2010-01-01 | XOI | 140300 | 0 | 21.14 | 800 | | | | | 2010-01-01 | XOI | 140300 | 1 | | | 140300 | 21.13 | 700 | 21.19 | 600 2010-01-01 | XOI | 142331 | 0 | 21.18 | 600 | | | | | 2010-01-01 | XOI | 142331 | 1 | | | 142331 | 21.10 | 1000 | 21.20 | 800 2010-01-01 | XOI | 143753 | 0 | 21.19 | 400 | | | | | 2010-01-01 | XOI | 143753 | 1 | | | 143753 | 21.02 | 700 | 21.18 | 1400 2010-01-01 | XOI | 150028 | 0 | 21.10 | 1000 | | | | | 2010-01-01 | XOI | 150028 | 1 | | | 150028 | 21.14 | 1000 | 21.18 | 800 2010-01-01 | XOI | 152303 | 0 | 21.15 | 700 | | | | | 2010-01-01 | XOI | 152303 | 1 | | | 152303 | 21.18 | 1100 | 21.26 | 1300 2010-01-01 | XOI | 154741 | 1 | | | 154741 | 21.12 | 1500 | 21.24 | 1300 2010-01-01 | XOI | 160609 | 1 | | | 160609 | 21.09 | 1300 | 21.21 | 800 Now we need to fill in the holes with values. For every trade, we would like to fill it in with the previous bid/ofr values. For example, we would like to turn: TDATE | SYMBOL | TIM | O | PRICE | QTY | QTIM | BID | BIDSIZ | OFR | OFRSIZ ------------+--------+--------+---+-------+------+--------+-------+--------+-------+-------- 2010-01-01 | UVK | 100407 | 1 | | | 100407 | 41.30 | 1300 | 41.45 | 900 2010-01-01 | UVK | 102337 | 0 | 41.39 | 500 | | | | | 2010-01-01 | UVK | 102337 | 1 | | | 102337 | 41.32 | 1500 | 41.42 | 1200 2010-01-01 | UVK | 105026 | 1 | | | 105026 | 41.33 | 400 | 41.36 | 1000 2010-01-01 | UVK | 111715 | 0 | 41.36 | 400 | | | | | into: TDATE | SYMBOL | TIM | O | PRICE | QTY | QTIM | BID | BIDSIZ | OFR | OFRSIZ ------------+--------+--------+---+-------+------+--------+-------+--------+-------+-------- 2010-01-01 | UVK | 100407 | 1 | | | 100407 | 41.30 | 1300 | 41.45 | 900 2010-01-01 | UVK | 102337 | 0 | 41.39 | 500 | 100407 | 41.30 | 1300 | 41.45 | 900 2010-01-01 | UVK | 102337 | 1 | | | 102337 | 41.32 | 1500 | 41.42 | 1200 2010-01-01 | UVK | 105026 | 1 | | | 105026 | 41.33 | 400 | 41.36 | 1000 2010-01-01 | UVK | 111715 | 0 | 41.36 | 400 | 105026 | 41.33 | 400 | 41.36 | 1000 We can do that with "last_value" function (ignoring nulls): select tdate,symbol,tim,o,price,qty, last_value(qtim ignore nulls) over (partition by tdate,symbol order by tim,o rows between unbounded preceding and current row) qtim, last_value(bid ignore nulls) over (partition by tdate,symbol order by tim,o rows between unbounded preceding and current row) bid, last_value(bidsiz ignore nulls) over (partition by tdate,symbol order by tim,o rows between unbounded preceding and current row) bidsiz, last_value(ofr ignore nulls) over (partition by tdate,symbol order by tim,o rows between unbounded preceding and current row) ofr, last_value(ofrsiz ignore nulls) over (partition by tdate,symbol order by tim,o rows between unbounded preceding and current row) ofrsiz from ( select tdate,symbol,tim,0 o,price,qty qty, null qtim,null bid,null bidsiz, null ofr,null ofrsiz from test_trades union all select tdate,symbol,tim,1 o,null,null, tim qtim,bid,bidsiz,ofr,ofrsiz from test_quotes ) a order by 1,2,3,4; TDATE | SYMBOL | TIM | O | PRICE | QTY | QTIM | BID | BIDSIZ | OFR | OFRSIZ ------------+--------+--------+---+-------+------+--------+-------+--------+-------+-------- 2010-01-01 | UVK | 100407 | 0 | 41.39 | 200 | | | | | 2010-01-01 | UVK | 100407 | 1 | | | 100407 | 41.30 | 1300 | 41.45 | 900 2010-01-01 | UVK | 102337 | 0 | 41.39 | 500 | 100407 | 41.30 | 1300 | 41.45 | 900 2010-01-01 | UVK | 102337 | 1 | | | 102337 | 41.32 | 1500 | 41.42 | 1200 2010-01-01 | UVK | 105026 | 1 | | | 105026 | 41.33 | 400 | 41.36 | 1000 2010-01-01 | UVK | 111715 | 0 | 41.36 | 400 | 105026 | 41.33 | 400 | 41.36 | 1000 2010-01-01 | UVK | 111715 | 1 | | | 111715 | 41.35 | 1300 | 41.43 | 1000 2010-01-01 | UVK | 113645 | 1 | | | 113645 | 41.33 | 700 | 41.43 | 1100 2010-01-01 | UVK | 115122 | 0 | 41.35 | 400 | 113645 | 41.33 | 700 | 41.43 | 1100 2010-01-01 | UVK | 115122 | 1 | | | 115122 | 41.27 | 900 | 41.40 | 1200 2010-01-01 | UVK | 121052 | 0 | 41.33 | 400 | 115122 | 41.27 | 900 | 41.40 | 1200 2010-01-01 | UVK | 121052 | 1 | | | 121052 | 41.24 | 1100 | 41.32 | 500 2010-01-01 | UVK | 124952 | 0 | 41.27 | 500 | 121052 | 41.24 | 1100 | 41.32 | 500 2010-01-01 | UVK | 124952 | 1 | | | 124952 | 41.19 | 600 | 41.25 | 700 2010-01-01 | UVK | 132852 | 0 | 41.25 | 500 | 124952 | 41.19 | 600 | 41.25 | 700 2010-01-01 | UVK | 132852 | 1 | | | 132852 | 41.17 | 1100 | 41.26 | 1100 2010-01-01 | UVK | 140033 | 1 | | | 140033 | 41.14 | 1000 | 41.25 | 1700 2010-01-01 | UVK | 141511 | 0 | 41.18 | 300 | 140033 | 41.14 | 1000 | 41.25 | 1700 2010-01-01 | UVK | 141511 | 1 | | | 141511 | 41.10 | 800 | 41.15 | 900 2010-01-01 | UVK | 143707 | 0 | 41.14 | 600 | 141511 | 41.10 | 800 | 41.15 | 900 2010-01-01 | UVK | 143707 | 1 | | | 143707 | 41.09 | 1100 | 41.17 | 700 2010-01-01 | UVK | 145637 | 0 | 41.10 | 400 | 143707 | 41.09 | 1100 | 41.17 | 700 2010-01-01 | UVK | 145637 | 1 | | | 145637 | 41.01 | 600 | 41.13 | 700 2010-01-01 | UVK | 152100 | 1 | | | 152100 | 41.09 | 1100 | 41.19 | 1200 2010-01-01 | UVK | 155015 | 0 | 41.10 | 300 | 152100 | 41.09 | 1100 | 41.19 | 1200 2010-01-01 | UVK | 155015 | 1 | | | 155015 | 41.08 | 900 | 41.19 | 1400 2010-01-01 | UVK | 161211 | 0 | 41.09 | 200 | 155015 | 41.08 | 900 | 41.19 | 1400 2010-01-01 | UVK | 161211 | 1 | | | 161211 | 41.06 | 800 | 41.10 | 800 2010-01-01 | XOI | 94422 | 1 | | | 94422 | 21.02 | 1200 | 21.09 | 900 2010-01-01 | XOI | 100047 | 0 | 21.07 | 500 | 94422 | 21.02 | 1200 | 21.09 | 900 2010-01-01 | XOI | 100047 | 1 | | | 100047 | 21.07 | 1200 | 21.11 | 1100 2010-01-01 | XOI | 101915 | 0 | 21.09 | 400 | 100047 | 21.07 | 1200 | 21.11 | 1100 2010-01-01 | XOI | 101915 | 1 | | | 101915 | 21.06 | 1400 | 21.16 | 1100 2010-01-01 | XOI | 105409 | 0 | 21.10 | 500 | 101915 | 21.06 | 1400 | 21.16 | 1100 2010-01-01 | XOI | 105409 | 1 | | | 105409 | 21.06 | 1000 | 21.14 | 600 2010-01-01 | XOI | 111441 | 1 | | | 111441 | 21.05 | 1300 | 21.18 | 1300 2010-01-01 | XOI | 113715 | 0 | 21.14 | 200 | 111441 | 21.05 | 1300 | 21.18 | 1300 2010-01-01 | XOI | 113715 | 1 | | | 113715 | 21.09 | 1000 | 21.24 | 1100 2010-01-01 | XOI | 120153 | 1 | | | 120153 | 21.06 | 1200 | 21.15 | 1400 2010-01-01 | XOI | 121615 | 1 | | | 121615 | 21.11 | 600 | 21.14 | 1100 2010-01-01 | XOI | 123241 | 1 | | | 123241 | 21.10 | 1700 | 21.18 | 600 2010-01-01 | XOI | 124500 | 0 | 21.14 | 700 | 123241 | 21.10 | 1700 | 21.18 | 600 2010-01-01 | XOI | 124500 | 1 | | | 124500 | 21.07 | 1100 | 21.17 | 1400 2010-01-01 | XOI | 130734 | 1 | | | 130734 | 21.04 | 700 | 21.14 | 1400 2010-01-01 | XOI | 133822 | 1 | | | 133822 | 21.05 | 1100 | 21.18 | 800 2010-01-01 | XOI | 140300 | 0 | 21.14 | 800 | 133822 | 21.05 | 1100 | 21.18 | 800 2010-01-01 | XOI | 140300 | 1 | | | 140300 | 21.13 | 700 | 21.19 | 600 2010-01-01 | XOI | 142331 | 0 | 21.18 | 600 | 140300 | 21.13 | 700 | 21.19 | 600 2010-01-01 | XOI | 142331 | 1 | | | 142331 | 21.10 | 1000 | 21.20 | 800 2010-01-01 | XOI | 143753 | 0 | 21.19 | 400 | 142331 | 21.10 | 1000 | 21.20 | 800 2010-01-01 | XOI | 143753 | 1 | | | 143753 | 21.02 | 700 | 21.18 | 1400 2010-01-01 | XOI | 150028 | 0 | 21.10 | 1000 | 143753 | 21.02 | 700 | 21.18 | 1400 2010-01-01 | XOI | 150028 | 1 | | | 150028 | 21.14 | 1000 | 21.18 | 800 2010-01-01 | XOI | 152303 | 0 | 21.15 | 700 | 150028 | 21.14 | 1000 | 21.18 | 800 2010-01-01 | XOI | 152303 | 1 | | | 152303 | 21.18 | 1100 | 21.26 | 1300 2010-01-01 | XOI | 154741 | 1 | | | 154741 | 21.12 | 1500 | 21.24 | 1300 2010-01-01 | XOI | 160609 | 1 | | | 160609 | 21.09 | 1300 | 21.21 | 800 At this point, we can filter out all the quotes (since we've grabbed their data into trades): select * from ( select tdate,symbol,tim,o,price,qty, last_value(qtim ignore nulls) over (partition by tdate,symbol order by tim,o rows between unbounded preceding and current row) qtim, last_value(bid ignore nulls) over (partition by tdate,symbol order by tim,o rows between unbounded preceding and current row) bid, last_value(bidsiz ignore nulls) over (partition by tdate,symbol order by tim,o rows between unbounded preceding and current row) bidsiz, last_value(ofr ignore nulls) over (partition by tdate,symbol order by tim,o rows between unbounded preceding and current row) ofr, last_value(ofrsiz ignore nulls) over (partition by tdate,symbol order by tim,o rows between unbounded preceding and current row) ofrsiz from ( select tdate,symbol,tim,0 o,price,qty::int qty, null::int qtim,null bid,null::int bidsiz, null ofr,null::int ofrsiz from test_trades union all select tdate,symbol,tim,1 o,null,null, tim qtim,bid,bidsiz,ofr,ofrsiz from test_quotes ) a ) a where o=0 order by 1,2,3,4; TDATE | SYMBOL | TIM | O | PRICE | QTY | QTIM | BID | BIDSIZ | OFR | OFRSIZ ------------+--------+--------+---+-------+------+--------+-------+--------+-------+-------- 2010-01-01 | UVK | 100407 | 0 | 41.39 | 200 | | | | | 2010-01-01 | UVK | 102337 | 0 | 41.39 | 500 | 100407 | 41.30 | 1300 | 41.45 | 900 2010-01-01 | UVK | 111715 | 0 | 41.36 | 400 | 105026 | 41.33 | 400 | 41.36 | 1000 2010-01-01 | UVK | 115122 | 0 | 41.35 | 400 | 113645 | 41.33 | 700 | 41.43 | 1100 2010-01-01 | UVK | 121052 | 0 | 41.33 | 400 | 115122 | 41.27 | 900 | 41.40 | 1200 2010-01-01 | UVK | 124952 | 0 | 41.27 | 500 | 121052 | 41.24 | 1100 | 41.32 | 500 2010-01-01 | UVK | 132852 | 0 | 41.25 | 500 | 124952 | 41.19 | 600 | 41.25 | 700 2010-01-01 | UVK | 141511 | 0 | 41.18 | 300 | 140033 | 41.14 | 1000 | 41.25 | 1700 2010-01-01 | UVK | 143707 | 0 | 41.14 | 600 | 141511 | 41.10 | 800 | 41.15 | 900 2010-01-01 | UVK | 145637 | 0 | 41.10 | 400 | 143707 | 41.09 | 1100 | 41.17 | 700 2010-01-01 | UVK | 155015 | 0 | 41.10 | 300 | 152100 | 41.09 | 1100 | 41.19 | 1200 2010-01-01 | UVK | 161211 | 0 | 41.09 | 200 | 155015 | 41.08 | 900 | 41.19 | 1400 2010-01-01 | XOI | 100047 | 0 | 21.07 | 500 | 94422 | 21.02 | 1200 | 21.09 | 900 2010-01-01 | XOI | 101915 | 0 | 21.09 | 400 | 100047 | 21.07 | 1200 | 21.11 | 1100 2010-01-01 | XOI | 105409 | 0 | 21.10 | 500 | 101915 | 21.06 | 1400 | 21.16 | 1100 2010-01-01 | XOI | 113715 | 0 | 21.14 | 200 | 111441 | 21.05 | 1300 | 21.18 | 1300 2010-01-01 | XOI | 124500 | 0 | 21.14 | 700 | 123241 | 21.10 | 1700 | 21.18 | 600 2010-01-01 | XOI | 140300 | 0 | 21.14 | 800 | 133822 | 21.05 | 1100 | 21.18 | 800 2010-01-01 | XOI | 142331 | 0 | 21.18 | 600 | 140300 | 21.13 | 700 | 21.19 | 600 2010-01-01 | XOI | 143753 | 0 | 21.19 | 400 | 142331 | 21.10 | 1000 | 21.20 | 800 2010-01-01 | XOI | 150028 | 0 | 21.10 | 1000 | 143753 | 21.02 | 700 | 21.18 | 1400 2010-01-01 | XOI | 152303 | 0 | 21.15 | 700 | 150028 | 21.14 | 1000 | 21.18 | 800 For every trade, we have the time, bid and ofr of previous quote. WORKAROUND So what do you do when last_value(... ignore nulls) doesn't exist or doesn't work as expected? One thing is to keep a running count, and then group on that count: with unionall as ( -- union all as before. select tdate,symbol,tim,0 o,price,qty::int qty, null::int qtim,null bid,null::int bidsiz, null ofr,null::int ofrsiz from test_trades union all select tdate,symbol,tim,1 o,null,null, tim qtim,bid,bidsiz,ofr,ofrsiz from test_quotes ), runninggrps as ( -- this generates a group (grp) for each quote. select a.*, sum(case when qtim is not null then 1 else 0 end) over (partition by tdate,symbol order by tim,o rows between unbounded preceding and current row) grp from unionall ), lastval as ( -- for each group, find the quote (there will be at most 1 per group). select tdate,symbol,tim,o,price,qty, max(qtim) over (partition by tdate,symbol,grp) qtim, max(bid) over (partition by tdate,symbol,grp) bid, max(bidsiz) over (partition by tdate,symbol,grp) bidsiz, max(ofr) over (partition by tdate,symbol,grp) ofr, max(ofrsiz) over (partition by tdate,symbol,grp) ofrsiz from runninggrps ) select * from lastval where o=0 order by 1,2,3,4; The DDL for the above examples is: drop table test_trades; create table test_trades ( tdate date, symbol varchar(10), tim numeric(6), price numeric(10,2), qty numeric(9) ); drop table test_quotes; create table test_quotes ( tdate date, symbol varchar(10), tim numeric(6), bid numeric(10,2), bidsiz numeric(9), ofr numeric(10,2), ofrsiz numeric(9) ); The data used in the above examples: insert into test_quotes values ('20100101','XOI',094422,21.02,1200,21.09,900); insert into test_trades values ('20100101','XOI',100047,21.07,500); insert into test_quotes values ('20100101','XOI',100047,21.07,1200,21.11,1100); insert into test_trades values ('20100101','XOI',101915,21.09,400); insert into test_quotes values ('20100101','XOI',101915,21.06,1400,21.16,1100); insert into test_trades values ('20100101','XOI',105409,21.10,500); insert into test_quotes values ('20100101','XOI',105409,21.06,1000,21.14,600); insert into test_quotes values ('20100101','XOI',111441,21.05,1300,21.18,1300); insert into test_trades values ('20100101','XOI',113715,21.14,200); insert into test_quotes values ('20100101','XOI',113715,21.09,1000,21.24,1100); insert into test_quotes values ('20100101','XOI',120153,21.06,1200,21.15,1400); insert into test_quotes values ('20100101','XOI',121615,21.11,600,21.14,1100); insert into test_quotes values ('20100101','XOI',123241,21.10,1700,21.18,600); insert into test_trades values ('20100101','XOI',124500,21.14,700); insert into test_quotes values ('20100101','XOI',124500,21.07,1100,21.17,1400); insert into test_quotes values ('20100101','XOI',130734,21.04,700,21.14,1400); insert into test_quotes values ('20100101','XOI',133822,21.05,1100,21.18,800); insert into test_trades values ('20100101','XOI',140300,21.14,800); insert into test_quotes values ('20100101','XOI',140300,21.13,700,21.19,600); insert into test_trades values ('20100101','XOI',142331,21.18,600); insert into test_quotes values ('20100101','XOI',142331,21.10,1000,21.20,800); insert into test_trades values ('20100101','XOI',143753,21.19,400); insert into test_quotes values ('20100101','XOI',143753,21.02,700,21.18,1400); insert into test_trades values ('20100101','XOI',150028,21.10,1000); insert into test_quotes values ('20100101','XOI',150028,21.14,1000,21.18,800); insert into test_trades values ('20100101','XOI',152303,21.15,700); insert into test_quotes values ('20100101','XOI',152303,21.18,1100,21.26,1300); insert into test_quotes values ('20100101','XOI',154741,21.12,1500,21.24,1300); insert into test_quotes values ('20100101','XOI',160609,21.09,1300,21.21,800); insert into test_trades values ('20100101','UVK',100407,41.39,200); insert into test_quotes values ('20100101','UVK',100407,41.30,1300,41.45,900); insert into test_trades values ('20100101','UVK',102337,41.39,500); insert into test_quotes values ('20100101','UVK',102337,41.32,1500,41.42,1200); insert into test_quotes values ('20100101','UVK',105026,41.33,400,41.36,1000); insert into test_trades values ('20100101','UVK',111715,41.36,400); insert into test_quotes values ('20100101','UVK',111715,41.35,1300,41.43,1000); insert into test_quotes values ('20100101','UVK',113645,41.33,700,41.43,1100); insert into test_trades values ('20100101','UVK',115122,41.35,400); insert into test_quotes values ('20100101','UVK',115122,41.27,900,41.40,1200); insert into test_trades values ('20100101','UVK',121052,41.33,400); insert into test_quotes values ('20100101','UVK',121052,41.24,1100,41.32,500); insert into test_trades values ('20100101','UVK',124952,41.27,500); insert into test_quotes values ('20100101','UVK',124952,41.19,600,41.25,700); insert into test_trades values ('20100101','UVK',132852,41.25,500); insert into test_quotes values ('20100101','UVK',132852,41.17,1100,41.26,1100); insert into test_quotes values ('20100101','UVK',140033,41.14,1000,41.25,1700); insert into test_trades values ('20100101','UVK',141511,41.18,300); insert into test_quotes values ('20100101','UVK',141511,41.10,800,41.15,900); insert into test_trades values ('20100101','UVK',143707,41.14,600); insert into test_quotes values ('20100101','UVK',143707,41.09,1100,41.17,700); insert into test_trades values ('20100101','UVK',145637,41.10,400); insert into test_quotes values ('20100101','UVK',145637,41.01,600,41.13,700); insert into test_quotes values ('20100101','UVK',152100,41.09,1100,41.19,1200); insert into test_trades values ('20100101','UVK',155015,41.10,300); insert into test_quotes values ('20100101','UVK',155015,41.08,900,41.19,1400); insert into test_trades values ('20100101','UVK',161211,41.09,200); insert into test_quotes values ('20100101','UVK',161211,41.06,800,41.10,800); Perl script that generated the sample data: use strict; sub tim2ticks { return int($_[0]/10000)*3600 + (int($_[0]/100.0)%100)*60 + int($_[0]%100) + ($_[0]-int($_[0])); } sub ticks2tim { return int($_[0]/3600)*10000 + int(($_[0]%3600)/60)*100 + int($_[0]%60) + ($_[0]-int($_[0])); } sub randnorm { our $randnorm_last; my $pi = 3.141592; local $_ = $randnorm_last; $randnorm_last = undef; return $_ if $_; my ($a,$b) = (rand(),rand()); $randnorm_last = sqrt(-2.0*log($a)) * cos(2.0 * $pi * $b); return sqrt(-2.0*log($a)) * sin(2.0 * $pi * $b); } sub randpoisson { my ($l,$k,$p) = (exp($_[0] ? -$_[0] : -1), 0, 1); for(;;){ $k++; $p *= rand(); return $k-1 if $p <= $l; } } my @s='AAA'...'ZZZ'; for(1..2){ my ($s) = $s[rand $#s]; # random symbol my $n = int(15+rand(5)); # quotes per day my $ticks = tim2ticks(93000); # start time my $ticksscale = (tim2ticks(160000)-tim2ticks(93000))/($n*10); my $price = sprintf("%.2f",rand()*100); # start price my $bid = sprintf("%.2f",$price - rand() * 0.1); my $ofr = sprintf("%.2f",$price + rand() * 0.1); for(1..$n){ $ticks += randpoisson(10) * $ticksscale; my $tim = sprintf("%06d",ticks2tim($ticks)); if(rand()<0.75){ print qq{insert into test_trades values ('20100101','$s',$tim,$price,}. ((randpoisson(5) || 1)*100).qq{);\n}; } my $nprice = sprintf("%.2f",$price + randnorm()/10); $nprice = $nprice < $bid ? $bid : $nprice; $nprice = $nprice > $ofr ? $ofr : $nprice; $price = $nprice; $bid = sprintf("%.2f",$price - rand() * 0.1); $ofr = sprintf("%.2f",$price + rand() * 0.1); print qq{insert into test_quotes values ('20100101','$s',$tim,$bid,}. ((randpoisson(10) || 1)*100).qq{,$ofr,}. ((randpoisson(10) || 1)*100).qq{);\n}; } }