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)                                │
└─────────────────────────────────────────────────────────────────────────────┘