Analizzare i dati utilizzando la sintassi con operatore pipe

Questo tutorial mostra come scrivere query utilizzando la sintassi con operatore pipe per analizzare i dati.

La sintassi con operatore pipe è un'estensione di GoogleSQL che supporta una struttura di query lineare progettata per semplificare la lettura, la scrittura e la manutenzione delle query. La sintassi con operatore pipe è costituita dal simbolo pipe |>, da un nome di operatore pipe e da eventuali argomenti. Per maggiori informazioni, consulta le seguenti risorse:

• Per un'introduzione alla sintassi con operatore pipe, consulta Utilizzare la sintassi delle query con operatore pipe.
• Per tutti i dettagli sulla sintassi, consulta la documentazione di riferimento sulla sintassi delle query con operatore pipe.

In questo tutorial, creerai una query complessa nella sintassi con operatore pipe utilizzando la tabella disponibile pubblicamente bigquery-public-data.austin_bikeshare.bikeshare_trips, che contiene dati sui viaggi in bicicletta.

Obiettivi

• Visualizzare i dati della tabella avviando una query con una FROM clausola.
• Aggiungere colonne utilizzando l'operatore pipe EXTEND.
• Aggregare i dati per giorno e settimana utilizzando l'operatore pipe AGGREGATE.
• Aggregare i dati in una finestra scorrevole utilizzando l'operatore pipe CROSS JOIN.
• Filtrare i dati utilizzando l'operatore pipe WHERE.
• Confrontare la struttura di query lineare della sintassi con operatore pipe con la struttura di query nidificata della sintassi standard quando si eseguono aggregazioni a più livelli.

Prima di iniziare

Per iniziare a utilizzare un set di dati pubblici di BigQuery, devi creare o selezionare un progetto. Il primo terabyte di dati elaborati al mese è senza costi, quindi puoi iniziare a eseguire query sui set di dati pubblici senza attivare la fatturazione. Se intendi superare il livello senza costi, devi anche attivare la fatturazione.

Accedi al tuo Google Cloud account. Se non hai mai utilizzato Google Cloud, crea un account per valutare il rendimento dei nostri prodotti in scenari reali. I nuovi clienti ricevono anche 300 $di crediti senza costi per eseguire, testare ed eseguire il deployment di carichi di lavoro.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

1. BigQuery è abilitato automaticamente nei nuovi progetti. Per attivare BigQuery in un progetto preesistente,

   Abilita l'API BigQuery.

   Ruoli necessari per abilitare le API

   Per abilitare le API, devi disporre del ruolo IAM Amministratore utilizzo servizi (roles/serviceusage.serviceUsageAdmin), che contiene l'autorizzazione serviceusage.services.enable. Scopri come concedere i ruoli.

   Abilitare l'API

Per ulteriori informazioni sui diversi modi per eseguire query, consulta Eseguire una query.

Visualizzare i dati della tabella

Per recuperare tutti i dati dalla tabella bikeshare_trips, esegui la seguente query:

Sintassi con operatore pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

Sintassi standard

SELECT *
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

Nella sintassi con operatore pipe, la query può iniziare con una FROM clausola senza una SELECT clausola per restituire i risultati della tabella.

Il risultato è simile al seguente:

+----------+-----------------+---------+-----------+-------------------------+-----+
| trip_id  | subscriber_type | bike_id | bike_type | start_time              | ... |
+----------+-----------------+---------+-----------+-------------------------+-----+
| 28875008 | Pay-as-you-ride | 18181   | electric  | 2023-02-12 12:46:32 UTC | ... |
| 28735401 | Explorer        | 214     | classic   | 2023-01-13 12:01:45 UTC | ... |
| 29381980 | Local365        | 21803   | electric  | 2023-04-20 08:43:46 UTC | ... |
| ...      | ...             | ...     | ...       | ...                     | ... |
+----------+-----------------+---------+-----------+-------------------------+-----+

Aggiungere colonne

Nella tabella bikeshare_trips, la colonna start_time è un timestamp, ma potresti voler aggiungere una colonna che mostri solo la data del viaggio. Per aggiungere una colonna nella sintassi con operatore pipe, utilizza l' EXTEND operatore pipe:

Sintassi con operatore pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date;

Sintassi standard

SELECT *, CAST(start_time AS DATE) AS date
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

Il risultato è simile al seguente:

+----------+-----------------+---------+-----------+-------------------------+------------+-----+
| trip_id  | subscriber_type | bike_id | bike_type | start_time              | date       | ... |
+----------+-----------------+---------+-----------+-------------------------+------------+-----+
| 28875008 | Pay-as-you-ride | 18181   | electric  | 2023-02-12 12:46:32 UTC | 2023-02-12 | ... |
| 28735401 | Explorer        | 214     | classic   | 2023-01-13 12:01:45 UTC | 2023-01-13 | ... |
| 29381980 | Local365        | 21803   | electric  | 2023-04-20 08:43:46 UTC | 2023-04-20 | ... |
| ...      | ...             | ...     | ...       | ...                     | ...        | ... |
+----------+-----------------+---------+-----------+-------------------------+------------+-----+

