如何在DolphinDB中基于复合键合并两个表格?

huangapple go评论89阅读模式
英文:

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)

输出:

如何在DolphinDB中基于复合键合并两个表格?

英文:

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:

如何在DolphinDB中基于复合键合并两个表格?

huangapple
  • 本文由 发表于 2023年8月9日 14:12:09
  • 转载请务必保留本文链接:https://go.coder-hub.com/76865017-2.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定