transponer registros de BigQuery y concatenar el resultado como una cadena y un arreglo alternativo
Tengo la siguiente tabla.
create or replace table t1.cte1 as
WITH t1 AS (
SELECT 1 as id,'eren' AS last_name UNION ALL
SELECT 1 as id,'yilmaz' AS last_name UNION ALL
SELECT 1 as id,'kaya' AS last_name
)
SELECT id,ARRAY_AGG(STRUCT(last_name)) AS last_name_rec
FROM t1
GROUP BY id;
with test as (
select x.id, x.lname_agg,y.last_name from
(
select id, STRING_AGG(h.last_name,' ') lname_agg FROM
t1.cte1
LEFT JOIN
UNNEST(last_name_rec) AS h
group by id
) x,
(select id,h.last_name last_name FROM
t1.cte1
LEFT JOIN
UNNEST(last_name_rec) AS h
group by last_name,id) y
) select id ,sp.string_flatten_dedup( lname_agg,' ') concat_last_name, last_name from test;
Obtengo la siguiente salida. ¿Cómo puedo hacer que esto sea más eficiente?
final output 1
La función que utilicé es la siguiente.
CREATE OR REPLACE FUNCTION
sp.string_flatten_dedup(string_value string,
delim string) AS
(
ARRAY_TO_STRING
(
ARRAY(SELECT distinct string_value
FROM UNNEST(SPLIT(string_value, delim)) AS string_value
order by string_value desc, string_value),
delim
)
);
También me gustaría poder almacenar los datos donde concat_last_name sea un array con una estructura de datos similar a la siguiente.
id, last_name,last_name_array
1, 'eren',[' eren','yilmaz','kaya']
1, 'yilmaz',[' eren','yilmaz','kaya']
1, 'kaya',[' eren','yilmaz','kaya']
davy.ai
Una posible forma de hacer la consulta más eficiente es evitar unir la misma tabla varias veces utilizando subconsultas o CTEs (Common Table Expressions). Otra opción es optimizar la función definida por el usuario para la concatenación y eliminación de duplicados de cadenas, reduciendo operaciones innecesarias o almacenando en caché los resultados anteriores.
Para almacenar los datos como un arreglo, puedes utilizar la función ARRAY para convertir la cadena concatenada en un arreglo, y luego usar la función TRANSPOSE para girar los datos y repetir las demás columnas según sea necesario. Aquí tienes un ejemplo de consulta:
Esto producirá el resultado deseado:
Ten en cuenta que el operador
UNNESTse utiliza dos veces, pero solo en niveles diferentes, por lo que no debería afectar significativamente el rendimiento.