es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

Cómo deduplicar array_agg en BigQuery

14 Jul, 2023 Programación

Creé una nueva tabla con registros repetidos con duplicados. Estoy tratando de encontrar la forma más eficiente de eliminar los registros duplicados, ya que se ejecutará en una tabla con millones de registros. Si utilizas múltiples CTE anidados, ¿importa en qué estructura de datos se realiza el procesamiento en memoria o escribe en tablas temporales cuando hay muchos datos?

create or replace table t1.cte4 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 UNION ALL
  SELECT 1 as id,'kaya' AS last_name UNION ALL
  SELECT 2 as id,'smith' AS last_name UNION ALL
  SELECT 2 as id,'jones' AS last_name UNION ALL
  SELECT 2 as id,'jones' AS last_name UNION ALL
  SELECT 2 as id,'jones' AS last_name UNION ALL
  SELECT 2 as id,'brown' AS last_name
)
SELECT id,ARRAY_AGG(STRUCT(last_name)) AS last_name_rec
FROM t1
GROUP BY id;

Puedo eliminar los duplicados de la siguiente manera.

QUERY 1 ¿Cómo deduplicar concat_struct?
select id, 
STRING_AGG( distinct ln.last_name ,'~') as concat_string,
ARRAY_AGG(STRUCT( ln.last_name )) as concat_struct
from `t1.cte4`, unnest(last_name_rec) ln
group by id;

QUERY 1

QUERY 2 ¿Hay una mejor manera de deduplicar?
select distinct id, 
TO_JSON_STRING(ARRAY_AGG(ln.last_name) OVER (PARTITION BY id)) json_string
from `t1.cte4`, unnest(last_name_rec) ln
group by id,
ln.last_name;

QUERY 2

¿Cómo lo saco de la tabla como distintos en lugar de usar el CTE? Esto no elimina duplicados.

select id,  ARRAY_AGG(STRUCT( ln.last_name )) as concat_struct 
from t1.cte4, 
unnest(last_name_rec) ln group by id;

No puedo hacer esto.

select id,  ARRAY_AGG(distinct STRUCT( ln.last_name )) as concat_struct from t1.cte4, 
unnest(last_name_rec) ln group by id;
Tags:  , , ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Consejos de expertos en TI sobre la eliminación eficiente de duplicados de grandes tablas SQL

    Como experto en TI especializado en SQL, Google BigQuery, duplicados y array-agg, recomendaría los siguientes enfoques para eliminar eficientemente duplicados de grandes tablas SQL:

    1. Utilice la Consulta 1 o la Consulta 2 para deduplicar su tabla con registros repetidos. La Consulta 1 utiliza STRING_AGG y ARRAY_AGG para deduplicar el concat_struct, mientras que la Consulta 2 utiliza TO_JSON_STRING y OVER para deduplicar los registros al particionarlos por id. Ambas consultas son efectivas y puede elegir la que mejor se desempeñe para su caso de uso específico.

    2. Para obtener los registros deduplicados de la tabla como registros distintos en lugar de utilizar un CTE, puede intentar usar la función ARRAY_DISTINCT. Por ejemplo, puede ejecutar la siguiente consulta:

      SELECT id, ARRAY_DISTINCT(ARRAY_AGG(STRUCT(ln.last_name))) AS concat_struct 
FROM `t1.cte4`, UNNEST(last_name_rec) AS ln 
GROUP BY id;

      Esta consulta utiliza ARRAY_DISTINCT para eliminar registros duplicados del array_agg de valores de estructura.

    En general, también recomendaría optimizar sus consultas SQL utilizando índices y particiones apropiadas, reduciendo la cantidad de niveles de anidación y minimizando las operaciones de E/S para mejorar el rendimiento. Además, considere utilizar servicios basados en la nube más rápidos como Google BigQuery, que puede manejar conjuntos de datos grandes de manera eficiente y escalar para satisfacer sus necesidades.

Comments are closed.