GCP: creando nuestras propias funciones

Las funciones contienen bloques de código que, al contrario de un procedimiento, devuelven valores o resultado de algún cálculo u operación. Y éstos valores pueden ser asignados variables con las que podemos realizar más operaciones.

Ejercicio 1. Crear una función que use la condición CASE expr para devolver un número si el valor introducido la función coincide con una letra en específico.

CREATE OR REPLACE FUNCTION `myproject.mydataset.getNumero`(orden STRING)
RETURNS INT64 AS(
  (
      CASE orden
      WHEN 'A' THEN 12
      WHEN 'B' THEN 23
      WHEN 'C' THEN 34
      WHEN 'D' THEN 45
      ELSE 0  END
  )
)

La podemos usar de la siguiente forma:

DECLARE orden STRING DEFAULT 'X';
DECLARE id_num INT64;

SET id_num = 56;

UPDATE `myproject.mydataset.numeros` 
SET numero = (SELECT `myproject.mydataset.getNumero`(orden)) 
WHERE id = id_num;

Asignamos dos variables: orden e id_num, de tipo STRING e INT64, respectivamente. Se les asigna un valor ("X" y 56). La función se encargará de devolver el número de acuerdo a la letra introducida a la función.

Para eliminar la función:

DROP FUNCTION `myproject.mydataset.getNumero`;

Ejercicio 2. Crear una función para determinar la diferencia entre 2 fechas. Las fechas serán introducidas como tipo STRING.

CREATE OR REPLACE FUNCTION `myproject.mydataset.getDifference`(day1 STRING, day2 STRING)
RETURNS NUMERIC AS(
  (SELECT DATE_DIFF(day1, day2, DAY))
)

Empleamos la función de DATE_DIFF para hacer la operación.

La podemos ocupar de la siguiente forma:

DECLARE dia1 STRING DEFAULT '2024-04-05';
DECLARE dia2 STRING DEFAULT '2024-04-02';
DECLARE resultado NUMERIC DEFAULT 0;

SET resultado = (SELECT  `myproject.mydataset.getDifference`(dia1, dia2));

SELECT 'Resultado: '||resultado;

Ejercicio 3. Crear tres funciones para sumar, restar y multiplicar.

-- Suma
CREATE OR REPLACE FUNCTION `myproject.mydataset.suma`(num1 NUMERIC, num2, NUMERIC)
RETURNS NUMERIC AS(
   (num1 + num2)
)

-- Resta
CREATE OR REPLACE FUNCTION `myproject.mydataset.resta`(num1 NUMERIC, num2, NUMERIC)
RETURNS NUMERIC AS(
   (num1 - num2)
)

-- Multiplicación
CREATE OR REPLACE FUNCTION `myproject.mydataset.multiplicacion`(num1 NUMERIC, num2, NUMERIC)
RETURNS NUMERIC AS(
   (num1 * num2)
)

Y las podemos emplear de la siguiente forma:

DECLARE x NUMERIC DEFAULT 34.0;
DECLARE y NUMERIC DEFAULT 8.430;

-- Suma
SELECT `myproject.mydataset.suma`(x,y) AS sumar; -- 42.43


-- Resta
SELECT `myproject.mydataset.resta`(x,y) AS restar; -- 25.57

-- Multiplicación
SELECT `myproject.mydataset.multiplicacion`(x,y) AS producto;-- 286.62

Ejercicio 4. Teniendo el siguiente pseudo código. Crear una función que calcule el factorial de un número

Pseudocódigo:

FUNCTION FACT(N)
IF N = 0 THEN 
  RETURN 1;
ELSE 
  RETURN N * FACT(N - 1)
END FUNCTION

Función en BigQuery:

CREATE OR REPLACE FUNCTION `myproject.mydataset.FACT`(N INT64)
RETURNS INT64
AS (
  CASE 
    WHEN N = 0 THEN 1
    ELSE N * `myproject.mydataset.FACT`(N - 1)
  END
);

Uso:

DECLARE numero INT64;

SET numero = 5;
SELECT `myproject.mydataset.FACT`(numero) AS factorial; -- 120

Imaginemos que tenemos un vector. Así podríamos obtener el factorial de cada uno de sus elementos:

WITH numeros AS (
  SELECT num
  FROM UNNEST([2, 3, 4, 5, 6]) AS num
)
SELECT 
  num,
  `myproject.mydataset.FACT`(num) AS factorial
FROM 
  numeros ;

Ejercicio 5. Crear tres funciones en la cual:

• Una obtenga el IMC. 
• Otra obtenga el diagnóstico de sobrepeso de acuerdo al IMC. 
• Y la final, que que nos devuelva el resultado.

Función que calcula el IMC:

CREATE OR REPLACE FUNCTION `myproject.mydataset.IMC`(PESO FLOAT64, TALLA FLOAT64)
RETURNS FLOAT64
AS (
  PESO / (TALLA * TALLA)
);

Función que obtiene el diagnóstico de acuerdo al IMC obtenido:

CREATE OR REPLACE FUNCTION `myproject.mydataset.DIAGNOSTICO`(IMC FLOAT64)
RETURNS STRING
AS (
  CASE
    WHEN IMC < 18.5 THEN 'Bajo peso'
    WHEN IMC BETWEEN 18.5 AND 24.9 THEN 'Normal'
    WHEN IMC BETWEEN 25.0 AND 29.9 THEN 'Sobrepeso'
    WHEN IMC BETWEEN 30.0 AND 34.9 THEN 'Obesidad grado 1'
    WHEN IMC BETWEEN 35.0 AND 39.9 THEN 'Obesidad grado 2'
    ELSE 'Obesidad grado 3'
  END
);

Función que devuelve el resultado final:

CREATE OR REPLACE FUNCTION `myproject.mydataset.OBTENER_MASA`(PESO FLOAT64, TALLA FLOAT64)
RETURNS STRUCT<IMC FLOAT64, DIAGNOSTICO STRING>
AS (
  STRUCT(
    `myproject.mydataset.IMC`(PESO, TALLA) AS IMC,
    `myproject.mydataset.DIAGNOSTICO`(`myproject.mydataset.IMC`(PESO, TALLA)) AS DIAGNOSTICO
  )
);

La usamos de la siguiente forma:

SELECT 
  resultado.IMC AS imc,
  resultado.DIAGNOSTICO AS diagnostico
FROM (
  SELECT `myproject.mydataset.OBTENER_MASA`(70, 1.75) AS resultado
);

¡Hemos visto como crear nuestras propias funciones en BigQuery!

Más sobre este tema en próximas entregas.

Enlaces:

https://codemonkeyjunior.blogspot.com/2024/11/gcp-funciones-condicionales-en-bigquery.html
https://codemonkeyjunior.blogspot.com/2024/10/gcp-bigquery-funciones-de-cadena-y.html
https://cloud.google.com/bigquery/docs/user-defined-functions