KX Community

Find answers, ask questions, and connect with our KX Community around the world.

Home Forums kdb+ Rounding in select statement

  • Rounding in select statement

    Posted by powerpeanuts on November 26, 2021 at 12:00 am

    Example:

    trade:([]time:`time$();sym:`symbol$();price:`float$();size:`int$());

    `trade insert(09:30:00.000;`a;10.75;100);
    `trade insert(09:31:00.000;`a;11.75;100);
    `trade insert(09:32:00.000;`a;13.2;100);
    `trade insert(09:30:00.000;`b;100.75;100);
    `trade insert(09:31:00.000;`b;106.95;100);
    `trade insert(09:32:00.000;`b;123.95;100);

     

    I would like to round the price to 1 decimal point in select statement. Pseudo code:

    select time, sym, round(price, 1) from trade

     

    What is the correct equivalent kdb syntax for the above? Thanks.

     

     

    powerpeanuts replied 4 weeks, 1 day ago 5 Members · 4 Replies
  • 4 Replies
  • mauricelim

    Member
    November 26, 2021 at 12:00 am

    You could do something like this:

    q)fix:{.Q.fmt'[x+1+count each string floor y;x;y]} 
    q)select time,sym,fix[1]price from trade 
    time sym price 
    ------------------------ 
    09:30:00.000 a "10.8" 
    09:31:00.000 a "11.8" 
    09:32:00.000 a "13.2" 
    09:30:00.000 b "100.8" 
    09:31:00.000 b "107.0" 
    09:32:00.000 b "124.0" 
    
    q)select time,sym,"F"$fix[1]price from trade 
    time       sym price 
    ---------------------- 
    09:30:00.000 a 10.8 
    09:31:00.000 a 11.8 
    09:32:00.000 a 13.2 
    09:30:00.000 b 100.8 
    09:31:00.000 b 107 
    09:32:00.000 b 124

    Reference: https://code.kx.com/q/ref/dotq/#qfmt-format

    Note that if you do cast it back to a float, the decimal does not show for .0

  • roryk

    Member
    November 26, 2021 at 12:00 am

    I would define

    round:{(floor 0.5+y*i)%i:10 xexp x}

    and then you can do

    select time, sym, round[1]price from trade.

    As a bonus, this works with negative numbers too, round[-3; 12345.678] is 12000.

  • sujoy

    Member
    November 27, 2021 at 12:00 am

    Checkout the latest https://code.kx.com/q/basics/internal/#-27xy-format

    q)ts:10000 select sym,”F”$-27!(1i;price) from trade

    69 1824

    q)ts:10000 select sym,”F”$(.Q.f[1;]’)price from trade

    153 1888

     

  • laura

    Administrator
    December 1, 2021 at 12:00 am

    Not quite what you asked, but better if you can to store prices as longs.

     

    trade:([]time:`time$();sym:`symbol$();price:`long$();size:`int$()); `trade insert(09:30:00.000;`a;1075;100); `trade insert(09:31:00.000;`a;1175;100); `trade insert(09:32:00.000;`a;1320;100); `trade insert(09:30:00.000;`b;10075;100); `trade insert(09:31:00.000;`b;10695;100); `trade insert(09:32:00.000;`b;12395;100);

     

    Your query then becomes

     

    q)select time,sym,price:%[;100] 10 xbar 5+trade`price from trade 
    time       sym price 
    ---------------------- 
    09:30:00.000 a 10.8 
    09:31:00.000 a 11.8 
    09:32:00.000 a 13.2 
    09:30:00.000 b 100.8 
    09:31:00.000 b 107 
    09:32:00.000 b 124

     

    If you want a general rounding function adapted for dollars stored as cents (or any price as 100ש

     

    q)roundi:{%[;100]s xbar y+.5*s:10 xexp 2-x} 
    q)select time,sym,price:roundi[1]price from trade 
    time       sym price 
    ---------------------- 
    09:30:00.000 a 10.8 
    09:31:00.000 a 11.8 
    09:32:00.000 a 13.2 
    09:30:00.000 b 100.8 
    09:31:00.000 b 107 
    09:32:00.000 b 124

     

    If you want formatted strings then the new internal function @sujoy13 mentions does the rounding for you.

     

    q)select time,sym,price:-27!(1i;price%100) from trade 
    time       sym price 
    ------------------------ 
    09:30:00.000 a "10.8" 
    09:31:00.000 a "11.8" 
    09:32:00.000 a "13.2" 
    09:30:00.000 b "100.8" 
    09:31:00.000 b "107.0" 
    09:32:00.000 b "124.0"

     

     

     

Log in to reply.