Funzioni e operatori SQL legacy
Questo documento descrive le funzioni e gli operatori SQL precedente. L'opzione la sintassi delle query per BigQuery è GoogleSQL. Per informazioni su GoogleSQL, consulta Funzioni e operatori di GoogleSQL.
Funzioni e operatori supportati
La maggior parte delle clausole delle istruzioni SELECT
supporta le funzioni. Campi
a cui viene fatto riferimento in una funzione non devono essere elencati in nessun SELECT
una clausola. Pertanto, la seguente query è valida, anche se
Il campo clicks
non viene visualizzato direttamente:
#legacySQL SELECT country, SUM(clicks) FROM table GROUP BY country;
Funzioni di aggregazione | |
---|---|
AVG() |
Restituisce la media dei valori per un gruppo di righe ... |
BIT_AND() |
Restituisce il risultato di un'operazione AND a livello di bit ... |
BIT_OR() |
Restituisce il risultato di un'operazione OR a livello di bit ... |
BIT_XOR() |
Restituisce il risultato di un'operazione XOR a livello di bit ... |
CORR() |
Restituisce il coefficiente di correlazione di Pearson di un insieme di coppie di numeri. |
COUNT() |
Restituisce il numero totale di valori ... |
COUNT([DISTINCT]) |
Restituisce il numero totale di valori non NULL ... |
COVAR_POP() |
Calcola la covarianza della popolazione dei valori ... |
COVAR_SAMP() |
Calcola la covarianza campione dei valori ... |
EXACT_COUNT_DISTINCT() |
Restituisce il numero esatto di valori distinti non NULL per il campo specificato. |
FIRST() |
Restituisce il primo valore sequenziale nell'ambito della funzione. |
GROUP_CONCAT() |
Concatena più stringhe in un'unica stringa ... |
GROUP_CONCAT_UNQUOTED() |
Concatena più stringhe in un'unica stringa ... senza le virgolette doppie ... |
LAST() |
Restituisce l'ultimo valore sequenziale ... |
MAX() |
Restituisce il valore massimo ... |
MIN() |
Restituisce il valore minimo ... |
NEST() |
Aggrega tutti i valori nell'ambito di aggregazione attuale in un campo ripetuto. |
NTH() |
Restituisce l'ennesimo valore sequenziale ... |
QUANTILES() |
Calcola minimo, massimo e quantili approssimativi ... |
STDDEV() |
Restituisce la deviazione standard ... |
STDDEV_POP() |
Calcola la deviazione standard della popolazione ... |
STDDEV_SAMP() |
Calcola la deviazione standard di un campione ... |
SUM() |
Restituisce la somma totale dei valori ... |
TOP() ... COUNT(*) |
Restituisce i primi max_records in base alla frequenza. |
UNIQUE() |
Restituisce l'insieme di valori univoci non NULL ... |
VARIANCE() |
Calcola la varianza dei valori ... |
VAR_POP() |
Calcola la varianza della popolazione dei valori ... |
VAR_SAMP() |
Calcola la varianza campionaria dei valori ... |
Operatori aritmetici | |
---|---|
+ |
Addizione |
- |
Sottrazione |
* |
Moltiplicazione |
/ |
Divisione |
% |
Modulo |
Funzioni bit per bit | |
---|---|
& |
AND a livello di bit |
| |
O a livello di bit |
^ |
XOR a livello di bit |
<< |
Spostamento a sinistra a livello di bit |
>> |
Spostamento a destra a livello di bit |
~ |
NON a livello di bit |
BIT_COUNT() |
Restituisce il numero di bit ... |
Funzioni di trasmissione | |
---|---|
BOOLEAN() |
Trasmetti al valore booleano. |
BYTES() |
Trasmetti ai byte. |
CAST(expr AS type) |
Converte expr in una variabile di tipo type . |
FLOAT() |
Trasmetti per raddoppiare. |
HEX_STRING() |
Trasmetti alla stringa esadecimale. |
INTEGER() |
Trasmetti a numero intero. |
STRING() |
Trasmetti alla stringa. |
Funzioni di confronto | |
---|---|
expr1 = expr2 |
Restituisce true se le espressioni sono uguali. |
expr1 != expr2 expr1 <> expr2
|
Restituisce true se le espressioni non sono uguali. |
expr1 > expr2 |
Restituisce true se expr1 è maggiore di expr2 . |
expr1 < expr2 |
Restituisce true se expr1 è inferiore a expr2 . |
expr1 >= expr2 |
Restituisce true se expr1 è maggiore di o uguale a expr2 . |
expr1 <= expr2 |
Restituisce true se expr1 è minore o uguale a expr2 . |
expr1 BETWEEN expr2 AND expr3 |
Restituisce true se il valore di expr1
è compreso tra expr2 e expr3 inclusi. |
expr IS NULL |
Restituisce true se expr è NULL. |
expr IN() |
Restituisce true se expr corrisponde
expr1 , expr2 o qualsiasi valore tra parentesi. |
COALESCE() |
Restituisce il primo argomento che non è NULL. |
GREATEST() |
Restituisce il parametro numeric_expr più grande. |
IFNULL() |
Se l'argomento non è nullo, restituisce l'argomento. |
IS_INF() |
Restituisce true se l'infinito positivo o negativo. |
IS_NAN() |
Restituisce true se l'argomento è NaN . |
IS_EXPLICITLY_DEFINED() |
deprecato: utilizza expr IS NOT NULL . |
LEAST() |
Restituisce l'argomento numeric_expr più piccolo parametro. |
NVL() |
Se expr non è null, restituisce expr , altrimenti restituisce null_default . |
Funzioni di data e ora | |
---|---|
CURRENT_DATE() |
Restituisce la data corrente nel formato %Y-%m-%d . |
CURRENT_TIME() |
Restituisce l'ora attuale del server nel formato %H:%M:%S . |
CURRENT_TIMESTAMP() |
Restituisce l'ora attuale del server nel formato %Y-%m-%d %H:%M:%S . |
DATE() |
Restituisce la data nel formato %Y-%m-%d . |
DATE_ADD() |
Aggiunge l'intervallo specificato a un tipo di dati TIMESTAMP. |
DATEDIFF() |
Restituisce il numero di giorni tra due tipi di dati TIMESTAMP. |
DAY() |
Restituisce il giorno del mese come numero intero compreso tra 1 e 31. |
DAYOFWEEK() |
Restituisce il giorno della settimana come un numero intero compreso tra 1 (domenica) e 7 (sabato). |
DAYOFYEAR() |
Restituisce il giorno dell'anno come numero intero compreso tra 1 e 366. |
FORMAT_UTC_USEC() |
Restituisce un timestamp UNIX nel formato YYYY-MM-DD HH:MM:SS.uuuuuu . |
HOUR() |
Restituisce l'ora di un TIMESTAMP come un numero intero compreso tra 0 e 23. |
MINUTE() |
Restituisce i minuti di un TIMESTAMP come un numero intero compreso tra 0 e 59. |
MONTH() |
Restituisce il mese di un TIMESTAMP come un numero intero compreso tra 1 e 12. |
MSEC_TO_TIMESTAMP() |
Converte un timestamp UNIX in millisecondi in TIMESTAMP. |
NOW() |
Restituisce il timestamp UNIX corrente in microsecondi. |
PARSE_UTC_USEC() |
Converte una stringa data in un timestamp UNIX in microsecondi. |
QUARTER() |
Restituisce il trimestre dell'anno di un TIMESTAMP come un numero intero compreso tra 1 e 4. |
SEC_TO_TIMESTAMP() |
Converte un timestamp UNIX in secondi in un TIMESTAMP. |
SECOND() |
Restituisce i secondi di un TIMESTAMP come un numero intero compreso tra 0 e 59. |
STRFTIME_UTC_USEC() |
Restituisce una stringa per la data nel formato date_format_str. |
TIME() |
Restituisce un valore TIMESTAMP nel formato %H:%M:%S . |
TIMESTAMP() |
Converti una stringa data in un TIMESTAMP. |
TIMESTAMP_TO_MSEC() |
Converte un TIMESTAMP in un timestamp UNIX in millisecondi. |
TIMESTAMP_TO_SEC() |
Converte un TIMESTAMP in un timestamp UNIX in secondi. |
TIMESTAMP_TO_USEC() |
Converte un TIMESTAMP in un timestamp UNIX in microsecondi. |
USEC_TO_TIMESTAMP() |
Converte un timestamp UNIX in microsecondi in un TIMESTAMP. |
UTC_USEC_TO_DAY() |
Sposta un timestamp UNIX in microsecondi all'inizio del giorno in cui si verifica. |
UTC_USEC_TO_HOUR() |
Sposta un timestamp UNIX in microsecondi all'inizio dell'ora in cui si verifica. |
UTC_USEC_TO_MONTH() |
Sposta un timestamp UNIX in microsecondi all'inizio del mese in cui compare. |
UTC_USEC_TO_WEEK() |
Restituisce un timestamp UNIX in microsecondi che rappresenta un giorno della settimana. |
UTC_USEC_TO_YEAR() |
Restituisce un timestamp UNIX in microsecondi che rappresenta l'anno. |
WEEK() |
Restituisce la settimana di un TIMESTAMP come un numero intero compreso tra 1 e 53. |
YEAR() |
Restituisce l'anno di un TIMESTAMP. |
Funzioni IP | |
---|---|
FORMAT_IP() |
Converte 32 bit meno significativi di integer_value in stringa di indirizzi IPv4 leggibile. |
PARSE_IP() |
Converte una stringa che rappresenta un indirizzo IPv4 in un valore intero senza segno. |
FORMAT_PACKED_IP() |
Restituisce un indirizzo IP leggibile nel modulo
10.1.5.23 o 2620:0:1009:1:216:36ff:feef:3f . |
PARSE_PACKED_IP() |
Restituisce un indirizzo IP in BYTES. |
Funzioni JSON | |
---|---|
JSON_EXTRACT() |
Seleziona un valore in base all'espressione JSONPath e restituisce una stringa JSON. |
JSON_EXTRACT_SCALAR() |
Seleziona un valore in base all'espressione JSONPath e restituisce un valore scalare JSON. |
Operatori logici | |
---|---|
expr AND expr |
Restituisce true se entrambe le espressioni sono vere. |
expr OR expr |
Restituisce true se una o entrambe le espressioni sono vere. |
NOT expr |
Restituisce true se l'espressione è falsa. |
Funzioni matematiche | |
---|---|
ABS() |
Restituisce il valore assoluto dell'argomento. |
ACOS() |
Restituisce il coseno dell'arco dell'argomento. |
ACOSH() |
Restituisce il coseno iperbolico dell'arco dell'argomento. |
ASIN() |
Restituisce l'arcoseno dell'argomento. |
ASINH() |
Restituisce il seno iperbolico ad arco dell'argomento. |
ATAN() |
Restituisce l'arcotangente dell'argomento. |
ATANH() |
Restituisce la tangente iperbolica dell'arco dell'argomento. |
ATAN2() |
Restituisce l'arcotangente dei due argomenti. |
CEIL() |
Arrotonda l'argomento al numero intero più vicino e restituisce il valore arrotondato. |
COS() |
Restituisce il coseno dell'argomento. |
COSH() |
Restituisce il coseno iperbolico dell'argomento. |
DEGREES() |
Converte da radianti a gradi. |
EXP() |
Restituisce e alla potenza dell'argomento. |
FLOOR() |
Arrotonda l'argomento per difetto al numero intero più vicino. |
LN() LOG()
|
Restituisce il logaritmo naturale dell'argomento. |
LOG2() |
Restituisce il logaritmo in Base-2 dell'argomento. |
LOG10() |
Restituisce il logaritmo in Base-10 dell'argomento. |
PI() |
Restituisce la costante p. |
POW() |
Restituisce il primo argomento alla potenza del secondo argomento. |
RADIANS() |
Converte da gradi a radianti. |
RAND() |
Restituisce un valore in virgola mobile casuale nell'intervallo 0,0 <= valore < 1,0. |
ROUND() |
Arrotonda l'argomento per eccesso o per difetto al numero intero più vicino. |
SIN() |
Restituisce il seno dell'argomento. |
SINH() |
Restituisce il seno iperbolico dell'argomento. |
SQRT() |
Restituisce la radice quadrata dell'espressione. |
TAN() |
Restituisce la tangente dell'argomento. |
TANH() |
Restituisce la tangente iperbolica dell'argomento. |
Funzioni basate su espressioni regolari | |
---|---|
REGEXP_MATCH() |
Restituisce true se l'argomento corrisponde all'espressione regolare. |
REGEXP_EXTRACT() |
Restituisce la parte dell'argomento che corrisponde al gruppo di acquisizione nell'espressione regolare. |
REGEXP_REPLACE() |
Sostituisce una sottostringa che corrisponde a un'espressione regolare. |
Funzioni di stringa | |
---|---|
CONCAT() |
Restituisce la concatenazione di due o più stringhe oppure NULL se uno dei valori è NULL. |
expr CONTAINS 'str' |
Restituisce true se expr contiene l'argomento stringa specificato. |
INSTR() |
Restituisce l'indice in base uno della prima occorrenza di una stringa. |
LEFT() |
Restituisce i caratteri più a sinistra di una stringa. |
LENGTH() |
Restituisce la lunghezza della stringa. |
LOWER() |
Restituisce la stringa originale con tutti i caratteri in minuscolo. |
LPAD() |
Inserisce caratteri a sinistra di una stringa. |
LTRIM() |
Rimuove i caratteri dal lato sinistro di una stringa. |
REPLACE() |
Sostituisce tutte le occorrenze di una sottostringa. |
RIGHT() |
Restituisce i caratteri più a destra di una stringa. |
RPAD() |
Inserisce caratteri a destra di una stringa. |
RTRIM() |
Rimuove i caratteri finali dal lato destro di una stringa. |
SPLIT() |
Divide una stringa in sottostringhe ripetute. |
SUBSTR() |
Restituisce una sottostringa ... |
UPPER() |
Restituisce la stringa originale con tutti i caratteri in maiuscolo. |
Funzioni con caratteri jolly della tabella | |
---|---|
TABLE_DATE_RANGE() |
Esegue query su più tabelle giornaliere che coprono un intervallo di date. |
TABLE_DATE_RANGE_STRICT() |
Esegue query su più tabelle giornaliere che coprono un intervallo di date, senza date mancanti. |
TABLE_QUERY() |
Tabelle di query i cui nomi corrispondono a un predicato specificato. |
Funzioni URL | |
---|---|
HOST() |
Fornito un URL, restituisce il nome host come stringa. |
DOMAIN() |
Dato un URL, restituisce il dominio come stringa. |
TLD() |
Specificato un URL, restituisce il dominio di primo livello più qualsiasi dominio del paese nell'URL. |
Funzioni finestra | |
---|---|
AVG() COUNT(*) COUNT([DISTINCT]) MAX() MIN() STDDEV() SUM() |
La stessa operazione Funzioni di aggregazione, ma che vengono calcolate su una finestra definita dalla clausola OVER. |
CUME_DIST() |
Restituisce un doppio che indica la distribuzione cumulativa di un valore in un gruppo di valori ... |
DENSE_RANK() |
Restituisce il rango intero di un valore in un gruppo di valori. |
FIRST_VALUE() |
Restituisce il primo valore del campo specificato nella finestra. |
LAG() |
Consente di leggere i dati di una riga precedente all'interno di una finestra. |
LAST_VALUE() |
Restituisce l'ultimo valore del campo specificato nella finestra. |
LEAD() |
Consente di leggere i dati di una riga successiva all'interno di una finestra. |
NTH_VALUE() |
Restituisce il valore di <expr> nella posizione
<n> del telaio della finestra ...
|
NTILE() |
Divide la finestra nel numero specificato di bucket. |
PERCENT_RANK() |
Restituisce la posizione della riga corrente rispetto alle altre righe della partizione. |
PERCENTILE_CONT() |
Restituisce un valore interpolato che verrebbe mappato all'argomento percentile rispetto alla finestra ... |
PERCENTILE_DISC() |
Restituisce il valore più vicino al percentile dell'argomento nella finestra. |
RANK() |
Restituisce il rango intero di un valore in un gruppo di valori. |
RATIO_TO_REPORT() |
Restituisce il rapporto di ciascun valore alla somma dei valori. |
ROW_NUMBER() |
Restituisce il numero di riga corrente del risultato della query nella finestra. |
Altre funzioni | |
---|---|
CASE WHEN ... THEN |
Utilizza CASE per scegliere tra due o più espressioni alternative nella query. |
CURRENT_USER() |
Restituisce l'indirizzo email dell'utente che esegue la query. |
EVERY() |
Restituisce true se l'argomento è vero per tutti i suoi input. |
FROM_BASE64() |
Converte la stringa di input codificata in base64 nel formato BYTES. |
HASH() |
Calcola e restituisce un valore hash firmato a 64 bit ... |
FARM_FINGERPRINT() |
Calcola e restituisce un valore di impronta firmato a 64 bit ... |
IF() |
Se il primo argomento è vero, restituisce il secondo argomento; altrimenti restituisce il terzo argomento. |
POSITION() |
Restituisce la posizione sequenziale su base uno dell'argomento. |
SHA1() |
Restituisce un hash SHA1 in formato BYTES. |
SOME() |
Restituisce true se l'argomento è vero per almeno uno dei suoi input. |
TO_BASE64() |
Converte l'argomento BYTES in una stringa codificata in base-64. |
Sintassi delle query
Nota: le parole chiave non sono sensibili alle maiuscole. In questo documento, parole chiave come
in quanto SELECT
è in maiuscolo a scopo illustrativo.
Clausola SELECT
La clausola SELECT
specifica un elenco di espressioni da calcolare. Le espressioni nella
La clausola SELECT
può contenere nomi di campo, valori letterali e
chiamate di funzione (incluse le funzioni aggregate)
e le funzioni finestra), nonché combinazioni dei tre. La
l'elenco di espressioni è separato da virgole.
A ogni espressione può essere assegnato un alias aggiungendo uno spazio seguito da un identificatore dopo il
un'espressione di base. È possibile aggiungere la parola chiave facoltativa AS
tra l'espressione e l'alias
per una migliore leggibilità. È possibile fare riferimento agli alias definiti in una clausola SELECT
nella sezione
clausole GROUP BY
, HAVING
e ORDER BY
della query, ma
non dalle clausole FROM
, WHERE
o OMIT RECORD IF
né per
nella stessa clausola SELECT
.
Note:
-
Se utilizzi una funzione di aggregazione in
SELECT
devi utilizzare una funzione aggregata in tutte le espressioni oppure la query deve avere una clausola ClausolaGROUP BY
che include tutti i campi non aggregati della clausolaSELECT
come chiavi di raggruppamento. Ad esempio:#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word, corpus; /* Succeeds because all non-aggregated fields are group keys. */
#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word; /* Fails because corpus is not aggregated nor is it a group key. */
-
Puoi utilizzare le parentesi quadre per l'interpretazione letterale
parole riservate
in modo da poterli utilizzare come nome campo e alias. Ad esempio, se hai una colonna denominata
"partition", una parola riservata nella sintassi BigQuery, le query che fanno riferimento
questo campo restituisce messaggi di errore invisibili, a meno che non venga inserito un carattere di escape con le parentesi quadre:
SELECT [partition] FROM ...
Esempio
Questo esempio definisce gli alias nella clausola SELECT
e fa riferimento a uno di questi nella
la clausola ORDER BY
. Tieni presente che non è possibile fare riferimento alla colonna word utilizzando
word_alias nella clausola WHERE
; a cui si deve fare riferimento
per nome. La
Inoltre, l'alias len non è visibile nella clausola WHERE
. Sarebbe visibile a un
HAVING
.
#legacySQL SELECT word AS word_alias, LENGTH(word) AS len FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS 'th' ORDER BY len;
Modificatore WITHIN per le funzioni aggregate
aggregate_function WITHIN RECORD [ [ AS ] alias ]
La parola chiave WITHIN
determina l'aggregazione della funzione aggregata tra valori ripetuti
all'interno di ogni record. Per ogni record di input, verrà generato esattamente un output aggregato. Questo
il tipo di aggregazione è detto aggregazione con ambito. Poiché l'aggregazione con ambito
produce un output per ogni record, le espressioni non aggregate possono essere selezionate
espressioni aggregate con ambito senza utilizzare una clausola GROUP BY
.
Quando usi l'aggregazione con ambito, utilizzerai più comunemente l'ambito RECORD
. Se
hanno uno schema nidificato e ripetuto molto complesso, potrebbe essere necessario eseguire aggregazioni
degli ambiti dei sottorecord. Per farlo, sostituisci la parola chiave RECORD
nella sintassi
riportato sopra con il nome del nodo dello schema in cui vuoi che venga eseguita l'aggregazione.
Per ulteriori informazioni sul comportamento avanzato, consulta
Gestione dei dati.
Esempio
Questo esempio esegue un'aggregazione COUNT
con ambito, quindi filtra e ordina
record in base al valore aggregato.
#legacySQL SELECT repository.url, COUNT(payload.pages.page_name) WITHIN RECORD AS page_count FROM [bigquery-public-data:samples.github_nested] HAVING page_count > 80 ORDER BY page_count DESC;
Clausola FROM
FROM [project_name:]datasetId.tableId [ [ AS ] alias ] | (subquery) [ [ AS ] alias ] |JOIN
clause |FLATTEN
clause | table wildcard function
La clausola FROM
specifica i dati di origine su cui eseguire query. Query BigQuery
può eseguire direttamente su tabelle, sottoquery, su tabelle unite e su tabelle modificate da
operatori specifici descritti di seguito. È possibile eseguire query su combinazioni di queste origini dati utilizzando
la comma, che è l'operatore UNION ALL
nella
in BigQuery.
Fare riferimento alle tabelle
Quando si fa riferimento a una tabella, è necessario specificare sia datasetId sia tableId. Il campo project_name è facoltativo. Se project_name non è specificato, BigQuery per impostazione predefinita il progetto attuale. Se il nome del progetto include un trattino, devi racchiudere l'intero testo riferimento a tabella tra parentesi.
Esempio
[my-dashed-project:dataset1.tableName]
Per assegnare un alias alle tabelle, puoi aggiungere uno spazio seguito da un identificatore dopo il nome della tabella. La
la parola chiave facoltativa AS
può essere aggiunta tra tableId e l'alias di
una migliore leggibilità.
Quando fai riferimento alle colonne di una tabella, puoi utilizzare il nome della colonna semplice oppure aggiungere il prefisso nome colonna con l'alias, se ne hai specificato uno, oppure con datasetId e tableId purché non sia stato specificato alcun project_name. Il project_name non possono essere inclusi nel prefisso di colonna perché i due punti non sono consentiti nei nomi dei campi.
Esempi
Questo esempio fa riferimento a una colonna senza prefisso di tabella.
#legacySQL SELECT word FROM [bigquery-public-data:samples.shakespeare];
In questo esempio, al nome della colonna vengono inseriti il prefisso datasetId e tableId. Avvisi che il valore di project_name non può essere incluso in questo esempio. Questo metodo funziona solo se il set di dati si trova nel progetto predefinito attuale.
#legacySQL SELECT samples.shakespeare.word FROM samples.shakespeare;
In questo esempio, il nome della colonna viene preceduto da un alias di tabella.
#legacySQL SELECT t.word FROM [bigquery-public-data:samples.shakespeare] AS t;
Utilizzo delle sottoquery
Una sottoquery è un'istruzione SELECT
nidificata racchiusa tra parentesi. La
le espressioni calcolate nella clausola SELECT
della sottoquery sono disponibili
proprio come sarebbero disponibili le colonne di una tabella.
Le sottoquery possono essere utilizzate per calcolare aggregazioni e altre espressioni. L'intera gamma di strumenti SQL sono disponibili nella sottoquery. Ciò significa che una sottoquery può a sua volta contenere altri possono eseguire join, raggruppare aggregazioni e così via.
Virgola come UNION ALL
A differenza di GoogleSQL, SQL precedente utilizza la virgola come operatore UNION ALL
anziché
rispetto a un operatore CROSS JOIN
. Si tratta di un comportamento legacy che si è evoluto perché
in passato BigQuery non supportava CROSS JOIN
e gli utenti BigQuery dovevano regolarmente scrivere
UNION ALL
query. In GoogleSQL, le query che eseguono unioni sono particolarmente
dettagliato. L'utilizzo della virgola come operatore di unione consente di scrivere queste query in modo molto più efficace.
in modo efficiente. Ad esempio, questa query può essere utilizzata per eseguire una singola query sui log di più
giorni.
#legacySQL SELECT FORMAT_UTC_USEC(event.timestamp_in_usec) AS time, request_url FROM [applogs.events_20120501], [applogs.events_20120502], [applogs.events_20120503] WHERE event.username = 'root' AND NOT event.source_ip.is_internal;
Query che uniscono un numero elevato di tabelle in genere vengono eseguite più lentamente rispetto a quelle che elaborano la stessa quantità di dati da una singola tabella. La differenza di prestazioni può arrivare a 50 ms per tabella aggiuntiva. Una singola query può unire al massimo 1000 tabelle.
Funzioni con caratteri jolly della tabella
Il termine funzione carattere jolly di tabella si riferisce a un tipo speciale di funzione univoco di BigQuery.
Queste funzioni vengono utilizzate nella clausola FROM
per trovare una corrispondenza con una raccolta di nomi tabella
con uno dei vari tipi di filtri disponibili. Ad esempio, la funzione TABLE_DATE_RANGE
può essere utilizzato per eseguire query solo su un insieme specifico di tabelle giornaliere. Per ulteriori informazioni su queste funzioni,
consulta Funzioni con caratteri jolly nella tabella.
Operatore FLATTEN
(FLATTEN([project_name:]datasetId.tableId, field_to_be_flattened)) (FLATTEN((subquery), field_to_be_flattened))
A differenza dei tipici sistemi di elaborazione SQL, BigQuery è progettato per gestire dati ripetuti. A causa di
Gli utenti di BigQuery a volte devono scrivere query che manipolano la struttura di risposte
record. Un modo per farlo è utilizzare l'operatore FLATTEN
.
FLATTEN
converte un nodo nello schema da ripetuto a facoltativo. Data/ora record
con uno o più valori per un campo ripetuto, FLATTEN
creerà più record,
una per ogni valore nel campo ripetuto. Tutti gli altri campi selezionati dal record sono duplicati
in ogni nuovo record di output. FLATTEN
può essere applicato ripetutamente per rimuovere
più livelli di ripetizione.
Per ulteriori informazioni ed esempi, vedi Gestione dei dati.
Operatore JOIN
BigQuery supporta più operatori JOIN
in ogni clausola FROM
.
Le operazioni JOIN
successive utilizzano i risultati dell'oggetto JOIN
precedente
come input JOIN
sinistro. Campi di qualsiasi input JOIN
precedente
possono essere utilizzate come chiavi nelle clausole ON
degli operatori JOIN
successivi.
Tipi JOIN
BigQuery supporta INNER
, [FULL|RIGHT|LEFT] OUTER
e
Operazioni CROSS JOIN
. Se non viene specificato, il valore predefinito è INNER
.
Le operazioni CROSS JOIN
non consentono clausole ON
. CROSS JOIN
può restituire una grande quantità di dati e potrebbe generare una query lenta e inefficiente o
che supera il numero massimo consentito di risorse per query. Queste query avranno esito negativo e restituiranno un errore. Quando
possibile, preferisci le query che non utilizzano CROSS JOIN
. Ad esempio, CROSS JOIN
viene spesso utilizzato in posizioni in cui funzioni finestra
essere più efficienti.
CIASCUNO modificatore
Il modificatore EACH
è un suggerimento che indica a BigQuery di eseguire JOIN
usando più partizioni. Ciò è particolarmente utile quando si sa che entrambi i lati della
JOIN
sono grandi. Il modificatore EACH
non può essere utilizzato in
clausole CROSS JOIN
.
In molti casi, EACH
in passato veniva incoraggiato, ma ora non è più così. Quando
possibile, utilizza JOIN
senza il modificatore EACH
per migliorare il rendimento.
Utilizza JOIN EACH
quando la query non è riuscita a causa di un messaggio di errore relativo al superamento delle risorse.
Semi-join e Anti-join
Oltre a supportare JOIN
nella clausola FROM
, BigQuery
supporta due tipi di join nella clausola WHERE
: semi-join e anti-semi-join. R
semi-join viene specificato utilizzando la parola chiave IN
con una sottoquery; anti-join, usando
NOT IN
parole chiave.
Esempi
La seguente query utilizza un semi-join per trovare gli ngram in cui anche la prima parola dell'ngram è la seconda parola in un altro ngram che ha "AND" come terza parola nell'ngram.
#legacySQL SELECT ngram FROM [bigquery-public-data:samples.trigrams] WHERE first IN (SELECT second FROM [bigquery-public-data:samples.trigrams] WHERE third = "AND") LIMIT 10;
La seguente query utilizza un semi-join per restituire il numero di donne di età superiore ai 50 anni che hanno partorito nel i 10 stati con il maggior numero di nascite.
#legacySQL SELECT mother_age, COUNT(mother_age) total FROM [bigquery-public-data:samples.natality] WHERE state IN (SELECT state FROM (SELECT state, COUNT(state) total FROM [bigquery-public-data:samples.natality] GROUP BY state ORDER BY total DESC LIMIT 10)) AND mother_age > 50 GROUP BY mother_age ORDER BY mother_age DESC
Per visualizzare i numeri degli altri 40 stati, puoi utilizzare un anti-join. La seguente query è
quasi identico all'esempio precedente, ma utilizza NOT IN
anziché IN
per restituire il numero di donne oltre i 50 anni che hanno partorito nei 40 stati con il minor numero di parti.
#legacySQL SELECT mother_age, COUNT(mother_age) total FROM [bigquery-public-data:samples.natality] WHERE state NOT IN (SELECT state FROM (SELECT state, COUNT(state) total FROM [bigquery-public-data:samples.natality] GROUP BY state ORDER BY total DESC LIMIT 10)) AND mother_age > 50 GROUP BY mother_age ORDER BY mother_age DESC
Note:
- BigQuery non supporta i semi- o anti-semi-join correlati. La sottoquery non può fare riferimento qualsiasi campo della query esterna.
- La sottoquery utilizzata in un semi- o anti-semi-join deve selezionare esattamente un campo.
-
I tipi del campo selezionato e del campo utilizzato dalla query esterna nella clausola
WHERE
devono corrispondere esattamente. BigQuery non esegue alcuna coercizione di tipo semi- o i semi-join.
Clausola WHERE
La clausola WHERE
, a volte chiamata predicato, filtra i record prodotti
FROM
utilizzando un'espressione booleana. È possibile unire più condizioni tramite i valori booleani
Clausole AND
e OR
, facoltativamente racchiuse tra parentesi—()—
per raggrupparli. I campi elencati in una clausola WHERE
non devono essere selezionati nella
la clausola SELECT
corrispondente e l'espressione della clausola WHERE
non possono
espressioni di riferimento calcolate nella clausola SELECT
della query a cui
La clausola WHERE
appartiene.
Nota: le funzioni di aggregazione non possono essere utilizzate nella clausola WHERE
. Utilizza un
HAVING
e una query esterna se devi applicare un filtro in base al
l'output di una funzione aggregata.
Esempio
L'esempio seguente utilizza una disgiunzione delle espressioni booleane in WHERE
una clausola: le due espressioni unite da un operatore OR
. Un record di input viene superato
attraverso il filtro WHERE
se una delle espressioni restituisce true
.
#legacySQL SELECT word FROM [bigquery-public-data:samples.shakespeare] WHERE (word CONTAINS 'prais' AND word CONTAINS 'ing') OR (word CONTAINS 'laugh' AND word CONTAINS 'ed');
Clausola OMIT RECORD IF
La clausola OMIT RECORD IF
è un costrutto univoco di BigQuery. È
particolarmente utile per
gestire schemi nidificati e ripetuti. È simile a una clausola WHERE
, ma differisce per due importanti aspetti. Innanzitutto, utilizza una condizione di esclusione,
il che significa che i record vengono omessi se l'espressione restituisce true
, ma vengono conservati se
l'espressione restituisce false
o null
. In secondo luogo, la clausola OMIT RECORD IF
può utilizzare (e di solito la fa) utilizzare funzioni aggregate con ambito nella sua condizione.
Oltre a filtrare i record completi, OMIT...IF
può specificare un ambito più limitato
per filtrare solo parti di un record. A questo scopo, utilizza il nome di un nodo non foglia nella
anziché RECORD
nella clausola OMIT...IF
. Questa funzionalità
viene usato raramente dagli utenti di BigQuery. Puoi trovare ulteriore documentazione su questo comportamento avanzato
il link è disponibile nella documentazione WITHIN
riportata sopra.
Se utilizzi OMIT...IF
per escludere una parte di un record in un campo ripetuto, mentre la query
seleziona altri campi che si ripetono in modo indipendente, BigQuery omette un
degli altri record ripetuti nella query. Se viene visualizzato l'errore
Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>,
ti consigliamo di passare a GoogleSQL. Per informazioni sulla migrazione
istruzioni OMIT...IF
per GoogleSQL, consulta
Migrazione
a GoogleSQL.
Esempio
Facendo riferimento all'esempio utilizzato per il modificatore WITHIN
, OMIT RECORD IF
può essere utilizzato per ottenere lo stesso risultato con WITHIN
e HAVING
usato in quell'esempio.
#legacySQL SELECT repository.url FROM [bigquery-public-data:samples.github_nested] OMIT RECORD IF COUNT(payload.pages.page_name) <= 80;
Clausola GROUP BY
La clausola GROUP BY
consente di raggruppare le righe che hanno gli stessi valori per un determinato
campo o un insieme di campi in modo da poter calcolare le aggregazioni dei campi correlati. Il raggruppamento avviene
dopo il filtro eseguito nella clausola WHERE
ma prima delle espressioni nella
Viene calcolata la clausola SELECT
. I risultati dell'espressione non possono essere utilizzati come chiavi di gruppo
la clausola GROUP BY
.
Esempio
Questa query trova le prime dieci prime parole più comuni nel set di dati di esempio dei trigrammi.
Oltre a dimostrare l'utilizzo della clausola GROUP BY
, dimostra come
è possibile utilizzare indici posizionali al posto dei nomi dei campi in GROUP BY
e
clausole ORDER BY
.
#legacySQL SELECT first, COUNT(ngram) FROM [bigquery-public-data:samples.trigrams] GROUP BY 1 ORDER BY 2 DESC LIMIT 10;
L'aggregazione eseguita utilizzando una clausola GROUP BY
è chiamata aggregazione raggruppata
. A differenza dell'aggregazione con ambito, l'aggregazione raggruppata è
comuni nella maggior parte dei sistemi di elaborazione SQL.
Il modificatore EACH
Il modificatore EACH
è un suggerimento che indica a BigQuery di eseguire GROUP BY
usando più partizioni. Ciò è particolarmente utile quando sai che il set di dati contiene un
un numero elevato di valori distinti per le chiavi di gruppo.
In molti casi, EACH
in passato veniva incoraggiato, ma ora non è più così.
L'utilizzo di GROUP BY
senza il modificatore di EACH
solitamente garantisce un rendimento migliore.
Utilizza GROUP EACH BY
quando la query non è riuscita a causa di un messaggio di errore relativo al superamento delle risorse.
La funzione ROLLUP
Quando viene utilizzata la funzione ROLLUP
, BigQuery aggiunge al risultato della query ulteriori righe che
rappresentano le aggregazioni raggruppate. Tutti i campi elencati dopo ROLLUP
devono essere
racchiuse tra un'unica serie di parentesi. In righe aggiunte a causa di ROLLUP
, NULL
indica le colonne per le quali l'aggregazione è raggruppata.
Esempio
Questa query genera il numero annuo delle nascite di uomini e donne dal set di dati campione sulla natalità.
#legacySQL SELECT year, is_male, COUNT(1) as count FROM [bigquery-public-data:samples.natality] WHERE year >= 2000 AND year <= 2002 GROUP BY ROLLUP(year, is_male) ORDER BY year, is_male;
Questi sono i risultati della query. Nota che sono presenti righe in cui una o entrambe le chiavi di gruppo
sono NULL
. Queste righe sono le righe di aggregazione.
+------+---------+----------+ | year | is_male | count | +------+---------+----------+ | NULL | NULL | 12122730 | | 2000 | NULL | 4063823 | | 2000 | false | 1984255 | | 2000 | true | 2079568 | | 2001 | NULL | 4031531 | | 2001 | false | 1970770 | | 2001 | true | 2060761 | | 2002 | NULL | 4027376 | | 2002 | false | 1966519 | | 2002 | true | 2060857 | +------+---------+----------+
Quando utilizzi la funzione ROLLUP
, puoi usare la funzione GROUPING
per distinguere le righe aggiunte per via della funzione ROLLUP
e le righe
che hanno in realtà un valore NULL
per la chiave di gruppo.
Esempio
Questa query aggiunge la funzione GROUPING
all'esempio precedente per identificare meglio
righe aggiunte a causa della funzione ROLLUP
.
#legacySQL SELECT year, GROUPING(year) as rollup_year, is_male, GROUPING(is_male) as rollup_gender, COUNT(1) as count FROM [bigquery-public-data:samples.natality] WHERE year >= 2000 AND year <= 2002 GROUP BY ROLLUP(year, is_male) ORDER BY year, is_male;
Questi sono i risultati restituiti dalla nuova query.
+------+-------------+---------+---------------+----------+ | year | rollup_year | is_male | rollup_gender | count | +------+-------------+---------+---------------+----------+ | NULL | 1 | NULL | 1 | 12122730 | | 2000 | 0 | NULL | 1 | 4063823 | | 2000 | 0 | false | 0 | 1984255 | | 2000 | 0 | true | 0 | 2079568 | | 2001 | 0 | NULL | 1 | 4031531 | | 2001 | 0 | false | 0 | 1970770 | | 2001 | 0 | true | 0 | 2060761 | | 2002 | 0 | NULL | 1 | 4027376 | | 2002 | 0 | false | 0 | 1966519 | | 2002 | 0 | true | 0 | 2060857 | +------+-------------+---------+---------------+----------+
Note:
-
I campi non aggregati della clausola
SELECT
devono essere elencati nella ClausolaGROUP BY
.#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word, corpus; /* Succeeds because all non-aggregated fields are group keys. */
#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word; /* Fails because corpus is not aggregated nor is it a group key. */
-
Le espressioni calcolate nella clausola
SELECT
non possono essere utilizzate nella clausolaGROUP BY
corrispondente.#legacySQL SELECT word, corpus, COUNT(word) word_count FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word, corpus, word_count; /* Fails because word_count is not visible to this
GROUP BY
clause. */ - Il raggruppamento per valori in virgola mobile o doppio non è supportato perché la funzione di uguaglianza per questi valori non è ben definito.
-
Poiché il sistema è interattivo, le query che producono un numero elevato di gruppi potrebbero non riuscire. La
utilizzo della funzione
TOP
anzichéGROUP BY
potrebbe risolvere alcuni problemi di scalabilità.
Clausola HAVING
La clausola HAVING
si comporta esattamente come WHERE
a condizione che venga valutata dopo la clausola SELECT
, pertanto i risultati di tutte
le espressioni calcolate sono visibili nella clausola HAVING
. La clausola HAVING può
fai riferimento agli output della clausola SELECT
corrispondente.
Esempio
Questa query calcola le prime parole più comuni nel set di dati campione ngram che contengono la lettera a e si ripetono al massimo 10.000 volte.
#legacySQL SELECT first, COUNT(ngram) ngram_count FROM [bigquery-public-data:samples.trigrams] GROUP BY 1 HAVING first contains "a" AND ngram_count < 10000 ORDER BY 2 DESC LIMIT 10;
Clausola ORDER BY
La clausola ORDER BY
ordina i risultati di una query in ordine crescente o decrescente.
utilizzando uno o più campi chiave. Per ordinare in base a più campi o alias, inseriscili come
elenco separato da virgole. I risultati vengono ordinati in base ai campi nell'ordine in cui sono elencati.
Utilizza DESC
(ordine decrescente) o ASC
(crescente) per specificare la direzione di ordinamento.
ASC
è l'impostazione predefinita. È possibile specificare una direzione di ordinamento diversa per ogni chiave di ordinamento.
La clausola ORDER BY
viene valutata dopo la clausola SELECT
per poter
fa riferimento all'output di qualsiasi espressione calcolata in SELECT
. Se un campo viene fornito
un alias nella clausola SELECT
, l'alias deve essere utilizzato nella clausola ORDER BY
una clausola.
Clausola LIMIT
La clausola LIMIT
limita il numero di righe nel set di risultati restituito. Poiché BigQuery
operano regolarmente su un numero molto elevato di righe, LIMIT
è un buon modo per
evitare query a lunga esecuzione elaborando solo un sottoinsieme di righe.
Note:
-
La clausola
LIMIT
interromperà l'elaborazione e restituirà i risultati una volta soddisfatto il tuo i tuoi requisiti. In questo modo è possibile ridurre i tempi di elaborazione per alcune query, ma quando specifichi l'aggregazione come le clausole COUNT oORDER BY
, l'intero set di risultati deve comunque essere elaborati prima di restituire i risultati. La clausolaLIMIT
è l'ultima a essere valutata. -
Una query con una clausola
LIMIT
potrebbe comunque essere non deterministica se non è presente un operatore nella query che garantisce l'ordine del set di risultati dell'output. Questo perché BigQuery viene eseguita utilizzando un numero elevato di worker paralleli. L'ordine di restituzione dei job paralleli è non è garantito. -
La clausola
LIMIT
non può contenere funzioni. prende solo una costante numerica.
Eseguire query grammaticali
Le singole clausole delle istruzioni SELECT
di BigQuery sono descritte in dettaglio
qui sopra. Qui presentiamo la grammatica completa di SELECT
istruzioni in un formato compatto con link che rimandano alle singole sezioni.
query: SELECT { * | field_path.* | expression } [ [ AS ] alias ] [ , ... ] [ FROM from_body [ WHERE bool_expression ] [ OMIT RECORD IF bool_expression] [ GROUP [ EACH ] BY [ ROLLUP ] { field_name_or_alias } [ , ... ] ] [ HAVING bool_expression ] [ ORDER BY field_name_or_alias [ { DESC | ASC } ] [, ... ] ] [ LIMIT n ] ]; from_body: { from_item [, ...] | # Warning: Comma means UNION ALL here from_item [ join_type ] JOIN [ EACH ] from_item [ ON join_predicate ] | (FLATTEN({ table_name | (query) }, field_name_or_alias)) | table_wildcard_function } from_item: { table_name | (query) } [ [ AS ] alias ] join_type: { INNER | [ FULL ] [ OUTER ] | RIGHT [ OUTER ] | LEFT [ OUTER ] | CROSS } join_predicate: field_from_one_side_of_the_join = field_from_the_other_side_of_the_join [ AND ...] expression: { literal_value | field_name_or_alias | function_call } bool_expression: { expression_which_results_in_a_boolean_value | bool_expression AND bool_expression | bool_expression OR bool_expression | NOT bool_expression }
Nota:
- Parentesi quadre "[ ]" indicare clausole facoltative.
- Parentesi graffe "{ }" racchiudono una serie di opzioni.
- La barra verticale "|" indica un operatore logico OR.
- Una virgola o una parola chiave seguita dai puntini di sospensione tra parentesi quadre "[, ... ]" indica che l'elemento precedente può ripetersi in un elenco con il separatore specificato.
- Parentesi "( )" indica le parentesi letterali.
Funzioni di aggregazione
Le funzioni di aggregazione restituiscono valori che rappresentano riepiloghi di set di dati più ampi, il che rende queste funzioni particolarmente utili per l'analisi dei log. Una funzione aggregata opera rispetto a una raccolta di valori e restituisce un singolo valore per tabella, gruppo o ambito:
- Aggregazione tabella
Utilizza una funzione aggregata per riepilogare tutte le righe idonee della tabella. Ad esempio:
SELECT COUNT(f1) FROM ds.Table;
- Aggregazione dei gruppi
Utilizza una funzione aggregata e una clausola
GROUP BY
che specifica un campo non aggregato per riepilogare le righe per gruppo. Ad esempio:SELECT COUNT(f1) FROM ds.Table GROUP BY b1;
La funzione TOP rappresenta un caso specializzato di aggregazione di gruppi.
- Aggregazione con ambito
Questa funzionalità si applica solo alle tabelle con campi nidificati.
utilizza una funzione di aggregazione e la parola chiaveWITHIN
per aggregare i valori ripetuti in un ambito definito. Ad esempio:SELECT COUNT(m1.f2) WITHIN RECORD FROM Table;
L'ambito può essere
RECORD
, che corrisponde a un'intera riga o a un nodo (campo ripetuto in una riga). Le funzioni di aggregazione operano sui valori all'interno dell'ambito e restituiscono risultati aggregati per ciascun record o nodo.
Puoi applicare una limitazione a una funzione aggregata utilizzando una delle seguenti opzioni:
-
Un alias in una query di selezione secondaria. La limitazione è specificata nella clausola
WHERE
esterna.#legacySQL SELECT corpus, count_corpus_words FROM (SELECT corpus, count(word) AS count_corpus_words FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus) AS sub_shakespeare WHERE count_corpus_words > 4000
-
Un alias in una clausola HAVING.
#legacySQL SELECT corpus, count(word) AS count_corpus_words FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus HAVING count_corpus_words > 4000;
Puoi anche fare riferimento a un alias nelle clausole GROUP BY
o ORDER BY
.
Sintassi
Funzioni di aggregazione | |
---|---|
AVG() |
Restituisce la media dei valori per un gruppo di righe ... |
BIT_AND() |
Restituisce il risultato di un'operazione AND a livello di bit ... |
BIT_OR() |
Restituisce il risultato di un'operazione OR a livello di bit ... |
BIT_XOR() |
Restituisce il risultato di un'operazione XOR a livello di bit ... |
CORR() |
Restituisce il coefficiente di correlazione di Pearson di un insieme di coppie di numeri. |
COUNT() |
Restituisce il numero totale di valori ... |
COUNT([DISTINCT]) |
Restituisce il numero totale di valori non NULL ... |
COVAR_POP() |
Calcola la covarianza della popolazione dei valori ... |
COVAR_SAMP() |
Calcola la covarianza campione dei valori ... |
EXACT_COUNT_DISTINCT() |
Restituisce il numero esatto di valori distinti non NULL per il campo specificato. |
FIRST() |
Restituisce il primo valore sequenziale nell'ambito della funzione. |
GROUP_CONCAT() |
Concatena più stringhe in un'unica stringa ... |
GROUP_CONCAT_UNQUOTED() |
Concatena più stringhe in un'unica stringa ... senza le virgolette doppie ... |
LAST() |
Restituisce l'ultimo valore sequenziale ... |
MAX() |
Restituisce il valore massimo ... |
MIN() |
Restituisce il valore minimo ... |
NEST() |
Aggrega tutti i valori nell'ambito di aggregazione attuale in un campo ripetuto. |
NTH() |
Restituisce l'ennesimo valore sequenziale ... |
QUANTILES() |
Calcola minimo, massimo e quantili approssimativi ... |
STDDEV() |
Restituisce la deviazione standard ... |
STDDEV_POP() |
Calcola la deviazione standard della popolazione ... |
STDDEV_SAMP() |
Calcola la deviazione standard di un campione ... |
SUM() |
Restituisce la somma totale dei valori ... |
TOP() ... COUNT(*) |
Restituisce i primi max_records in base alla frequenza. |
UNIQUE() |
Restituisce l'insieme di valori univoci non NULL ... |
VARIANCE() |
Calcola la varianza dei valori ... |
VAR_POP() |
Calcola la varianza della popolazione dei valori ... |
VAR_SAMP() |
Calcola la varianza campionaria dei valori ... |
AVG(numeric_expr)
- Restituisci la media dei valori per un gruppo di righe calcolata come
numeric_expr
. Le righe con valore NULL non sono incluse nel calcolo. BIT_AND(numeric_expr)
- Restituisci il risultato di un'operazione
AND
bit a bit tra ogni dinumeric_expr
in tutte le righe. I valoriNULL
vengono ignorati. Questa funzione restituisceNULL
se tutte le istanze dinumeric_expr
hanno come risultatoNULL
. BIT_OR(numeric_expr)
- Restituisci il risultato di un'operazione
OR
bit a bit tra ogni dinumeric_expr
in tutte le righe. I valoriNULL
vengono ignorati. Questa funzione restituisceNULL
se tutte le istanze dinumeric_expr
hanno come risultatoNULL
. BIT_XOR(numeric_expr)
- Restituisci il risultato di un'operazione
XOR
bit a bit tra ogni dinumeric_expr
in tutte le righe. I valoriNULL
vengono ignorati. Questa funzione restituisceNULL
se tutte le istanze dinumeric_expr
hanno come risultatoNULL
. CORR(numeric_expr, numeric_expr)
- Restituisci Coefficiente di correlazione di Pearson di un insieme di coppie di numeri.
COUNT(*)
- Restituisci il numero totale di valori (NULL e non NULL) nell'ambito della funzione. A meno che utilizzi
COUNT(*)
con la funzioneTOP
, è meglio specificare esplicitamente il campo da conteggiare. COUNT([DISTINCT] field [, n])
- Restituisci il numero totale di valori non NULL nell'ambito della funzione.
Se utilizzi la parola chiave
DISTINCT
, la funzione restituisce il numero di valori distinti per il campo specificato. Tieni presente che il valore restituito perDISTINCT
è un'approssimazione statistica e non è garantito che sia esatto.Usa
EXACT_COUNT_DISTINCT()
per una risposta esatta.Se richiedi una maggiore precisione da
, puoi specificare un secondo parametro,COUNT(DISTINCT)
n
, che fornisce la soglia al di sotto della quale sono garantiti risultati esatti. Per impostazione predefinita, il valore din
è 1000, ma se assegni un valoren
più alto, otterrai risultati esatti perCOUNT(DISTINCT)
fino a un valore massimo din
. Tuttavia, l'assegnazione di valori più elevati din
ridurrà la scalabilità di questo operatore e potrebbe incrementare notevolmente il tempo di esecuzione della query o causare la mancata riuscita della query.Per calcolare il numero esatto di valori distinti, utilizza EXACT_COUNT_DISTINCT. In alternativa, per un approccio più scalabile, valuta la possibilità di utilizzare
GROUP EACH BY
nei campi pertinenti e poi di applicareCOUNT(*)
. L'approccioGROUP EACH BY
è più scalabile, ma potrebbe incorrere in una lieve penalità iniziale in termini di prestazioni. COVAR_POP(numeric_expr1, numeric_expr2)
- Calcola la covarianza della popolazione dei valori calcolati da
numeric_expr1
enumeric_expr2
. COVAR_SAMP(numeric_expr1, numeric_expr2)
- Calcola la covarianza del campione dei valori calcolati da
numeric_expr1
enumeric_expr2
. EXACT_COUNT_DISTINCT(field)
- Restituisci il numero esatto di valori distinti non NULL per il campo specificato. Per ottenere scalabilità e prestazioni migliori, utilizza COUNT(DISTINCT campo).
FIRST(expr)
- Restituisce il primo valore sequenziale nell'ambito della funzione.
GROUP_CONCAT('str' [, separator])
-
Concatena più stringhe in un'unica stringa, in cui ogni valore è separato dal parametro facoltativo
separator
. Seseparator
viene omesso, BigQuery restituisce una stringa separata da virgole.Se una stringa nei dati di origine contiene virgolette doppie,
GROUP_CONCAT
restituisce la stringa con l'aggiunta di virgolette doppie. Ad esempio, la stringaa"b
verrà restituita come"a""b"
. UtilizzaGROUP_CONCAT_UNQUOTED
se preferisci che queste stringhe non vengano restituite con l'aggiunta di virgolette doppie.Esempio:
#legacySQL SELECT GROUP_CONCAT(x) FROM ( SELECT 'a"b' AS x), ( SELECT 'cd' AS x);
GROUP_CONCAT_UNQUOTED('str' [, separator])
-
Concatena più stringhe in un'unica stringa, in cui ogni valore è separato dal parametro facoltativo
separator
. Seseparator
viene omesso, BigQuery restituisce una stringa separata da virgole.A differenza di
GROUP_CONCAT
, questa funzione non aggiunge virgolette doppie ai valori restituiti che includono virgolette doppie. Ad esempio, la stringaa"b
verrà restituita comea"b
.Esempio:
#legacySQL SELECT GROUP_CONCAT_UNQUOTED(x) FROM ( SELECT 'a"b' AS x), ( SELECT 'cd' AS x);
LAST(field)
- Restituisci l'ultimo valore sequenziale nell'ambito della funzione.
MAX(field)
- Restituisci il valore massimo nell'ambito della funzione.
MIN(field)
- Restituisci il valore minimo nell'ambito della funzione.
NEST(expr)
-
Aggrega tutti i valori nell'ambito di aggregazione attuale in un campo ripetuto. Ad esempio, la query
"SELECT x, NEST(y) FROM ... GROUP BY x"
restituisce un record di output per ogni valorex
distinto e contiene un campo ripetuto per tutti i valoriy
abbinati ax
nell'input della query. La funzioneNEST
richiede una clausolaGROUP BY
.BigQuery appiattisce automaticamente i risultati delle query, quindi se utilizzi la funzione
NEST
nella query di primo livello, i risultati non conterranno campi ripetuti. Usa la funzioneNEST
quando utilizzi una sottoselezione che produce risultati intermedi per l'uso immediato da parte della stessa query. NTH(n, field)
- Restituisce il
n
° valore sequenziale nell'ambito della funzione, doven
è una costante. Il conteggio della funzioneNTH
inizia da 1, quindi non è presente alcun termine zero. Se l'ambito della funzione ha meno din
valori, la funzione restituisceNULL
. QUANTILES(expr[, buckets])
-
Calcola il minimo, il massimo e i quantili approssimativi per l'espressione di input. I valori di input
NULL
vengono ignorati. Un input vuoto o in modo esclusivo conNULL
genera un output diNULL
. Il numero di quantili calcolati viene controllato tramite il parametro facoltativobuckets
, che include il valore minimo e massimo nel conteggio. Per calcolare N-schede approssimative, utilizza N+1buckets
. Il valore predefinito dibuckets
è 100. Nota: il valore predefinito di 100 non stima i percentili. Per stimare i percentili, utilizza almeno 101buckets
. Se specificato esplicitamente, il valore dibuckets
deve essere almeno 2.L'errore frazionario per quantile è epsilon = 1 /
buckets
. il che significa che l'errore diminuisce con l'aumento del numero di bucket. Ad esempio:QUANTILES(<expr>, 2) # computes min and max with 50% error. QUANTILES(<expr>, 3) # computes min, median, and max with 33% error. QUANTILES(<expr>, 5) # computes quartiles with 25% error. QUANTILES(<expr>, 11) # computes deciles with 10% error. QUANTILES(<expr>, 21) # computes vigintiles with 5% error. QUANTILES(<expr>, 101) # computes percentiles with 1% error.
La funzione
NTH
può essere utilizzata per scegliere un particolare quantile, ma ricorda cheNTH
è su base 1 e cheQUANTILES
restituisce il quantile minimo ("0°") nella prima posizione e il quantile massimo ("100°" percentile o "N°" N-riquadro) nell'ultima posizione. Ad esempio,NTH(11, QUANTILES(expr, 21))
stima la media diexpr
, mentreNTH(20, QUANTILES(expr, 21))
stima il 19° vigintile (95° percentile) diexpr
. Entrambe le stime hanno un margine di errore del 5%.Per migliorare l'accuratezza, utilizza più bucket. Ad esempio, per ridurre il margine di errore per i calcoli precedenti dal 5% allo 0,1%, usare 1001 bucket invece di 21 e regolare l'argomento con
NTH
funzionano di conseguenza. Per calcolare la mediana con 0,1% di errore, utilizzaNTH(501, QUANTILES(expr, 1001))
; per il 95° percentile con 0,1% di errore, utilizzaNTH(951, QUANTILES(expr, 1001))
. STDDEV(numeric_expr)
- Restituisci la deviazione standard dei valori calcolati da
numeric_expr
. Le righe con valore NULL non sono incluse nel calcolo. La funzioneSTDDEV
è un alias perSTDDEV_SAMP
. STDDEV_POP(numeric_expr)
- Calcola la deviazione standard della popolazione del valore calcolato da
numeric_expr
. UtilizzaSTDDEV_POP()
per calcolare la deviazione standard di un set di dati che comprende l'intera popolazione di interesse. Se il set di dati comprende solo un campione rappresentativo della popolazione, utilizza inveceSTDDEV_SAMP()
. Per ulteriori informazioni sulla differenza tra popolazione e deviazione standard del campione, vedi Deviazione standard su Wikipedia. STDDEV_SAMP(numeric_expr)
- Calcola la deviazione standard del campione del valore calcolato da
numeric_expr
. UtilizzaSTDDEV_SAMP()
per calcolare la deviazione standard di un'intera popolazione in base a un campione rappresentativo della popolazione. Se il set di dati comprende l'intera popolazione, utilizza inveceSTDDEV_POP()
. Per ulteriori informazioni sulla differenza tra popolazione e deviazione standard del campione, vedi Deviazione standard su Wikipedia. SUM(field)
- Restituisce la somma totale dei valori nell'ambito della funzione. Da utilizzare solo con i tipi di dati numerici.
TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)
- Restituisce i primi max_records in base alla frequenza. Per informazioni dettagliate, leggi la descrizione PRINCIPALE di seguito.
UNIQUE(expr)
- Restituisci l'insieme di valori univoci e non NULL nell'ambito della funzione in un ordine non definito. Analogamente a una clausola
GROUP BY
di grandi dimensioni senza la parola chiaveEACH
, la query avrà esito negativo e restituisce un messaggio di errore "Risorse superate" se sono presenti troppi valori distinti. Tuttavia, a differenza diGROUP BY
, la funzioneUNIQUE
può essere applicata con l'aggregazione con ambito, consentendo un funzionamento efficiente sui campi nidificati con un numero limitato di valori. VARIANCE(numeric_expr)
- Calcola la varianza dei valori calcolati da
numeric_expr
. Le righe con valore NULL non sono incluse nel calcolo. La funzioneVARIANCE
è un alias perVAR_SAMP
. VAR_POP(numeric_expr)
- Calcola la varianza della popolazione dei valori calcolati da
numeric_expr
. Per ulteriori informazioni sulla differenza tra popolazione e deviazione standard del campione, vedi Deviazione standard su Wikipedia. VAR_SAMP(numeric_expr)
- Calcola la varianza del campione dei valori calcolati da
numeric_expr
. Per ulteriori informazioni sulla differenza tra popolazione e deviazione standard del campione, vedi Deviazione standard su Wikipedia.
Funzione TOP()
TOP è una funzione che rappresenta un'alternativa alla clausola GROUP BY. Viene utilizzata come sintassi semplificata per GROUP BY ... ORDER BY ... LIMIT ...
. In genere, la funzione TOP viene eseguita più rapidamente della query completa ... GROUP BY ... ORDER BY ... LIMIT ...
, ma potrebbe restituire solo risultati approssimativi. Di seguito è riportata la sintassi per la funzione TOP:
TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)
Se utilizzi TOP in una clausola SELECT
, devi includere COUNT(*)
in uno dei campi.
Una query che utilizza la funzione TOP() può restituire solo due campi: il campo TOP e il valore COUNT(*).
field|alias
- Il campo o l'alias da restituire.
max_values
- [Facoltativo] Il numero massimo di risultati da restituire. Il valore predefinito è 20.
multiplier
- Un numero intero positivo che aumenta i valori restituiti da
COUNT(*)
del multiplo specificato.
Esempi TOP()
-
Query di esempio di base che utilizzano
TOP()
Le seguenti query utilizzano
TOP()
per restituire 10 righe.Esempio 1:
#legacySQL SELECT TOP(word, 10) as word, COUNT(*) as cnt FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th";
Esempio 2:
#legacySQL SELECT word, left(word, 3) FROM (SELECT TOP(word, 10) AS word, COUNT(*) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th");
-
Confronta
TOP()
conGROUP BY...ORDER BY...LIMIT
La query restituisce, in ordine, le prime 10 parole più utilizzate, contenenti "th" e il numero di documenti in cui sono state utilizzate le parole. La
TOP
query verrà eseguita molto più velocemente:Esempio senza
TOP()
:#legacySQL SELECT word, COUNT(*) AS cnt FROM ds.Table WHERE word CONTAINS 'th' GROUP BY word ORDER BY cnt DESC LIMIT 10;
Esempio con
TOP()
:#legacySQL SELECT TOP(word, 10), COUNT(*) FROM ds.Table WHERE word contains 'th';
-
Utilizzo del parametro
multiplier
.Le seguenti query mostrano in che modo il parametro
multiplier
influisce sul risultato della query. La prima query restituisce il numero di nascite mensili in Wyoming. La seconda query utilizza il parametromultiplier
per moltiplicare i valori dicnt
per 100.Esempio senza il parametro
multiplier
:#legacySQL SELECT TOP(month,3) as month, COUNT(*) as cnt FROM [bigquery-public-data:samples.natality] WHERE state = "WY";
Resi:
+-------+-------+ | month | cnt | +-------+-------+ | 7 | 19594 | | 5 | 19038 | | 8 | 19030 | +-------+-------+
Esempio con il parametro
multiplier
:#legacySQL SELECT TOP(month,3,100) as month, COUNT(*) as cnt FROM [bigquery-public-data:samples.natality] WHERE state = "WY";
Resi:
+-------+---------+ | month | cnt | +-------+---------+ | 7 | 1959400 | | 5 | 1903800 | | 8 | 1903000 | +-------+---------+
Nota: per usare TOP
, devi includere COUNT(*)
nella clausola SELECT
.
Esempi avanzati
-
Media e deviazione standard raggruppate per condizione
La seguente query restituisce la media e la deviazione standard dei pesi alla nascita in Ohio nel 2003, raggruppati per madri che fumano e non fumano.
Esempio:
#legacySQL SELECT cigarette_use, /* Finds average and standard deviation */ AVG(weight_pounds) baby_weight, STDDEV(weight_pounds) baby_weight_stdev, AVG(mother_age) mother_age FROM [bigquery-public-data:samples.natality] WHERE year=2003 AND state='OH' /* Group the result values by those */ /* who smoked and those who didn't. */ GROUP BY cigarette_use;
-
Filtrare i risultati delle query utilizzando un valore aggregato
Per filtrare i risultati delle query utilizzando un valore aggregato (ad esempio, filtrando in base al valore di
SUM
), usaHAVING
personalizzata.HAVING
confronta un valore con un risultato determinato da un funzione di aggregazione, a differenza diWHERE
, che opera su ogni riga prima dell'aggregazione.Esempio:
#legacySQL SELECT state, /* If 'is_male' is True, return 'Male', */ /* otherwise return 'Female' */ IF (is_male, 'Male', 'Female') AS sex, /* The count value is aliased as 'cnt' */ /* and used in the HAVING clause below. */ COUNT(*) AS cnt FROM [bigquery-public-data:samples.natality] WHERE state != '' GROUP BY state, sex HAVING cnt > 3000000 ORDER BY cnt DESC
Resi:
+-------+--------+---------+ | state | sex | cnt | +-------+--------+---------+ | CA | Male | 7060826 | | CA | Female | 6733288 | | TX | Male | 5107542 | | TX | Female | 4879247 | | NY | Male | 4442246 | | NY | Female | 4227891 | | IL | Male | 3089555 | +-------+--------+---------+
Operatori aritmetici
Gli operatori aritmetici accettano argomenti numerici e restituiscono un risultato numerico. Ogni argomento può essere un valore letterale numerico o un valore numerico restituito da una query. Se l'operazione aritmetica restituisce un risultato non definito, l'operazione restituisce NULL
.
Sintassi
Operatore | Descrizione | Esempio |
---|---|---|
+ | Addizione |
Resi: 10 |
- | Sottrazione |
Resi: 1 |
* | Moltiplicazione |
Resi: 24 |
/ | Divisione |
Resi: 1,5 |
% | Modulo |
Resi: 2 |
Funzioni bit per bit
Le funzioni bit per bit operano a livello di singoli bit e richiedono argomenti numerici. Per ulteriori informazioni sulle funzioni bitwise, consulta l'articolo Operazione bitwise.
Altre tre funzioni a bit, BIT_AND
, BIT_OR
e BIT_XOR
, sono documentate nelle funzioni aggregate.
Sintassi
Operatore | Descrizione | Esempio |
---|---|---|
& | AND a livello di bit |
Resi: 0 |
| | O a livello di bit |
Resi: 28 |
^ | XOR a livello di bit |
Resi: 1 |
<< | Spostamento a sinistra a livello di bit |
Resi: 16 |
>> | Spostamento a destra a livello di bit |
Resi: 2 |
~ | NON a livello di bit |
Resi: -3 |
BIT_COUNT(<numeric_expr>) |
Restituisce il numero di bit impostato in |
Resi: 4 |
Funzioni di trasmissione
Le funzioni di trasmissione modificano il tipo di dati di un'espressione numerica. Le funzioni di trasmissione sono particolarmente utili per garantire che gli argomenti in una funzione di confronto abbiano lo stesso tipo di dati.
Sintassi
Funzioni di trasmissione | |
---|---|
BOOLEAN() |
Trasmetti al valore booleano. |
BYTES() |
Trasmetti ai byte. |
CAST(expr AS type) |
Converte expr in una variabile di tipo type . |
FLOAT() |
Trasmetti per raddoppiare. |
HEX_STRING() |
Trasmetti alla stringa esadecimale. |
INTEGER() |
Trasmetti a numero intero. |
STRING() |
Trasmetti alla stringa. |
BOOLEAN(<numeric_expr>)
-
- Restituisce
true
se<numeric_expr>
è diverso da 0 e non NULL. - Restituisce
false
se<numeric_expr>
è 0. - Restituisce
NULL
se<numeric_expr>
è NULL.
- Restituisce
BYTES(string_expr)
- Restituisci
string_expr
come valore di tipobytes
. CAST(expr AS type)
- Converte
expr
in una variabile di tipotype
. FLOAT(expr)
-
Restituisce
expr
come un doppio.expr
può essere una stringa come'45.78'
, ma la funzione restituisceNULL
per i valori non numerici. HEX_STRING(numeric_expr)
- Restituisce
numeric_expr
come stringa esadecimale. INTEGER(expr)
-
Trasmette
expr
a un numero intero a 64 bit.- Restituisce NULL se
expr
è una stringa che non corrisponde a un valore intero. - Restituisce il numero di microsecondi dall'epoca unix se
expr
è un timestamp.
- Restituisce NULL se
STRING(numeric_expr)
- Restituisce
numeric_expr
come stringa.
Funzioni di confronto
Le funzioni di confronto restituiscono true
o false
, in base ai seguenti tipi di confronti:
- Confronto tra due espressioni.
- Confronto di un'espressione o di un insieme di espressioni con un criterio specifico, ad esempio l'inclusione in un elenco specificato, il fatto che è NULL o un valore facoltativo non predefinito.
Alcune delle funzioni elencate di seguito restituiscono valori diversi da true
o false
, ma i valori restituiti si basano su operazioni di confronto.
Puoi utilizzare espressioni numeriche o stringhe come argomenti per le funzioni di confronto. Le costanti delle stringhe devono essere racchiuse tra virgolette singole o doppie. Le espressioni possono essere valori letterali o valori recuperati da una query. Le funzioni di confronto vengono spesso utilizzate come condizioni di filtro nelle clausole WHERE
, ma possono essere utilizzate in altre clausole.
Sintassi
Funzioni di confronto | |
---|---|
expr1 = expr2 |
Restituisce true se le espressioni sono uguali. |
expr1 != expr2 expr1 <> expr2
|
Restituisce true se le espressioni non sono uguali. |
expr1 > expr2 |
Restituisce true se expr1 è maggiore di expr2 . |
expr1 < expr2 |
Restituisce true se expr1 è inferiore a expr2 . |
expr1 >= expr2 |
Restituisce true se expr1 è maggiore di o uguale a expr2 . |
expr1 <= expr2 |
Restituisce true se expr1 è minore o uguale a expr2 . |
expr1 BETWEEN expr2 AND expr3 |
Restituisce true se il valore di expr1
è compreso tra expr2 e expr3 inclusi. |
expr IS NULL |
Restituisce true se expr è NULL. |
expr IN() |
Restituisce true se expr corrisponde
expr1 , expr2 o qualsiasi valore tra parentesi. |
COALESCE() |
Restituisce il primo argomento che non è NULL. |
GREATEST() |
Restituisce il parametro numeric_expr più grande. |
IFNULL() |
Se l'argomento non è nullo, restituisce l'argomento. |
IS_INF() |
Restituisce true se l'infinito positivo o negativo. |
IS_NAN() |
Restituisce true se l'argomento è NaN . |
IS_EXPLICITLY_DEFINED() |
deprecato: utilizza expr IS NOT NULL . |
LEAST() |
Restituisce l'argomento numeric_expr più piccolo parametro. |
NVL() |
Se expr non è null, restituisce expr , altrimenti restituisce null_default . |
expr1 = expr2
- Restituisce
true
se le espressioni sono uguali. expr1 != expr2
expr1 <> expr2
- Restituisce
true
se le espressioni non sono uguali. expr1 > expr2
- restituisce
true
seexpr1
è maggiore diexpr2
. expr1 < expr2
- restituisce
true
seexpr1
è inferiore aexpr2
. expr1 >= expr2
- restituisce
true
seexpr1
è maggiore o uguale aexpr2
. expr1 <= expr2
- restituisce
true
seexpr1
è inferiore o uguale aexpr2
. expr1 BETWEEN expr2 AND expr3
-
Restituisce
true
se il valore diexpr1
è maggiore o uguale aexpr2
e minore o uguale aexpr3
. expr IS NULL
- Restituisce
true
seexpr
è NULL. expr IN(expr1, expr2, ...)
- Restituisce
true
seexpr
corrisponde aexpr1
,expr2
o a qualsiasi valore tra parentesi. La parola chiaveIN
è un'abbreviazione efficace di(expr = expr1 || expr = expr2 || ...)
. Le espressioni utilizzate con la parola chiaveIN
devono essere costanti e devono corrispondere al tipo di datiexpr
. La clausolaIN
può essere utilizzata anche per creare semi-join e anti-join. Per ulteriori informazioni, consulta le sezioni Semi-join e Anti-join. COALESCE(<expr1>, <expr2>, ...)
- Restituisci il primo argomento che non è NULL.
GREATEST(numeric_expr1, numeric_expr2, ...)
-
Restituisce il parametro
numeric_expr
più grande. Tutti i parametri devono essere numerici e tutti i parametri devono essere dello stesso tipo. Se un parametro èNULL
, questa funzione restituisceNULL
.Per ignorare i valori
NULL
, utilizza la funzioneIFNULL
per modificare i valori diNULL
in un valore che non influisce sul confronto. Nel codice di esempio che segue, la funzioneIFNULL
viene utilizzata per modificare i valori diNULL
in-1
, il che non influisce sul confronto tra numeri positivi.SELECT GREATEST(IFNULL(a,-1), IFNULL(b,-1)) FROM (SELECT 1 as a, NULL as b);
IFNULL(expr, null_default)
- Se
expr
non è null, restituisceexpr
, altrimenti restituiscenull_default
. IS_INF(numeric_expr)
- Restituisci
true
senumeric_expr
è un infinito positivo o negativo. IS_NAN(numeric_expr)
- Restituisci
true
senumeric_expr
è il valore numerico specialeNaN
. IS_EXPLICITLY_DEFINED(expr)
-
Questa funzione è deprecata. Usa invece il criterio
expr IS NOT NULL
. LEAST(numeric_expr1, numeric_expr2, ...)
-
Restituisce il parametro
numeric_expr
più piccolo. Tutti i parametri devono essere numerici e tutti i parametri devono essere dello stesso tipo. Se un parametro èNULL
, questa funzione restituisceNULL
NVL(expr, null_default)
- Se
expr
non è null, restituisceexpr
, altrimenti restituiscenull_default
. La funzioneNVL
è un alias perIFNULL
.
Funzioni di data e ora
Le seguenti funzioni abilitano la manipolazione di data e ora per i timestamp di UNIX. stringhe di date e tipi di dati TIMESTAMP. Per ulteriori informazioni sull'utilizzo di il tipo di dati TIMESTAMP, consulta la sezione Utilizzo di TIMESTAMP.
Le funzioni di data e ora che funzionano con i timestamp UNIX operano in Ora UNIX. Data e ora restituiscono valori in base al fuso orario UTC.
Sintassi
Funzioni di data e ora | |
---|---|
CURRENT_DATE() |
Restituisce la data corrente nel formato %Y-%m-%d . |
CURRENT_TIME() |
Restituisce l'ora attuale del server nel formato %H:%M:%S . |
CURRENT_TIMESTAMP() |
Restituisce l'ora attuale del server nel formato %Y-%m-%d %H:%M:%S . |
DATE() |
Restituisce la data nel formato %Y-%m-%d . |
DATE_ADD() |
Aggiunge l'intervallo specificato a un tipo di dati TIMESTAMP. |
DATEDIFF() |
Restituisce il numero di giorni tra due tipi di dati TIMESTAMP. |
DAY() |
Restituisce il giorno del mese come numero intero compreso tra 1 e 31. |
DAYOFWEEK() |
Restituisce il giorno della settimana come un numero intero compreso tra 1 (domenica) e 7 (sabato). |
DAYOFYEAR() |
Restituisce il giorno dell'anno come numero intero compreso tra 1 e 366. |
FORMAT_UTC_USEC() |
Restituisce un timestamp UNIX nel formato YYYY-MM-DD HH:MM:SS.uuuuuu . |
HOUR() |
Restituisce l'ora di un TIMESTAMP come un numero intero compreso tra 0 e 23. |
MINUTE() |
Restituisce i minuti di un TIMESTAMP come un numero intero compreso tra 0 e 59. |
MONTH() |
Restituisce il mese di un TIMESTAMP come un numero intero compreso tra 1 e 12. |
MSEC_TO_TIMESTAMP() |
Converte un timestamp UNIX in millisecondi in TIMESTAMP. |
NOW() |
Restituisce il timestamp UNIX corrente in microsecondi. |
PARSE_UTC_USEC() |
Converte una stringa data in un timestamp UNIX in microsecondi. |
QUARTER() |
Restituisce il trimestre dell'anno di un TIMESTAMP come un numero intero compreso tra 1 e 4. |
SEC_TO_TIMESTAMP() |
Converte un timestamp UNIX in secondi in un TIMESTAMP. |
SECOND() |
Restituisce i secondi di un TIMESTAMP come un numero intero compreso tra 0 e 59. |
STRFTIME_UTC_USEC() |
Restituisce una stringa per la data nel formato date_format_str. |
TIME() |
Restituisce un valore TIMESTAMP nel formato %H:%M:%S . |
TIMESTAMP() |
Converti una stringa data in un TIMESTAMP. |
TIMESTAMP_TO_MSEC() |
Converte un TIMESTAMP in un timestamp UNIX in millisecondi. |
TIMESTAMP_TO_SEC() |
Converte un TIMESTAMP in un timestamp UNIX in secondi. |
TIMESTAMP_TO_USEC() |
Converte un TIMESTAMP in un timestamp UNIX in microsecondi. |
USEC_TO_TIMESTAMP() |
Converte un timestamp UNIX in microsecondi in un TIMESTAMP. |
UTC_USEC_TO_DAY() |
Sposta un timestamp UNIX in microsecondi all'inizio del giorno in cui si verifica. |
UTC_USEC_TO_HOUR() |
Sposta un timestamp UNIX in microsecondi all'inizio dell'ora in cui si verifica. |
UTC_USEC_TO_MONTH() |
Sposta un timestamp UNIX in microsecondi all'inizio del mese in cui compare. |
UTC_USEC_TO_WEEK() |
Restituisce un timestamp UNIX in microsecondi che rappresenta un giorno della settimana. |
UTC_USEC_TO_YEAR() |
Restituisce un timestamp UNIX in microsecondi che rappresenta l'anno. |
WEEK() |
Restituisce la settimana di un TIMESTAMP come un numero intero compreso tra 1 e 53. |
YEAR() |
Restituisce l'anno di un TIMESTAMP. |
CURRENT_DATE()
Restituisce una stringa leggibile della data corrente nel formato
%Y-%m-%d
.Esempio:
SELECT CURRENT_DATE();
Resi: 01-02-2013
CURRENT_TIME()
Restituisce una stringa leggibile che indica l'ora corrente del server nel formato
%H:%M:%S
.Esempio:
SELECT CURRENT_TIME();
Resi: 01:32:56
CURRENT_TIMESTAMP()
Restituisce un tipo di dati TIMESTAMP dell'ora attuale del server nel formato
%Y-%m-%d %H:%M:%S
.Esempio:
SELECT CURRENT_TIMESTAMP();
Resi: 01-02-2013 01:33:35 UTC
DATE(<timestamp>)
Restituisce una stringa leggibile di un tipo di dati TIMESTAMP nel formato
%Y-%m-%d
.Esempio:
SELECT DATE(TIMESTAMP('2012-10-01 02:03:04'));
Resi: 01-10-2012
DATE_ADD(<timestamp>,<interval>,
<interval_units>)Aggiunge l'intervallo specificato a un tipo di dati TIMESTAMP. I valori di
interval_units
possibili sonoYEAR
,MONTH
,DAY
,HOUR
,MINUTE
eSECOND
. Seinterval
è un numero negativo, l'intervallo viene sottratto dal tipo di dati TIMESTAMP.Esempio:
SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), 5, "YEAR");
Resi: 01-10-2017 02:03:04 UTC
SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), -5, "YEAR");
Resi: 01-10-2007 02:03:04 UTC
DATEDIFF(<timestamp1>,<timestamp2>)
Restituisce il numero di giorni tra due tipi di dati TIMESTAMP. Il risultato è positivo se il primo tipo di dati TIMESTAMP viene dopo il secondo tipo di dati TIMESTAMP, altrimenti il risultato è negativo.
Esempio:
SELECT DATEDIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'));
Resi: 466
Esempio:
SELECT DATEDIFF(TIMESTAMP('2011-06-24 12:18:35'), TIMESTAMP('2012-10-02 05:23:48'));
Resi: -466
DAY(<timestamp>)
Restituisce il giorno del mese di un tipo di dati TIMESTAMP come numero intero compreso tra 1 e 31 inclusi.
Esempio:
SELECT DAY(TIMESTAMP('2012-10-02 05:23:48'));
Resi: 2
DAYOFWEEK(<timestamp>)
Restituisce il giorno della settimana di un tipo di dati TIMESTAMP come un numero intero compreso tra 1 (domenica) e 7 (sabato), inclusi.
Esempio:
SELECT DAYOFWEEK(TIMESTAMP("2012-10-01 02:03:04"));
Resi: 2
DAYOFYEAR(<timestamp>)
Restituisce il giorno dell'anno di un tipo di dati TIMESTAMP sotto forma di numero intero compreso tra 1 e 366 inclusi. Il numero intero 1 si riferisce al 1° gennaio.
Esempio:
SELECT DAYOFYEAR(TIMESTAMP("2012-10-01 02:03:04"));
Resi: 275
FORMAT_UTC_USEC(<unix_timestamp>)
Restituisce una rappresentazione stringa leggibile di un timestamp UNIX nel formato
YYYY-MM-DD HH:MM:SS.uuuuuu
.Esempio:
SELECT FORMAT_UTC_USEC(1274259481071200);
Resi: 2010-05-19 08:58:01.071200
HOUR(<timestamp>)
Restituisce l'ora di un tipo di dati TIMESTAMP sotto forma di numero intero compreso tra 0 e 23 inclusi.
Esempio:
SELECT HOUR(TIMESTAMP('2012-10-02 05:23:48'));
Resi: 5
MINUTE(<timestamp>)
Restituisce i minuti di un tipo di dati TIMESTAMP come un numero intero compreso tra 0 e 59 inclusi.
Esempio:
SELECT MINUTE(TIMESTAMP('2012-10-02 05:23:48'));
Resi: 23
MONTH(<timestamp>)
Restituisce il mese di un tipo di dati TIMESTAMP come numero intero compreso tra 1 e 12 inclusi.
Esempio:
SELECT MONTH(TIMESTAMP('2012-10-02 05:23:48'));
Resi: 10
MSEC_TO_TIMESTAMP(<expr>)
- Converte un timestamp UNIX in millisecondi in un tipo di dati TIMESTAMP.
Esempio:
SELECT MSEC_TO_TIMESTAMP(1349053323000);
Resi: 01-10-2012 01:02:03 UTC
SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)
Resi: 01-10-2012 01:02:04 UTC
NOW()
Restituisce il timestamp UNIX corrente in microsecondi.
Esempio:
SELECT NOW();
Resi: 1359685811687920
PARSE_UTC_USEC(<date_string>)
-
Converte una stringa data in un timestamp UNIX in microsecondi.
date_string
deve essere nel formatoYYYY-MM-DD HH:MM:SS[.uuuuuu]
. La parte frazionaria del secondo può contenere fino a 6 cifre o essere omessa.TIMESTAMP_TO_USEC è una funzione equivalente che converte un argomento di tipo di dati TIMESTAMP invece di una stringa di data.
Esempio:
SELECT PARSE_UTC_USEC("2012-10-01 02:03:04");
Resi: 1349056984000000
QUARTER(<timestamp>)
Restituisce il trimestre dell'anno di un tipo di dati TIMESTAMP come numero intero compreso tra 1 e 4.
Esempio:
SELECT QUARTER(TIMESTAMP("2012-10-01 02:03:04"));
Resi: 4
SEC_TO_TIMESTAMP(<expr>)
Converte un timestamp UNIX in secondi in un tipo di dati TIMESTAMP.
Esempio:
SELECT SEC_TO_TIMESTAMP(1355968987);
Resi: 20-12-2012 02:03:07 UTC
SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));
Resi: 20-12-2012 02:03:07 UTC
SECOND(<timestamp>)
-
Restituisce i secondi di un tipo di dati TIMESTAMP come un numero intero compreso tra 0 e 59 inclusi.
Durante un secondo intercalare, l'intervallo di valori interi è compreso tra 0 e 60 inclusi.
Esempio:
SELECT SECOND(TIMESTAMP('2012-10-02 05:23:48'));
Resi: 48
STRFTIME_UTC_USEC(<unix_timestamp>,
<date_format_str>)-
Restituisce una stringa di data leggibile nel formato date_format_str. date_format_str può includere caratteri di punteggiatura correlati alle date (come / e -) e i caratteri speciali accettati dal funzione strftime in C++ (ad esempio %d per il giorno del mese).
Utilizza le funzioni
UTC_USEC_TO_<function_name>
se prevedi di raggruppare i dati delle query in base a intervalli di tempo, ad esempio recuperare tutti i dati di un determinato mese, perché le funzioni sono più efficienti.Esempio:
SELECT STRFTIME_UTC_USEC(1274259481071200, "%Y-%m-%d");
Resi: 19-05-2010
TIME(<timestamp>)
Restituisce una stringa leggibile di un tipo di dati TIMESTAMP, nel formato
%H:%M:%S
.Esempio:
SELECT TIME(TIMESTAMP('2012-10-01 02:03:04'));
Resi: 02:03:04
TIMESTAMP(<date_string>)
Converti una stringa data in un tipo di dati TIMESTAMP.
Esempio:
SELECT TIMESTAMP("2012-10-01 01:02:03");
Resi: 01-10-2012 01:02:03 UTC
TIMESTAMP_TO_MSEC(<timestamp>)
Converte un tipo di dati TIMESTAMP in un timestamp UNIX in millisecondi.
Esempio:
SELECT TIMESTAMP_TO_MSEC(TIMESTAMP("2012-10-01 01:02:03"));
Resi: 1349053323000
TIMESTAMP_TO_SEC(<timestamp>)
- Converte un tipo di dati TIMESTAMP in un timestamp UNIX in secondi.
Esempio:
SELECT TIMESTAMP_TO_SEC(TIMESTAMP("2012-10-01 01:02:03"));
Resi: 1349053323
TIMESTAMP_TO_USEC(<timestamp>)
-
Converte un tipo di dati TIMESTAMP in un timestamp UNIX in microsecondi.
PARSE_UTC_USEC è una funzione equivalente che converte un argomento di stringa di dati anziché un tipo di dati TIMESTAMP.
Esempio:
SELECT TIMESTAMP_TO_USEC(TIMESTAMP("2012-10-01 01:02:03"));
Resi: 1349053323000000
USEC_TO_TIMESTAMP(<expr>)
Converte un timestamp UNIX in microsecondi in un tipo di dati TIMESTAMP.
Esempio:
SELECT USEC_TO_TIMESTAMP(1349053323000000);
Resi: 01-10-2012 01:02:03 UTC
SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)
Resi: 01-10-2012 01:02:04 UTC
UTC_USEC_TO_DAY(<unix_timestamp>)
-
Sposta un timestamp UNIX in microsecondi all'inizio del giorno in cui si verifica.
Ad esempio, se
unix_timestamp
si verifica il 19 maggio alle 08:58, questa funzione restituisce un timestamp UNIX per il 19 maggio alle 00:00 (mezzanotte).Esempio:
SELECT UTC_USEC_TO_DAY(1274259481071200);
Resi: 1274227200000000
UTC_USEC_TO_HOUR(<unix_timestamp>)
-
Sposta un timestamp UNIX in microsecondi all'inizio dell'ora in cui si verifica.
Ad esempio, se
unix_timestamp
si verifica alle 08:58, questa funzione restituisce un timestamp UNIX per le 08:00 dello stesso giorno.Esempio:
SELECT UTC_USEC_TO_HOUR(1274259481071200);
Resi: 1274256000000000
UTC_USEC_TO_MONTH(<unix_timestamp>)
-
Sposta un timestamp UNIX in microsecondi all'inizio del mese in cui compare.
Ad esempio, se
unix_timestamp
si verifica il 19 marzo, questa funzione restituisce un timestamp UNIX per il 1° marzo dello stesso anno.Esempio:
SELECT UTC_USEC_TO_MONTH(1274259481071200);
Resi: 1272672000000000
UTC_USEC_TO_WEEK(<unix_timestamp>,
<day_of_week>)-
Restituisce un timestamp UNIX in microsecondi che rappresenta un giorno della settimana dell'argomento
unix_timestamp
. Questa funzione accetta due argomenti: un timestamp UNIX in microsecondi e un giorno della settimana da 0 (domenica) a 6 (sabato).Ad esempio, se
unix_timestamp
si verifica venerdì 11/04/2008 e impostiday_of_week
su 2 (martedì), la funzione restituisce un timestamp UNIX per martedì 8/04/2008.Esempio:
SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;
Resi: 1207612800000000
UTC_USEC_TO_YEAR(<unix_timestamp>)
-
Restituisce un timestamp UNIX in microsecondi che rappresenta l'anno dell'argomento
unix_timestamp
.Ad esempio, se
unix_timestamp
compare nel 2010, la funzione restituisce1274259481071200
, la rappresentazione in microsecondi di2010-01-01 00:00
.Esempio:
SELECT UTC_USEC_TO_YEAR(1274259481071200);
Resi: 1262304000000000
WEEK(<timestamp>)
Restituisce la settimana di un tipo di dati TIMESTAMP come numero intero compreso tra 1 e 53 inclusi.
Le settimane iniziano di domenica, quindi se il 1° gennaio è un giorno diverso da La prima settimana di domenica ha meno di 7 giorni e la prima domenica dell'anno è il primo giorno della seconda settimana.
Esempio:
SELECT WEEK(TIMESTAMP('2014-12-31'));
Resi: 53
YEAR(<timestamp>)
- Restituisci l'anno di un tipo di dati TIMESTAMP.
Esempio:
SELECT YEAR(TIMESTAMP('2012-10-02 05:23:48'));
Resi: 2012
Esempi avanzati
-
Convertire i risultati con timestamp interi in un formato leggibile
La seguente query trova i primi 5 momenti in cui è avvenuta la maggior parte delle revisioni di Wikipedia. Per visualizzare i risultati in un formato leggibile utilizza la funzione
FORMAT_UTC_USEC()
di BigQuery, che accetta come input un timestamp in microsecondi. Questa query moltiplica i timestamp del formato POSIX di Wikipedia (in secondi) per 1000000 per convertire il valore in microsecondi.Esempio:
#legacySQL SELECT /* Multiply timestamp by 1000000 and convert */ /* into a more human-readable format. */ TOP (FORMAT_UTC_USEC(timestamp * 1000000), 5) AS top_revision_time, COUNT (*) AS revision_count FROM [bigquery-public-data:samples.wikipedia];
Resi:
+----------------------------+----------------+ | top_revision_time | revision_count | +----------------------------+----------------+ | 2002-02-25 15:51:15.000000 | 20976 | | 2002-02-25 15:43:11.000000 | 15974 | | 2010-02-02 03:34:51.000000 | 3 | | 2010-02-02 01:04:59.000000 | 3 | | 2010-02-01 23:55:05.000000 | 3 | +----------------------------+----------------+
-
Raggruppamento dei risultati per timestamp
È utile utilizzare le funzioni di data e ora per raggruppare i risultati delle query in bucket corrispondenti a determinati anni, mesi o giorni. L'esempio seguente utilizza la funzione
UTC_USEC_TO_MONTH()
per visualizzare il numero di caratteri utilizzati al mese da ogni collaboratore di Wikipedia nei commenti delle revisioni.Esempio:
#legacySQL SELECT contributor_username, /* Return the timestamp shifted to the * start of the month, formatted in * a human-readable format. Uses the * 'LEFT()' string function to return only * the first 7 characters of the formatted timestamp. */ LEFT (FORMAT_UTC_USEC( UTC_USEC_TO_MONTH(timestamp * 1000000)),7) AS month, SUM(LENGTH(comment)) as total_chars_used FROM [bigquery-public-data:samples.wikipedia] WHERE (contributor_username != '' AND contributor_username IS NOT NULL) AND timestamp > 1133395200 AND timestamp < 1157068800 GROUP BY contributor_username, month ORDER BY total_chars_used DESC;
Restituzioni (troncate):
+--------------------------------+---------+-----------------------+ | contributor_username | month | total_chars_used | +--------------------------------+---------+-----------------------+ | Kingbotk | 2006-08 | 18015066 | | SmackBot | 2006-03 | 7838365 | | SmackBot | 2006-05 | 5148863 | | Tawkerbot2 | 2006-05 | 4434348 | | Cydebot | 2006-06 | 3380577 | etc ...
Funzioni IP
Le funzioni IP convertono gli indirizzi IP in formato leggibile.
Sintassi
Funzioni IP | |
---|---|
FORMAT_IP() |
Converte 32 bit meno significativi di integer_value in stringa di indirizzi IPv4 leggibile. |
PARSE_IP() |
Converte una stringa che rappresenta un indirizzo IPv4 in un valore intero senza segno. |
FORMAT_PACKED_IP() |
Restituisce un indirizzo IP leggibile nel modulo
10.1.5.23 o 2620:0:1009:1:216:36ff:feef:3f . |
PARSE_PACKED_IP() |
Restituisce un indirizzo IP in BYTES. |
FORMAT_IP(integer_value)
- Converte 32 bit meno significativi di
integer_value
in stringa di indirizzi IPv4 leggibile. Ad esempio,FORMAT_IP(1)
restituirà la stringa'0.0.0.1'
. PARSE_IP(readable_ip)
- Converte una stringa che rappresenta un indirizzo IPv4 in un valore intero senza segno. Ad esempio,
PARSE_IP('0.0.0.1')
restituirà1
. Se la stringa non è un indirizzo IPv4 valido,PARSE_IP
restituiràNULL
.
BigQuery supporta la scrittura di indirizzi IPv4 e IPv6 in stringhe pacchettizzate, come Dati binari a 4 o 16 byte in ordine di byte di rete. Le funzioni descritte di seguito supporta l'analisi degli indirizzi da e verso il formato leggibile. Queste funzioni funzionano solo su campi stringa con IP.
Sintassi
FORMAT_PACKED_IP(packed_ip)
Restituisce un indirizzo IP leggibile, nel formato
10.1.5.23
o2620:0:1009:1:216:36ff:feef:3f
. Esempi:-
FORMAT_PACKED_IP('0123456789@ABCDE')
restituisce'3031:3233:3435:3637:3839:4041:4243:4445'
FORMAT_PACKED_IP('0123')
restituisce'48.49.50.51'
-
PARSE_PACKED_IP(readable_ip)
Restituisce un indirizzo IP in BYTES. Se la stringa di input non è un indirizzo IPv4 o IPv6 valido,
PARSE_PACKED_IP
restituiràNULL
. Esempi:PARSE_PACKED_IP('48.49.50.51')
restituisce'MDEyMw=='
PARSE_PACKED_IP('3031:3233:3435:3637:3839:4041:4243:4445')
restituisce'MDEyMzQ1Njc4OUBBQkNERQ=='
Funzioni JSON
Le funzioni JSON di BigQuery consentono di trovare i valori all'interno dei dati JSON archiviati utilizzando espressioni simili a JSONPath.
L'archiviazione dei dati JSON può essere più flessibile della dichiarazione di tutti i singoli campi nello schema della tabella, ma può comportare costi più elevati. Quando selezioni i dati da una stringa JSON, ti viene addebitato un costo per la scansione dell'intera stringa, più costoso rispetto all'inserimento di ogni campo in una colonna separata. La query è inoltre più lenta poiché l'intera stringa deve essere analizzata al momento della query. Ma per schemi ad hoc o in rapida evoluzione, la flessibilità di JSON può valere il costo aggiuntivo.
Se lavori con dati strutturati, utilizza le funzioni JSON invece delle funzioni di espressione regolare di BigQuery, poiché le funzioni JSON sono più facili da usare.
Sintassi
Funzioni JSON | |
---|---|
JSON_EXTRACT() |
Seleziona un valore in base all'espressione JSONPath e restituisce una stringa JSON. |
JSON_EXTRACT_SCALAR() |
Seleziona un valore in base all'espressione JSONPath e restituisce un valore scalare JSON. |
JSON_EXTRACT(json, json_path)
-
Seleziona un valore in
json
in base all'espressione JSONPathjson_path
.json_path
deve essere una costante stringa. Restituisce il valore nel formato stringa JSON. JSON_EXTRACT_SCALAR(json, json_path)
-
Seleziona un valore in
json
in base all'espressione JSONPathjson_path
.json_path
deve essere una costante stringa. Restituisce un valore JSON scalare.
Operatori logici
Gli operatori logici eseguono la logica binaria o ternaria sulle espressioni. La logica binaria restituisce true
o false
. La logica ternaria supporta i valori NULL
e restituisce true
, false
o NULL
.
Sintassi
Operatori logici | |
---|---|
expr AND expr |
Restituisce true se entrambe le espressioni sono vere. |
expr OR expr |
Restituisce true se una o entrambe le espressioni sono vere. |
NOT expr |
Restituisce true se l'espressione è falsa. |
expr AND expr
- Restituisce
true
se entrambe le espressioni sono vere. - Restituisce
false
se una o entrambe le espressioni sono false. - Restituisce
NULL
se entrambe le espressioni sono NULL o un'espressione è true e l'altra è NULL.
- Restituisce
expr OR expr
- Restituisce
true
se una o entrambe le espressioni sono vere. - Restituisce
false
se entrambe le espressioni sono false. - Restituisce
NULL
se entrambe le espressioni sono NULL o un'espressione è falsa e l'altra è NULL.
- Restituisce
NOT expr
- Restituisce
true
se l'espressione è falsa. - Restituisce
false
se l'espressione è vera. - Restituisce
NULL
se l'espressione è NULL.
Puoi utilizzare
NOT
con altre funzioni come operatore di negazione. Ad esempio,NOT IN(expr1, expr2)
oIS NOT NULL
.- Restituisce
Funzioni matematiche
Le funzioni matematiche accettano argomenti numerici e restituiscono un risultato numerico. Ogni argomento può essere un valore letterale numerico o un valore numerico restituito da una query. Se la funzione matematica restituisce un risultato non definito, l'operazione restituisce NULL
.
Sintassi
Funzioni matematiche | |
---|---|
ABS() |
Restituisce il valore assoluto dell'argomento. |
ACOS() |
Restituisce il coseno dell'arco dell'argomento. |
ACOSH() |
Restituisce il coseno iperbolico dell'arco dell'argomento. |
ASIN() |
Restituisce l'arcoseno dell'argomento. |
ASINH() |
Restituisce il seno iperbolico ad arco dell'argomento. |
ATAN() |
Restituisce l'arcotangente dell'argomento. |
ATANH() |
Restituisce la tangente iperbolica dell'arco dell'argomento. |
ATAN2() |
Restituisce l'arcotangente dei due argomenti. |
CEIL() |
Arrotonda l'argomento al numero intero più vicino e restituisce il valore arrotondato. |
COS() |
Restituisce il coseno dell'argomento. |
COSH() |
Restituisce il coseno iperbolico dell'argomento. |
DEGREES() |
Converte da radianti a gradi. |
EXP() |
Restituisce e alla potenza dell'argomento. |
FLOOR() |
Arrotonda l'argomento per difetto al numero intero più vicino. |
LN() LOG()
|
Restituisce il logaritmo naturale dell'argomento. |
LOG2() |
Restituisce il logaritmo in Base-2 dell'argomento. |
LOG10() |
Restituisce il logaritmo in Base-10 dell'argomento. |
PI() |
Restituisce la costante p. |
POW() |
Restituisce il primo argomento alla potenza del secondo argomento. |
RADIANS() |
Converte da gradi a radianti. |
RAND() |
Restituisce un valore in virgola mobile casuale nell'intervallo 0,0 <= valore < 1,0. |
ROUND() |
Arrotonda l'argomento per eccesso o per difetto al numero intero più vicino. |
SIN() |
Restituisce il seno dell'argomento. |
SINH() |
Restituisce il seno iperbolico dell'argomento. |
SQRT() |
Restituisce la radice quadrata dell'espressione. |
TAN() |
Restituisce la tangente dell'argomento. |
TANH() |
Restituisce la tangente iperbolica dell'argomento. |
ABS(numeric_expr)
- Restituisci il valore assoluto dell'argomento.
ACOS(numeric_expr)
- Restituisci l'arco coseno dell'argomento.
ACOSH(numeric_expr)
- Restituisci il coseno iperbolico dell'arco dell'argomento.
ASIN(numeric_expr)
- Restituisci l'arcoseno dell'argomento.
ASINH(numeric_expr)
- Restituisci il seno iperbolico dell'arco dell'argomento.
ATAN(numeric_expr)
- Restituisci l'arcotangente dell'argomento.
ATANH(numeric_expr)
- Restituisci la tangente iperbolica dell'arco dell'argomento.
ATAN2(numeric_expr1, numeric_expr2)
- Restituisci l'arcotangente dei due argomenti.
CEIL(numeric_expr)
- Arrotonda l'argomento al numero intero più vicino e restituisce il valore arrotondato.
COS(numeric_expr)
- Restituisci il coseno dell'argomento.
COSH(numeric_expr)
- Restituisci il coseno iperbolico dell'argomento.
DEGREES(numeric_expr)
- Restituisci
numeric_expr
, convertito da radianti a gradi. EXP(numeric_expr)
- Restituisci il risultato dell'elevazione della costante "e" - la base del logaritmo naturale - alla potenza di numeric_expr.
FLOOR(numeric_expr)
- Arrotonda l'argomento per difetto al numero intero più vicino e restituisce il valore arrotondato.
LN(numeric_expr)
LOG(numeric_expr)
- Restituisce il logaritmo naturale dell'argomento.
LOG2(numeric_expr)
- Restituisci il logaritmo in Base-2 dell'argomento.
LOG10(numeric_expr)
- Restituisci il logaritmo in Base-10 dell'argomento.
PI()
- Restituisci la costante p. La funzione
PI()
richiede le parentesi per indicare che si tratta di una funzione, ma non accetta argomenti in queste parentesi. Puoi utilizzarePI()
come una costante con funzioni matematiche e aritmetiche. POW(numeric_expr1, numeric_expr2)
- Restituisci il risultato dell'aumento di
numeric_expr1
alla potenza dinumeric_expr2
. RADIANS(numeric_expr)
- Restituisci
numeric_expr
, convertito da gradi a radianti. Tieni presente che pi radianti corrispondono a 180 gradi. RAND([int32_seed])
- Restituisci un valore in virgola mobile casuale nell'intervallo 0,0 <= valore < 1,0. Ogni valore
int32_seed
genera sempre la stessa sequenza di numeri casuali all'interno di una determinata query, a condizione che non venga utilizzata una clausolaLIMIT
. Seint32_seed
non è specificato, BigQuery utilizza il timestamp corrente come valore seed. ROUND(numeric_expr [, digits])
- Arrotonda l'argomento per eccesso o per difetto al numero intero più vicino (o, se specificato, al numero di cifre specificato) e restituisce il valore arrotondato.
SIN(numeric_expr)
- Restituisci il seno dell'argomento.
SINH(numeric_expr)
- Restituisci il seno iperbolico dell'argomento.
SQRT(numeric_expr)
- Restituisci la radice quadrata dell'espressione.
TAN(numeric_expr)
- Restituisci la tangente dell'argomento.
TANH(numeric_expr)
- Restituisci la tangente iperbolica dell'argomento.
Esempi avanzati
-
Query riquadro di delimitazione
La seguente query restituisce una raccolta di punti all'interno di un riquadro di delimitazione rettangolare centrato intorno a San Francisco (37,46, -122,50).
Esempio:
#legacySQL SELECT year, month, AVG(mean_temp) avg_temp, MIN(min_temperature) min_temp, MAX(max_temperature) max_temp FROM [weather_geo.table] WHERE /* Return values between a pair of */ /* latitude and longitude coordinates */ lat / 1000 > 37.46 AND lat / 1000 < 37.65 AND long / 1000 > -122.50 AND long / 1000 < -122.30 GROUP BY year, month ORDER BY year, month ASC;
-
Query intorno ai cerchi di delimitazione approssimativa
Restituisci una raccolta di massimo 100 punti all'interno di un cerchio approssimativo determinato dal utilizzando Legge sferica del coseno, centrata intorno a Denver Colorado (39,73, -104,98). Questa query utilizza le funzioni matematiche e trigonometriche di BigQuery, ad esempio
PI()
,SIN()
eCOS()
.Poiché la Terra non è una sfera assoluta e longitudine + latitudine converge a ai poli, questa query restituisce un'approssimazione che può essere utile per molti tipi di dati.
Esempio:
#legacySQL SELECT distance, lat, long, temp FROM (SELECT ((ACOS(SIN(39.73756700 * PI() / 180) * SIN((lat/1000) * PI() / 180) + COS(39.73756700 * PI() / 180) * COS((lat/1000) * PI() / 180) * COS((-104.98471790 - (long/1000)) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance, AVG(mean_temp) AS temp, AVG(lat/1000) lat, AVG(long/1000) long FROM [weather_geo.table] WHERE month=1 GROUP BY distance) WHERE distance < 100 ORDER BY distance ASC LIMIT 100;
Funzioni basate su espressioni regolari
BigQuery fornisce il supporto delle espressioni regolari utilizzando la libreria re2; consulta la documentazione per la sintassi delle espressioni regolari.
Tieni presente che le espressioni regolari sono corrispondenze globali; per trovare la corrispondenza dall'inizio di una parola, devi utilizzare il carattere ^.
Sintassi
Funzioni basate su espressioni regolari | |
---|---|
REGEXP_MATCH() |
Restituisce true se l'argomento corrisponde all'espressione regolare. |
REGEXP_EXTRACT() |
Restituisce la parte dell'argomento che corrisponde al gruppo di acquisizione nell'espressione regolare. |
REGEXP_REPLACE() |
Sostituisce una sottostringa che corrisponde a un'espressione regolare. |
REGEXP_MATCH('str', 'reg_exp')
Restituisce true se str corrisponde all'espressione regolare. Per la corrispondenza delle stringhe senza espressioni regolari, utilizza CONTAINS anziché REGEXP_MATCH.
Esempio:
#legacySQL SELECT word, COUNT(word) AS count FROM [bigquery-public-data:samples.shakespeare] WHERE (REGEXP_MATCH(word,r'\w\w\'\w\w')) GROUP BY word ORDER BY count DESC LIMIT 3;
Resi:
+-------+-------+ | word | count | +-------+-------+ | ne'er | 42 | | we'll | 35 | | We'll | 33 | +-------+-------+
REGEXP_EXTRACT('str', 'reg_exp')
Restituisce la parte di str che corrisponde al gruppo di acquisizione nell'espressione regolare.
Esempio:
#legacySQL SELECT REGEXP_EXTRACT(word,r'(\w\w\'\w\w)') AS fragment FROM [bigquery-public-data:samples.shakespeare] GROUP BY fragment ORDER BY fragment LIMIT 3;
Resi:
+----------+ | fragment | +----------+ | NULL | | Al'ce | | As'es | +----------+
REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')
Restituisce una stringa in cui ogni sottostringa di orig_str corrispondente a reg_exp viene sostituita con replace_str. Ad esempio, REGEXP_REPLACE ("Hello", "lo", "p") restituisce Help.
Esempio:
#legacySQL SELECT REGEXP_REPLACE(word, r'ne\'er', 'never') AS expanded_word FROM [bigquery-public-data:samples.shakespeare] WHERE REGEXP_MATCH(word, r'ne\'er') GROUP BY expanded_word ORDER BY expanded_word LIMIT 5;
Resi:
+---------------+ | expanded_word | +---------------+ | Whenever | | never | | nevertheless | | whenever | +---------------+
Esempi avanzati
-
Filtrare i risultati impostati in base alla corrispondenza tramite espressione regolare
Le funzioni di espressione regolare di BigQuery possono essere utilizzate per filtrare i risultati in una clausola
WHERE
e per visualizzarli nellaSELECT
. L'esempio seguente combina entrambi questi casi d'uso relativi alle espressioni regolari in un'unica query.Esempio:
#legacySQL SELECT /* Replace white spaces in the title with underscores. */ REGEXP_REPLACE(title, r'\s+', '_') AS regexp_title, revisions FROM (SELECT title, COUNT(revision_id) as revisions FROM [bigquery-public-data:samples.wikipedia] WHERE wp_namespace=0 /* Match titles that start with 'G', end with * 'e', and contain at least two 'o's. */ AND REGEXP_MATCH(title, r'^G.*o.*o.*e$') GROUP BY title ORDER BY revisions DESC LIMIT 100);
-
Utilizzare espressioni regolari su dati interi o in virgola mobile
Sebbene le funzioni di espressione regolare di BigQuery funzionino solo per i dati stringa, è possibile utilizzare la funzione
STRING()
per trasmettere dati interi o in virgola mobile in formato stringa. In questo esempio,STRING()
viene utilizzato per trasmettere il valore interocorpus_date
a una stringa, che viene poi alterata daREGEXP_REPLACE
.Esempio:
#legacySQL SELECT corpus_date, /* Cast the corpus_date to a string value */ REGEXP_REPLACE(STRING(corpus_date), '^16', 'Written in the sixteen hundreds, in the year \'' ) AS date_string FROM [bigquery-public-data:samples.shakespeare] /* Cast the corpus_date to string, */ /* match values that begin with '16' */ WHERE REGEXP_MATCH(STRING(corpus_date), '^16') GROUP BY corpus_date, date_string ORDER BY date_string DESC LIMIT 5;
Funzioni di stringa
Le funzioni stringa operano sui dati stringa. Le costanti stringa devono essere racchiuse
tra virgolette singole o doppie. Per impostazione predefinita, le funzioni stringa sono sensibili alle maiuscole.
Puoi aggiungere IGNORE CASE
alla fine di una query per abilitare
corrispondenza non sensibile. IGNORE CASE
funziona solo con i caratteri ASCII
e solo al livello superiore della query.
I caratteri jolly non sono supportati in queste funzioni. per l'espressione regolare funzionalità, utilizza un'espressione regolare .
Sintassi
Funzioni di stringa | |
---|---|
CONCAT() |
Restituisce la concatenazione di due o più stringhe oppure NULL se uno dei valori è NULL. |
expr CONTAINS 'str' |
Restituisce true se expr contiene l'argomento stringa specificato. |
INSTR() |
Restituisce l'indice in base uno della prima occorrenza di una stringa. |
LEFT() |
Restituisce i caratteri più a sinistra di una stringa. |
LENGTH() |
Restituisce la lunghezza della stringa. |
LOWER() |
Restituisce la stringa originale con tutti i caratteri in minuscolo. |
LPAD() |
Inserisce caratteri a sinistra di una stringa. |
LTRIM() |
Rimuove i caratteri dal lato sinistro di una stringa. |
REPLACE() |
Sostituisce tutte le occorrenze di una sottostringa. |
RIGHT() |
Restituisce i caratteri più a destra di una stringa. |
RPAD() |
Inserisce caratteri a destra di una stringa. |
RTRIM() |
Rimuove i caratteri finali dal lato destro di una stringa. |
SPLIT() |
Divide una stringa in sottostringhe ripetute. |
SUBSTR() |
Restituisce una sottostringa ... |
UPPER() |
Restituisce la stringa originale con tutti i caratteri in maiuscolo. |
CONCAT('str1', 'str2', '...')
str1 + str2 + ...- Restituisce la concatenazione di due o più stringhe oppure NULL se uno dei valori è NULL. Esempio: se
str1
èJava
estr2
èScript
,CONCAT
restituisceJavaScript
. expr CONTAINS 'str'
- Restituisce
true
seexpr
contiene l'argomento stringa specificato. Questo confronto fa distinzione tra maiuscole e minuscole. INSTR('str1', 'str2')
- Restituisci l'indice in base uno della prima occorrenza di str2 in str1 oppure restituisce 0 se str2 non si verifica in str1.
LEFT('str', numeric_expr)
- Restituisce i caratteri numeric_expr all'estrema sinistra di
str
. Se il numero è più lungo di str, verrà restituita la stringa completa. Esempio:LEFT('seattle', 3)
restituiscesea
. LENGTH('str')
- Restituisce un valore numerico per la lunghezza della stringa. Esempio: se
str
è'123456'
,LENGTH
restituisce6
. LOWER('str')
- Restituisce la stringa originale con tutti i caratteri in minuscolo.
LPAD('str1', numeric_expr, 'str2')
- Pad
str1
a sinistra constr2
, ripetendostr2
fino a quando la stringa del risultato è esattamentenumeric_expr
caratteri. Esempio:LPAD('1', 7, '?')
restituisce??????1
. LTRIM('str1' [, str2])
-
Rimuove i caratteri dal lato sinistro di str1. Se str2 viene omesso,
LTRIM
rimuove gli spazi dal lato sinistro di str1. In caso contrario,LTRIM
rimuove tutti i caratteri in str2 dal lato sinistro di str1 (sensibile alle maiuscole).Esempi:
SELECT LTRIM("Say hello", "yaS")
restituisce" hello"
.SELECT LTRIM("Say hello", " ySa")
restituisce"hello"
. REPLACE('str1', 'str2', 'str3')
-
Sostituisce tutte le istanze di str2 all'interno di str1 con str3.
RIGHT('str', numeric_expr)
- Restituisce i caratteri numeric_expr più a destra di
str
. Se il numero è più lungo della stringa, restituirà l'intera stringa. Esempio:RIGHT('kirkland', 4)
restituisceland
. RPAD('str1', numeric_expr, 'str2')
- Tampona
str1
a destra constr2
, ripetendostr2
fino a quando la stringa del risultato non è esattamentenumeric_expr
caratteri. Esempio:RPAD('1', 7, '?')
restituisce1??????
. RTRIM('str1' [, str2])
-
Rimuove i caratteri finali dal lato destro di str1. Se str2 viene omesso,
RTRIM
rimuove gli spazi finali da str1. In caso contrario,RTRIM
rimuove tutti i caratteri in str2 dal lato destro di str1 (sensibile alle maiuscole).Esempi:
SELECT RTRIM("Say hello", "leo")
restituisce"Say h"
.SELECT RTRIM("Say hello ", " hloe")
restituisce"Say"
. SPLIT('str' [, 'delimiter'])
- Suddividi una stringa in sottostringhe ripetute. Se
delimiter
è specificato, la funzioneSPLIT
suddividestr
in sottostringhe, utilizzandodelimiter
come delimitatore. SUBSTR('str', index [, max_len])
- Restituisce una sottostringa di
str
, a partire daindex
. Se viene utilizzato il parametro facoltativomax_len
, la stringa restituita contiene al massimomax_len
caratteri. Il conteggio parte da 1, quindi il primo carattere della stringa si trova nella posizione 1 (non zero). Seindex
è5
, la sottostringa inizia con il quinto carattere da sinistra instr
. Seindex
è-4
, la sottostringa inizia con il quarto carattere a destra instr
. Esempio:SUBSTR('awesome', -4, 4)
restituisce la sottostringasome
. UPPER('str')
- Restituisce la stringa originale con tutti i caratteri in maiuscolo.
Maiuscole di caratteri speciali nelle stringhe
Per eseguire l'interpretazione letterale dei caratteri speciali, utilizza uno dei seguenti metodi:
- Utilizza la notazione
'\xDD'
, dove'\x'
è seguito dalla rappresentazione esadecimale a due cifre del carattere. - Utilizza una barra di escape davanti a barre, virgolette singole e doppie virgolette.
- Utilizza sequenze di tipo C (
'\a', '\b', '\f', '\n', '\r', '\t',
e'\v'
) per gli altri caratteri.
Alcuni esempi di escape:
'this is a space: \x20'
'this string has \'single quote\' inside it'
'first line \n second line'
"double quotes are also ok"
'\070' -> ERROR: octal escaping is not supported
Funzioni con caratteri jolly della tabella
Le funzioni con caratteri jolly di tabella sono un modo conveniente per eseguire query sui dati da una insieme di tabelle. Una funzione carattere jolly nella tabella equivale a un carattere di tutte le tabelle corrispondenti alla funzione con caratteri jolly. Quando utilizzi una tabella con caratteri jolly, BigQuery accede e addebita solo le tabelle corrispondenti il carattere jolly. Le funzioni con caratteri jolly della tabella sono specificate nella clausola FROM della query.
Se utilizzi funzioni con caratteri jolly nella tabella in una query, le funzioni non hanno più bisogno essere racchiusa tra parentesi. Ad esempio, alcuni dei seguenti esempi utilizzano le parentesi, mentre altre no.
I risultati memorizzati nella cache non sono supportati per le query su più tabelle utilizzando una funzione con caratteri jolly (anche se è selezionata l'opzione Utilizza risultati memorizzati nella cache). Se esegui la stessa query con caratteri jolly più volte, ti viene addebitata ogni query.
Sintassi
Funzioni con caratteri jolly della tabella | |
---|---|
TABLE_DATE_RANGE() |
Esegue query su più tabelle giornaliere che coprono un intervallo di date. |
TABLE_DATE_RANGE_STRICT() |
Esegue query su più tabelle giornaliere che coprono un intervallo di date, senza date mancanti. |
TABLE_QUERY() |
Tabelle di query i cui nomi corrispondono a un predicato specificato. |
TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)
-
Tabelle giornaliere delle query che si sovrappongono nell'intervallo di tempo compreso tra
<timestamp1>
e<timestamp2>
.I nomi delle tabelle devono avere il seguente formato:
<prefix><day>
, dove<day>
è nel formatoYYYYMMDD
.Puoi utilizzare le funzioni di data e ora per generare i parametri timestamp. Ad esempio:
TIMESTAMP('2012-10-01 02:03:04')
DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')
Esempio: ottenere tabelle tra due giorni
Questo esempio presuppone l'esistenza delle seguenti tabelle:
- mydata.people20140325
- mydata.people20140326
- mydata.people20140327
#legacySQL SELECT name FROM TABLE_DATE_RANGE([myproject-1234:mydata.people], TIMESTAMP('2014-03-25'), TIMESTAMP('2014-03-27')) WHERE age >= 35
Corrisponde alle seguenti tabelle:
- mydata.people20140325
- mydata.people20140326
- mydata.people20140327
Esempio: visualizzare le tabelle in un intervallo di 2 giorni fino a "ora"
In questo esempio si presuppone che esistano le seguenti tabelle in un progetto denominato
myproject-1234
:- mydata.people20140323
- mydata.people20140324
- mydata.people20140325
#legacySQL SELECT name FROM (TABLE_DATE_RANGE([myproject-1234:mydata.people], DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), CURRENT_TIMESTAMP())) WHERE age >= 35
Corrisponde alle seguenti tabelle:
- mydata.people20140323
- mydata.people20140324
- mydata.people20140325
TABLE_DATE_RANGE_STRICT(prefix, timestamp1, timestamp2)
-
Questa funzione è equivalente a
TABLE_DATE_RANGE
. L'unica differenza è che se nella sequenza manca una tabella giornaliera,TABLE_DATE_RANGE_STRICT
non riesce e restituisce un erroreNot Found: Table <table_name>
.Esempio: errore in una tabella mancante
Questo esempio presuppone l'esistenza delle seguenti tabelle:
- people20140325
- people20140327
#legacySQL SELECT name FROM (TABLE_DATE_RANGE_STRICT([myproject-1234:mydata.people], TIMESTAMP('2014-03-25'), TIMESTAMP('2014-03-27'))) WHERE age >= 35
L'esempio precedente restituisce l'errore "Non trovato" per la tabella "persone20140326".
TABLE_QUERY(dataset, expr)
-
Tabelle di query i cui nomi corrispondono al valore
expr
specificato. Il parametroexpr
deve essere rappresentato come una stringa e deve contenere un'espressione da valutare. Ad esempio:'length(table_id) < 3'
.Esempio: tabelle delle corrispondenze i cui nomi contengono "oo" e hanno una lunghezza superiore a 4
Questo esempio presuppone l'esistenza delle seguenti tabelle:
- mydata.boo
- mydata.fork
- mydata.ooze
- mydata.spoon
#legacySQL SELECT speed FROM (TABLE_QUERY([myproject-1234:mydata], 'table_id CONTAINS "oo" AND length(table_id) >= 4'))
Corrisponde alle seguenti tabelle:
- mydata.ooze
- mydata.spoon
Esempio: tabelle delle corrispondenze i cui nomi iniziano con "boo", seguito da 3-5 cifre numeriche
In questo esempio si presuppone che esistano le seguenti tabelle in un progetto denominato
myproject-1234
:- mydata.book4
- mydata.book418
- mydata.boom12345
- mydata.boom123456789
- mydata.taboo999
#legacySQL SELECT speed FROM TABLE_QUERY([myproject-1234:mydata], 'REGEXP_MATCH(table_id, r"^boo[\d]{3,5}")')
Corrisponde alle seguenti tabelle:
- mydata.book418
- mydata.boom12345
Funzioni URL
Sintassi
Funzioni URL | |
---|---|
HOST() |
Fornito un URL, restituisce il nome host come stringa. |
DOMAIN() |
Dato un URL, restituisce il dominio come stringa. |
TLD() |
Specificato un URL, restituisce il dominio di primo livello più qualsiasi dominio del paese nell'URL. |
HOST('url_str')
- Dato un URL, restituisce il nome host come stringa. Esempio: HOST('http://www.google.com:80/index.html') restituisce "www.google.com"
DOMAIN('url_str')
- Dato un URL, restituisce il dominio come stringa. Esempio: DOMAIN('http://www.google.com:80/index.html') restituisce "google.com".
TLD('url_str')
- Dato un URL, restituisce il dominio di primo livello più qualsiasi dominio del paese nell'URL. Esempio:TLD('http://www.google.com:80/index.html') restituisce ".com". TLD('http://www.google.co.uk:80/index.html') restituisce ".co.uk".
Note:
- Queste funzioni non eseguono ricerca DNS, pertanto se chiami queste funzioni utilizzando un indirizzo IP, restituirà segmenti dell'indirizzo IP anziché segmenti del nome host.
- Tutte le funzioni di analisi degli URL prevedono caratteri minuscoli. Caratteri maiuscoli nell'URL restituirà un risultato NULL o altrimenti errato. Valuta la possibilità di trasmettere un input a questo tramite LOWER() se i dati contengono lettere maiuscole e minuscole.
Esempio avanzato
Analizzare i nomi di dominio dai dati degli URL
Questa query utilizza il parametro
DOMAIN()
per restituire i domini più popolari elencati come home page del repository su GitHub. Tieni presente
uso di HAVING per filtrare i record utilizzando il risultato della funzione DOMAIN()
. Questo
è una funzione utile per determinare le informazioni di riferimento dai dati dell'URL.
Esempi:
#legacySQL SELECT DOMAIN(repository_homepage) AS user_domain, COUNT(*) AS activity_count FROM [bigquery-public-data:samples.github_timeline] GROUP BY user_domain HAVING user_domain IS NOT NULL AND user_domain != '' ORDER BY activity_count DESC LIMIT 5;
Resi:
+-----------------+----------------+ | user_domain | activity_count | +-----------------+----------------+ | github.com | 281879 | | google.com | 34769 | | khanacademy.org | 17316 | | sourceforge.net | 15103 | | mozilla.org | 14091 | +-----------------+----------------+
Per esaminare in modo specifico le informazioni del dominio di primo livello, utilizza la funzione TLD()
. Questo
l'esempio mostra i principali TLD che non sono inclusi in un elenco di esempi comuni.
#legacySQL SELECT TLD(repository_homepage) AS user_tld, COUNT(*) AS activity_count FROM [bigquery-public-data:samples.github_timeline] GROUP BY user_tld HAVING /* Only consider TLDs that are NOT NULL */ /* or in our list of common TLDs */ user_tld IS NOT NULL AND NOT user_tld IN ('','.com','.net','.org','.info','.edu') ORDER BY activity_count DESC LIMIT 5;
Resi:
+----------+----------------+ | user_tld | activity_count | +----------+----------------+ | .de | 22934 | | .io | 17528 | | .me | 13652 | | .fr | 12895 | | .co.uk | 9135 | +----------+----------------+
Funzioni finestra
Le funzioni finestra, note anche come funzioni di analisi, consentono i calcoli su una un sottoinsieme specifico, o "finestra", di un insieme di risultati. Le funzioni finestra è più facile creare report che includano analisi complesse come medie e totali correnti.
Ogni funzione finestra richiede una clausola OVER
che specifichi
nella parte superiore e inferiore della finestra. I tre componenti di OVER
(partizionamento, ordine e framing) forniscono un controllo aggiuntivo
sopra la finestra. Il partizionamento consente di dividere i dati di input in
gruppi logici che hanno una caratteristica comune. L'ordine ti consente
per ordinare i risultati all'interno di una partizione. L'inquadratura consente
di creare il telaio di una finestra scorrevole all'interno di una partizione che si sposta
rispetto alla riga corrente. Puoi configurare le dimensioni del telaio della finestra mobile
in base a un numero di righe o a un intervallo di valori, ad esempio un intervallo di tempo.
#legacySQL SELECT <window_function> OVER ( [PARTITION BY <expr>] [ORDER BY <expr> [ASC | DESC]] [<window-frame-clause>] )
PARTITION BY
- Definisce la partizione di base su cui opera questa funzione.
Specifica uno o più nomi di colonna separati da virgole. una partizione sarà
creati per ogni insieme distinto di valori per queste colonne, simili
a una clausola
GROUP BY
. SePARTITION BY
viene omesso, la partizione di base è costituita da tutte le righe nell'input della funzione finestra. - La clausola
PARTITION BY
consente anche alle funzioni finestra di partizionare i dati e parallelizzare l'esecuzione. Se vuoi utilizzare una finestra conallowLargeResults
o se intendi applicare ulteriori join o aggregazioni nell'output della funzione finestra, usaPARTITION BY
per parallelizzare l'esecuzione.
Le clausole JOIN EACH
eGROUP EACH BY
non possono essere utilizzati sull'output delle funzioni finestra. Per generare risultati delle query di grandi dimensioni quando utilizzi le funzioni finestra, devi usarePARTITION BY
.ORDER BY
- Ordina la partizione. Se
ORDER BY
non è presente, non vi è alcuna garanzia di per qualsiasi ordinamento predefinito. L'ordinamento avviene a livello di partizione, prima viene applicata qualsiasi clausola relativa al frame della finestra. Se specifichi una finestraRANGE
, devi aggiungere una clausolaORDER BY
. L'ordine predefinito èASC
. ORDER BY
è facoltativo in alcuni casi, ma alcune funzioni finestra, ad esempio rank() o dense_rank(), richiedono la clausola.- Se utilizzi
ORDER BY
senza specificareROWS
oRANGE
,ORDER BY
implica che la finestra si estende dall'inizio della partizione alla riga corrente. Nella in assenza di una clausolaORDER BY
, la finestra rappresenta l'intera partizione. <window-frame-clause>
-
{ROWS | RANGE} {BETWEEN <start> AND <end> | <start> | <end>}
- Un sottoinsieme della partizione su cui operare. Può essere lo stesso
o una dimensione minore della partizione. Se utilizzi
ORDER BY
senzawindow-frame-clause
, il frame della finestra predefinito èRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
. Se ometti siaORDER BY
siawindow-frame-clause
, il frame della finestra predefinito è l'intera partizione.ROWS
: definisce una finestra in termini di posizione della riga, rispetto alla riga corrente. Ad esempio, per aggiungere una colonna che mostra la somma delle precedenti 5 righe di valori dello stipendio, esegui una query suSUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
. L'insieme di righe in genere include la riga corrente, ma questo non è obbligatorio.RANGE
: definisce una finestra in termini di un intervallo di valori in una data colonna, rispetto al valore della colonna nella riga corrente. Funziona solo su numeri e date, dove i valori delle date sono numeri interi semplici (microsecondi dall'epoca). Le righe adiacenti con lo stesso valore vengono chiamate righe peer. Le righe peer diCURRENT ROW
sono incluse in un frame di una finestra che specificaCURRENT ROW
. Ad esempio, se specifica che l'estremità della finestra siaCURRENT ROW
e la riga seguente nella finestra ha lo stesso valore, verrà incluso nella funzione calcolo.BETWEEN <start> AND <end>
: un intervallo, che include le righe di inizio e fine. L'intervallo non deve includere il parametro riga corrente, ma<start>
deve precedere o uguale a<end>
.<start>
- Specifica l'offset iniziale per questa finestra rispetto alla riga corrente. Le seguenti opzioni sono supportati:{UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
dove<expr>
è un numero intero positivo,PRECEDING
indica una precedente un numero di riga o un valore di intervallo eFOLLOWING
indica una riga successiva un numero o un valore di intervallo.UNBOUNDED PRECEDING
indica la prima riga della partizione. Se l'inizio precede la finestra, sulla prima riga della partizione.<end>
: specifica l'offset finale per questa finestra, rispetto alla riga corrente. Sono supportate le seguenti opzioni:{UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
dove<expr>
è un numero intero positivo,PRECEDING
indica una riga precedente numero o intervallo di valori eFOLLOWING
indica il numero di riga successivo o un valore di intervallo.UNBOUNDED FOLLOWING
indica l'ultima riga delle della partizione di testo. Se la fine è oltre la fine della finestra, verrà impostata sul all'ultima riga della partizione.
A differenza delle funzioni di aggregazione, che comprimono molte righe di input in una
riga di output, le funzioni finestra restituiscono una riga di output per ogni riga di input.
Questa funzionalità semplifica la creazione di query per il calcolo dei totali correnti
e medie mobili. Ad esempio, la seguente query restituisce un totale corrente
per un piccolo set di dati di cinque righe definito dalle istruzioni SELECT
:
#legacySQL SELECT name, value, SUM(value) OVER (ORDER BY value) AS RunningTotal FROM (SELECT "a" AS name, 0 AS value), (SELECT "b" AS name, 1 AS value), (SELECT "c" AS name, 2 AS value), (SELECT "d" AS name, 3 AS value), (SELECT "e" AS name, 4 AS value);
Valore restituito:
+------+-------+--------------+ | name | value | RunningTotal | +------+-------+--------------+ | a | 0 | 0 | | b | 1 | 1 | | c | 2 | 3 | | d | 3 | 6 | | e | 4 | 10 | +------+-------+--------------+
L'esempio seguente calcola una media mobile dei valori in riga corrente e la riga che la precede. Il telaio della finestra è costituito da due righe che si muovono insieme alla riga corrente.
#legacySQL SELECT name, value, AVG(value) OVER (ORDER BY value ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS MovingAverage FROM (SELECT "a" AS name, 0 AS value), (SELECT "b" AS name, 1 AS value), (SELECT "c" AS name, 2 AS value), (SELECT "d" AS name, 3 AS value), (SELECT "e" AS name, 4 AS value);
Valore restituito:
+------+-------+---------------+ | name | value | MovingAverage | +------+-------+---------------+ | a | 0 | 0.0 | | b | 1 | 0.5 | | c | 2 | 1.5 | | d | 3 | 2.5 | | e | 4 | 3.5 | +------+-------+---------------+
Sintassi
Funzioni finestra | |
---|---|
AVG() COUNT(*) COUNT([DISTINCT]) MAX() MIN() STDDEV() SUM() |
La stessa operazione Funzioni di aggregazione, ma che vengono calcolate su una finestra definita dalla clausola OVER. |
CUME_DIST() |
Restituisce un doppio che indica la distribuzione cumulativa di un valore in un gruppo di valori ... |
DENSE_RANK() |
Restituisce il rango intero di un valore in un gruppo di valori. |
FIRST_VALUE() |
Restituisce il primo valore del campo specificato nella finestra. |
LAG() |
Consente di leggere i dati di una riga precedente all'interno di una finestra. |
LAST_VALUE() |
Restituisce l'ultimo valore del campo specificato nella finestra. |
LEAD() |
Consente di leggere i dati di una riga successiva all'interno di una finestra. |
NTH_VALUE() |
Restituisce il valore di <expr> nella posizione
<n> del telaio della finestra ...
|
NTILE() |
Divide la finestra nel numero specificato di bucket. |
PERCENT_RANK() |
Restituisce la posizione della riga corrente rispetto alle altre righe della partizione. |
PERCENTILE_CONT() |
Restituisce un valore interpolato che verrebbe mappato all'argomento percentile rispetto alla finestra ... |
PERCENTILE_DISC() |
Restituisce il valore più vicino al percentile dell'argomento nella finestra. |
RANK() |
Restituisce il rango intero di un valore in un gruppo di valori. |
RATIO_TO_REPORT() |
Restituisce il rapporto di ciascun valore alla somma dei valori. |
ROW_NUMBER() |
Restituisce il numero di riga corrente del risultato della query nella finestra. |
-
AVG(numeric_expr)
COUNT(*)
COUNT([DISTINCT] field)
MAX(field)
MIN(field)
STDDEV(numeric_expr)
SUM(field)
-
Queste funzioni finestra eseguono la stessa operazione
Funzioni di aggregazione, ma vengono calcolate
su una finestra definita dalla clausola OVER.
Un'altra differenza significativa è che Funzione
COUNT([DISTINCT] field)
produce risultati esatti quando utilizzata come funzione finestra, con comportamento simile alla funzione aggregataEXACT_COUNT_DISTINCT()
.Nella query di esempio, la clausola
ORDER BY
causa la finestra da calcolare dall'inizio della partizione riga corrente, che genera una somma cumulativa per l'anno in questione.#legacySQL SELECT corpus_date, corpus, word_count, SUM(word_count) OVER ( PARTITION BY corpus_date ORDER BY word_count) annual_total FROM [bigquery-public-data:samples.shakespeare] WHERE word='love' ORDER BY corpus_date, word_count
Resi:
corpus_date corpus word_count annual_total 0 various 37 37 0 sonetti 157 194 1590 2Kinghenryvi 18 18 1590 1kinghenryvi 24 42 1590 3Kinghenryvi 40 82 CUME_DIST()
-
Restituisce un doppio che indica la distribuzione cumulativa di un valore in un gruppo di valori, calcolati utilizzando la formula
<number of rows preceding or tied with the current row> / <total rows>
. I valori legati restituiscono la stessa distribuzione cumulativa valore.Questa funzione finestra richiede
ORDER BY
nella clausolaOVER
.#legacySQL SELECT word, word_count, CUME_DIST() OVER (PARTITION BY corpus ORDER BY word_count DESC) cume_dist, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Resi:
parola word_count cume_dist fazzoletto 29 0,2 soddisfazione 5 0,4 disappunto 4 0,8 strumentazioni 4 0,8 circostanza 3 1,0 DENSE_RANK()
-
Restituisce il rango intero di un valore in un gruppo di valori. Il ranking viene calcolato in base confronti con altri valori nel gruppo.
I valori legati vengono visualizzati come lo stesso ranking. La posizione del valore successivo viene incrementata di 1. Per Ad esempio, se due valori sono associati al ranking 2, il successivo valore di ranking è 3. Se preferisci creare un intervallo utilizza rank().
Questa funzione finestra richiede
ORDER BY
nella clausolaOVER
.#legacySQL SELECT word, word_count, DENSE_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) dense_rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Resi:parola word_count dense_rank fazzoletto 29 1 soddisfazione 5 2 disappunto 4 3 strumentazioni 4 3 circostanza 3 4 FIRST_VALUE(<field_name>)
-
Restituisce il primo valore di
<field_name>
nella finestra.#legacySQL SELECT word, word_count, FIRST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) fv, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 1
Resi:parola word_count v in modo imperfetto 1 in modo imperfetto LAG(<expr>[, <offset>[, <default_value>]])
-
Consente di leggere i dati di una riga precedente all'interno di una finestra. Nello specifico,
LAG()
restituisce il valore<expr>
per la riga posizionato<offset>
righe prima della riga corrente. Se la riga non esiste,<default_value>
i resi.#legacySQL SELECT word, word_count, LAG(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lag, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Resi:
parola word_count attesa fazzoletto 29 null soddisfazione 5 fazzoletto disappunto 4 soddisfazione strumentazioni 4 disappunto circostanza 3 strumentazioni LAST_VALUE(<field_name>)
-
Restituisce l'ultimo valore di
<field_name>
nella finestra.#legacySQL SELECT word, word_count, LAST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) lv, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 1
Resi:
parola word_count lv in modo imperfetto 1 in modo imperfetto LEAD(<expr>[, <offset>[, <default_value>]])
-
Consente di leggere i dati di una riga successiva all'interno di una finestra. Nello specifico,
LEAD()
restituisce il valore<expr>
per la riga posizionato<offset>
righe dopo la riga corrente. Se la riga non esiste,<default_value>
i resi.#legacySQL SELECT word, word_count, LEAD(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lead, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Resi:parola word_count lead fazzoletto 29 soddisfazione soddisfazione 5 disappunto disappunto 4 strumentazioni strumentazioni 4 circostanza circostanza 3 null NTH_VALUE(<expr>, <n>)
-
Restituisce il valore di
<expr>
nella posizione<n>
del telaio della finestra, dove<n>
è un indice in base uno. NTILE(<num_buckets>)
-
Divide una sequenza di righe in
<num_buckets>
bucket e assegna un il numero del bucket corrispondente, espresso in numero intero, per ogni riga. La La funzionentile()
assegna i numeri ai bucket nello stesso modo possibile e restituisce un valore compreso tra 1 e<num_buckets>
per ogni riga.#legacySQL SELECT word, word_count, NTILE(2) OVER (PARTITION BY corpus ORDER BY word_count DESC) ntile, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Resi:parola word_count ntile fazzoletto 29 1 soddisfazione 5 1 disappunto 4 1 strumentazioni 4 2 circostanza 3 2 PERCENT_RANK()
-
Restituisce la posizione della riga corrente rispetto alle altre righe nella della partizione di testo. I valori restituiti sono compresi tra 0 e 1 inclusi. Il primo è 0,0.
Questa funzione finestra richiede
ORDER BY
nella clausolaOVER
.#legacySQL SELECT word, word_count, PERCENT_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) p_rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Resi:parola word_count p_rank fazzoletto 29 0.0 soddisfazione 5 0,25 disappunto 4 0,5 strumentazioni 4 0,5 circostanza 3 1,0 PERCENTILE_CONT(<percentile>)
-
Restituisce un valore interpolato che verrebbe mappato all'argomento percentile rispetto a nella finestra, dopo averle ordinate secondo la clausola
ORDER BY
.<percentile>
deve essere compreso tra 0 e 1.Questa funzione finestra richiede
ORDER BY
nella clausolaOVER
.#legacySQL SELECT word, word_count, PERCENTILE_CONT(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_cont, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Resi:parola word_count p_cont fazzoletto 29 4 soddisfazione 5 4 disappunto 4 4 strumentazioni 4 4 circostanza 3 4 PERCENTILE_DISC(<percentile>)
-
Restituisce il valore più vicino al percentile dell'argomento nella finestra.
<percentile>
deve essere compreso tra 0 e 1.Questa funzione finestra richiede
ORDER BY
nella clausolaOVER
.#legacySQL SELECT word, word_count, PERCENTILE_DISC(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_disc, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Resi:parola word_count p_disc fazzoletto 29 4 soddisfazione 5 4 disappunto 4 4 strumentazioni 4 4 circostanza 3 4 RANK()
-
Restituisce il rango numero intero di un valore in un gruppo di valori. Il ranking viene calcolato in base confronti con altri valori nel gruppo.
I valori legati vengono visualizzati come lo stesso ranking. Il rango del valore successivo viene incrementato in base con il numero di valori legati che si sono verificati prima. Ad esempio, se due valori sono associati al ranking 2, il valore il valore di ranking successivo è 4, non 3. Se preferisci che non ci siano lacune nell'elenco di ranking, utilizza dense_rank().
Questa funzione finestra richiede
ORDER BY
nella clausolaOVER
.#legacySQL SELECT word, word_count, RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Resi:parola word_count rank fazzoletto 29 1 soddisfazione 5 2 disappunto 4 3 strumentazioni 4 3 circostanza 3 5 RATIO_TO_REPORT(<column>)
-
Restituisce il rapporto di ogni valore alla somma dei valori, come un doppio tra 0 e 1.
#legacySQL SELECT word, word_count, RATIO_TO_REPORT(word_count) OVER (PARTITION BY corpus ORDER BY word_count DESC) r_to_r, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Resi:parola word_count r_to_r fazzoletto 29 0,6444444444444445 soddisfazione 5 0,1111111111111111 disappunto 4 0,08888888888888889 strumentazioni 4 0,08888888888888889 circostanza 3 0,06666666666666667 ROW_NUMBER()
-
Restituisce il numero di riga corrente del risultato della query nella finestra, a partire da 1.
#legacySQL SELECT word, word_count, ROW_NUMBER() OVER (PARTITION BY corpus ORDER BY word_count DESC) row_num, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Resi:parola word_count row_num fazzoletto 29 1 soddisfazione 5 2 disappunto 4 3 strumentazioni 4 4 circostanza 3 5
Altre funzioni
Sintassi
Altre funzioni | |
---|---|
CASE WHEN ... THEN |
Utilizza CASE per scegliere tra due o più espressioni alternative nella query. |
CURRENT_USER() |
Restituisce l'indirizzo email dell'utente che esegue la query. |
EVERY() |
Restituisce true se l'argomento è vero per tutti i suoi input. |
FROM_BASE64() |
Converte la stringa di input codificata in base64 nel formato BYTES. |
HASH() |
Calcola e restituisce un valore hash firmato a 64 bit ... |
FARM_FINGERPRINT() |
Calcola e restituisce un valore di impronta firmato a 64 bit ... |
IF() |
Se il primo argomento è vero, restituisce il secondo argomento; altrimenti restituisce il terzo argomento. |
POSITION() |
Restituisce la posizione sequenziale su base uno dell'argomento. |
SHA1() |
Restituisce un hash SHA1 in formato BYTES. |
SOME() |
Restituisce true se l'argomento è vero per almeno uno dei suoi input. |
TO_BASE64() |
Converte l'argomento BYTES in una stringa codificata in base-64. |
CASE WHEN when_expr1 THEN then_expr1
WHEN when_expr2 THEN then_expr2 ...
ELSE else_expr END- Utilizza CASE per scegliere tra due o più espressioni alternative nella query. QUANDO Le espressioni devono essere booleane e tutte le espressioni nelle clausole THEN e nella clausola ELSE devono essere compatibili.
CURRENT_USER()
- Restituisci l'indirizzo email dell'utente che esegue la query.
EVERY(<condition>)
- Restituisci
true
secondition
è vero per tutti gli attributi i suoi input. Se utilizzata con la clausolaOMIT IF
, questa funzione è utile per le query che implicano campi ripetuti. FROM_BASE64(<str>)
- Converte la stringa di input codificata in base64
str
nel formato BYTES. Per convertire BYTES in una stringa con codifica base64, utilizza TO_BASE64(). HASH(expr)
- Calcola e restituisce un valore hash firmato a 64 bit dei byte di
expr
come definita dal CityHash (versione 1.0.3). È supportata qualsiasi stringa o espressione di numeri interi e la funzione rispettaIGNORE CASE
per le stringhe, restituendo valori invarianti per le maiuscole. FARM_FINGERPRINT(expr)
- Calcola e restituisce un valore di impronta firmato a 64 bit di
STRING
oBYTES
utilizzandoFingerprint64
dalla funzione libreria open source FarmHash. L'output di questa funzione per un determinato input non cambierà mai e corrisponde all'output delFARM_FINGERPRINT
quando utilizzi GoogleSQL. RispettaIGNORE CASE
per le stringhe, restituendo valori invarianti per le maiuscole. IF(condition, true_return, false_return)
- restituisce
true_return
ofalse_return
, a seconda checondition
sia vero o falso. I valori restituiti possono essere letterali o valori derivati dai campi, ma devono essere dello stesso tipo di dati. Valori derivati dal campo non devono essere incluse nella clausolaSELECT
. POSITION(field)
- Restituisci la posizione sequenziale su base uno di field all'interno di un insieme di valori ripetuti campi.
SHA1(<str>)
- Restituisci un hash SHA1, nel formato BYTES, della stringa di input
str
. Puoi convertire il risultato in base64 utilizzando TO_BASE64(). Ad esempio:#legacySQL SELECT TO_BASE64(SHA1(corpus)) FROM [bigquery-public-data:samples.shakespeare] LIMIT 100;
SOME(<condition>)
- Restituisci
true
secondition
è true per alle almeno uno dei suoi input. Se utilizzata con la clausolaOMIT IF
, questa funzione viene utile per le query che includono campi ripetuti. TO_BASE64(<bin_data>)
- Converte l'input BYTES
bin_data
in una stringa con codifica base64. Ad esempio:#legacySQL SELECT TO_BASE64(SHA1(title)) FROM [bigquery-public-data:samples.wikipedia] LIMIT 100;
Per convertire una stringa con codifica base64 in BYTES, utilizza FROM_BASE64().
Esempi avanzati
-
Raggruppamento dei risultati in categorie utilizzando le condizionali
La seguente query usa un blocco
CASE/WHEN
per eseguire il bucket dei risultati in "region" in base a un elenco di stati. Se lo stato non è visualizzato tra le opzioni in una delleWHEN
, il valore predefinito dello stato è "Nessuna".Esempio:
#legacySQL SELECT CASE WHEN state IN ('WA', 'OR', 'CA', 'AK', 'HI', 'ID', 'MT', 'WY', 'NV', 'UT', 'CO', 'AZ', 'NM') THEN 'West' WHEN state IN ('OK', 'TX', 'AR', 'LA', 'TN', 'MS', 'AL', 'KY', 'GA', 'FL', 'SC', 'NC', 'VA', 'WV', 'MD', 'DC', 'DE') THEN 'South' WHEN state IN ('ND', 'SD', 'NE', 'KS', 'MN', 'IA', 'MO', 'WI', 'IL', 'IN', 'MI', 'OH') THEN 'Midwest' WHEN state IN ('NY', 'PA', 'NJ', 'CT', 'RI', 'MA', 'VT', 'NH', 'ME') THEN 'Northeast' ELSE 'None' END as region, average_mother_age, average_father_age, state, year FROM (SELECT year, state, SUM(mother_age)/COUNT(mother_age) as average_mother_age, SUM(father_age)/COUNT(father_age) as average_father_age FROM [bigquery-public-data:samples.natality] WHERE father_age < 99 GROUP BY year, state) ORDER BY year LIMIT 5;
Resi:
+--------+--------------------+--------------------+-------+------+ | region | average_mother_age | average_father_age | state | year | +--------+--------------------+--------------------+-------+------+ | South | 24.342600163532296 | 27.683769419460344 | AR | 1969 | | West | 25.185041908446163 | 28.268214055448098 | AK | 1969 | | West | 24.780776677578217 | 27.831181063905248 | CA | 1969 | | West | 25.005834769924412 | 27.942978384829598 | AZ | 1969 | | South | 24.541730952905738 | 27.686430093306885 | AL | 1969 | +--------+--------------------+--------------------+-------+------+
-
Simulare una tabella pivot
Utilizza le istruzioni condizionali per organizzare i risultati di una query sottoselezionata in righe e colonne. Nell'esempio seguente, i risultati di una ricerca degli articoli di Wikipedia più aggiornati che iniziano con il valore "Google" sono organizzate in colonne, dove i conteggi delle revisioni visualizzati se soddisfano diversi criteri.
Esempio:
#legacySQL SELECT page_title, /* Populate these columns as True or False, */ /* depending on the condition */ IF (page_title CONTAINS 'search', INTEGER(total), 0) AS search, IF (page_title CONTAINS 'Earth' OR page_title CONTAINS 'Maps', INTEGER(total), 0) AS geo, FROM /* Subselect to return top revised Wikipedia articles */ /* containing 'Google', followed by additional text. */ (SELECT TOP (title, 5) as page_title, COUNT (*) as total FROM [bigquery-public-data:samples.wikipedia] WHERE REGEXP_MATCH (title, r'^Google.+') AND wp_namespace = 0 );
Resi:
+---------------+--------+------+ | page_title | search | geo | +---------------+--------+------+ | Google search | 4261 | 0 | | Google Earth | 0 | 3874 | | Google Chrome | 0 | 0 | | Google Maps | 0 | 2617 | | Google bomb | 0 | 0 | +---------------+--------+------+
-
Utilizzare HASH per selezionare un campione casuale dei dati
Alcune query possono fornire un risultato utile utilizzando un sottocampionamento casuale di l'insieme di risultati. Per recuperare un campionamento casuale di valori, utilizza il metodo Funzione
HASH
per restituire risultati in cui il modulo "n" di l'hash è uguale a zero.Ad esempio, la seguente query troverà il
HASH()
di il "titolo" e poi controlla se quel valore è modulo "2" è zero. Questo dovrebbe comportare l'etichettatura di circa il 50% dei valori come "campionati". A campiona meno valori, aumenta il valore dell'operazione modulo da "2" a qualcosa di più grande. La query utilizza la funzioneABS
in combinazione conHASH
, perchéHASH
può restituire valori negativi e il modulo operatore su un valore negativo genera un valore negativo.Esempio:
#legacySQL SELECT title, HASH(title) AS hash_value, IF(ABS(HASH(title)) % 2 == 1, 'True', 'False') AS included_in_sample FROM [bigquery-public-data:samples.wikipedia] WHERE wp_namespace = 0 LIMIT 5;