如何在KDB中执行between join子句? [英] How to do a between join clause in KDB?

问题描述

假设我有一个表A，其中的列bucket_start_date，bucket_end_date和

Suppose I have a table A with the columns bucket_start_date, bucket_end_date,

A
bucket_start_date | bucket_end_date
2015.05.02        | 2015.05.08
2015.05.08        | 2015.05.12

还假设我有一个表B，其中列有日期，硬币.

Also suppose i have a table B with the columns date, coins.

A
date        | coins
2015.05.02  | 5
2015.05.06  | 11     
2015.05.09  | 32

我如何在逻辑上看起来像kdb的联接

How do I do a join in kdb that logically looks like

从A连接B中选择A.bucket_start_date，A.bucket_end_date，和(硬币)，其中B.date在A.bucket_start_date和A.bucket_end_date之间按A.bucket_start_date，A.bucket_end_date

select A.bucket_start_date, A.bucket_end_date, sum(coins) from A join B where B.date BETWEEN A.bucket_start_date and A.bucket_end_date group by A.bucket_start_date, A.bucket_end_date

所以我希望结果看起来像

So I want the result to look like

bucket_start_date | bucket_end_date | sum(coins) 
2015.05.02        | 2015.05.08      | 16 
2015.05.08        | 2015.05.12      | 32

推荐答案

一个窗口加入是获得此结果的自然方法.下面是一个wj1函数，该函数可以获取您想要的内容:

A window join is a natural way of acheiving this result. Below is a wj1 function that will get what you are after:

q)wj1[A`bucket_start_date`bucket_end_date;`date;A;(B;(sum;`coins))]
bucket_start_date bucket_end_date coins
---------------------------------------
2015.05.02        2015.05.08      16
2015.05.08        2015.05.12      32

第一个变量是一对日期列表，第一个是开始日期，最后一个是结束日期.

The first variable is a pair of lists of dates, with the first being beginning dates and last being end dates.

第二个变量是公共列，在这种情况下，您要使用date列，因为您正在查找每个日期适合的窗口.

The second variable is the common columns, in this case you want to use the date column, since you are looking in which window each date fits in.

第三个和第四个变量包含要连接的简单表，最后(sum;`coins)是要应用于给定列的函数的列表.同样，在这种情况下，您需要对每个窗口中的硬币列求和.

The third and fourth variable contains the simple tables to join, and finally (sum;`coins) is a list of the function to be applied to the given column. Again, in this case you are summing the coins column within each window.

A wj考虑进入每个间隔时的主要值，而wj1考虑仅出现在每个间隔中的值.您可以在函数中将wj1更改为wj来查看区别.

A wj considers prevailing values on entry to each interval, whilst wj1 considers only values occuring in each interval. You can change wj1 to wj in the function to see the difference.

这篇关于如何在KDB中执行between join子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！