select avg (ignore null)

[davidcrossey]

Hi powerpeanuts,

As per this note avg does ignore nulls for a single vector, so you could apply the average to each pair of values from each column as follows:

q)t:([]c1:`a`b`c;c2:1 0N 2;c3:5 3 5) 

q)update c4:avg each flip (c2;c3) from t 
c1 c2 c3 c4 
------------ 
a  1  5  3 
b     3  3 
c  2  5  3.5

Hope this helps

[powerpeanuts]

perfect, exactly what I am looking for. Thanks David.

[davidcrossey]

avg/min/max/sum where implemented similar to SQL92 as per the note here.

From what I gather, there are some optimizations available for a vector that aren’t available for a slice, hence the functionality we see today, and trying to change this would be difficult now as you might, (and probably will) see different results in an existing system.

[darrenwsun]

Thanks David for following up.

Ignoring null makes perfect sense, especially for avg/max/sum. Imagine otherwise, the result would be null as long as the input contains one null, effectively forcing everyone to explicitly coalesce.

I understand such change is not backward compatible, and given the ability to manually exclude null like what you shared, I’d agree it’s not worth doing it. However I’m curious if we could leave legacy burden behind, what would be the decision from language designers.