2
votes

Script BQ: écriture des résultats d'une boucle dans une table

Je travaille avec des scripts BigQuery, j'ai écrit une simple boucle WHILE qui parcourt les tableaux quotidiens de Google Analytics et additionne les visites, maintenant j'aimerais écrire ces résultats dans une table.

J'ai obtenu en ce qui concerne la création de la table, mais je ne peux pas capturer la valeur des visites à partir de ma requête SQL pour remplir la table. La Date fonctionne très bien, car elle est définie en dehors du SQL. J'ai essayé de DECLARE la valeur des visites avec une nouvelle variable, mais là encore cela ne fonctionne pas car elle n'est pas connue en dehors de la déclaration.

SET vis = visites;

Comment puis-je écrire correctement mes résultats dans un tableau?

WHILE d > '2019-10-01' DO
  SET vis_count = (SELECT SUM(totals.visits) AS visits
                    FROM `mindful-agency-136314.43786551.ga_sessions_*`
                    WHERE _table_suffix = pfix);

  INSERT INTO test.looped_results VALUES (d, vis_count);

  SET d = DATE_SUB(d, INTERVAL 1 DAY);

END WHILE;

Mettre à jour : J'ai également essayé une solution alternative, en attribuant des visites à sa propre variable, mais cela produit la même erreur:

DECLARE d DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
DECLARE pfix STRING DEFAULT REGEXP_REPLACE(CAST(d AS STRING),"-","");
DECLARE vis INT64;

CREATE OR REPLACE TABLE test.looped_results (Date DATE, Visits INT64);

WHILE d > '2019-10-01' DO
  SELECT d, SUM(totals.visits) AS visits
  FROM `project.dataset.ga_sessions_*`
  WHERE _table_suffix = pfix
  GROUP  BY Date;
  SET d = DATE_SUB(d, INTERVAL 1 DAY);
  SET vis = visits;
  INSERT INTO test.looped_results VALUES (d, visits);
END WHILE;

Résultats:

entrez la description de l'image ici

Dans mes résultats, je vois le nombre correct de lignes créées, avec les dates correctes, mais la valeur de visites pour chacune est la valeur du jour le plus récent.

google-bigquery

0 commentaires

4 Réponses :

1
votes

Après avoir revu mon code (plusieurs fois!), j'ai réalisé que je n'actualisais pas la variable qui transforme les données en préfixe de table dans la boucle.

Voici une version fonctionnelle du script, où j'ai défini pfix à la fin de la boucle:

DECLARE d DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
DECLARE pfix STRING DEFAULT REGEXP_REPLACE(CAST(d AS STRING),"-","");
DECLARE vis_count INT64;
CREATE OR REPLACE TABLE test.looped_results (Date DATE, Visits INT64);

WHILE d > '2019-10-01' DO
  SET vis_count = (SELECT SUM(totals.visits) AS visits
                    FROM `project.dataset.ga_sessions_*`
                    WHERE _table_suffix = pfix);
  INSERT INTO test.looped_results VALUES (d, vis_count);
  SET d = DATE_SUB(d, INTERVAL 1 DAY);
  SET pfix = REGEXP_REPLACE(CAST(d AS STRING),"-","");
END WHILE;

0 commentaires

1
votes

En fait, vous devez mettre à jour la variable pfix ici. Il est également judicieux d'instancier les visites . Enfin, votre GROUPBY n'a pas nécessairement besoin d'une dimension si vous lui fournissez une contrainte pfix .

Cela devrait le faire:

DECLARE d DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
DECLARE pfix STRING DEFAULT REGEXP_REPLACE(CAST(d AS STRING),'-','');
DECLARE visits int64;
SET visits = 0;

CREATE OR REPLACE TABLE project.dataset.looped_results (Date DATE, Visits INT64);

WHILE d > '2019-10-01' DO
  SET visits = (SELECT SUM(totals.visits) FROM `project.dataset.ga_sessions_*` WHERE _table_suffix = pfix);
  SET d = DATE_SUB(d, INTERVAL 1 DAY);
  SET pfix = REGEXP_REPLACE(CAST(d AS STRING),"-","");
  INSERT INTO dataset.looped_results VALUES (d, visits);
END WHILE;

J'espère que cela vous aidera.

1 commentaires

Merci @khan sur place! Le seul changement étant que j'ai déplacé le INSERT au-dessus du SET pour les nouvelles variables, afin que je puisse les insérer dans le tableau avant de faire les changements pour la boucle suivante. Je vous remercie!

2
votes

Voici un meilleur moyen qui est plus rapide et sans utiliser de boucle.

En gros, vous formez un tableau de suffixes et faites SELECT / INSERT en une seule requête:

DECLARE date_range ARRAY<DATE> DEFAULT
  GENERATE_DATE_ARRAY(DATE '2019-10-01', DATE '2019-10-10', INTERVAL 1 DAY);

DECLARE suffix_array ARRAY<STRING> 
  DEFAULT (SELECT ARRAY_AGG(REGEXP_REPLACE(CAST(dates AS STRING),"-","")) 
           FROM UNNEST(date_range) dates);

CREATE OR REPLACE TABLE test.looped_results (Date DATE, Visits INT64);

INSERT INTO test.looped_results
SELECT Date, SUM(totals.visits)
FROM `project.dataset.ga_sessions_*`
WHERE _table_suffix in UNNEST(suffix_array);
GROUP BY Date;

0 commentaires

2
votes

Je déplacerais également INSERT INTO en dehors de la boucle WHILE en collectant le résultat dans la variable result (avec quelques autres changements mineurs) comme dans l'exemple ci-dessous

DECLARE d DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
DECLARE pfix STRING; 
DECLARE vis_count INT64;
DECLARE result ARRAY<STRUCT<vis_date DATE, vis_count INT64>> DEFAULT [];

CREATE OR REPLACE TABLE test.looped_results (Date DATE, Visits INT64);

WHILE d > '2019-10-01' DO
  SET pfix = REGEXP_REPLACE(CAST(d AS STRING),"-","");
  SET vis_count = (SELECT SUM(totals.visits) AS visits
                    FROM `project.dataset.ga_sessions_*`
                    WHERE _table_suffix = pfix);
  SET result = ARRAY_CONCAT(result, [STRUCT(d, vis_count)]);
  SET d = DATE_SUB(d, INTERVAL 1 DAY);
END WHILE;

INSERT INTO test.looped_results SELECT * FROM UNNEST(result);

Remarque: j'espère que votre exemple est destiné à l'apprentissage des scripts et non à la production, car dans la mesure du possible, nous devrions nous en tenir au traitement basé sur les ensembles, ce qui peut être facilement fait dans votre cas p >

2 commentaires

Merci @Mikhail - pouvez-vous expliquer ce que vous entendez par «traitement basé sur les ensembles»?

check Comprendre les approches «basées sur des ensembles» et «procédurales» dans SQL . vous pouvez google pour plus: o)