Comment supprimer les données en double de BigQuery et les enregistrer dans une autre table comportant beaucoup d'attributs

Vous pouvez créer une sous-requête comptant pour chaque ligne le nombre de colonnes remplies, puis vous triez:

SELECT *
FROM (
  SELECT
      *,
      ROW_NUMBER()
          OVER (PARTITION BY company_name ORDER BY columns_filled DESC)
          row_number
  FROM (
        SELECT *, 
        IF(uppose !="", 1,0) + IF(company_name !="", 1,0) + IF(phone !="", 1,0) + 
        IF(email !="", 1,0) + IF(address !="", 1,0) + IF(city !="", 1,0) + 
        IF(state !="", 1,0) + <SAME FOR EACH FIELD> as columns_filled
        FROM `local-bastion-154121.Property_Dataset.pmDATA`
   )
)
WHERE row_number = 1

C'est tout :)

J'interprète "avec l'attribut le plus élevé" comme signifiant la ligne avec le plus de valeurs non NULL pour un company_name particulier. Vous devriez pouvoir faire quelque chose comme ceci:

WITH existing_table AS (
  SELECT 'Microsoft' AS company_name, 2355 AS x, NULL AS email, NULL AS city, NULL AS y, NULL AS z UNION ALL
  SELECT 'Microsoft', 1234, 'ms@example.com', 'seattle', 'XYZ', 'KC' UNION ALL
  SELECT 'Microsoft', 2355, NULL, NULL, NULL, NULL
)
SELECT
  company_name,
  ARRAY_AGG(
    (SELECT AS STRUCT t.* EXCEPT (company_name))
    ORDER BY ARRAY_LENGTH(SPLIT(TO_JSON_STRING(t), ':null'))
  )[OFFSET(0)].*
FROM existing_table AS t
GROUP BY company_name

À titre d'exemple sur des exemples de données:

CREATE TABLE dataset.new_table AS
SELECT
  company_name,
  ARRAY_AGG(
    (SELECT AS STRUCT t.* EXCEPT (company_name))
    ORDER BY ARRAY_LENGTH(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r': null'))
  )[OFFSET(0)].*
FROM dataset.existing_table AS t
GROUP BY company_name

L'avantage d'utiliser cette astuce et compter les valeurs NULL en utilisant SPLIT en conjonction avec TO_JSON_STRING , c'est que vous n'avez pas besoin d'écrire la liste des autres colonnes explicitement. Ce qu'il fait est de construire une structure de toutes les colonnes à l'exception de nom_colonne , et de trier par le nombre de valeurs NULL dans la ligne dans l'ordre croissant, ce qui signifie que vous obtenez le ligne avec les valeurs les plus renseignées pour chaque nom_entreprise.

Je considérerais une traduction légèrement différente de "avec l'attribut le plus élevé" en introduisant un poids par champ, comme par exemple je m'attendrais à ce qu'avoir email soit plus important que city , state donc un seul champ surpondérerait deux champs pour moi

​​Voici pour BigQuery Standard SQL et tente une approche pondérée

Row company_name    phone   email           city    address state   score   
1   Microsoft       2355    any@example.com null    null    null    104  
2   Microsoft       1234    null            seattle XYZ     KC      14   
3   Microsoft       2355    null            null    null    null    4    

Vous pouvez test, jouez avec ceci en utilisant des exemples de données de votre question comme ci-dessous

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'Microsoft' company_name, 2355 phone, NULL email, NULL city, NULL address, NULL state UNION ALL
  SELECT 'Microsoft', 1234, NULL, 'seattle', 'XYZ', 'KC' UNION ALL
  SELECT 'Microsoft', 2355, 'any@example.com', NULL, NULL, NULL
), weights AS (
  SELECT 'phone' field, 4 weight UNION ALL
  SELECT 'email', 100 UNION ALL
  SELECT 'city', 2 UNION ALL
  SELECT 'address', 1 UNION ALL
  SELECT 'state', 7
)
SELECT 
  ANY_VALUE(t).*,
  SUM(weight) score
FROM `project.dataset.table` t
CROSS JOIN weights w 
WHERE REGEXP_EXTRACT(TO_JSON_STRING(t), CONCAT(r'', field, '":"?(.*?)"?[,}]')) != 'null'
GROUP BY TO_JSON_STRING(t)
ORDER BY score DESC

avec résultat

Row company_name    phone   email           city    address state    
1   Microsoft       2355    any@example.com null    null    null      

Comme vous pouvez le voir ici , le gagnant a moins d'attributs disponibles que les autres lignes car il a plus d'attribut "précieux"

Vous pouvez voir le score en utilisant ci-dessous

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'Microsoft' company_name, 2355 phone, NULL email, NULL city, NULL address, NULL state UNION ALL
  SELECT 'Microsoft', 1234, NULL, 'seattle', 'XYZ', 'KC' UNION ALL
  SELECT 'Microsoft', 2355, 'any@example.com', NULL, NULL, NULL
), weights AS (
  SELECT 'phone' field, 4 weight UNION ALL
  SELECT 'email', 100 UNION ALL
  SELECT 'city', 2 UNION ALL
  SELECT 'address', 1 UNION ALL
  SELECT 'state', 7
)
SELECT
  ARRAY_AGG(r ORDER BY score DESC LIMIT 1)[OFFSET(0)].*
FROM (
  SELECT 
    ANY_VALUE(t) r,
    SUM(weight) score
  FROM `project.dataset.table` t
  CROSS JOIN weights w 
  WHERE REGEXP_EXTRACT(TO_JSON_STRING(t), CONCAT(r'', field, '":"?(.*?)"?[,}]')) != 'null'
  GROUP BY TO_JSON_STRING(t)
)
GROUP BY r.company_name   

donc les scores sont p>

#standardSQL
WITH weights AS (
  SELECT 'phone' field, 4 weight UNION ALL
  SELECT 'email', 100 UNION ALL
  SELECT 'city', 2 UNION ALL
  SELECT 'address', 1 UNION ALL
  SELECT 'state', 7
)
SELECT
  ARRAY_AGG(r ORDER BY score DESC LIMIT 1)[OFFSET(0)].*
FROM (
  SELECT 
    ANY_VALUE(t) r,
    SUM(weight) score
  FROM `local-bastion-154121.Property_Dataset.pmDATA` t
  CROSS JOIN weights w 
  WHERE REGEXP_EXTRACT(TO_JSON_STRING(t), CONCAT(r'', field, '":"?(.*?)"?[,}]')) != 'null'
  GROUP BY TO_JSON_STRING(t)
)
GROUP BY r.company_name