-
Error encountered while calculating median using percentile_cont function
Hey, I’m currently dealing with a table named “masterdata” that has the following setup: user_eng_time, day, user_pseudo_id. Ran into a little issue when I tried running this code:
`
select percentile_cont(x,0.5) over () as median_time_in_seconds
from(
select round(user_eng_time/1000) from masterdata
) as x
`
But all I got was this pesky error:
`
No matching signature for aggregate function PERCENTILE_CONT for argument types: STRUCT, FLOAT64. Supported signatures: PERCENTILE_CONT(FLOAT64, FLOAT64); PERCENTILE_CONT(NUMERIC, NUMERIC); PERCENTILE_CONT(BIGNUMERIC, BIGNUMERIC) at [55:8]
` -
2 Replies
-
The function
percentile_contrequires a float value, not a table. In your query, you need to usex.colto refer to the columncolin your tablex. Here’s how you can adjust your code:With your ‘masterdata’ table data source,
the code ‘Select 10000*rand() as user_eng_time from unnest (generate_array(1,100))‘ generates data for masterdata.Then,
the query selects everything and calculates the percentile using
‘percentile_cont(x.col,0.5) over () as median_time_in_seconds‘.And this selection is made within a subquery that selects and rounds ‘user_eng_time’ divided by 1000,
as named by column ‘col’ from the masterdata table:
‘select round(user_eng_time/1000) as col from masterdata‘.This subquery is represented as table ‘x’ in the main query.
-
The error message is saying that the function “PERCENTILE_CONT” isn’t being used correctly. This function needs two specific types of numbers as inputs, but in your code, it’s getting a different type of data that it doesn’t know how to handle. To fix this error, you need to make sure that the data you’re feeding into the function is of a compatible type. From the error message, it’s clear that supported types are decimal numbers (FLOAT64), precise numbers (NUMERIC), and large precise numbers (BIGNUMERIC). Keep in mind that these requirements may vary depending on your specific data and setup.
Log in to reply.