• Google Analytics 4

  Error encountered while calculating median using percentile_cont function

  Posted by Noah on 14 September 2022 at 8:19 am

  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]
  `

  Samuel replied 1 year, 4 months ago 3 Members · 2 Replies
• 2 Replies

• 

  Logan

  Member

  22 June 2023 at 4:28 am

  The function percentile_cont requires a float value, not a table. In your query, you need to use x.col to refer to the column col in your table x. 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.

• 

  Samuel

  Member

  3 July 2023 at 3:29 am

  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