Bigquery Standard SQL "max_by" and "regr_slope" functions

Question

Is there any equivalent function in Bigquery using Standard SQL for max_by and regr_slope functions. If not how to achieve this.

Thanks, Maniyar

Answer 1

It does not appear that BigQuery supports either of these functions out of the box. For MAX_BY, you may just use ROW_NUMBER:

-- replacement for MAX_BY(col, val)
-- find the value for col which generates the MAX value

WITH cte AS (
    SELECT col, ROW_NUMBER() OVER (ORDER BY val DESC) rn
    FROM yourTable
)

SELECT col
FROM cte
WHERE rn = 1;

Or, you could use LIMIT if your version of BigQuery supports that:

SELECT col
FROM yourTable
ORDER BY val DESC
LIMIT 1;

As for doing linear regressions in BigQuery, I refer you to this highly upvoted Stack Overflow question and answer.

Answer 2

As @felipe-hoffa commented in another question, the shortest way to replace MAX_BY seems to use ARRAY_AGG like this:

SELECT
  ARRAY_AGG(x ORDER BY y DESC LIMIT 1)[OFFSET(0)] AS max_x_by_y
FROM yadda_yadda