Eseguire query su campi nidificati e ripetuti in SQL precedente
Questo documento descrive come eseguire query su dati nidificati e ripetuti nella sintassi delle query SQL precedente. La sintassi delle query preferita per BigQuery è GoogleSQL. Per informazioni su come gestire i dati nidificati e ripetuti in GoogleSQL, consulta la guida alla migrazione di GoogleSQL.
BigQuery supporta il caricamento
e l'esportazione di dati nidificati e ripetuti
di file JSON e Avro. Per molte query SQL precedente, BigQuery può automaticamente
appiattire i dati. Ad esempio, molte istruzioni SELECT possono recuperare nidificate o ripetute
mantenendo la struttura dei dati e le clausole WHERE possono filtrare i dati
mantenendone comunque la struttura. Al contrario, le clausole ORDER BY e GROUP BY
appiattiscono implicitamente i dati sottoposti a query. Per le circostanze in cui i dati non sono appiattiti implicitamente, come l'esecuzione di query su più campi ripetuti in SQL precedente, puoi eseguire query sui dati utilizzando le funzioni SQL FLATTEN e WITHIN.
PIATTAFORMA
Quando esegui query sui dati nidificati, BigQuery appiattisce automaticamente i dati della tabella. Ad esempio, diamo un'occhiata a uno schema di esempio per i dati delle persone:
Last modified Schema Total Rows Total Bytes Expiration
----------------- ----------------------------------- ------------ ------------- ------------
27 Sep 10:01:06 |- kind: string 4 794
|- fullName: string (required)
|- age: integer
|- gender: string
+- phoneNumber: record
| |- areaCode: integer
| |- number: integer
+- children: record (repeated)
| |- name: string
| |- gender: string
| |- age: integer
+- citiesLived: record (repeated)
| |- place: string
| +- yearsLived: integer (repeated)Nota che sono presenti diversi campi ripetuti e nidificati. Se esegui una query SQL precedente come seguenti rispetto alla tabella delle persone :
SELECT fullName AS name, age, gender, citiesLived.place, citiesLived.yearsLived FROM [dataset.tableId]
BigQuery restituisce i dati con un output bidimensionale:
+---------------+-----+--------+-------------------+------------------------+ | name | age | gender | citiesLived_place | citiesLived_yearsLived | +---------------+-----+--------+-------------------+------------------------+ | John Doe | 22 | Male | Seattle | 1995 | | John Doe | 22 | Male | Stockholm | 2005 | | Mike Jones | 35 | Male | Los Angeles | 1989 | | Mike Jones | 35 | Male | Los Angeles | 1993 | | Mike Jones | 35 | Male | Los Angeles | 1998 | | Mike Jones | 35 | Male | Los Angeles | 2002 | | Mike Jones | 35 | Male | Washington DC | 1990 | | Mike Jones | 35 | Male | Washington DC | 1993 | | Mike Jones | 35 | Male | Washington DC | 1998 | | Mike Jones | 35 | Male | Washington DC | 2008 | | Mike Jones | 35 | Male | Portland | 1993 | | Mike Jones | 35 | Male | Portland | 1998 | | Mike Jones | 35 | Male | Portland | 2003 | | Mike Jones | 35 | Male | Portland | 2005 | | Mike Jones | 35 | Male | Austin | 1973 | | Mike Jones | 35 | Male | Austin | 1998 | | Mike Jones | 35 | Male | Austin | 2001 | | Mike Jones | 35 | Male | Austin | 2005 | | Anna Karenina | 45 | Female | Stockholm | 1992 | | Anna Karenina | 45 | Female | Stockholm | 1998 | | Anna Karenina | 45 | Female | Stockholm | 2000 | | Anna Karenina | 45 | Female | Stockholm | 2010 | | Anna Karenina | 45 | Female | Moscow | 1998 | | Anna Karenina | 45 | Female | Moscow | 2001 | | Anna Karenina | 45 | Female | Moscow | 2005 | | Anna Karenina | 45 | Female | Austin | 1995 | | Anna Karenina | 45 | Female | Austin | 1999 | +---------------+-----+--------+-------------------+------------------------+
In questo esempio, citiesLived.place è ora citiesLived_place e
citiesLived.yearsLived adesso è citiesLived_yearsLived.
Sebbene BigQuery possa appiattire automaticamente i campi nidificati, potresti dover
chiamare esplicitamente FLATTEN quando si gestiscono più campi ripetuti. Ad esempio,
se provi a eseguire una query SQL precedente come la seguente:
SELECT fullName, age FROM [dataset.tableId] WHERE (citiesLived.yearsLived > 1995 ) AND (children.age > 3)
BigQuery restituisce un errore simile a questo:
Cannot query the cross product of repeated fields children.age and citiesLived.yearsLived
Per eseguire query su più di un campo ripetuto, devi appiattire uno dei campi:
SELECT fullName, age, gender, citiesLived.place FROM (FLATTEN([dataset.tableId], children)) WHERE (citiesLived.yearsLived > 1995) AND (children.age > 3) GROUP BY fullName, age, gender, citiesLived.place
che restituisce:
+------------+-----+--------+-------------------+ | fullName | age | gender | citiesLived_place | +------------+-----+--------+-------------------+ | John Doe | 22 | Male | Stockholm | | Mike Jones | 35 | Male | Los Angeles | | Mike Jones | 35 | Male | Washington DC | | Mike Jones | 35 | Male | Portland | | Mike Jones | 35 | Male | Austin | +------------+-----+--------+-------------------+
Clausola WITHIN
La parola chiave WITHIN funziona specificamente con le funzioni di aggregazione per eseguire l'aggregazione tra elementi secondari e campi ripetuti all'interno di record e campi nidificati. Quando specifichi la parola chiave WITHIN
, devi specificare l'ambito in base al quale vuoi eseguire l'aggregazione:
WITHIN RECORD: aggrega i dati nei valori ripetuti all'interno del record.WITHIN node_name: aggrega i dati nei valori ripetuti all'interno del nodo specificato, dove un nodo è un nodo padre del campo di aggregazione.
Supponiamo di voler trovare il numero di figli di ciascuna persona nell'esempio precedente. Per farlo, puoi contare il numero di children.name di ogni record:
SELECT fullName, COUNT(children.name) WITHIN RECORD AS numberOfChildren FROM [dataset.tableId];
Ottieni il seguente risultato:
+---------------+------------------+ | fullName | numberOfChildren | +---------------+------------------+ | John Doe | 2 | | Jane Austen | 2 | | Mike Jones | 3 | | Anna Karenina | 0 | +---------------+------------------+
Per fare un confronto, prova a elencare tutti i nomi dei bambini:
SELECT fullName, children.name FROM [dataset.tableId]
+---------------+---------------+ | fullName | children_name | +---------------+---------------+ | John Doe | Jane | | John Doe | John | | Jane Austen | Josh | | Jane Austen | Jim | | Mike Jones | Earl | | Mike Jones | Sam | | Mike Jones | Kit | | Anna Karenina | None | +---------------+---------------+
Ciò corrisponde ai risultati della query WITHIN RECORD. Mario Rossi ha due figli
di nome Jane e John, Jane Austen ha due figli di nome Josh e Jim, Mike Jones ha tre
bambini di nome Earl, Sam e Kit, e Anna Karenina non ha figli.
Supponiamo ora che tu voglia trovare il numero di volte in cui una persona ha vissuto in luoghi diversi.
Puoi utilizzare la clausola WITHIN per eseguire l'aggregazione in un determinato nodo:
SELECT fullName, COUNT(citiesLived.place) WITHIN RECORD AS numberOfPlacesLived, citiesLived.place, COUNT(citiesLived.yearsLived) WITHIN citiesLived AS numberOfTimesInEachCity, FROM [dataset.tableId];
+---------------+---------------------+-------------------+-------------------------+ | fullName | numberOfPlacesLived | citiesLived_place | numberOfTimesInEachCity | +---------------+---------------------+-------------------+-------------------------+ | John Doe | 2 | Seattle | 1 | | John Doe | 2 | Stockholm | 1 | | Mike Jones | 4 | Los Angeles | 4 | | Mike Jones | 4 | Washington DC | 4 | | Mike Jones | 4 | Portland | 4 | | Mike Jones | 4 | Austin | 4 | | Anna Karenina | 3 | Stockholm | 4 | | Anna Karenina | 3 | Moscow | 3 | | Anna Karenina | 3 | Austin | 2 | +---------------+---------------------+-------------------+-------------------------+
Questa query esegue le seguenti operazioni:
- Esegue un
WITHIN RECORDsucitiesLived.placee conteggia il numero di luoghi in cui ha vissuto ogni persona - Esegue un
WITHINsucitiesLived.yearsLivede conteggia il numero di volte in cui ogni persona ha vissuto in ogni città (contando fino acitiesLived).
L'utilizzo dell'aggregazione con ambito in campi nidificati e ripetuti è una delle opzioni molto potenti, che spesso possono eliminare i join costosi nelle query.