Skip to content

Window Join window-join, window-join1

Returns a table with records from the left-join of t1 and t2. Join columns (but last one) are matched for equality. The resulting set matches for the last and values over the matching intervals in windows argument is passed to an aggregation functions set, producing additional columns in the resulting table.

 (set n 100000)
100000
 (set tsym (take n (concat (take 99 'AAPL) (take 1 'MSFT))))
[AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL..]
 (set ttime (+ 09:00:00 (as 'Time (/ (* (til n) 3) 10))))
[09:00:00.000 09:00:00.000 09:00:00.000 09:00:00.000 09:00:00.001 09:00:00.001 09:00:00.001..]
 (set price (+ 10 (til n)))
[10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36..]
 (set bsym (take (* 2 n) (concat (concat (take 3 'AAPL) (take 2 'MSFT)) (take 1 'GOOG))))
[AAPL AAPL AAPL MSFT MSFT GOOG AAPL AAPL AAPL MSFT MSFT GOOG AAPL AAPL AAPL MSFT..]
 (set btime (+ 09:00:00 (as 'Time (/ (* (til (* 2 n)) 2) 10))))
[09:00:00.000 09:00:00.000 09:00:00.000 09:00:00.000 09:00:00.000 09:00:00.001 09:00:00.001..]
 (set bid (+ 8 (/ (til (* 2 n))2)))
[8 8 9 9 10 10 11 11 12 12 13 13 14 14 15 15 16 16 17 17 18 18 19 19 20 20 21 21..]
 (set ask (+ 12 (/ (til (* 2 n))2)))
[12 12 13 13 14 14 15 15 16 16 17 17 18 18 19 19 20 20 21 21 22 22 23 23 24 24 25..]
 (set trades (table [Sym Ts Price] (list tsym ttime price)))
┌──────┬──────────────┬──────────────────────┐
 Sym   Ts            Price                
├──────┼──────────────┼──────────────────────┤
 AAPL  09:00:00.000  10                   
 AAPL  09:00:00.000  11                   
 AAPL  09:00:00.000  12                   
 AAPL  09:00:00.000  13                   
 AAPL  09:00:00.001  14                   
 AAPL  09:00:00.001  15                   
 AAPL  09:00:00.001  16                   
 AAPL  09:00:00.002  17                   
 AAPL  09:00:00.002  18                   
 AAPL  09:00:00.002  19                   
                                       
 AAPL  09:00:29.997  100000               
 AAPL  09:00:29.997  100001               
 AAPL  09:00:29.997  100002               
 AAPL  09:00:29.997  100003               
 AAPL  09:00:29.998  100004               
 AAPL  09:00:29.998  100005               
 AAPL  09:00:29.998  100006               
 AAPL  09:00:29.999  100007               
 AAPL  09:00:29.999  100008               
 MSFT  09:00:29.999  100009               
├──────┴──────────────┴──────────────────────┤
 100000 rows (20 shown) 3 columns (3 shown) 
└────────────────────────────────────────────┘
 (set quotes (table [Sym Ts Bid Ask] (list bsym btime bid ask)))
┌──────┬──────────────┬────────┬─────────────┐
 Sym   Ts            Bid     Ask         
├──────┼──────────────┼────────┼─────────────┤
 AAPL  09:00:00.000  8       12          
 AAPL  09:00:00.000  8       12          
 AAPL  09:00:00.000  9       13          
 MSFT  09:00:00.000  9       13          
 MSFT  09:00:00.000  10      14          
 GOOG  09:00:00.001  10      14          
 AAPL  09:00:00.001  11      15          
 AAPL  09:00:00.001  11      15          
 AAPL  09:00:00.001  12      16          
 MSFT  09:00:00.001  12      16          
                                     
 MSFT  09:00:39.998  100003  100007      
 GOOG  09:00:39.998  100003  100007      
 AAPL  09:00:39.998  100004  100008      
 AAPL  09:00:39.998  100004  100008      
 AAPL  09:00:39.998  100005  100009      
 MSFT  09:00:39.999  100005  100009      
 MSFT  09:00:39.999  100006  100010      
 GOOG  09:00:39.999  100006  100010      
 AAPL  09:00:39.999  100007  100011      
 AAPL  09:00:39.999  100007  100011      
├──────┴──────────────┴────────┴─────────────┤
 200000 rows (20 shown) 4 columns (4 shown) 
└────────────────────────────────────────────┘
 (set intervals (map-left + [-1000 1000] (at trades 'Ts)))
(
  [08:59:59.000 08:59:59.000 08:59:59.000 08:59:59.000 08:59:59.001 08:59:59.001 08:59:59.001..]
  [09:00:01.000 09:00:01.000 09:00:01.000 09:00:01.000 09:00:01.001 09:00:01.001 09:00:01.001..]
)
 (window-join [Sym Ts] intervals trades quotes {bid: (min Bid) ask: (max Ask)})
┌──────┬──────────────┬────────┬───────┬───────┐
 Sym   Ts            Price   bid    ask   
├──────┼──────────────┼────────┼───────┼───────┤
 AAPL  09:00:00.000  10      8      2514  
 AAPL  09:00:00.000  11      8      2514  
 AAPL  09:00:00.000  12      8      2514  
 AAPL  09:00:00.000  13      8      2514  
 AAPL  09:00:00.001  14      8      2515  
 AAPL  09:00:00.001  15      8      2515  
 AAPL  09:00:00.001  16      8      2515  
 AAPL  09:00:00.002  17      8      2518  
 AAPL  09:00:00.002  18      8      2518  
 AAPL  09:00:00.002  19      8      2518  
                                     
 AAPL  09:00:29.997  100000  72501  77506 
 AAPL  09:00:29.997  100001  72501  77506 
 AAPL  09:00:29.997  100002  72501  77506 
 AAPL  09:00:29.997  100003  72501  77506 
 AAPL  09:00:29.998  100004  72504  77509 
 AAPL  09:00:29.998  100005  72504  77509 
 AAPL  09:00:29.998  100006  72504  77509 
 AAPL  09:00:29.999  100007  72507  77511 
 AAPL  09:00:29.999  100008  72507  77511 
 MSFT  09:00:29.999  100009  72507  77510 
├──────┴──────────────┴────────┴───────┴───────┤
 100000 rows (20 shown) 5 columns (5 shown)   
└──────────────────────────────────────────────┘
 (window-join1 [Sym Ts] intervals trades quotes {bid: (min Bid) ask: (max Ask)})
┌──────┬──────────────┬────────┬───────┬───────┐
 Sym   Ts            Price   bid    ask   
├──────┼──────────────┼────────┼───────┼───────┤
 AAPL  09:00:00.000  10      8      2514  
 AAPL  09:00:00.000  11      8      2514  
 AAPL  09:00:00.000  12      8      2514  
 AAPL  09:00:00.000  13      8      2514  
 AAPL  09:00:00.001  14      8      2515  
 AAPL  09:00:00.001  15      8      2515  
 AAPL  09:00:00.001  16      8      2515  
 AAPL  09:00:00.002  17      8      2518  
 AAPL  09:00:00.002  18      8      2518  
 AAPL  09:00:00.002  19      8      2518  
                                     
 AAPL  09:00:29.997  100000  72500  77506 
 AAPL  09:00:29.997  100001  72500  77506 
 AAPL  09:00:29.997  100002  72500  77506 
 AAPL  09:00:29.997  100003  72500  77506 
 AAPL  09:00:29.998  100004  72503  77509 
 AAPL  09:00:29.998  100005  72503  77509 
 AAPL  09:00:29.998  100006  72503  77509 
 AAPL  09:00:29.999  100007  72506  77511 
 AAPL  09:00:29.999  100008  72506  77511 
 MSFT  09:00:29.999  100009  72507  77510 
├──────┴──────────────┴────────┴───────┴───────┤
 100000 rows (20 shown) 5 columns (5 shown)   
└──────────────────────────────────────────────┘

Info

The difference between window-join and window-join1 is how they interpret window intervals: window-join1 include interval bounds into aggregation.