Google Bigquery Complex UDF

J-t 0
Viewed 38

I am creating a UDF in bigquery to call in a more powerful query. The input to the UDF is a string made up of numbers and different length units. There are three main cases. See below for an explanation of the cases and also an example for each case. The "#" represents a number. "Unit" represents one of 3 distance units (Miles, Yards, and Furlongs). Case 3 is comprised of two different units that are added together. The end goal of the UDF is to normalize the input to one unit (yards), remove any alphabetic characters, and convert any complex fractions to floats. The UDF would then return back a string.

Cases:

  1. '# Unit'; Example: Input '350 Y', Output '350'
  2. '# #/#Unit'; Example: Input '5 1/2F', Output '1210'
  3. '#Unit1 #Unit2'; Example:Input '4F 70Y', Output '950.002'

I have tried to do this using If statements. In my first attempt at this, I could only get rid of the complex fraction and two units that get added to each other. Is there a way to do many if statements to hit all possible combinations? I haven't found a way to use else-if conditional statements. Any advice, guidance, or code would be very much appreciated. I am relatively new to using SQL/bigquery so please let me know if I am doing this in a bad way. Below is my first attempt code:

CREATE OR REPLACE FUNCTION `location`(str STRING) AS (
(
  if(  
    REGEXP_CONTAINS(str, r' ') AND REGEXP_CONTAINS(str, r'/')=FALSE #does not contain /

    , (1760 * SAFE_CAST(SPLIT(REGEXP_REPLACE(str, 'M',''),' ')[OFFSET(0)] AS FLOAT64)) + SAFE_CAST(SPLIT(str,' ')[OFFSET(1)] AS FLOAT64)
    ,if(
      REGEXP_CONTAINS(str, r'/'),
      SAFE_CAST(SPLIT(str,' ')[OFFSET(0)] AS FLOAT64) + SAFE_CAST(SPLIT(SPLIT(str,' ')[OFFSET(1)], '/')[OFFSET(0)] AS INT64) / SAFE_CAST(SPLIT(SPLIT(str,' ')[OFFSET(1)], '/')[OFFSET(1)] AS INT64), 
      IFNULL(SAFE_CAST(REGEXP_REPLACE(str, r'FYM','') AS FLOAT64), -1)
    )
  )

)
);
google-bigquery sql
1 Answers

Consider below

create temp function eval (str string) returns float64
language js as r"""
  return eval(str);
""";
select str, 
  ( 
    select sum( 
      case right(x,1) 
        when 'M' then 1760
        when 'F' then 220
        when 'Y' then 1
      end * eval(replace(trim(translate(x, 'MFY', '')), ' ', '+')))
    from unnest(regexp_extract_all(str, r'[^MFY]+(?:M|F|Y)')) val,
    unnest([struct(trim(val) as x)])
  ) yards
from your_table

if applied to sample data in your question - output is

enter image description here

Update (per recent comments): you can package whole stuff into js udf and sql udf as in below example

create temp function eval (str string) returns float64
language js as r"""
  return eval(str);
""";
create temp function to_yards(str string) as (( 
    select sum( 
      case right(x,1) 
        when 'M' then 1760
        when 'F' then 220
        when 'Y' then 1
      end * eval(replace(trim(translate(x, 'MFY', '')), ' ', '+')))
    from unnest(regexp_extract_all(str, r'[^MFY]+(?:M|F|Y)')) val,
    unnest([struct(trim(val) as x)])
));
select str, to_yards(str) as yards
from your_table

with same output as above

Mikhail Berlyant0
Related