英文:
How to merge two tables based on a composite key in DolphinDB?
问题
我有两个因子表,一个包含数值,另一个包含字符串。我想要根据一个复合键将这两个表合并成一个表,并返回给用户。
我尝试了以下脚本:
t2 = table(1 2 3 as A, `a`b`c as B, rand(1.0, 3) as C, rand(1.0, 3) as D, now() + 1..3 as F)
t3 = table(1..3 as A, `a`b`d as B, take(`TESTE`TESTD, 3) as E, now() + 11..13 as F)
tmp = table(1:0, `A`B`C`D`E`F, [INT, SYMBOL, DOUBLE, DOUBLE, STRING, TIMESTAMP])
tmp.tableInsert(dict(t2.colNames(), t2.values()))
tmp.tableInsert(dict(t3.colNames(), t3.values()))
t1 = select A, B, firstNot(C,NULL) as C, firstNot(D, NULL) as D, firstNot(E,NULL) as E, max(F) as F from tmp group by A, B
但我想知道是否有更简单的方法。
英文:
I have two factor tables, one containing numerical values and the other containing strings. I want to merge the two tables into a single table based on a composite key and return it to users.
I have tried the following script:
t2 = table(1 2 3 as A, `a`b`c as B, rand(1.0, 3) as C, rand(1.0, 3) as D, now() + 1..3 as F)
t3 = table(1..3 as A, `a`b`d as B, take(`TESTE`TESTD, 3) as E, now() + 11..13 as F)
tmp = table(1:0, `A`B`C`D`E`F, [INT, SYMBOL, DOUBLE, DOUBLE, STRING, TIMESTAMP])
tmp.tableInsert(dict(t2.colNames(), t2.values()))
tmp.tableInsert(dict(t3.colNames(), t3.values()))
t1 = select A, B, firstNot(C,NULL) as C, firstNot(D, NULL) as D, firstNot(E,NULL) as E, max(F) as F from tmp group by A, B
But I’m wondering if there is a simpler method.
答案1
得分: 1
你可以在DolphinDB中使用fj
(全连接)来使用“A”和“B”列作为匹配列:
t2 = table(1 2 3 as A, `a`b`c as B, rand(1.0, 3) as C, rand(1.0, 3) as D, now() + 1..3 as F)
t3 = table(1..3 as A, `a`b`d as B, take(`TESTE`TESTD, 3) as E, now() + 11..13 as F)
t1 = select nullFill(A, t3.A) as A, nullFill(B, t3.B) as B, C , D, E, nullFill(F, t3.F) from fj(t2, t3, `A`B)
输出:
英文:
You can use fj
(full join) in DolphinDB by using the “A” and “B” cloumns as the matching columns:
t2 = table(1 2 3 as A, `a`b`c as B, rand(1.0, 3) as C, rand(1.0, 3) as D, now() + 1..3 as F)
t3 = table(1..3 as A, `a`b`d as B, take(`TESTE`TESTD, 3) as E, now() + 11..13 as F)
t1=select nullFill(A, t3.A) as A, nullFill(B, t3.B) as B, C , D, E, nullFill(F, t3.F) from fj(t2, t3, `A`B)
Output:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论