Hi,Having trouble with a query in KDB+Given:id sym time bid ask flag---------------------------------------------------------482623 EUR/USD 2001.01.02T04:27:06.000 0.9461 0.9465 D482627 EUR/USD 2001.01.02T04:27:54.000 0.946 0.9464 D482635 EUR/USD 2001.01.02T04:31:44.000 0.9462 0.9466 D482647 EUR/USD 2001.01.02T04:34:57.000 0.9457 0.9461 D482651 EUR/USD 2001.01.02T04:37:34.000 0.9456 0.946 D482671 EUR/USD 2001.01.02T04:43:25.000 0.9454 0.9458 Dexecute:q)select time, bid, ask, pb:prev bid, pa:prev ask, diff:abs[bid-prevbid] from quotes where abs[bid - prev bid] > 0.01time bid ask pb pa diff----------------------------------------------------------2001.01.02T09:33:59.000 0.9395 0.93992001.01.02T09:44:33.000 0.9397 0.9401 0.9395 0.9399 0.00022001.01.03T03:12:08.000 0.953 0.9535 0.9397 0.9401 0.01332001.01.03T03:15:00.000 0.9532 0.9537 0.953 0.9535 0.00022001.01.03T03:42:23.000 0.9533 0.9537 0.9532 0.9537 0.00012001.01.03T06:46:04.000 0.9547 0.9551 0.9533 0.9537 0.00142001.01.03T06:59:54.000 0.9541 0.9545 0.9547 0.9551 0.00062001.01.03T07:49:34.000 0.9535 0.9539 0.9541 0.9545 0.00062001.01.03T08:06:15.000 0.9533 0.9537 0.9535 0.9539 0.00022001.01.03T09:42:07.000 0.9477 0.9481 0.9533 0.9537 0.0056I end up with records returned where the abs[bid-prev bid] is < than0.01. I must be missing something plainly obvious with my whereclause, but can’t for the life of me figure out what’s going on. Anyhelp would be appreciated.Thanks!
your where clause is fine.
Assuming a subset of data passes the where clause constraint,
you are then taking the prev bid from that <subset> to store in diff, not the original set of bid values used in the where constraint.
I think what’s happening is that I’m thinking “Record-wise”, not"column-wise". If I refactored the expression into:q)diffs:select id, time, bid, ask, pb:prev bid, pa:prev ask,diff:abs[bid-prev bid] from quotesq)select from diffs where diff > 0.01id time bid ask pb pa diff-----------------------------------------------------------------483361 2001.01.02T09:33:59.000 0.9395 0.9399 0.9517 0.9521 0.0122483397 2001.01.02T09:44:33.000 0.9397 0.9401 0.9535 0.954 0.0138485959 2001.01.03T03:12:08.000 0.953 0.9535 0.9383 0.9388 0.0147485967 2001.01.03T03:15:00.000 0.9532 0.9537 0.9353 0.9358 0.0179486080 2001.01.03T03:42:23.000 0.9533 0.9537 0.935 0.9355 0.0183then I get something I recognize. Any pointers on the precedence rulesfor applying the components of a select statement?Regards,AOn Aug 16, 4:06?pm, an <anayya…> wrote:> Hi,>> Having trouble with a query in KDB+>> Given:> id ? ? sym ? ? time ? ? ? ? ? ? ? ? ? ?bid ? ?ask ? ?flag> ---------------------------------------------------------> 482623 EUR/USD 2001.01.02T04:27:06.000 0.9461 0.9465 D> 482627 EUR/USD 2001.01.02T04:27:54.000 0.946 ?0.9464 D> 482635 EUR/USD 2001.01.02T04:31:44.000 0.9462 0.9466 D> 482647 EUR/USD 2001.01.02T04:34:57.000 0.9457 0.9461 D> 482651 EUR/USD 2001.01.02T04:37:34.000 0.9456 0.946 ?D> 482671 EUR/USD 2001.01.02T04:43:25.000 0.9454 0.9458 D>> execute:> q)select time, bid, ask, pb:prev bid, pa:prev ask, diff:abs[bid-prev> bid] from q> uotes where abs[bid - prev bid] > 0.01>> time ? ? ? ? ? ? ? ? ? ?bid ? ?ask ? ?pb ? ? pa ? ? diff> ----------------------------------------------------------> 2001.01.02T09:33:59.000 0.9395 0.9399> 2001.01.02T09:44:33.000 0.9397 0.9401 0.9395 0.9399 0.0002> 2001.01.03T03:12:08.000 0.953 ?0.9535 0.9397 0.9401 0.0133> 2001.01.03T03:15:00.000 0.9532 0.9537 0.953 ?0.9535 0.0002> 2001.01.03T03:42:23.000 0.9533 0.9537 0.9532 0.9537 0.0001> 2001.01.03T06:46:04.000 0.9547 0.9551 0.9533 0.9537 0.0014> 2001.01.03T06:59:54.000 0.9541 0.9545 0.9547 0.9551 0.0006> 2001.01.03T07:49:34.000 0.9535 0.9539 0.9541 0.9545 0.0006> 2001.01.03T08:06:15.000 0.9533 0.9537 0.9535 0.9539 0.0002> 2001.01.03T09:42:07.000 0.9477 0.9481 0.9533 0.9537 0.0056>> I end up with records returned where the abs[bid-prev bid] is < than> 0.01. I must be missing something plainly obvious with my where> clause, but can’t for the life of me figure out what’s going on. Any> help would be appreciated.>> Thanks!</anayya…>
you can see it happening, just insert some debug
/ sample table
q)t:(a:til 5;b:1 1 2 2 3)
0N!x - prints and returns x. wca - where clause a, wcb - where clause b, etc.
q)select {0N!(fa;x);x}a by {0N!(gb;x);x}b from t where {0N!(wca;x);count[1_x]#1b}a,{0N!(wcb;x);1b}b
(`wca;0 1 2 3 4)
(`wcb;1 1 2 2)
(`gb;1 1 2 2)
(`fa;0 1)
(`fa;2 3)
b| a
-| —
1| 0 1
2| 2 3
i.e. executes through the where clause, left to right, clauses separated by commas, trimming down the subset it is operating on. Then does the group by, then the select of the columns
Thanks!Makes sense now.Cheers,APS. Way cool debugging!On Aug 16, 4:34?pm, Charles Skelton <char…> wrote:> you can see it happening, just insert some debug>> / sample table> q)t:(a:til 5;b:1 1 2 2 3)>> 0N!x - prints and returns x. wca - where clause a, wcb - where clause b,> etc.>> q)select {0N!(fa;x);x}a by {0N!(gb;x);x}b from t where> {0N!(wca;x);count[1_x]#1b}a,{0N!(wcb;x);1b}b> (wca;0 1 2 3 4)> (wcb;1 1 2 2)> (gb;1 1 2 2)> (fa;0 1)> (`fa;2 3)> b| a> -| —> 1| 0 1> 2| 2 3>> i.e. executes through the where clause, left to right, clauses separated by> commas, trimming down the subset it is operating on. Then does the group by,> then the select of the columns>>>> On Mon, Aug 16, 2010 at 5:21 PM, an <anayya…> wrote:> > I think what’s happening is that I’m thinking “Record-wise”, not> > “column-wise”. If I refactored the expression into:>> > q)diffs:select id, time, bid, ask, pb:prev bid, pa:prev ask,> > diff:abs[bid-prev bid] from quotes> > q)select from diffs where diff > 0.01>> > id ? ? time ? ? ? ? ? ? ? ? ? ?bid ? ?ask ? ?pb ? ? pa ? ? diff> > -----------------------------------------------------------------> > 483361 2001.01.02T09:33:59.000 0.9395 0.9399 0.9517 0.9521 0.0122> > 483397 2001.01.02T09:44:33.000 0.9397 0.9401 0.9535 0.954 ?0.0138> > 485959 2001.01.03T03:12:08.000 0.953 ?0.9535 0.9383 0.9388 0.0147> > 485967 2001.01.03T03:15:00.000 0.9532 0.9537 0.9353 0.9358 0.0179> > 486080 2001.01.03T03:42:23.000 0.9533 0.9537 0.935 ?0.9355 0.0183>> > then I get something I recognize. Any pointers on the precedence rules> > for applying the components of a select statement?>> > Regards,> > A>> > On Aug 16, 4:06 pm, an <anayya…> wrote:> > > Hi,>> > > Having trouble with a query in KDB+>> > > Given:> > > id ? ? sym ? ? time ? ? ? ? ? ? ? ? ? ?bid ? ?ask ? ?flag> > > ---------------------------------------------------------> > > 482623 EUR/USD 2001.01.02T04:27:06.000 0.9461 0.9465 D> > > 482627 EUR/USD 2001.01.02T04:27:54.000 0.946 ?0.9464 D> > > 482635 EUR/USD 2001.01.02T04:31:44.000 0.9462 0.9466 D> > > 482647 EUR/USD 2001.01.02T04:34:57.000 0.9457 0.9461 D> > > 482651 EUR/USD 2001.01.02T04:37:34.000 0.9456 0.946 ?D> > > 482671 EUR/USD 2001.01.02T04:43:25.000 0.9454 0.9458 D>> > > execute:> > > q)select time, bid, ask, pb:prev bid, pa:prev ask, diff:abs[bid-prev> > > bid] from q> > > uotes where abs[bid - prev bid] > 0.01>> > > time ? ? ? ? ? ? ? ? ? ?bid ? ?ask ? ?pb ? ? pa ? ? diff> > > ----------------------------------------------------------> > > 2001.01.02T09:33:59.000 0.9395 0.9399> > > 2001.01.02T09:44:33.000 0.9397 0.9401 0.9395 0.9399 0.0002> > > 2001.01.03T03:12:08.000 0.953 ?0.9535 0.9397 0.9401 0.0133> > > 2001.01.03T03:15:00.000 0.9532 0.9537 0.953 ?0.9535 0.0002> > > 2001.01.03T03:42:23.000 0.9533 0.9537 0.9532 0.9537 0.0001> > > 2001.01.03T06:46:04.000 0.9547 0.9551 0.9533 0.9537 0.0014> > > 2001.01.03T06:59:54.000 0.9541 0.9545 0.9547 0.9551 0.0006> > > 2001.01.03T07:49:34.000 0.9535 0.9539 0.9541 0.9545 0.0006> > > 2001.01.03T08:06:15.000 0.9533 0.9537 0.9535 0.9539 0.0002> > > 2001.01.03T09:42:07.000 0.9477 0.9481 0.9533 0.9537 0.0056>> > > I end up with records returned where the abs[bid-prev bid] is < than> > > 0.01. I must be missing something plainly obvious with my where> > > clause, but can’t for the life of me figure out what’s going on. Any> > > help would be appreciated.>> > > Thanks!>> > –> >
Submitted via Google Groups</anayya…></anayya…></char…>