I'm trying to implement a query in AWS Athena where I compute the Pearson Correlation coefficient of a selected column against every other column in a matrix, and return a sorted list.
The data is stored in S3 as a CSV file and looks like this:
col_1 col_2 col_3 col_4 .... col_15000
0 124 56 200 .... 4
0 0 44 0 .... 0
....
I want to be able to calculate corr(col_1, col_2), corr(col_1, col_3), ...cor(col_1, col_15000) and sort the results by the correlation coefficients (find the most/least correlated columns).
What is the most efficient way to write this query? I would like this query to be as performant as possible. The obvious answer is to generate a query like this:
SELECT
corr(col_1, col_2) AS cor_2,
corr(col_1, col_3) AS cor_3,
...
corr(col_1, col_15000) AS cor_15000
However, this seems tedious, and the query size limit is quickly reached. Is there a better way that doesn't sacrifice (or increases) performance? This can easily be parallelized, as each individual correlation coefficient can be computed independently.
