CODEWITHSUNDEEP
sqlgoogle-bigquery
user1367204
user1367204

Reputation: 4797

BigQuery Standard SQL: How to get median of difference between two columns?

How can I get the median difference after subtracting two columns in a table after I set the dialect to Standard SQL?

These are the things that I have tried and failed with:

SELECT
  symbol,
  median(max_height-min_height)
FROM
  `body_table-1345.bodies.heights`
WHERE
  symbol = 'mouse_1344'
  AND max_height> 0
  AND min_height> 0
  AND (measure_date> '2017-11-01 08:45:00.000 UTC'
    AND measure_date< '2017-11-30 17:00:00.000 UTC')
GROUP BY
  symbol

but I get the error Error: Function not found: median at [3:3]

The following works with Legacy SQL but not with Standard SQL:

SELECT
  symbol,
  NTH(501, QUANTILES(max_height-min_height, 1001))
FROM
  `body_table-1345.bodies.heights`
WHERE
  symbol = 'mouse_1344'
  AND max_height> 0
  AND min_height> 0
  AND (measure_date> '2017-11-01 08:45:00.000 UTC'
    AND measure_date< '2017-11-30 17:00:00.000 UTC')
GROUP BY
  symbol

The columns I'd like to subtract are max_height and min_height.

Upvotes: 2

Views: 4392

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Instead, you can use a slight variation of ANSI standard percentile functions.

SELECT DISTINCT symbol,
       percentile_cont(max_height-min_height, 0.5) over (partition by symbol )
FROM `body_table-1345.bodies.heights`
WHERE symbol = 'mouse_1344' AND
      max_height > 0 AND
      min_height > 0 AND
      (measure_date> '2017-11-01 08:45:00.000 UTC' AND
       measure_date< '2017-11-30 17:00:00.000 UTC'
      );

Unfortunately, this is one of the analytic functions that is only available as an analytic function and not as an aggregation function. Hence, the SELECT DISTINCT syntax rather than GROUP BY.

Upvotes: 4

Related Questions