cancel
Showing results for 
Search instead for 
Did you mean: 

Rounding in select statement

powerpeanuts
New Contributor

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.

 

 

2 REPLIES 2

mauricelim
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

 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

rak1507
New Contributor

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.