Aggregare i dati giornalieri

Puoi raggruppare per data per trovare il numero totale di viaggi effettuati e le biciclette utilizzate al giorno.

• Utilizza l' AGGREGATE operatore pipe con la funzione COUNT per trovare il numero totale di viaggi effettuati e biciclette utilizzate.

• Utilizza la clausola GROUP BY per raggruppare i risultati per data.

Sintassi con operatore pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> AGGREGATE
     COUNT(*) AS trips,
     COUNT(DISTINCT bike_id) AS distinct_bikes
   GROUP BY date;

Sintassi standard

SELECT
  CAST(start_time AS DATE) AS date,
  COUNT(*) AS trips,
  COUNT(DISTINCT bike_id) AS distinct_bikes
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY date;

Il risultato è simile al seguente:

+------------+-------+----------------+
| date       | trips | distinct_bikes |
+------------+-------+----------------+
| 2023-04-20 | 841   | 197            |
| 2023-01-27 | 763   | 148            |
| 2023-06-12 | 562   | 202            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Ordinare i risultati

Per ordinare i risultati in ordine decrescente in base alla colonna date, aggiungi il DESC suffisso alla clausola GROUP BY:

Sintassi con operatore pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> AGGREGATE
     COUNT(*) AS trips,
     COUNT(DISTINCT bike_id) AS distinct_bikes
   GROUP BY date DESC;

Sintassi standard

SELECT
  CAST(start_time AS DATE) AS date,
  COUNT(*) AS trips,
  COUNT(DISTINCT bike_id) AS distinct_bikes
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY date
ORDER BY date DESC;

Il risultato è simile al seguente:

+------------+-------+----------------+
| date       | trips | distinct_bikes |
+------------+-------+----------------+
| 2024-06-30 | 331   | 90             |
| 2024-06-29 | 395   | 123            |
| 2024-06-28 | 437   | 137            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Nella sintassi con operatore pipe, puoi aggiungere il suffisso di ordinamento direttamente alla GROUP BY clausola senza utilizzare l' ORDER BY operatore pipe. L'aggiunta del suffisso alla clausola GROUP BY è una delle diverse funzionalità di ordinamento abbreviato facoltative con AGGREGATE supportate dalla sintassi con operatore pipe. Nella sintassi standard, questa operazione non è possibile e devi utilizzare la clausola ORDER BY per l'ordinamento.

Aggregare i dati settimanali

Ora che hai i dati sul numero di biciclette utilizzate ogni giorno, puoi creare una query per trovare il numero di biciclette distinte utilizzate in ogni finestra di sette giorni.

Per aggiornare le righe della tabella in modo da visualizzare le settimane anziché i giorni, utilizza la DATE_TRUNC funzione nella clausola GROUP BY e imposta la granularità su WEEK:

Sintassi con operatore pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> AGGREGATE
    COUNT(*) AS trips,
    COUNT(DISTINCT bike_id) AS distinct_bikes,
GROUP BY DATE_TRUNC(date, WEEK) AS date DESC;

Sintassi standard

SELECT
  DATE_TRUNC(CAST(start_time AS DATE), WEEK) AS date,
  COUNT(*) AS trips,
  COUNT(DISTINCT bike_id) AS distinct_bikes,
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY date
ORDER BY date DESC;

Il risultato è simile al seguente:

+------------+-------+----------------+
| date       | trips | distinct_bikes |
+------------+-------+----------------+
| 2024-06-30 | 331   | 90             |
| 2024-06-23 | 3206  | 213            |
| 2024-06-16 | 3441  | 212            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Aggregare in una finestra scorrevole

I risultati nella sezione precedente mostrano i viaggi in una finestra fissa tra le date di inizio e fine, ad esempio da 2024-06-23 a 2024-06-29. In alternativa, potresti voler visualizzare i viaggi in una finestra scorrevole, in un periodo di sette giorni che avanza nel tempo con ogni nuovo giorno. In altre parole, per una determinata data potresti voler conoscere il numero di viaggi effettuati e le biciclette utilizzate nella settimana successiva.

Per applicare una finestra scorrevole ai dati, copia prima ogni viaggio in avanti di altri sei giorni attivi dalla data di inizio. Poi, calcola le date dei giorni attivi utilizzando la funzione DATE_ADD. Infine, aggrega i viaggi e gli ID delle biciclette per ogni giorno attivo.

1. Per copiare i dati in avanti, utilizza la funzione GENERATE_ARRAY e un cross join:

   Sintassi con operatore pipe

   FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
   |> EXTEND CAST(start_time AS DATE) AS date
   |> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days;
   

   Sintassi standard

   SELECT *, CAST(start_time AS DATE) AS date
   FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
   CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days;
   

   La funzione GENERATE_ARRAY crea un array con sette elementi, da 0 a 6. L'operazione CROSS JOIN UNNEST crea sette copie di ogni riga, con una nuova colonna diff_days che contiene uno dei valori degli elementi dell'array da 0 a 6 per ogni riga. Puoi utilizzare i valori diff_days come aggiustamento alla data originale per spostare la finestra in avanti di quel numero di giorni, fino a sette giorni dopo la data originale.

2. Per visualizzare le date attive calcolate per i viaggi, utilizza l'operatore pipe EXTEND con la funzione DATE_ADD per creare una colonna denominata active_date che contenga la data di inizio più il valore nella colonna diff_days:

   Sintassi con operatore pipe

   FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
   |> EXTEND CAST(start_time AS DATE) AS date
   |> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days
   |> EXTEND DATE_ADD(date, INTERVAL diff_days DAY) AS active_date;
   

   Sintassi standard

   SELECT *, DATE_ADD(date, INTERVAL diff_days DAY) AS active_date
   FROM (
     SELECT *, CAST(start_time AS DATE) AS date
     FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
     CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days)
   

   Ad esempio, un viaggio che inizia il giorno 2024-05-20 è considerato attivo anche ogni giorno fino al giorno 2024-05-26.

3. Infine, aggrega gli ID dei viaggi e gli ID delle biciclette e raggruppa per active_date:

   Sintassi con operatore pipe

   FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
   |> EXTEND CAST(start_time AS DATE) AS date
   |> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days
   |> EXTEND DATE_ADD(date, INTERVAL diff_days DAY) AS active_date
   |> AGGREGATE COUNT(DISTINCT bike_id) AS active_7d_bikes,
               COUNT(trip_id) AS active_7d_trips
   GROUP BY active_date DESC;
   

   Sintassi standard

   SELECT
     DATE_ADD(date, INTERVAL diff_days DAY) AS active_date,
     COUNT(DISTINCT bike_id) AS active_7d_bikes,
     COUNT(trip_id) AS active_7d_trips
   FROM (
     SELECT *, CAST(start_time AS DATE) AS date
     FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
     CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days)
   GROUP BY active_date
   ORDER BY active_date DESC;
   

   Il risultato è simile al seguente:

   +-------------+-----------------+-----------------+
   | active_date | active_7d_bikes | active_7d_trips |
   +-------------+-----------------+-----------------+
   | 2024-07-06  | 90              | 331             |
   | 2024-07-05  | 142             | 726             |
   | 2024-07-04  | 186             | 1163            |
   | ...         | ...             | ...             |
   +-------------+-----------------+-----------------+
   

Filtrare le date future

Nella query precedente, le date si estendono nel futuro fino a sei giorni oltre l'ultima data nei dati. Per filtrare le date che si estendono oltre la fine dei dati, imposta una data massima nella query:

1. Aggiungi un altro operatore pipe EXTEND che utilizza una funzione finestra con una clausola OVER per calcolare la data massima nella tabella.
2. Utilizza l'operatore pipe WHERE per filtrare le righe generate che superano la data massima.

Sintassi con operatore pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> EXTEND MAX(date) OVER () AS max_date
|> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days
|> EXTEND DATE_ADD(date, INTERVAL diff_days DAY) AS active_date
|> WHERE active_date <= max_date
|> AGGREGATE COUNT(DISTINCT bike_id) AS active_7d_bikes,
             COUNT(trip_id) AS active_7d_trips
   GROUP BY active_date DESC;

Sintassi standard

SELECT
  DATE_ADD(date, INTERVAL diff_days DAY) AS active_date,
  COUNT(DISTINCT bike_id) AS active_7d_bikes,
  COUNT(trip_id) AS active_7d_trips
FROM(
  SELECT *
  FROM (
    SELECT *,
      DATE_ADD(date, INTERVAL diff_days DAY) AS active_date,
      MAX(date) OVER () AS max_date
    FROM(
      SELECT *, CAST(start_time AS DATE) AS date,
      FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
      CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days))
  WHERE active_date <= max_date)
GROUP BY active_date
ORDER BY active_date DESC;

Il risultato è simile al seguente:

+-------------+-----------------+-----------------+
| active_date | active_7d_bikes | active_7d_trips |
+-------------+-----------------+-----------------+
| 2024-06-30  | 212             | 3031            |
| 2024-06-29  | 213             | 3206            |
| 2024-06-28  | 219             | 3476            |
| ...         | ...             | ...             |
+-------------+-----------------+-----------------+

Passaggi successivi

• Per ulteriori informazioni sul funzionamento della sintassi con operatore pipe, consulta Utilizzare la sintassi delle query con operatore pipe.
• Per ulteriori informazioni tecniche, consulta la documentazione di riferimento sulla sintassi delle query con operatore pipe.