How to apply a function to an entire column?

Question

I have the following table from a JDBC connection in Q.

q)r
some_int this              created_at                    updated_at          ..
    -----------------------------------------------------------------------------..
1231231    "ASD" 2016.02.11D14:16:29.743260000 2016.02.11D14:16:29...
13312    "TSM" 2016.02.11D14:16:29.743260000 2016.02.11D14:16:29...

I would like to apply the following function to the first column.

deviation:{a:avg x; sqrt avg (x*x)-a*a}

This works for arrays.

q)l
1 2 3 4
q)deviation l
1.118034

How can I apply deviation on a column in a table? It seems my approach does not work:

q)select deviation(some_id) from r
'rank

UPDATE:

I cannot explain the following:

q)select avg(some_int) from r
some_int
---------
1005341
q)select min(some_int) from r
some_int
---------
812361
q)select max(some_int) from r
some_int
---------
1184014
q)select sum(some_int) from r
some_int
---------

my answer updated for your second question – terrylynch Mar 14 '16 at 13:32 — terrylynch, Mar 14 '16 at 13:32

terrylynch · Accepted Answer · 2016-03-14T13:32:04.967

You need to enlist the result if it is an atom since table columns must be lists, not atoms. Normally kdb can do this for you but often not when you're performing your own custom aggregations. For example, even if you define a function sum2 to be an exact copy of sum:

q)sum2:sum

kdb can only recognise sum as an aggregation and will enlist automatically, but not for sum2

q)select sum col1 from ([]col1:1 2 3 4)
col1
----
10

q)select sum2 col1 from ([]col1:1 2 3 4)
'rank

So you need to enlist in the second case:

q)select enlist sum2 col1 from ([]col1:1 2 3 4)
col1
----
10

UPDATE:

To answer your second question - it looks like your sum of numbers has spilled over the boundary for an integer. You'd need to convert them to long and then sum

q)select sum col1 from ([]col1:2147483645 1i)
col1
----------
2147483646

Above is the maximum integer. Adding one more gives infinity for an int

q)select sum col1 from ([]col1:2147483645 1 1i)
col1
----
0W

Adding anything more than that shows a blank (null)

q)select sum col1 from ([]col1:2147483645 1 1 1i)
col1
----

Solution is to cast to long before summing (or make them long in the first place)

q)select sum `long$col1 from ([]col1:2147483645 1 1 1i)
col1
----------
2147483648

Ha, sorry lol. Also, I think the aggregations it recognises are stored in .Q.a0 and/or .Q.a1 — terrylynch, Mar 11 '16 at 20:32
Thank you very much this makes sense. For some weird reason I get empty results. See above I updated the question. — Istvan, Mar 12 '16 at 08:18

score 0 · Answer 2 · answered Mar 11 '16 at 20:31

You get a rank because the function does not return a list. Since the function returns a single number presumably you just want the single number answer? In which case you can simple index into the table (or use exec) to get the column vector and apply it:

deviation t`some_id

Else if you want to retain a table as the answer if you enlist the result:

select enlist deviation some_id from t

How to apply a function to an entire column?

2 Answers2