KDB/q:如何用包含不同列的行更新表? [英] KDB/q: How to update a table with row containing disparate columns?

问题描述

我正在尝试使用辅助表/行(B)更新表(A)，如下所示:

I'm trying to update a table (A) with a secondary table/row(B) as follows:

table:()
row:([]x:`val1;)

table upsert row
table insert row

当所有后续行都具有相同的架构时，这将起作用. 但是，我试图解释的一个极端情况与后续行可能具有不同架构的情况有关，即:

Which works when all subsequent rows have the same schema. However an edge case I'm trying to account for relates to when the subsequent rows could be of a different schema i.e.:

row:([]x:`val1;y:`val2)

以上内容引发了预期的mismatch错误. 如何修改上述插入/插入逻辑，以解决采用其他架构的更新的出现?

The above throws a mismatch error which would be expected. How do I modify the above insert/upsert logic to account for the advent of updates with a different schema?

推荐答案

首先按照问题中所述的操作顺序进行操作，而无需在表上使用`指定upsert:

First with the order of operations as stated in your question, without specifing the upsert to be inplace using a ` on the table:

`表更新行

或将其再次分配给它本身，第二个操作将不起作用并抛出类型错误，因为无法将行插入到空列表中.

or assigning it to itself again the second operation would not work and throw out a type error, as its not possible to insert a row into an empty list.

进一步创建具有单例行的表而又不会引发排名错误的操作，则需要列出列的值:

Further to create a table with singleton rows without the operation throwing a rank error, the values of the columns need to be enlisted:

row:([] x:`val1;y:`val2)
'rank
  [6]  row:([] x:`val1;y:`val2)
               ^
row:([] x:enlist `val1;y:enlist `val2)
x    y
---------
val1 val2

现在要回答您的问题.

为了能够在不同模式下插入或插入行，建议使用强力方法@ terrylynch 联合加入(uj) 表将需要重新分配给自身或分配给新变量.此操作实质上创建了两个表的超集. 请注意，第一次连接完成后，空列表的类型将提升为表:

To be able to upsert or insert rows with different schemas the brute force method as @terrylynch suggested is to use a Union Join (uj) where the table would need to be assigned back onto itself or assigned to a new variable. This operation essentially creates a superset of the two tables. Note the empty list has its type promoted to a table when the first join is done:

row:([] x:enlist `val1;y:enlist `val2)
table:()
row1:([] x:enlist `val1)
table:table uj row1
table:table uj row

table
x    y
---------
val1
val1 val2

应该注意的是，一旦附加列被插入到表中，则现在只能键入类型为symbol的数据到该列中.通常，这是一件好事，因为在类型列上运行查询和操作比在非类型列上运行要快得多.

It should be noted that once the additional column is uj into the table only data of type symbol can be inserted into that column as the column is now typed. Generally this is a good thing as running queries and operations on typed columns is alot quicker than working with non-typed columns.

meta table
c| t f a
-| -----
x| s
y| s

如果您要能够动态地将不同类型的数据插入到表中，则可以创建一个空表，该表具有如下无类型的列:

If what you are after is being able to dynamically insert into a table data of different types, an empty table can be created that has an untyped column as such:

table:([] a:0N 0N;b:(();())

table
a b
---


meta table
c| t f a
-| -----
a| j
b|

在b列中需要空列表的空记录(正弦记录)是为了确保Q不会按插入值的类型键入列表.

The need for the empty records (sentinal records) of empty lists in column b is to make sure that Q does not type the list to the type of the inserted value.

要插入"到该空模式中:

To insert into this empty schema "inplace":

`table upsert row1
`table

table
a b
------
  ()
  ()
1 `ABB

meta table
c| t f a
-| -----
a| j
b|

通常注意，当插入具有上述指定为动态模式的表时，或者以其他方式代替插入单例表时，将行指定为列表/混合列表，其中每个元素表示元素类型与列匹配的列列类型.

Note generally when inserting into the table with a schema specified to be dynamic as above or otherwise instead of inserting a singleton table, the row is specified as a list/mixed list where each element represents the column where the element type matches the column type.

table:([] a:0N 0N;b:(();()))

`table insert (1;`ABB)
,2

`table insert (1;"h")
,3

`table insert (1;3)
,4

table
a b
------
  ()
  ()
1 `ABB
1 "h"
1 3

meta table
c| t f a
-| -----
a| j
b|

这篇关于KDB/q:如何用包含不同列的行更新表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！