aj outer join

261 views
Skip to first unread message

Markus Sieber

unread,
Jun 15, 2015, 10:17:26 AM6/15/15
to personal...@googlegroups.com
Hi 
i want to join two tables on the their time column but i dont want to "left join" them as aj is doing but instead I want an outer join.

currently i am using a temporary table with the date values of both tables and doing 2 aj:

a:([]date:2014.01.01 2014.01.03 2014.01.05; a:1 2 3)
b:([]date:2014.01.01 2014.01.04 2014.01.05; b:4 5 6)
aj[`date;aj[`date;([]date:asc distinct a[`date],b`date);a];b]

is there a better version? especially the asc distinct is not quite performant i guess

Markus

Yan Yan

unread,
Jun 15, 2015, 11:22:37 AM6/15/15
to personal...@googlegroups.com
/ tables renamed to avoid symbol reuse
t1:([]date:2014.01.01 2014.01.03 2014.01.05; a:1 2 3);
t2:([]date:2014.01.01 2014.01.04 2014.01.05; b:4 5 6);
(1!t1) uj (1!t2)

©¬ date a b
2014.01.01 1 4
2014.01.03 2 0N
2014.01.05 3 6
2014.01.04 0N 5

Yan Yan

unread,
Jun 15, 2015, 11:40:05 AM6/15/15
to personal...@googlegroups.com

Markus Sieber

unread,
Jun 15, 2015, 3:25:43 PM6/15/15
to personal...@googlegroups.com
unfortunately this is not the result I want. I want this:


q)a:([]date:2014.01.01 2014.01.03 2014.01.05; a:1 2 3)
b:([]date:2014.01.01 2014.01.04 2014.01.05; b:4 5 6)
q)aj[`date;aj[`date;([]date:asc distinct a[`date],b`date);a];b]q)
date       a b
--------------
2014.01.01 1 4
2014.01.03 2 4
2014.01.04 2 5
2014.01.05 3 6



i guess I can work with uj like this:
q)select date,fills a,fills b from xasc[`date] (1!t1) uj (1!t2)

but this takes twice the time than my aj approach with the temp table.

Jonny Press

unread,
Jun 16, 2015, 3:45:39 AM6/16/15
to personal...@googlegroups.com
I think your aj approach is good

Unless you want the final result sorted, then you don’t need the asc

(aj only requires the “value” tables, a and b, to be sorted by date in your case)

aj[`date;aj[`date;([]date:distinct a[`date],b`date);a];b]


--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbpl...@googlegroups.com.
To post to this group, send email to personal...@googlegroups.com.
Visit this group at http://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.

Attila Vrabecz

unread,
Jun 18, 2015, 10:00:13 PM6/18/15
to [kdb+] [kdb+]
this looks nice, can be easily generalised to 2+ tables

q)t:{get x set flip(`date,x)!(asc n?2*n;n?n:10000000)}each`a`b`c;

q)\ts ([]date:asc distinct raze t@\:`date)aj[`date]/t
12118 805307968

the distinct (even with the asc) is less than 10% of time

q)\ts asc distinct raze t@\:`date
607 671088992

Cheers,
  Attila
On 15 Jun 2015, at 20:19, Markus Sieber <sieb...@gmail.com> wrote:

Reply all
Reply to author
Forward
0 new messages