Multiply group by functions mysql

Question

The problem comes when I was trying to calculate correlation in group.

When I have multiple groups I need to do something like this for the numerator:

sum((x - avg(x)) * (y - avg(y)))

I was hoping that the inner x - avg() will perform subtract for every x. But turns out I can't do this in MySQL like this

select sum((x - avg(x)) * (y - avg(y))) numerator
from tbl t group by group_colk

This gives invalid use of group function. There are 3 ways to get around this afaik.

1. Layout the formula some other way so that it doesn't contain 2 aggregate functions. Which sometimes is hard because I can't find other forms online easily.
2. Create another table that calculates the inner aggregated result (the mean in this case). Join it with another for the outer one (the sum). This way will have me join another table that causes performance loss.
3. Use variables to store the results. This will have me run the query for more than once to get the result.

Is there any better way to get around this? Haven't try subqueries yet but it seems subqueries hurts more performance than joining another table based on my past experience.

question from:https://stackoverflow.com/questions/65886052/multiply-group-by-functions-mysql

Answer 1

Precompute the averages, then get to work on the main formula:

select grp, sum((x - avgx) * (y - avgy)) numerator
    FROM ( SELECT grp,
                  avg(x) AS avgx, 
                  avg(y) AS avgy
              FROM tbl
         ) AS z
    JOIN tbl  USING(grp)
    group by grp;

Assuming that works OK, enhance it to do the whole formula:

 SELECT grp, ( sum((..)*(..)) ) /
             ( SQRT( ... ) )  as Correlation