How to join tables¶
This page shows how to combine tables in q and compares each join with its ANSI SQL equivalent, or with pandas where SQL has none.
A join combines the columns or rows of two tables. q provides a keyword for each common kind of join, so you rarely write a join condition. Instead, the shape of the arguments decides how rows match:
- Keyed joins, such as
ljandij, match the columns of the first table with the key columns of a keyed table. - As-of joins, such as
ajandwj, match on one or more columns for equality and on a time column for the most recent value. They are central to time-series analysis and have no direct equivalent in ANSI SQL.
For the full list of join operators, refer to the joins reference.
Sample tables¶
The examples use US equity trades and quotes generated by the datagen module. To create the tables in memory:
q)([getInMemoryTables]): use `kx.datagen.capmkts
q)(trade; quote; nbbo; master; exnames): getInMemoryTables[]
The examples use these tables:
trade: one row per trade, with the exchange where it executed inex.quote: one row per bid and ask quote.master: reference data, keyed onsym. Itsexcolumn holds the exchange where the stock is listed.exnames: a dictionary from exchange code to exchange name.
q)3#master
sym | description cusip securityType testFlag ex roundLot issueprice
----| -------------------------------------------------------------------------------------------
AAPL| "Apple Inc." 037833100 A 0 Q 100 255.44
AGEN| "Agenus Inc." 00847G804 A 0 Q 100 3.99
AIG | "American International Group, Inc." 026874784 A 0 N 100 79.86
To keep the results short, most examples join t, the first five trades of the day, and qt, the quote table without its size columns:
q)show t:5#select sym, time, price, size, ex from trade
sym time price size ex
----------------------------------------
CUZ 0D09:30:00.011627745 29.06 65 L
PRU 0D09:30:00.153776659 103.02 20 J
TSLA 0D09:30:00.167902241 463.29 27 V
EMA 0D09:30:01.205070767 47.51 12 M
CCL 0D09:30:01.508512258 28.55 29 L
q)qt:select sym, time, bid, ask from quote
Load the same tables into an SQL database or pandas
Export the tables from q to CSV files. The commands below remove the 0D prefix from the timespans, so the time column loads into an SQL TIME column:
q)`:trade.csv 0: csv 0: update time:2_'string time from trade
`:trade.csv
q)`:quote.csv 0: csv 0: update time:2_'string time from select sym, time, bid, ask from quote
`:quote.csv
q)`:master.csv 0: csv 0: select sym, description, ex from master
`:master.csv
Create the tables, load the CSV files with your database's import tool, and then create t:
CREATE TABLE trade (sym VARCHAR(16), time TIME(9), price DECIMAL(10,2), size INT,
stop BOOLEAN, cond CHAR(1), ex CHAR(1));
CREATE TABLE quote (sym VARCHAR(16), time TIME(9), bid DECIMAL(10,2), ask DECIMAL(10,2));
CREATE TABLE master (sym VARCHAR(16) PRIMARY KEY, description VARCHAR(100), ex CHAR(1));
CREATE TABLE t AS (SELECT sym, time, price, size, ex FROM trade
ORDER BY time FETCH FIRST 5 ROWS ONLY) WITH DATA;
The SQL results on this page come from SQLite. SQL does not guarantee row order without ORDER BY, so your database might return rows in a different order.
The pandas examples load the same CSV files:
>>> import pandas as pd
>>> trade = pd.read_csv('trade.csv', usecols=['sym', 'time', 'price', 'size', 'ex'])
>>> quote = pd.read_csv('quote.csv')
>>> trade['time'] = pd.to_timedelta(trade['time'])
>>> quote['time'] = pd.to_timedelta(quote['time'])
>>> t = trade.head(5)
Left join: lj¶
A left join keeps every row of the first table and adds the columns of the second. In q, the second argument of lj must be a keyed table, and its key columns are the join columns. To add the company name and listing exchange to each trade, key the columns you need from master on sym with xkey:
q
q)t lj `sym xkey select sym, description, listEx:ex from master
sym time price size ex description listEx
----------------------------------------------------------------------------
CUZ 0D09:30:00.011627745 29.06 65 L "Cousins Properties Inc." N
PRU 0D09:30:00.153776659 103.02 20 J "Prudential Financial, Inc." N
TSLA 0D09:30:00.167902241 463.29 27 V "Tesla, Inc. Common Stock" Q
EMA 0D09:30:01.205070767 47.51 12 M "Emera Incorporated" N
CCL 0D09:30:01.508512258 28.55 29 L "Carnival Corporation" N
SQL
SELECT t.*, m.description, m.ex AS listEx
FROM t
LEFT JOIN master m ON t.sym = m.sym;
sym time price size ex description listEx
---- ------------------ ------ ---- -- -------------------------- ------
CUZ 09:30:00.011627745 29.06 65 L Cousins Properties Inc. N
PRU 09:30:00.153776659 103.02 20 J Prudential Financial, Inc. N
TSLA 09:30:00.167902241 463.29 27 V Tesla, Inc. Common Stock Q
EMA 09:30:01.205070767 47.51 12 M Emera Incorporated N
CCL 09:30:01.508512258 28.55 29 L Carnival Corporation N
The result always has the same number of rows as the first table, because each key in a keyed table identifies a single row.
q has no right join. To get one, swap the arguments, keying the original left table first. This works only if that table's join columns are unique.
Columns that appear in both tables¶
The example above renames ex to listEx, because both tables have an ex column with different meanings. If a non-key column appears in both tables, lj takes its value from the second table for every matching row, even when that value is null. Without the rename, the listing exchange replaces the trade's exchange:
q
q)t lj `sym xkey select sym, ex from master
sym time price size ex
----------------------------------------
CUZ 0D09:30:00.011627745 29.06 65 N
PRU 0D09:30:00.153776659 103.02 20 N
TSLA 0D09:30:00.167902241 463.29 27 Q
EMA 0D09:30:01.205070767 47.51 12 N
CCL 0D09:30:01.508512258 28.55 29 N
SQL
SELECT t.sym, t.time, t.price, t.size,
CASE WHEN m.sym IS NULL THEN t.ex
ELSE m.ex END AS ex
FROM t
LEFT JOIN master m ON t.sym = m.sym;
sym time price size ex
---- ------------------ ------ ---- --
CUZ 09:30:00.011627745 29.06 65 N
PRU 09:30:00.153776659 103.02 20 N
TSLA 09:30:00.167902241 463.29 27 Q
EMA 09:30:01.205070767 47.51 12 N
CCL 09:30:01.508512258 28.55 29 N
The column also changes type, from symbol in t to char in master, and q signals no error. This behavior is the same as an upsert, and it is useful when the second table holds corrections. Otherwise, rename the clashing columns in the second table before you join.
Keyed joins also match the join columns by name. If they have different names in the two tables, rename one of them with xcol first.
Rows with no match¶
Every stock in t appears in master. Where a row of the first table has no match, the new columns are null. To show this, the following keyed table assigns a sector to only some of the stocks:
q
q)show sector:([sym:`CUZ`TSLA`CCL`AAPL] sector:`RealEstate`ConsumerDisc`ConsumerDisc`Technology)
sym | sector
----| ------------
CUZ | RealEstate
TSLA| ConsumerDisc
CCL | ConsumerDisc
AAPL| Technology
q)t lj sector
sym time price size ex sector
-----------------------------------------------------
CUZ 0D09:30:00.011627745 29.06 65 L RealEstate
PRU 0D09:30:00.153776659 103.02 20 J
TSLA 0D09:30:00.167902241 463.29 27 V ConsumerDisc
EMA 0D09:30:01.205070767 47.51 12 M
CCL 0D09:30:01.508512258 28.55 29 L ConsumerDisc
SQL
CREATE TABLE sector (sym VARCHAR(16) PRIMARY KEY, sector VARCHAR(16));
INSERT INTO sector VALUES
('CUZ','RealEstate'), ('TSLA','ConsumerDisc'),
('CCL','ConsumerDisc'), ('AAPL','Technology');
SELECT t.*, s.sector
FROM t
LEFT JOIN sector s ON t.sym = s.sym;
sym time price size ex sector
---- ------------------ ------ ---- -- ------------
CUZ 09:30:00.011627745 29.06 65 L RealEstate
PRU 09:30:00.153776659 103.02 20 J
TSLA 09:30:00.167902241 463.29 27 V ConsumerDisc
EMA 09:30:01.205070767 47.51 12 M
CCL 09:30:01.508512258 28.55 29 L ConsumerDisc
Join on several columns¶
To match on more than one column, key the second table on all of them. A select with a by clause returns a table keyed on the by columns, so you can join an aggregation directly. The following adds the total volume each stock traded on each exchange during the day:
q
q)t lj select exVol:sum size by sym, ex from trade
sym time price size ex exVol
----------------------------------------------
CUZ 0D09:30:00.011627745 29.06 65 L 467
PRU 0D09:30:00.153776659 103.02 20 J 560
TSLA 0D09:30:00.167902241 463.29 27 V 978
EMA 0D09:30:01.205070767 47.51 12 M 341
CCL 0D09:30:01.508512258 28.55 29 L 233
SQL
SELECT t.*, v.exVol
FROM t
LEFT JOIN (SELECT sym, ex, SUM(size) AS exVol
FROM trade GROUP BY sym, ex) v
ON t.sym = v.sym AND t.ex = v.ex;
sym time price size ex exVol
---- ------------------ ------ ---- -- -----
CUZ 09:30:00.011627745 29.06 65 L 467
PRU 09:30:00.153776659 103.02 20 J 560
TSLA 09:30:00.167902241 463.29 27 V 978
EMA 09:30:01.205070767 47.51 12 M 341
CCL 09:30:01.508512258 28.55 29 L 233
Inner join: ij¶
An inner join returns only the rows of the first table that have a match in the second. Like lj, ij takes a keyed table as its second argument:
q
q)t ij sector
sym time price size ex sector
-----------------------------------------------------
CUZ 0D09:30:00.011627745 29.06 65 L RealEstate
TSLA 0D09:30:00.167902241 463.29 27 V ConsumerDisc
CCL 0D09:30:01.508512258 28.55 29 L ConsumerDisc
SQL
SELECT t.*, s.sector
FROM t
INNER JOIN sector s ON t.sym = s.sym;
sym time price size ex sector
---- ------------------ ------ ---- -- ------------
CUZ 09:30:00.011627745 29.06 65 L RealEstate
TSLA 09:30:00.167902241 463.29 27 V ConsumerDisc
CCL 09:30:01.508512258 28.55 29 L ConsumerDisc
The PRU and EMA trades are dropped because sector has no row for them.
Equi join: ej¶
An SQL inner join returns one row for every matching pair, so a row of the first table can appear many times. ej does the same. It takes the join columns as its first argument, and the second table does not need to be keyed. The following table lists the benchmark indexes that some of the stocks belong to. TSLA belongs to two:
q
q)show idx:([] sym:`TSLA`TSLA`PRU`CCL; bench:`SPX`NDX`SPX`SPX)
sym bench
----------
TSLA SPX
TSLA NDX
PRU SPX
CCL SPX
q)ej[`sym;t;idx]
sym time price size ex bench
----------------------------------------------
PRU 0D09:30:00.153776659 103.02 20 J SPX
TSLA 0D09:30:00.167902241 463.29 27 V SPX
TSLA 0D09:30:00.167902241 463.29 27 V NDX
CCL 0D09:30:01.508512258 28.55 29 L SPX
SQL
CREATE TABLE idx (sym VARCHAR(16), bench VARCHAR(8));
INSERT INTO idx VALUES
('TSLA','SPX'), ('TSLA','NDX'), ('PRU','SPX'), ('CCL','SPX');
SELECT t.*, i.bench
FROM t
INNER JOIN idx i ON t.sym = i.sym;
sym time price size ex bench
---- ------------------ ------ ---- -- -----
TSLA 09:30:00.167902241 463.29 27 V SPX
TSLA 09:30:00.167902241 463.29 27 V NDX
PRU 09:30:00.153776659 103.02 20 J SPX
CCL 09:30:01.508512258 28.55 29 L SPX
Compare this with ij. If you key idx on sym, which has duplicates, ij uses only the first matching row for each key:
q)t ij `sym xkey idx
sym time price size ex bench
----------------------------------------------
PRU 0D09:30:00.153776659 103.02 20 J SPX
TSLA 0D09:30:00.167902241 463.29 27 V SPX
CCL 0D09:30:01.508512258 28.55 29 L SPX
Use ej when the second table can have several matches per row and you want all of them. Use ij or lj when each row has at most one match.
Union join: uj¶
uj combines all the rows and all the columns of two tables. Its behavior depends on whether the arguments are keyed. Both arguments must be keyed, or neither.
Keyed tables: full outer join¶
With two keyed tables, uj returns one row for each key in either table. This is a full outer join on the key columns. The following compares the volume each stock traded in the first and last two seconds of the day. Some stocks traded in only one of them:
q
q)openVol:select openVol:sum size by sym from trade where time<09:30:02
q)closeVol:select closeVol:sum size by sym from trade where time>=15:59:58
q)openVol uj closeVol
sym | openVol closeVol
------| ----------------
CCL | 29 79
CUZ | 65 20
EMA | 12
ITOL | 67
PBW | 82
PRU | 20
TSLA | 27
ARBE.W| 32
BSBK | 43
DERM | 12
IBM | 72
ISHG | 90
META | 17
PEP | 97
VDE | 17
SQL
SELECT COALESCE(o.sym, c.sym) AS sym,
o.openVol, c.closeVol
FROM (SELECT sym, SUM(size) AS openVol FROM trade
WHERE time < '09:30:02' GROUP BY sym) o
FULL OUTER JOIN
(SELECT sym, SUM(size) AS closeVol FROM trade
WHERE time >= '15:59:58' GROUP BY sym) c
ON o.sym = c.sym;
sym openVol closeVol
------ ------- --------
CCL 29 79
CUZ 65 20
EMA 12
ITOL 67
PBW 82
PRU 20
TSLA 27
ARBE.W 32
BSBK 43
DERM 12
IBM 72
ISHG 90
META 17
PEP 97
VDE 17
As with lj, values from the second table replace values from the first table in columns that appear in both.
Unkeyed tables: append rows¶
To append the rows of a table with the same columns, use the join operator ,. This is the equivalent of UNION ALL. The following appends the last two trades of the day to t:
q
q)last2:-2#select sym, time, price, size, ex from trade
q)t,last2
sym time price size ex
----------------------------------------
CUZ 0D09:30:00.011627745 29.06 65 L
PRU 0D09:30:00.153776659 103.02 20 J
TSLA 0D09:30:00.167902241 463.29 27 V
EMA 0D09:30:01.205070767 47.51 12 M
CCL 0D09:30:01.508512258 28.55 29 L
VDE 0D15:59:59.899706248 133.57 17 Y
ISHG 0D15:59:59.911661552 80.48 90 Y
SQL
SELECT * FROM t
UNION ALL
SELECT * FROM last2;
sym time price size ex
---- ------------------ ------ ---- --
CUZ 09:30:00.011627745 29.06 65 L
PRU 09:30:00.153776659 103.02 20 J
TSLA 09:30:00.167902241 463.29 27 V
EMA 09:30:01.205070767 47.51 12 M
CCL 09:30:01.508512258 28.55 29 L
VDE 15:59:59.899706248 133.57 17 Y
ISHG 15:59:59.911661552 80.48 90 Y
If the tables have different columns, such as trades and quotes, , signals a 'mismatch error. Use uj instead. It matches columns by name and fills missing values with nulls, whereas in SQL you must list the columns and add the missing ones yourself:
q
q)q2:2#qt
q)t,q2
'mismatch
q)t uj q2
sym time price size ex bid ask
------------------------------------------------------
CUZ 0D09:30:00.011627745 29.06 65 L
PRU 0D09:30:00.153776659 103.02 20 J
TSLA 0D09:30:00.167902241 463.29 27 V
EMA 0D09:30:01.205070767 47.51 12 M
CCL 0D09:30:01.508512258 28.55 29 L
ORCL 0D09:30:00.003406498 289.88 290.04
TZOO 0D09:30:00.004116350 9.87 9.89
SQL
SELECT sym, time, price, size, ex,
NULL AS bid, NULL AS ask
FROM t
UNION ALL
SELECT sym, time, NULL, NULL, NULL, bid, ask
FROM q2;
sym time price size ex bid ask
---- ------------------ ------ ---- -- ------ ------
CUZ 09:30:00.011627745 29.06 65 L
PRU 09:30:00.153776659 103.02 20 J
TSLA 09:30:00.167902241 463.29 27 V
EMA 09:30:01.205070767 47.51 12 M
CCL 09:30:01.508512258 28.55 29 L
ORCL 09:30:00.003406498 289.88 290.04
TZOO 09:30:00.004116350 9.87 9.89
uj does not check column types
uj on unkeyed tables appends rows without checking that matching columns have the same type. If the types differ, the result has a mixed list column. Use upsert or , when you want q to signal an error instead.
Plus join: pj¶
pj is a left join that adds values instead of replacing them. For each matching row, it adds the numeric columns of the second table to the same columns of the first. A null in the second table counts as zero. The following adds the afternoon volume of each stock to its morning volume:
q
q)am:select sum size by sym from trade where time<12:00
q)pm:select sum size by sym from trade where time>=12:00
q)3#am pj pm
sym | size
----| ----
AAPL| 2788
AGEN| 3357
AIG | 3078
SQL
SELECT a.sym,
a.size + COALESCE(p.size, 0) AS size
FROM (SELECT sym, SUM(size) AS size FROM trade
WHERE time < '12:00' GROUP BY sym) a
LEFT JOIN
(SELECT sym, SUM(size) AS size FROM trade
WHERE time >= '12:00' GROUP BY sym) p
ON a.sym = p.sym
ORDER BY a.sym
FETCH FIRST 3 ROWS ONLY;
sym size
---- ----
AAPL 2788
AGEN 3357
AIG 3078
The result is the volume for the whole day:
q)(am pj pm)~select sum size by sym from trade
1b
As with any left join, pj drops the rows of the second table that have no match in the first.
Join row by row: ,'¶
To join two tables of the same length row by row, use join with the Each iterator: ,'. Row i of the result combines row i of each table, and no columns are matched. SQL tables have no row order, so there is no SQL equivalent. In pandas, use concat along the column axis:
q
q)q5:5#qt
q)q5,'select mid:0.5*bid+ask, spread:ask-bid from q5
sym time bid ask mid spread
-----------------------------------------------------
ORCL 0D09:30:00.003406498 289.88 290.04 289.96 0.16
TZOO 0D09:30:00.004116350 9.87 9.89 9.88 0.02
VWID 0D09:30:00.004184972 33.85 33.87 33.86 0.02
CUZ 0D09:30:00.011627744 29.05 29.07 29.06 0.02
PBW 0D09:30:00.108156062 30.51 30.53 30.52 0.02
pandas
>>> q5 = quote.head(5)
>>> pd.concat([q5, pd.DataFrame({'mid': 0.5 * (q5.bid + q5.ask), 'spread': q5.ask - q5.bid})], axis=1)
sym time bid ask mid spread
0 ORCL 0 days 09:30:00.003406498 289.88 290.04 289.96 0.16
1 TZOO 0 days 09:30:00.004116350 9.87 9.89 9.88 0.02
2 VWID 0 days 09:30:00.004184972 33.85 33.87 33.86 0.02
3 CUZ 0 days 09:30:00.011627744 29.05 29.07 29.06 0.02
4 PBW 0 days 09:30:00.108156062 30.51 30.53 30.52 0.02
If both tables have a column with the same name, the result takes it from the second table.
Look up values in a dictionary¶
A dictionary maps keys to values, so indexing it with a column is a lookup join. exnames maps each exchange code to its name:
q)update exname:exnames ex from t
sym time price size ex exname
-------------------------------------------------------------------
CUZ 0D09:30:00.011627745 29.06 65 L "Long-Term Stock Exchange"
PRU 0D09:30:00.153776659 103.02 20 J "Cboe EDGA Exchange"
TSLA 0D09:30:00.167902241 463.29 27 V "The Investors' Exchange"
EMA 0D09:30:01.205070767 47.51 12 M "Chicago Stock Exchange"
CCL 0D09:30:01.508512258 28.55 29 L "Long-Term Stock Exchange"
In SQL, you would store the mapping as a table and use a left join.
Implicit join with a foreign key¶
If a column is a foreign key to a keyed table, you can reach the columns of that table with dot notation in a query. q performs the join for you. The following makes sym a foreign key to master and reads the company name and listing exchange through it:
q
q)f:update sym:`master$sym from t
q)select sym, time, price, sym.description, sym.ex from f
sym time price description ex
----------------------------------------------------------------
CUZ 0D09:30:00.011627745 29.06 "Cousins Properties Inc." N
PRU 0D09:30:00.153776659 103.02 "Prudential Financial, Inc." N
TSLA 0D09:30:00.167902241 463.29 "Tesla, Inc. Common Stock" Q
EMA 0D09:30:01.205070767 47.51 "Emera Incorporated" N
CCL 0D09:30:01.508512258 28.55 "Carnival Corporation" N
SQL
SELECT t.sym, t.time, t.price,
m.description, m.ex
FROM t
JOIN master m ON t.sym = m.sym;
sym time price description ex
---- ------------------ ------ -------------------------- --
CUZ 09:30:00.011627745 29.06 Cousins Properties Inc. N
PRU 09:30:00.153776659 103.02 Prudential Financial, Inc. N
TSLA 09:30:00.167902241 463.29 Tesla, Inc. Common Stock Q
EMA 09:30:01.205070767 47.51 Emera Incorporated N
CCL 09:30:01.508512258 28.55 Carnival Corporation N
As in SQL, the foreign key enforces referential integrity. A value that is not a key of master signals an error:
q)`master$`XYZ
'cast
The foreign key is resolved once, when you create the column, so an implicit join is faster than an explicit lj and builds no intermediate table.
As-of join: aj¶
An as-of join matches each row of the first table with the most recent row of the second table at or before its time. The typical use is finding the quote that prevailed at the time of each trade.
ANSI SQL has no as-of join. Some databases add one as an extension, but in standard SQL you need a correlated subquery or a window function, which is slow on large tables. pandas provides merge_asof.
These are the quotes for the stocks in t up to the time of the last trade in t:
q)select from qt where sym in t`sym, time<09:30:01.6
sym time bid ask
---------------------------------------
CUZ 0D09:30:00.011627744 29.05 29.07
PRU 0D09:30:00.153776659 103.01 103.05
CCL 0D09:30:00.155456672 28.55 28.57
TSLA 0D09:30:00.167902243 463.22 463.43
TSLA 0D09:30:00.275206284 463.22 463.4
EMA 0D09:30:00.386418549 47.5 47.53
TSLA 0D09:30:00.653276833 463.29 463.47
EMA 0D09:30:01.205070767 47.5 47.52
CCL 0D09:30:01.304340681 28.55 28.57
CCL 0D09:30:01.508512259 28.54 28.56
The first argument of aj lists the join columns. All but the last must match exactly, and the last is the column searched as of, typically time:
q
q)aj[`sym`time;t;qt]
sym time price size ex bid ask
------------------------------------------------------
CUZ 0D09:30:00.011627745 29.06 65 L 29.05 29.07
PRU 0D09:30:00.153776659 103.02 20 J 103.01 103.05
TSLA 0D09:30:00.167902241 463.29 27 V
EMA 0D09:30:01.205070767 47.51 12 M 47.5 47.52
CCL 0D09:30:01.508512258 28.55 29 L 28.55 28.57
pandas
>>> pd.merge_asof(t, quote, on='time', by='sym')
sym time price size ex bid ask
0 CUZ 0 days 09:30:00.011627745 29.06 65 L 29.05 29.07
1 PRU 0 days 09:30:00.153776659 103.02 20 J 103.01 103.05
2 TSLA 0 days 09:30:00.167902241 463.29 27 V NaN NaN
3 EMA 0 days 09:30:01.205070767 47.51 12 M 47.50 47.52
4 CCL 0 days 09:30:01.508512258 28.55 29 L 28.55 28.57
The CCL trade at 09:30:01.508512258 gets the quote from 09:30:01.304340681, not the one that arrived a nanosecond after the trade. The first TSLA quote arrived two nanoseconds after the TSLA trade, so the trade has no earlier quote and its quote columns are null.
The result keeps the time of each trade. To see the time of the matching quote instead, use aj0:
q)aj0[`sym`time;t;qt]
sym time price size ex bid ask
------------------------------------------------------
CUZ 0D09:30:00.011627744 29.06 65 L 29.05 29.07
PRU 0D09:30:00.153776659 103.02 20 J 103.01 103.05
TSLA 0D09:30:00.167902241 463.29 27 V
EMA 0D09:30:01.205070767 47.51 12 M 47.5 47.52
CCL 0D09:30:01.304340681 28.55 29 L 28.55 28.57
Sort the second table and list the time column last
aj returns the last matching row of the second table in row order. The second table must be sorted by time within each sym, otherwise the result is wrong but q signals no error. The same applies if you put the columns in the wrong order, such as `time`sym.
For large tables, apply the g# attribute to sym on an in-memory table, or use the p# attribute on disk. The datagen tables already have g# on sym. The aj reference explains the options.
Window join: wj¶
A window join aggregates the rows of the second table that fall within a time window around each row of the first. Use it, for example, to find the best bid and ask within one second either side of each trade.
The first argument of wj is a pair of lists: the start and end time of each window, one per row of the first table:
q)show w:(-1 1*0D00:00:01)+\:t`time
0D09:29:59.011627745 0D09:29:59.153776659 0D09:29:59.167902241 0D09:30:00.205070767 0D09:30:00.508512258
0D09:30:01.011627745 0D09:30:01.153776659 0D09:30:01.167902241 0D09:30:02.205070767 0D09:30:02.508512258
The last argument lists the second table, followed by an aggregation function and a column for each new column. The second table must be sorted by sym then time, with the p# attribute on sym.
The wj1 form uses only the quotes inside each window. Standard SQL can express this with a range condition and GROUP BY:
q
q)qp:update `p#sym from `sym`time xasc qt
q)wj1[w;`sym`time;t;(qp;(max;`bid);(min;`ask))]
sym time price size ex bid ask
------------------------------------------------------
CUZ 0D09:30:00.011627745 29.06 65 L 29.05 29.07
PRU 0D09:30:00.153776659 103.02 20 J 103.01 103.05
TSLA 0D09:30:00.167902241 463.29 27 V 463.29 463.4
EMA 0D09:30:01.205070767 47.51 12 M 47.5 47.52
CCL 0D09:30:01.508512258 28.55 29 L 28.55 28.56
SQL
SELECT t.sym, t.time, t.price, t.size, t.ex,
MAX(q.bid) AS bid, MIN(q.ask) AS ask
FROM t
LEFT JOIN quote q
ON q.sym = t.sym
AND q.time BETWEEN t.time - INTERVAL '1' SECOND
AND t.time + INTERVAL '1' SECOND
GROUP BY t.sym, t.time, t.price, t.size, t.ex
ORDER BY t.time;
sym time price size ex bid ask
---- ------------------ ------ ---- -- ------ ------
CUZ 09:30:00.011627745 29.06 65 L 29.05 29.07
PRU 09:30:00.153776659 103.02 20 J 103.01 103.05
TSLA 09:30:00.167902241 463.29 27 V 463.29 463.4
EMA 09:30:01.205070767 47.51 12 M 47.5 47.52
CCL 09:30:01.508512258 28.55 29 L 28.55 28.56
Unlike aj, the window extends past the trade, so the TSLA trade now picks up the quotes that arrived after it.
Where a window holds no quotes, q applies the aggregation to an empty list, so max returns negative infinity (-0w) and min returns infinity (0w). SQL returns null.
wj also includes the quote that prevails at the start of each window, because a quote stays valid until the next one arrives. This has no simple SQL equivalent. To see which values each form uses, pass the identity function :: in place of the aggregation:
q)wj[w;`sym`time;t;(qp;(::;`bid))]
sym time price size ex bid
-------------------------------------------------------------
CUZ 0D09:30:00.011627745 29.06 65 L ,29.05
PRU 0D09:30:00.153776659 103.02 20 J ,103.01
TSLA 0D09:30:00.167902241 463.29 27 V 463.22 463.22 463.29
EMA 0D09:30:01.205070767 47.51 12 M 47.5 47.5
CCL 0D09:30:01.508512258 28.55 29 L 28.55 28.55 28.54
q)wj1[w;`sym`time;t;(qp;(::;`bid))]
sym time price size ex bid
-------------------------------------------------------------
CUZ 0D09:30:00.011627745 29.06 65 L ,29.05
PRU 0D09:30:00.153776659 103.02 20 J ,103.01
TSLA 0D09:30:00.167902241 463.29 27 V 463.22 463.22 463.29
EMA 0D09:30:01.205070767 47.51 12 M 47.5 47.5
CCL 0D09:30:01.508512258 28.55 29 L 28.55 28.54
The CCL window starts at 09:30:00.508512258. The quote from 09:30:00.155456672 is still in force at that time, so wj includes its bid of 28.55, and wj1 does not.
Choose a join¶
| To get | q | ANSI SQL | pandas |
|---|---|---|---|
Every row of x, with matching columns of y |
x lj y |
LEFT JOIN |
merge(how='left') |
Rows of x with a match in y |
x ij y |
INNER JOIN on a key |
merge(how='inner') |
| One row per matching pair | ej[c;x;y] |
INNER JOIN |
merge(how='inner') |
| All keys of both keyed tables | x uj y |
FULL OUTER JOIN |
merge(how='outer') |
| Rows of both tables, same columns | x,y |
UNION ALL |
concat |
| Rows of both tables, any columns | x uj y |
UNION ALL with NULL columns |
concat |
Values of y added to x |
x pj y |
LEFT JOIN with x.v + COALESCE(y.v, 0) |
merge(how='left'), then add |
Row i of x with row i of y |
x,'y |
none | concat(axis=1) |
Most recent row of y for each row of x |
aj[c;x;y] |
none | merge_asof |
Aggregates of y over a time window |
wj[w;c;x;y] |
Range join with GROUP BY (wj1 only) |
none |
In the keyed joins lj, ij, uj and pj, y is a keyed table and its key columns are the join columns.
Next steps¶
- Learn more about keyed tables, the second argument of most keyed joins.
- Link tables permanently with foreign keys and linking columns.
- Read the joins reference for the full list of join operators, including
asof,ajfandijf. - Query joined tables with qSQL.
- Generate larger test data with the datagen module.