Given a table t with structure ([sym] date; price} I am struggling tocompute a table of the price deltas from one day to the next. The bestI could come up with is ungroup select 1_date, 1_deltas price by sym from `date xasc tThat works but that seems like a lot of data movement to me.
any reason why this doesn’t do?
?select deltas price by date from t
or
?select deltas price by date,sym from t?
No, the tables have completely the wrong structure: price and date arevectors. Try it ont: ([sym:raze 3#'ab;date:6#enlist .z.d+til 3]price:6#4 2 1)
Your definition of t does not look correct:q)([sym:raze 3#'ab;date:6#enlist .z.d+til 3]price:6#4 2 1)sym date | price------------------------------------| -----a 2009.03.26 2009.03.27 2009.03.28| 4a 2009.03.26 2009.03.27 2009.03.28| 2a 2009.03.26 2009.03.27 2009.03.28| 1…This has multiple rows per symbol, and only one price per row.Perhaps you mean something like:q)t2:([sym:ab];date:2#enlist .z.d+til 3;price:2 3#2 7 5 3 4 8)q)t2sym| date price—| --------------------------------------a | 2009.03.26 2009.03.27 2009.03.28 2 7 5b | 2009.03.26 2009.03.27 2009.03.28 3 4 8In this case, you can do:q)select date,deltas price by sym from ungroup t2sym| date price—| ---------------------------------------a | 2009.03.26 2009.03.27 2009.03.28 2 5 -2b | 2009.03.26 2009.03.27 2009.03.28 3 1 4On Mar 27, 5:49?am, annakh7 <anna…> wrote:> No, the tables have completely the wrong structure: price and date are> vectors. Try it on> t: ([sym:raze 3#'ab;date:6#enlist .z.d+til 3]price:6#4 2 1)</anna…>
Yes, sorry, typo in example. I meantq)t: ([sym:raze 3#'ab;date:6#.z.d+til 3]price:6#4 2 1)q)tsym date | price--------------| -----a 2009.03.26| 4a 2009.03.27| 2a 2009.03.28| 1b 2009.03.26| 4b 2009.03.27| 2b 2009.03.28| 1and I wantsym date | price--------------| -----a 2009.03.27| -2a 2009.03.28| -1b 2009.03.27| -2b 2009.03.28| -1
OK, with the new definition:q)show t:([sym:raze 3#'ab;date:6#.z.d+til 3]price:6#4 2 1)sym date | price--------------| -----a 2009.03.27| 4a 2009.03.28| 2a 2009.03.29| 1b 2009.03.27| 4b 2009.03.28| 2b 2009.03.29| 1this gives the full deltas, including the first day:q)ungroup select date,deltas price by sym from tsym date price--------------------a 2009.03.27 4a 2009.03.28 -2a 2009.03.29 -1b 2009.03.27 4b 2009.03.28 -2b 2009.03.29 -1or if you want to drop off the first day’s values:q)ungroup select 1_date,1_deltas price by sym from tsym date price--------------------a 2009.03.28 -2a 2009.03.29 -1b 2009.03.28 -2b 2009.03.29 -1On Mar 27, 7:07?am, annakh7 <anna…> wrote:> Yes, sorry, typo in example. I meant> q)t: ([sym:raze 3#'ab;date:6#.z.d+til 3]price:6#4 2 1)> q)t> sym date ? ? ?| price> --------------| -----> a ? 2009.03.26| 4> a ? 2009.03.27| 2> a ? 2009.03.28| 1> b ? 2009.03.26| 4> b ? 2009.03.27| 2> b ? 2009.03.28| 1> and I want> sym date ? ? ?| price> --------------| -----> a ? 2009.03.27| -2> a ? 2009.03.28| -1> b ? 2009.03.27| -2> b ? 2009.03.28| -1</anna…>
X-Mailer: Apple Mail (2.930.3)i told him privately thatupdate deltas price by sym from date xasc tis almost what he wants (we need xasc if we presume that t is no sorted by date in the general case)anyway the code sent originally is exactly the same as yours for dropping first by symand i don't think it can be done any shortermaybe faster on a long list, but haven't really checked that AttilaOn 27 Mar 2009, at 00:42, Chris Burke wrote:\>\> OK, with the new definition:\>\> q)show t:([sym:raze 3#'ab;date:6#.z.d+til 3]price:6#4 2 1)\> sym date | price\> --------------| -----\> a 2009.03.27| 4\> a 2009.03.28| 2\> a 2009.03.29| 1\> b 2009.03.27| 4\> b 2009.03.28| 2\> b 2009.03.29| 1\>\> this gives the full deltas, including the first day:\>\> q)ungroup select date,deltas price by sym from t\> sym date price\> --------------------\> a 2009.03.27 4\> a 2009.03.28 -2\> a 2009.03.29 -1\> b 2009.03.27 4\> b 2009.03.28 -2\> b 2009.03.29 -1\>\> or if you want to drop off the first day's values:\>\> q)ungroup select 1\_date,1\_deltas price by sym from t\> sym date price\> --------------------\> a 2009.03.28 -2\> a 2009.03.29 -1\> b 2009.03.28 -2\> b 2009.03.29 -1\>\>\> On Mar 27, 7:07 am, annakh7 <anna...> wrote:>> Yes, sorry, typo in example. I meant>> q)t: ([sym:raze 3#'a`b;date:6#.z.d+til 3]price:6#4 2 1)>> q)t>> sym date | price>> --------------| ----->> a 2009.03.26| 4>> a 2009.03.27| 2>> a 2009.03.28| 1>> b 2009.03.26| 4>> b 2009.03.27| 2>> b 2009.03.28| 1>> and I want>> sym date | price>> --------------| ----->> a 2009.03.27| -2>> a 2009.03.28| -1>> b 2009.03.27| -2>> b 2009.03.28| -1> ></anna…>
Thanks. I am gratified that I seem to be getting the hang of Q atlast. All this emphasis on tables and queries is quite new from K.I think it has to count as a quirk that -': doesn’t drop the firstitem: it is unlike the others and doesn’t really belong in there. K3did drop it, I am sure of that; I wonder why the change.
X-Mailer: Apple Mail (2.930.3)if you use it in a non aggregating query, you need uniform function that returns the same number of elements as the input.On 27 Mar 2009, at 20:05, annakh7 wrote:>> Thanks. I am gratified that I seem to be getting the hang of Q at> last. All this emphasis on tables and queries is quite new from K.>> I think it has to count as a quirk that -': doesn’t drop the first> item: it is unlike the others and doesn’t really belong in there. K3> did drop it, I am sure of that; I wonder why the change.>> >