cancel
Showing results for
Did you mean:

## Rounding in select statement

New Contributor II

Example:

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.

4 REPLIES 4
New Contributor II

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``````

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

New Contributor II

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.

New Contributor II

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

Contributor III

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

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

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"``````