Asof Join asof-join¶
Returns a table with records from the left-join of t1 and t2. Join columns (but last one) are matched for equality. The last one matches by the greatest value of resulting set.
(set n 10000000)
10000000
↪ (set trades (table [Sym Ts Qty Price] (list (take n (as 'Symbol (map-right as 'String (til 8000)))) (as 'Time (+ 2 (* 1000 (til n)))) (til n) (as 'F64 (til n)))))
┌──────┬───────────────┬─────────┬──────────────┐
│ Sym │ Ts │ Qty │ Price │
├──────┼───────────────┼─────────┼──────────────┤
│ 0 │ 00:00:00.002 │ 0 │ 0.00 │
│ 1 │ 00:00:01.002 │ 1 │ 1.00 │
│ 2 │ 00:00:02.002 │ 2 │ 2.00 │
│ 3 │ 00:00:03.002 │ 3 │ 3.00 │
│ 4 │ 00:00:04.002 │ 4 │ 4.00 │
│ 5 │ 00:00:05.002 │ 5 │ 5.00 │
│ 6 │ 00:00:06.002 │ 6 │ 6.00 │
│ 7 │ 00:00:07.002 │ 7 │ 7.00 │
│ 8 │ 00:00:08.002 │ 8 │ 8.00 │
│ 9 │ 00:00:09.002 │ 9 │ 9.00 │
┆ … ┆ … ┆ … ┆ … ┆
│ 7990 │ 135:40:55.410 │ 9999990 │ 9.999990e+06 │
│ 7991 │ 135:40:56.410 │ 9999991 │ 9.999991e+06 │
│ 7992 │ 135:40:57.410 │ 9999992 │ 9.999992e+06 │
│ 7993 │ 135:40:58.410 │ 9999993 │ 9.999993e+06 │
│ 7994 │ 135:40:59.410 │ 9999994 │ 9.999994e+06 │
│ 7995 │ 135:41:00.410 │ 9999995 │ 9.999995e+06 │
│ 7996 │ 135:41:01.410 │ 9999996 │ 9.999996e+06 │
│ 7997 │ 135:41:02.410 │ 9999997 │ 9.999997e+06 │
│ 7998 │ 135:41:03.410 │ 9999998 │ 9.999998e+06 │
│ 7999 │ 135:41:04.410 │ 9999999 │ 9.999999e+06 │
├──────┴───────────────┴─────────┴──────────────┤
│ 10000000 rows (20 shown) 4 columns (4 shown) │
└───────────────────────────────────────────────┘
↪ (set quotes (table [Sym Ts Bid Ask] (list (take n (as 'Symbol (map-right as 'String (til 18000)))) (as 'Time (* 1000 (til n))) (as 'F64 (til n)) (as 'F64 (til n)))))
┌──────┬───────────────┬──────────────┬──────────────┐
│ Sym │ Ts │ Bid │ Ask │
├──────┼───────────────┼──────────────┼──────────────┤
│ 0 │ 00:00:00.000 │ 0.00 │ 0.00 │
│ 1 │ 00:00:01.000 │ 1.00 │ 1.00 │
│ 2 │ 00:00:02.000 │ 2.00 │ 2.00 │
│ 3 │ 00:00:03.000 │ 3.00 │ 3.00 │
│ 4 │ 00:00:04.000 │ 4.00 │ 4.00 │
│ 5 │ 00:00:05.000 │ 5.00 │ 5.00 │
│ 6 │ 00:00:06.000 │ 6.00 │ 6.00 │
│ 7 │ 00:00:07.000 │ 7.00 │ 7.00 │
│ 8 │ 00:00:08.000 │ 8.00 │ 8.00 │
│ 9 │ 00:00:09.000 │ 9.00 │ 9.00 │
┆ … ┆ … ┆ … ┆ … ┆
│ 9990 │ 135:40:55.408 │ 9.999990e+06 │ 9.999990e+06 │
│ 9991 │ 135:40:56.408 │ 9.999991e+06 │ 9.999991e+06 │
│ 9992 │ 135:40:57.408 │ 9.999992e+06 │ 9.999992e+06 │
│ 9993 │ 135:40:58.408 │ 9.999993e+06 │ 9.999993e+06 │
│ 9994 │ 135:40:59.408 │ 9.999994e+06 │ 9.999994e+06 │
│ 9995 │ 135:41:00.408 │ 9.999995e+06 │ 9.999995e+06 │
│ 9996 │ 135:41:01.408 │ 9.999996e+06 │ 9.999996e+06 │
│ 9997 │ 135:41:02.408 │ 9.999997e+06 │ 9.999997e+06 │
│ 9998 │ 135:41:03.408 │ 9.999998e+06 │ 9.999998e+06 │
│ 9999 │ 135:41:04.408 │ 9.999999e+06 │ 9.999999e+06 │
├──────┴───────────────┴──────────────┴──────────────┤
│ 10000000 rows (20 shown) 4 columns (4 shown) │
└────────────────────────────────────────────────────┘
↪ (asof-join [Sym Ts] trades quotes)
┌──────┬───────────────┬──────────────┬──────────────┬─────────┬──────────────┐
│ Sym │ Ts │ Bid │ Ask │ Qty │ Price │
├──────┼───────────────┼──────────────┼──────────────┼─────────┼──────────────┤
│ 0 │ 00:00:00.002 │ 4.284000e+06 │ 4.284000e+06 │ 0 │ 0.00 │
│ 1 │ 00:00:01.002 │ 4.284001e+06 │ 4.284001e+06 │ 1 │ 1.00 │
│ 2 │ 00:00:02.002 │ 4.284002e+06 │ 4.284002e+06 │ 2 │ 2.00 │
│ 3 │ 00:00:03.002 │ 4.284003e+06 │ 4.284003e+06 │ 3 │ 3.00 │
│ 4 │ 00:00:04.002 │ 4.284004e+06 │ 4.284004e+06 │ 4 │ 4.00 │
│ 5 │ 00:00:05.002 │ 4.284005e+06 │ 4.284005e+06 │ 5 │ 5.00 │
│ 6 │ 00:00:06.002 │ 4.284006e+06 │ 4.284006e+06 │ 6 │ 6.00 │
│ 7 │ 00:00:07.002 │ 4.284007e+06 │ 4.284007e+06 │ 7 │ 7.00 │
│ 8 │ 00:00:08.002 │ 4.284008e+06 │ 4.284008e+06 │ 8 │ 8.00 │
│ 9 │ 00:00:09.002 │ 4.284009e+06 │ 4.284009e+06 │ 9 │ 9.00 │
┆ … ┆ … ┆ … ┆ … ┆ … ┆ … ┆
│ 7990 │ 135:40:55.410 │ 9.997990e+06 │ 9.997990e+06 │ 9999990 │ 9.999990e+06 │
│ 7991 │ 135:40:56.410 │ 9.997991e+06 │ 9.997991e+06 │ 9999991 │ 9.999991e+06 │
│ 7992 │ 135:40:57.410 │ 9.997992e+06 │ 9.997992e+06 │ 9999992 │ 9.999992e+06 │
│ 7993 │ 135:40:58.410 │ 9.997993e+06 │ 9.997993e+06 │ 9999993 │ 9.999993e+06 │
│ 7994 │ 135:40:59.410 │ 9.997994e+06 │ 9.997994e+06 │ 9999994 │ 9.999994e+06 │
│ 7995 │ 135:41:00.410 │ 9.997995e+06 │ 9.997995e+06 │ 9999995 │ 9.999995e+06 │
│ 7996 │ 135:41:01.410 │ 9.997996e+06 │ 9.997996e+06 │ 9999996 │ 9.999996e+06 │
│ 7997 │ 135:41:02.410 │ 9.997997e+06 │ 9.997997e+06 │ 9999997 │ 9.999997e+06 │
│ 7998 │ 135:41:03.410 │ 9.997998e+06 │ 9.997998e+06 │ 9999998 │ 9.999998e+06 │
│ 7999 │ 135:41:04.410 │ 9.997999e+06 │ 9.997999e+06 │ 9999999 │ 9.999999e+06 │
├──────┴───────────────┴──────────────┴──────────────┴─────────┴──────────────┤
│ 10000000 rows (20 shown) 6 columns (6 shown) │
└─────────────────────────────────────────────────────────────────────────────┘