KDB查询:如果value为null，则从其他表中获取空白列值

Question

我有两个表作为查询结果，如下所示：

select customer,date,product,orderId,version,size from tableA where date=2020.04.08,product in (`Derivative)



+----------+----------+------------+---------+---------+------+
| customer |   date   |  product   | orderId | version | size |
+----------+----------+------------+---------+---------+------+
| XYZ fund | 4/8/2020 | Derivative |       1 |       6 |      |
| XYZ fund | 4/8/2020 | Derivative |       2 |       6 | 1000 |
| XYZ fund | 4/8/2020 | Derivative |       3 |       4 |      |
+----------+----------+------------+---------+---------+------+


select sum size by date,product,parent_orderId,parent_version from tableB where date=2020.04.08,product in (`Derivative)


+----------+------------+----------------+----------------+------+
|   date   |  product   | parent_orderId | parent_version | size |
+----------+------------+----------------+----------------+------+
| 4/8/2020 | Derivative |              1 |              1 |   10 |
| 4/8/2020 | Derivative |              1 |              2 |   10 |
| 4/8/2020 | Derivative |              1 |              3 |   10 |
| 4/8/2020 | Derivative |              1 |              4 |   10 |
| 4/8/2020 | Derivative |              1 |              5 |   10 |
| 4/8/2020 | Derivative |              1 |              6 |   10 |
| 4/8/2020 | Derivative |              3 |              1 |   20 |
| 4/8/2020 | Derivative |              3 |              2 |   20 |
| 4/8/2020 | Derivative |              3 |              3 |   20 |
| 4/8/2020 | Derivative |              3 |              4 |   20 |
+----------+------------+----------------+----------------+------+

所以基本上，我希望如果结果1缺少大小，那么应该根据匹配的列，即date=date，product=product，orderId=parent_orderId，version=parent_version，从结果2填充它。有没有办法在KBD中使用query来做到这一点？以下是预期的o/p：

+----------+----------+------------+---------+---------+------+
| customer |   date   |  product   | orderId | version | size |
+----------+----------+------------+---------+---------+------+
| XYZ fund | 4/8/2020 | Derivative |       1 |       6 |   10 |
| XYZ fund | 4/8/2020 | Derivative |       2 |       6 | 1000 |
| XYZ fund | 4/8/2020 | Derivative |       3 |       4 |   20 |
+----------+----------+------------+---------+---------+------+

Answer 1

您可以使用left join运算符来实现此目的：

q)res1:select customer,date,product,orderId,version,size from tableA where date=2020.04.08,product in (`Derivative);
q)res2:select sum size by date,product,orderId:parent_orderId,version:parent_version from tableB where date=2020.04.08,product in (`Derivative);
q)res1 lj res2
customer date     product    orderId version size
-------------------------------------------------
XYZ fund 4/8/2020 Derivative 1       6       10  
XYZ fund 4/8/2020 Derivative 2       6       1000
XYZ fund 4/8/2020 Derivative 3       4       20  

请注意，我们必须确保第二个表中的列名与我们希望在第一个表中联接的列名相匹配。