What Julia tabular data types and functions might I use in order to join two tables using a join condition that is more complex than simple equality between sets of columns in each table (an equi-join)? As far as I can tell, none of the join functions in DataFrames.jl, for example, can accept an arbitrary join predicate. Do any other Julia tabular types have join functions that accept a join predicate?
As a specific example, I’d like to translate the following SQL schema and query to Julia.
PostgreSQL 9.6 Schema Setup:
CREATE TABLE exchange_rate(time INT, rate FLOAT); INSERT INTO exchange_rate VALUES (1, 1.0) ,(4, 1.1) ,(5, 1.5) ,(7, 2.0) ,(9, 1.8) ,(10, 1.7) ,(11, 1.9) ; CREATE TABLE trade(time int); INSERT INTO trade VALUES (1) ,(2) ,(3) ,(4) ,(5) ,(6) ,(7) ,(8) ,(9) ,(10) ,(11) ,(12) ;
SELECT t.time, e.rate FROM trade t JOIN exchange_rate e ON e.time = (SELECT MAX(time) FROM exchange_rate WHERE time <= t.time)
I described this scenario in more detail in issue Feature request: Complex join conditions that I raised for the Miller CSV processing tool. Instead of a nested sub-query like the one that I used in the example query above, the SQL query that I showed in the Miller issue uses a Common Table Expression and a window function.
(As an aside, this is an example of a Gaps and Islands problem because there are gaps between consecutive time sequences in the
exchange_rate table while the times in the
trade table are continuous, or gap-free.)