1

Below is the schema for the athena table

enter image description here

I wish to calculate median for 'parameter_value' group by standard_lab_parameter_name & units. For this I followed link : https://docs.aws.amazon.com/redshift/latest/dg/r_MEDIAN.html But on running the query

select median(parameter_value) from table_name group by standard_lab_parameter_name, units

It throws error

 SYNTAX_ERROR: line 1:8: Function median not registered

Any help? Or if some alternative query would be great

Mehul Gupta
  • 1,243
  • 7
  • 17
  • Athena is not Redshift – Nick Nov 23 '20 at 05:03
  • [This](https://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql) or [this](https://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server) might help. Plenty of other solutions available if you just google "sql calculate median" – Nick Nov 23 '20 at 06:25

1 Answers1

2

Athena is based on Presto 0.172 - you can see all supported functions in AWS DML Queries, Functions, and Operators. I guess you could use approx_percentile(x, percentage) or approx_percentile(x, w, percentage, accuracy), see Presto Aggregate Functions:

Returns the approximate percentile for all input values of x at the given percentage. The value of percentage must be between zero and one and must be constant for all input rows.

select approx_percentile(parameter_value,0.5) 
from table_name 
group by standard_lab_parameter_name, units

Keep in mind that this is a Approximate Aggregate Functions.

Philipp Johannis
  • 1,868
  • 1
  • 9
  • 12