Alcune o tutte le informazioni in questa pagina potrebbero non essere applicabili a Trusted Cloud by S3NS.

Questa pagina è stata tradotta dall'API Cloud Translation.

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 pipe, vedi Utilizzare la sintassi delle query pipe.
Per tutti i dettagli sulla sintassi, consulta la documentazione di riferimento Sintassi della 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 pipeCROSS JOIN.
Filtra i dati utilizzando l'WHERE operatore 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 è gratuito, quindi puoi iniziare a eseguire query sui set di dati pubblici senza attivare la fatturazione. Se intendi superare il livello gratuito, devi anche attivare la fatturazione.

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

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 Trusted Cloud project.
BigQuery viene attivato automaticamente nei nuovi progetti. Per attivare BigQuery in un progetto preesistente,
Enable the BigQuery API.
Enable the 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 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 i dati 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 con AGGREGATE supportate dalla sintassi della barra verticale. 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 da visualizzare 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 mobile

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.

Per copiare i dati in avanti, utilizza la funzione GENERATE_ARRAY e un prodotto incrociato:
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 alla data originale per spostare la finestra in avanti di quel numero di giorni, fino a sette giorni dopo la data originale.

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, una corsa che inizia il giorno 2024-05-20 è considerata attiva anche in tutti i giorni fino al giorno 2024-05-26.

Infine, aggrega gli ID viaggio e gli ID bicicletta 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            |
| ...         | ...             | ...             |
+-------------+-----------------+-----------------+

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:

Aggiungi un altro operatore EXTEND pipe che utilizza una funzione finestra con una clausola OVER per calcolare la data massima nella tabella.
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 ulteriori informazioni sul funzionamento della sintassi della barra verticale, consulta Utilizzare la sintassi delle query con la barra verticale.
Per informazioni più tecniche, consulta la documentazione di riferimento sulla sintassi delle query pipe.