aj outer join

[Markus Sieber]

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]

/ 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]

(uj/)1!'(Table1;Table2;Table3)

http://www.thalesians.com/finance/index.php/Knowledge_Base/Databases/Kdb#Union_join

[Markus Sieber]

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]

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]

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: