Analizzare i dati utilizzando la sintassi della pipe

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

La sintassi 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 pipe è costituita dal simbolo pipe |>, da un operatore pipe nome e da eventuali argomenti. Per maggiori informazioni, consulta le seguenti risorse:

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

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

Obiettivi

• Visualizza i dati della tabella iniziando una query con una clausola FROM.
• Aggiungi colonne utilizzando l'operatore pipe EXTEND.
• Aggrega i dati per giorno e settimana utilizzando l'operatore pipe AGGREGATE.
• Aggrega i dati in una finestra scorrevole utilizzando l'operatore pipe CROSS JOIN.
• Filtra i dati utilizzando l'WHEREoperatore pipe.
• Confronta la struttura della query lineare della sintassi pipe con la struttura della query nidificata della sintassi standard quando esegui aggregazioni multilivello.

Prima di iniziare

Per iniziare a utilizzare un set di dati pubblici 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 account Google Cloud . Se non conosci Google Cloud, crea un account per valutare le prestazioni dei nostri prodotti in scenari reali. I nuovi clienti ricevono anche 300 $di crediti senza costi per l'esecuzione, il test e il deployment dei workload.

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 viene attivato automaticamente nei nuovi progetti. Per attivare BigQuery in un progetto preesistente:

   Abilita l'API BigQuery.

   Ruoli richiesti per abilitare le API

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

   Abilitare l'API

Per saperne di più sui diversi modi per eseguire query, vedi Eseguire una query.

Visualizzare i dati della tabella

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

Sintassi pipe

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

Sintassi standard

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

Nella sintassi della pipe, la query può iniziare con una clausola FROM senza una clausola SELECT 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 | ... |
| ...      | ...             | ...     | ...       | ...                     | ... |
+----------+-----------------+---------+-----------+-------------------------+-----+

Aggiungi 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 pipe, utilizza l'operatore pipe EXTEND:

Sintassi 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'operatore pipe AGGREGATE con la funzione COUNT per trovare il numero totale di viaggi effettuati e di biciclette utilizzate.

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

Sintassi 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            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Ordina risultati

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

Sintassi 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 della pipe, puoi aggiungere il suffisso di ordinamento direttamente alla clausola GROUP BY senza utilizzare l'operatore pipe ORDER BY. L'aggiunta del suffisso alla clausola GROUP BY è una delle diverse funzionalità di ordinamento abbreviato facoltative con AGGREGATE che la sintassi della pipe supporta. 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 ampliare la query per trovare il numero di biciclette distinte utilizzate in ogni finestra di sette giorni.

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

Sintassi 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 su una finestra scorrevole

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

Per applicare una finestra mobile ai tuoi dati, copia prima ogni viaggio in avanti di sei giorni attivi aggiuntivi dalla data di inizio. Quindi, 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 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 della 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 della colonna diff_days:

   Sintassi 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 in tutti i giorni fino al giorno 2024-05-26.

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

   Sintassi 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 fino a sei giorni dopo l'ultima data nei dati. Per filtrare le date che vanno 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 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 saperne di più sul funzionamento della sintassi della barra verticale, consulta la sezione Utilizzare la sintassi delle query con la barra verticale.
• Per informazioni più tecniche, consulta la documentazione di riferimento sulla sintassi delle query pipe.