Reference/wj

From Kx Wiki
(Difference between revisions)
Jump to: navigation, search
Line 1: Line 1:
= wj =
+
{{refheader|wj|window join function}}
  
window join is a generalization of asof join, and is available in KDB+ 2.6 and later.
+
Window join is a generalization of asof join, and is available from kdb+ 2.6. asof join takes a snapshot of the current state, while window join aggregates all values of specified columns within intervals.
asof join takes a snapshot of current state.
+
window join aggregates all values of specified columns within an interval.
+
  
e.g. regnms
+
== Syntax ==
  
 
<pre>
 
<pre>
/ i have taq 2003.09.10(5.6M trades and 35M quotes) on my laptop
+
  wj[w;c;t;(q;(f0;c0);(f1;c1))]
d:`:/taq/2003.09.10
+
</pre>
  
x:select sym,time,price from d`trade
+
where:
  
q:select sym,time,bid,ask from d`quote
+
*<tt>t</tt> and <tt>q</tt> are the tables to be joined
 +
*<tt>w</tt> is a pair of lists of times, begin and end
 +
*<tt>c</tt> are the common column names, for syms and times
 +
*<tt>f0</tt>,<tt>f1</tt> are aggregation functions applied to values in q columns <tt>c0</tt>,<tt>c1</tt> over the intervals
  
f:`sym`time
+
Typically. this might be:
  
/ 264,000 trades outside (bid;ask)
+
<pre>
 +
  wj[w;`sym`time;trade;(quote;(max;`ask);(min;`bid))]
 +
</pre>
  
\t a:select from aj[f;x;q]where not price within(bid;ask)
+
For each record in <tt>t</tt>, the result has a record with additional columns <tt>c0</tt> and <tt>c1</tt>, which are the results of the aggregation functions applied to values over the matching intervals in <tt>w</tt>.
  
/ 4 seconds to process 5.6M trades against 35M quotes
+
== Example ==
  
w:-3000 1000 / look at quotes from 3 seconds before to 1 second after
+
<pre>
 +
q)t
 +
sym time    price
 +
------------------
 +
ibm 10:01:01 100
 +
ibm 10:01:04 101
 +
ibm 10:01:08 105
  
\t b:select from wj[w;f;x;(q;(max;`ask);(min;`bid))]where not price within(bid;ask)
+
q)q
 +
sym time    ask bid
 +
--------------------
 +
ibm 10:01:01 101 98
 +
ibm 10:01:02 103 99
 +
ibm 10:01:03 103 102
 +
ibm 10:01:04 104 103
 +
ibm 10:01:05 104 103
 +
ibm 10:01:06 107 104
 +
ibm 10:01:07 108 106
 +
ibm 10:01:08 107 106
 +
ibm 10:01:09 108 107
  
/ 28 seconds to do all the windows. max ask and min bid within each window
+
q)f:`sym`time
 +
q)w:-2 1+\:t.time
  
\t c:select from wj[w;f;a;(q;(max;`ask);(min;`bid))]where not price within(bid;ask)
+
q)wj[w;f;t;(q;(max;`ask);(min;`bid))]
 
+
sym time    price ask bid
/ same result in 1.4seconds by using initial aj snapshot
+
--------------------------
 +
ibm 10:01:01 100  103 98
 +
ibm 10:01:04 101  104 99
 +
ibm 10:01:08 105  108 104
 
</pre>
 
</pre>
  
now 37,000 violations instead of 264,000
+
The interval values may be seen as:
 +
 
 +
<pre>
 +
q)wj[w;f;t;(q;(::;`ask);(::;`bid))]
 +
sym time    price ask            bid
 +
--------------------------------------------------
 +
ibm 10:01:01 100  101 103        98 99
 +
ibm 10:01:04 101  103 103 104 104 99 102 103 103
 +
ibm 10:01:08 105  107 108 107 108 104 106 106 107
 +
</pre>
  
 
== See also ==
 
== See also ==

Revision as of 08:23, 8 October 2009

Contents

wj (window join function)

Window join is a generalization of asof join, and is available from kdb+ 2.6. asof join takes a snapshot of the current state, while window join aggregates all values of specified columns within intervals.

Syntax

  wj[w;c;t;(q;(f0;c0);(f1;c1))]

where:

Typically. this might be:

  wj[w;`sym`time;trade;(quote;(max;`ask);(min;`bid))]

For each record in t, the result has a record with additional columns c0 and c1, which are the results of the aggregation functions applied to values over the matching intervals in w.

Example

q)t
sym time     price
------------------
ibm 10:01:01 100
ibm 10:01:04 101
ibm 10:01:08 105

q)q
sym time     ask bid
--------------------
ibm 10:01:01 101 98
ibm 10:01:02 103 99
ibm 10:01:03 103 102
ibm 10:01:04 104 103
ibm 10:01:05 104 103
ibm 10:01:06 107 104
ibm 10:01:07 108 106
ibm 10:01:08 107 106
ibm 10:01:09 108 107

q)f:`sym`time
q)w:-2 1+\:t.time

q)wj[w;f;t;(q;(max;`ask);(min;`bid))]
sym time     price ask bid
--------------------------
ibm 10:01:01 100   103 98
ibm 10:01:04 101   104 99
ibm 10:01:08 105   108 104

The interval values may be seen as:

q)wj[w;f;t;(q;(::;`ask);(::;`bid))]
sym time     price ask             bid
--------------------------------------------------
ibm 10:01:01 100   101 103         98 99
ibm 10:01:04 101   103 103 104 104 99 102 103 103
ibm 10:01:08 105   107 108 107 108 104 106 106 107

See also


For a complete list of functions, see the kdb+ Function Reference.

Personal tools
Namespaces
Variants
Actions
Navigation
Print/export
Toolbox