Analizza i dati utilizzando la sintassi pipe

La sintassi pipe in GoogleSQL ti consente di strutturare le query in modo lineare, semplificando la lettura, la scrittura e la manutenzione di analisi dei dati complesse. In questo tutorial, crei una query utilizzando la tabella bigquery-public-data.austin_bikeshare.bikeshare_trips disponibile pubblicamente per filtrare, aggregare e analizzare i dati dei percorsi in bicicletta. Per informazioni di base sui concetti e sulle specifiche degli operatori, consulta Utilizza la sintassi delle query pipe e Sintassi delle query pipe.

Obiettivi

• Visualizza i dati della tabella avviando 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 temporale scorrevole utilizzando l'operatore pipe CROSS JOIN.
• Filtra i dati utilizzando l'operatore pipe WHERE.
• Confronta la struttura di query lineare della sintassi pipe con la struttura di query nidificata della sintassi standard quando esegui aggregazioni multilivello.

Prima di iniziare

Per iniziare a utilizzare un set di dati pubblico 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 abilitare la fatturazione. Se intendi superare il Livello senza costi, devi anche abilitare 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 abilitato automaticamente nei nuovi progetti. Per attivare BigQuery in un progetto preesistente:

   Abilita l'API BigQuery, se non è già abilitata.

   Ruoli richiesti per abilitare le API

   Per abilitare le API, devi disporre dell'autorizzazione serviceusage.services.enable. Se hai creato il progetto, probabilmente hai già questa autorizzazione tramite il ruolo Proprietario (roles/owner). In caso contrario, puoi ottenere questa autorizzazione tramite il ruolo Amministratore Service Usage (roles/serviceusage.serviceUsageAdmin). Scopri come concedere i ruoli.

   Abilitare l'API

Per saperne di più sui diversi modi per eseguire le query, consulta Esegui una query.

Visualizza i dati della tabella

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

Sintassi pipe

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

Sintassi standard

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

Nella sintassi 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 della corsa. 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 | ... |
| ...      | ...             | ...     | ...       | ...                     | ...        | ... |
+----------+-----------------+---------+-----------+-------------------------+------------+-----+

Dati giornalieri aggregati

Puoi raggruppare per data per trovare il numero totale di corse effettuate e di biciclette utilizzate al giorno.

• Utilizza l'operatore pipe AGGREGATE con la funzione COUNT per trovare il numero totale di corse effettuate 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 i 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 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 supportate dalla sintassi pipe. Nella sintassi standard, questo non è possibile e devi utilizzare la clausola ORDER BY per l'ordinamento.

Dati settimanali aggregati

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

Per aggiornare le righe della tabella in modo che vengano visualizzate 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            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Aggrega su una finestra temporale scorrevole

I risultati nella sezione precedente mostrano i percorsi in una finestra fissa tra le date di inizio e di fine, ad esempio dal giorno 2024-06-23 al giorno 2024-06-29. Invece, potresti voler visualizzare le corse in una finestra temporale 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 corse effettuate e di biciclette utilizzate nella settimana successiva.

Per applicare una finestra temporale scorrevole ai tuoi dati, copia prima ogni corsa in avanti di sei giorni attivi aggiuntivi rispetto alla data di inizio. Poi, calcola le date dei giorni attivi utilizzando la funzione DATE_ADD. Infine, aggrega le corse 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 far scorrere la finestra in avanti di un numero di giorni corrispondente, fino a sette giorni dopo la data originale.

2. Per visualizzare le date attive calcolate per le corse, utilizza l'operatore pipe EXTEND con la funzione DATE_ADD per creare una colonna denominata active_date che contiene la data di inizio più il valore nella 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 ogni giorno fino al giorno 2024-05-26.

3. Infine, aggrega gli ID dei percorsi e gli ID delle biciclette e raggruppali 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            |
   | ...         | ...             | ...             |
   +-------------+-----------------+-----------------+
   

Filtra le date future

Nella query precedente, le date si estendono nel futuro fino a sei giorni oltre l'ultima data dei 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 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ù su come funziona la sintassi pipe, consulta Utilizza la sintassi delle query pipe.
• Per ulteriori informazioni tecniche, consulta la documentazione di riferimento sulla sintassi delle query con pipe.