Hi Sam,
The equivalent qSQL for in-memory data would be:
q)t1:( k:10?AB; v1:til 10; v2:til 10; v3:til 10)
q)t2:( p:10*til 5; k:5#AB`C)
q)t1
k v1 v2 v3
A 0 0 0
A 1 1 1
B 2 2 2
B 3 3 3
B 4 4 4
B 5 5 5
B 6 6 6
B 7 7 7
A 8 8 8
A 9 9 9
q)t2
p k
0 A
10 B
20 C
30 A
40 B
q)ungroup (k xkey t2) lj k xgroup t1
k p v1 v2 v3
A 0 0 0 0
A 0 1 1 1
A 0 8 8 8
A 0 9 9 9
B 10 2 2 2
B 10 3 3 3
B 10 4 4 4
B 10 5 5 5
B 10 6 6 6
B 10 7 7 7
A 30 0 0 0
A 30 1 1 1
A 30 8 8 8
A 30 9 9 9
B 40 2 2 2
B 40 3 3 3
B 40 4 4 4
B 40 5 5 5
B 40 6 6 6
B 40 7 7 7
qSQL is not like standard SQL that will maintain indices for you and join seamlessly over large on disk tables while shuffling data in and out to maintain a low memory profile. If the tables are too large you have two choices:
- Denormalize the two tables into one big table
- Manually maintain index tables.
Hope this helps, regards,
-Ryan
From : “Sam” <jain.samit@gmail.com>
Sent : Monday, December 03, 2012 5:34 PM
To : personal-kdbplus@googlegroups.com
Subject : [personal kdb+] generic join on splayed and mapped tables on any column
How do I do a generic join on splayed and mapped tables? In SQL, we can do something like:
select from T1,T2 where T1.c1=T2.c2
The tables are not keyed. An alternative is to create linked columns but it is messy and requires updating. I want to be able to join against any columns. These are fairly large tables. Designating a primary key is not possible as there are potentially repeating entries. I want to be able to get everything in T1 and everything in T2 (and also be able to get first/last entries optionally) joined across that column.
thanks
–
Submitted via Google Groups