Funzioni e operatori SQL legacy

Questo documento descrive nel dettaglio le funzioni e gli operatori SQL precedente. La sintassi delle query preferita per BigQuery è GoogleSQL. Per informazioni su GoogleSQL, consulta la pagina relativa alle funzioni e agli operatori di GoogleSQL.

Funzioni e operatori supportati

La maggior parte delle clausole dell'istruzione SELECT supporta le funzioni. I campi a cui viene fatto riferimento in una funzione non devono essere elencati in alcuna clausola SELECT. Di conseguenza, 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 campionaria 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 ... non aggiunge 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'n-esimo valore sequenziale ...
`QUANTILES()`	Calcola il minimo, il massimo e i quantili approssimativi ...
`STDDEV()`	Restituisce la deviazione standard ...
`STDDEV_POP()`	Calcola la deviazione standard della popolazione ...
`STDDEV_SAMP()`	Calcola la deviazione standard del campione ...
`SUM()`	Restituisce la somma totale dei valori ...
`TOP() ... COUNT(*)`	Restituisce i primi record max_records per 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 del campione dei valori ...

Operatori aritmetici
`+`	Aggiunta
`-`	Sottrazione
`*`	Moltiplicazione
`/`	Divisione
`%`	Modulo

Funzioni a livello di bit
`&`	A livello di bit E
`\|`	O a livello di bit
`^`	XOR a livello di bit
`<<`	Spostamento a sinistra a livello di bit
`>>`	Spostamento a destra a livello di bit
`~`	A livello di bit, NON
`BIT_COUNT()`	Restituisce il numero di bit ...

Funzioni di trasmissione
`BOOLEAN()`	Trasmetti in valore booleano.
`BYTES()`	Trasmetti in byte.
`CAST(expr AS type)`	Converte `expr` in una variabile di tipo `type`.
`FLOAT()`	Trasmetti per raddoppiare.
`HEX_STRING()`	Converti in 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 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 a `expr1`, `expr2` o a qualsiasi valore tra parentesi.
`COALESCE()`	Restituisce il primo argomento diverso da NULL.
`GREATEST()`	Restituisce il parametro `numeric_expr` più grande.
`IFNULL()`	Se l'argomento non è null, restituisce l'argomento.
`IS_INF()`	Restituisce `true` se infinito positivo o negativo.
`IS_NAN()`	Restituisce `true` se l'argomento è `NaN`.
`IS_EXPLICITLY_DEFINED()`	deprecato: utilizza `expr IS NOT NULL`.
`LEAST()`	Restituisce il parametro `numeric_expr` dell'argomento più piccolo.
`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 corrente del server nel formato `%H:%M:%S`.
`CURRENT_TIMESTAMP()`	Restituisce l'ora corrente 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 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 numero intero compreso tra 0 e 23.
`MINUTE()`	Restituisce i minuti di un TIMESTAMP come numero intero compreso tra 0 e 59.
`MONTH()`	Restituisce il mese di un TIMESTAMP come 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 di data in un timestamp UNIX in microsecondi.
`QUARTER()`	Restituisce il trimestre dell'anno di un TIMESTAMP come numero intero compreso tra 1 e 4.
`SEC_TO_TIMESTAMP()`	Converte un timestamp UNIX di secondi in un TIMESTAMP.
`SECOND()`	Restituisce i secondi di TIMESTAMP come numero intero compreso tra 0 e 59.
`STRFTIME_UTC_USEC()`	Restituisce una stringa di data nel formato date_format_str.
`TIME()`	Restituisce un elemento TIMESTAMP nel formato `%H:%M:%S`.
`TIMESTAMP()`	Converti una stringa di data in TIMESTAMP.
`TIMESTAMP_TO_MSEC()`	Converte TIMESTAMP in timestamp UNIX in millisecondi.
`TIMESTAMP_TO_SEC()`	Converte TIMESTAMP in timestamp UNIX in secondi.
`TIMESTAMP_TO_USEC()`	Converte TIMESTAMP in timestamp UNIX in microsecondi.
`USEC_TO_TIMESTAMP()`	Converte un timestamp UNIX in microsecondi in TIMESTAMP.
`UTC_USEC_TO_DAY()`	Sposta un timestamp UNIX in microsecondi dall'inizio del giorno in cui si verifica.
`UTC_USEC_TO_HOUR()`	Sposta un timestamp UNIX in microsecondi all'inizio dell'ora in cui si trova.
`UTC_USEC_TO_MONTH()`	Sposta un timestamp UNIX in microsecondi all'inizio del mese in cui si trova.
`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 numero intero compreso tra 1 e 53.
`YEAR()`	Restituisce l'anno di un TIMESTAMP.

Funzioni IP
`FORMAT_IP()`	Converte i 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 formato `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 uno 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 è false.

Funzioni matematiche
`ABS()`	Restituisce il valore assoluto dell'argomento.
`ACOS()`	Restituisce l'arco coseno dell'argomento.
`ACOSH()`	Restituisce l'arco coseno iperbolico dell'argomento.
`ASIN()`	Restituisce l'arcoseno dell'argomento.
`ASINH()`	Restituisce il seno iperbolico dell'arco dell'argomento.
`ATAN()`	Restituisce l'arco tangente dell'argomento.
`ATANH()`	Restituisce la tangente iperbolica ad arco dell'argomento.
`ATAN2()`	Restituisce l'arco tangente dei due argomenti.
`CEIL()`	Arrotonda per eccesso 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 per difetto l'argomento 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 Base10 dell'argomento.
`PI()`	Restituisce la costante π.
`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 di espressione regolare
`REGEXP_MATCH()`	Restituisce true se l'argomento corrisponde all'espressione regolare.
`REGEXP_EXTRACT()`	Restituisce la parte dell'argomento che corrisponde al gruppo di acquisizione all'interno dell'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 qualsiasi dei valori è NULL.
`expr CONTAINS 'str'`	Restituisce `true` se `expr` contiene l'argomento di stringa specificato.
`INSTR()`	Restituisce l'indice in base una 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 i 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 nella 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()`	Esegue query nelle tabelle i cui nomi corrispondono a un predicato specificato.

Funzioni URL
`HOST()`	Data un URL, restituisce il nome host come stringa.
`DOMAIN()`	Data 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 di finestra
`AVG()` `COUNT(*)` `COUNT([DISTINCT])` `MAX()` `MIN()` `STDDEV()` `SUM()`	Si tratta della stessa operazione delle funzioni di aggregazione corrispondenti, ma che vengono calcolate in base a 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 frame 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 del percentile rispetto alla finestra ...
`PERCENTILE_DISC()`	Restituisce il valore più vicino al percentile dell'argomento sulla finestra.
`RANK()`	Restituisce il rango intero di un valore in un gruppo di valori.
`RATIO_TO_REPORT()`	Restituisce il rapporto di ogni 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 è true 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 firmata a 64 bit ...
`IF()`	Se il primo argomento è true, restituisce il secondo argomento, altrimenti il terzo argomento.
`POSITION()`	Restituisce la posizione sequenziale in base una sola dell'argomento.
`SHA1()`	Restituisce un hash SHA1 nel formato BYTES.
`SOME()`	Restituisce true se l'argomento è true per almeno uno dei relativi input.
`TO_BASE64()`	Converte l'argomento BYTES in una stringa codificata in Base64.

Sintassi delle query

Nota: le parole chiave non fanno distinzione tra maiuscole e minuscole. In questo documento, le parole chiave come SELECT sono in maiuscolo a scopo illustrativo.

Clausola SELECT

La clausola SELECT specifica un elenco di espressioni da calcolare. Le espressioni nella clausola SELECT possono contenere nomi di campo, valori letterali e chiamate di funzione (incluse funzioni aggregate e funzioni finestra), nonché combinazioni dei tre. L'elenco delle espressioni è separato da virgole.

A ogni espressione è possibile assegnare un alias aggiungendo uno spazio seguito da un identificatore dopo l'espressione. La parola chiave AS facoltativa può essere aggiunta tra l'espressione e l'alias per una migliore leggibilità. È possibile fare riferimento agli alias definiti in una clausola SELECT nelle clausole GROUP BY, HAVING e ORDER BY della query, ma non nelle clausole FROM, WHERE o OMIT RECORD IF o in altre espressioni nella stessa clausola SELECT.

Note:

Se utilizzi una funzione aggregata nella clausola SELECT, devi utilizzare una funzione aggregata in tutte le espressioni oppure la query deve avere una clausola GROUP BY che includa tutti i campi non aggregati nella clausola

SELECT

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 eseguire l'escape delle parole riservate in modo da utilizzarle come nome e alias di campo. Ad esempio, se hai una colonna denominata "partition", che è una parola riservata nella sintassi di BigQuery, le query che fanno riferimento a quel campo avranno esito negativo e verranno visualizzati messaggi di errore oscuri, a meno che tu non utilizzi l'escape con parentesi quadre:
```
SELECT [partition] FROM ...
```

Esempio

Questo esempio definisce gli alias nella clausola SELECT e fa riferimento a uno di questi nella clausola ORDER BY. Nota che non è possibile fare riferimento alla colonna word utilizzando word_alias nella clausola WHERE; è necessario farvi riferimento in base al nome. Inoltre, l'alias len non è visibile nella clausola WHERE. Sarebbe visibile a una clausola 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 funzioni aggregate

aggregate_function WITHIN RECORD [ [ AS ] alias ]

La parola chiave WITHIN determina l'aggregazione della funzione aggregata in base ai valori ripetuti all'interno di ciascun record. Per ogni record di input, verrà prodotto esattamente un output aggregato. Questo tipo di aggregazione è denominato aggregazione mirata. Poiché l'aggregazione con ambito produce output per ogni record, le espressioni non aggregate possono essere selezionate insieme alle espressioni aggregate con ambito senza utilizzare una clausola GROUP BY.

Più comunemente, utilizzerai l'ambito RECORD quando usi l'aggregazione mirata. Se hai uno schema nidificato e ripetuto molto complesso, potresti dover eseguire aggregazioni negli ambiti dei record secondari. A questo scopo, sostituisci la parola chiave RECORD nella sintassi riportata sopra con il nome del nodo nello schema in cui vuoi che venga eseguita l'aggregazione. Per maggiori informazioni su questo comportamento avanzato, consulta la sezione Gestire i dati.

Esempio

Questo esempio esegue un'aggregazione COUNT con ambito, quindi filtra e ordina i 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 la query. Le query BigQuery possono essere eseguite direttamente sulle tabelle, su sottoquery, su tabelle unite e su tabelle modificate da operatori per scopi speciali descritti di seguito. È possibile eseguire query sulle combinazioni di queste origini dati utilizzando la virgola, che è l'operatore UNION ALL in BigQuery.

Fare riferimento alle tabelle

Quando fai riferimento a una tabella, è necessario specificare sia datasetId sia tableId. project_name è facoltativo. Se project_name non è specificato, BigQuery utilizza il progetto corrente per impostazione predefinita. Se il nome del progetto include un trattino, devi racchiudere l'intero riferimento della tabella tra parentesi.

Esempio

[my-dashed-project:dataset1.tableName]

Alle tabelle è possibile assegnare un alias aggiungendo uno spazio seguito da un identificatore dopo il nome della tabella. La parola chiave facoltativa AS può essere aggiunta tra tableId e l'alias per una migliore leggibilità.

Quando fai riferimento alle colonne di una tabella, puoi utilizzare il nome semplice della colonna oppure puoi anteporre al nome della colonna l'alias, se ne hai specificato uno, oppure datasetId e tableId, purché non sia stato specificato alcun project_name. Il project_name non può essere incluso nel prefisso della colonna perché i due punti non sono consentiti nei nomi dei campi.

Esempi

Questo esempio fa riferimento a una colonna senza prefisso della tabella.

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare];

Questo esempio fa precedere il nome della colonna da datasetId e tableId. Nota che 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;

Questo esempio precede il nome della colonna con 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. Le espressioni calcolate nella clausola SELECT della sottoquery sono disponibili per la query esterna proprio come sarebbero disponibili le colonne di una tabella.

Le sottoquery possono essere utilizzate per calcolare aggregazioni e altre espressioni. L'intervallo completo di operatori SQL è disponibile nella sottoquery. Ciò significa che una sottoquery può contenere a sua volta altre sottoquery, che possono eseguire unioni e aggregazioni di raggruppamento e così via.

Virgola come `UNION ALL`

A differenza di GoogleSQL, l'SQL precedente utilizza la virgola come operatore UNION ALL anziché come operatore CROSS JOIN. Si tratta di un comportamento legacy che si è evoluto perché storicamente BigQuery non supportava CROSS JOIN e gli utenti di BigQuery dovevano regolarmente scrivere query UNION ALL. In GoogleSQL, le query che eseguono unioni sono particolarmente complesse. L'utilizzo della virgola come operatore di unione consente di scrivere query di questo tipo in modo molto più 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;

Le query che uniscono un numero elevato di tabelle in genere vengono eseguite più lentamente delle query che elaborano la stessa quantità di dati da una singola tabella. La differenza di prestazioni può arrivare fino a 50 ms per tabella aggiuntiva. Una singola query può unire al massimo 1000 tabelle.

Funzioni con caratteri jolly nella tabella

Il termine funzione carattere jolly della tabella si riferisce a un tipo speciale di funzione univoco di BigQuery. Queste funzioni vengono utilizzate nella clausola FROM per creare corrispondenze con una raccolta di nomi di tabelle utilizzando uno dei vari tipi di filtri. Ad esempio, la funzione TABLE_DATE_RANGE può essere utilizzata 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. Per questo motivo, a volte gli utenti di BigQuery devono scrivere query che manipolano la struttura dei record ripetuti. Un modo per farlo è utilizzare l'operatore FLATTEN.

FLATTEN converte un nodo nello schema da ripetuto a facoltativo. Dato un record con uno o più valori per un campo ripetuto, FLATTEN creerà più record, uno per ogni valore nel campo ripetuto. Tutti gli altri campi selezionati dal record vengono duplicati in ogni nuovo record di output. FLATTEN può essere applicato più volte per rimuovere più livelli di ripetizione.

Per ulteriori informazioni ed esempi, consulta la pagina Gestire i dati.

Operatore JOIN

BigQuery supporta più operatori JOIN in ogni clausola FROM. Le operazioni JOIN successive utilizzano i risultati dell'operazione JOIN precedente come input JOIN a sinistra. I campi di qualsiasi input JOIN precedente possono essere utilizzati come chiavi nelle clausole ON degli operatori JOIN successivi.

Tipi JOIN

BigQuery supporta le operazioni INNER, [FULL|RIGHT|LEFT] OUTER e CROSS JOIN. Se non specificato, il valore predefinito è INNER.

Le operazioni CROSS JOIN non consentono clausole ON. CROSS JOIN può restituire una grande quantità di dati e potrebbe comportare una query lenta e inefficiente o una query che supera il numero massimo consentito di risorse per query. Queste query avranno esito negativo e generano un errore. Se possibile, scegli query che non utilizzano CROSS JOIN. Ad esempio, CROSS JOIN viene spesso utilizzato in luoghi in cui le funzioni finestra sarebbero più efficienti.

CIASCUNO modificatore

Il modificatore EACH è un suggerimento che indica a BigQuery di eseguire JOIN utilizzando più partizioni. Ciò è particolarmente utile quando sai che entrambi i lati di JOIN sono di grandi dimensioni. Il modificatore EACH non può essere utilizzato nelle clausole CROSS JOIN.

In molti casi, l'utilizzo di EACH era consigliato, ma ora non è più così. Se possibile, utilizza JOIN senza il modificatore EACH per migliorare le prestazioni. Utilizza JOIN EACH quando la query non è riuscita con un messaggio di errore relativo al superamento delle risorse.

Semi-join e Anti-join

Oltre a supportare JOIN nella clausola FROM, BigQuery supporta anche due tipi di join nella clausola WHERE: semi-join e anti-semi-join. Un semi-join viene specificato utilizzando la parola chiave IN con una sottoquery; anti-join, utilizzando le parole chiave NOT IN.

Esempi

La seguente query utilizza un semi-join per trovare ngram in cui la prima parola nel ngram è anche la seconda parola in un altro ngram che ha "AND" come terza parola nel 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 a 50 anni che hanno partorito nei 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'opzione anti-join. La seguente query è quasi identica all'esempio precedente, ma utilizza NOT IN anziché IN per restituire il numero di donne di età superiore a 50 anni che hanno partorito nei 40 stati con il minor numero di nascite.

#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-join correlati o antisemi. La sottoquery non può fare riferimento a nessun campo della query esterna.
La sottoquery utilizzata in un semi-join deve selezionare esattamente un campo.
I tipi del campo selezionato e il campo utilizzato dalla query esterna nella clausola WHERE devono corrispondere esattamente. BigQuery non eseguirà alcuna coercizione di tipo per i semi-join o antisemi.

Clausola WHERE

La clausola WHERE, a volte chiamata predicato, filtra i record prodotti dalla clausola FROM utilizzando un'espressione booleana. È possibile unire più condizioni da clausole booleane AND e OR, facoltativamente racchiuse tra parentesi (()) per raggrupparle. I campi elencati in una clausola WHERE non devono essere selezionati nella clausola SELECT corrispondente e l'espressione della clausola WHERE non può fare riferimento a espressioni calcolate nella clausola SELECT della query a cui appartiene la clausola WHERE.

Nota:le funzioni di aggregazione non possono essere utilizzate nella clausola WHERE. Utilizza una clausola HAVING e una query esterna se devi filtrare in base all'output di una funzione aggregata.

Esempio

L'esempio seguente utilizza una disgiunzione di espressioni booleane nella clausola WHERE, le due espressioni unite da un operatore OR. Un record di input passerà 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 si differenzia 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ò (e di solito lo fa) utilizzare nella sua condizione funzioni aggregate con ambito.

Oltre a filtrare i record completi, OMIT...IF può specificare un ambito più ristretto per filtrare solo porzioni di un record. Per farlo, utilizza il nome di un nodo non foglia nello schema anziché RECORD nella clausola OMIT...IF. Questa funzionalità viene utilizzata raramente dagli utenti di BigQuery. Puoi trovare ulteriore documentazione su questo comportamento avanzato facendo clic sul link nella documentazione relativa a WITHIN qui sopra.

Se utilizzi OMIT...IF per escludere una parte di un record in un campo ripetuto e la query seleziona anche altri campi che si ripetono in modo indipendente, BigQuery omette una parte 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 delle istruzioni OMIT...IF a GoogleSQL, consulta la pagina relativa alla migrazione a GoogleSQL.

Esempio

Facendo riferimento all'esempio utilizzato per il modificatore WITHIN, OMIT RECORD IF può essere utilizzato per ottenere la stessa funzione usata per WITHIN e HAVING 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 insieme di campi, in modo da poter calcolare aggregazioni di campi correlati. Il raggruppamento si verifica dopo il filtro eseguito nella clausola WHERE, ma prima che vengano calcolate le espressioni nella clausola SELECT. I risultati dell'espressione non possono essere utilizzati come chiavi di gruppo nella 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, viene illustrato come utilizzare gli indici di posizione al posto dei nomi dei campi nelle clausole GROUP BY e 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 raggruppati . A differenza dell'aggregazione basata, l'aggregazione raggruppata è comune nella maggior parte dei sistemi di elaborazione SQL.

Modificatore `EACH`

Il modificatore EACH è un suggerimento che indica a BigQuery di eseguire GROUP BY utilizzando più partizioni. Questo è particolarmente utile se sai che il set di dati contiene un numero elevato di valori distinti per le chiavi di gruppo.

In molti casi, l'utilizzo di EACH era consigliato, ma ora non è più così. L'utilizzo di GROUP BY senza il modificatore EACH di solito offre prestazioni migliori. Utilizza GROUP EACH BY quando la query non è riuscita con un messaggio di errore relativo al superamento delle risorse.

La funzione `ROLLUP`

Quando viene utilizzata la funzione ROLLUP, BigQuery aggiunge al risultato della query altre righe che rappresentano le aggregazioni aggregate. Tutti i campi elencati dopo ROLLUP devono essere racchiusi in una singola serie di parentesi. Nelle righe aggiunte a causa della funzione ROLLUP, NULL indica le colonne per cui è stata aggregata l'aggregazione.

Esempio

Questa query genera conteggi annuali delle nascite di uomini e donne dal set di dati sulla natalità campione.

#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. Tieni presente 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 che sono state aggiunte grazie alla funzione ROLLUP e le righe che hanno effettivamente un valore NULL per la chiave di gruppo.

Esempio

Questa query aggiunge la funzione GROUPING all'esempio precedente per identificare meglio le righe aggiunte grazie alla 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 il risultato restituito 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 clausola GROUP 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 clausola


    GROUP 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 e doppio non è supportato, perché la funzione di uguaglianza per questi tipi non è ben definita.
Poiché il sistema è interattivo, le query che generano un numero elevato di gruppi potrebbero non riuscire. L'utilizzo della funzione TOP anziché GROUP BY potrebbe risolvere alcuni problemi di scalabilità.

Clausola HAVING

La clausola HAVING si comporta esattamente come la clausola WHERE, tranne per il fatto che viene valutata dopo la clausola SELECT, in modo che i risultati di tutte le espressioni calcolate siano visibili alla clausola HAVING. La clausola HAVING può fare riferimento solo agli output della clausola SELECT corrispondente.

Esempio

Questa query calcola le prime parole più comuni nel set di dati di esempio ngram che contengono la lettera a e ricorrono 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 i dati 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 (ordine 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 in modo da poter fare riferimento all'output di qualsiasi espressione calcolata in SELECT. Se a un campo viene assegnato un alias nella clausola SELECT, l'alias deve essere utilizzato nella clausola ORDER BY.

Clausola LIMIT

La clausola LIMIT limita il numero di righe nel set di risultati restituito. Poiché le query di 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 smetterà di elaborare e restituire i risultati quando soddisferà i tuoi requisiti. Ciò può ridurre il tempo di elaborazione di alcune query, ma quando specifichi funzioni aggregate come le clausole COUNT o ORDER BY, l'intero set di risultati deve comunque essere elaborato prima di restituire i risultati. La clausola LIMIT è l'ultima a essere valutata.
Una query con una clausola LIMIT può ancora essere non deterministica se nella query non è presente alcun operatore che garantisca l'ordine del set di risultati di output. Questo perché BigQuery viene eseguito utilizzando un numero elevato di worker paralleli. L'ordine in cui vengono restituiti i job paralleli non è garantito.
La clausola LIMIT non può contenere funzioni; prende solo una costante numerica.

Grammatica delle query

Le singole clausole delle istruzioni SELECT di BigQuery sono descritte in dettaglio sopra. Qui presentiamo la grammatica completa delle istruzioni SELECT 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
    }

Notazione:

Le parentesi quadre "[ ]" indicano clausole facoltative.
Le parentesi graffe "{ }" racchiudono un insieme di opzioni.
La barra verticale "|" indica un operatore logico OR.
Una virgola o una parola chiave seguita da puntini di sospensione all'interno di parentesi quadre "[, ... ]" indica che l'elemento precedente può essere ripetuto in un elenco con il separatore specificato.
Le parentesi "( )" indicano parentesi letterali.

Funzioni di aggregazione

Le funzioni di aggregazione restituiscono valori che rappresentano riepiloghi di insiemi di dati più ampi, per cui queste funzioni sono particolarmente utili per l'analisi dei log. Una funzione aggregata opera in base a una raccolta di valori e restituisce un singolo valore per tabella, gruppo o ambito:

Aggregazione della tabella
Utilizza una funzione aggregata per riepilogare tutte le righe idonee nella 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 mirata
Questa funzionalità si applica solo alle tabelle con campi nidificati.
Utilizza una funzione aggregata e la parola chiave WITHIN per aggregare i valori ripetuti all'interno di 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 un nodo (campo ripetuto in una riga). Le funzioni di aggregazione operano sui valori all'interno dell'ambito e restituiscono risultati aggregati per ogni 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 campionaria 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 ... non aggiunge 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'n-esimo valore sequenziale ...
`QUANTILES()`	Calcola il minimo, il massimo e i quantili approssimativi ...
`STDDEV()`	Restituisce la deviazione standard ...
`STDDEV_POP()`	Calcola la deviazione standard della popolazione ...
`STDDEV_SAMP()`	Calcola la deviazione standard del campione ...
`SUM()`	Restituisce la somma totale dei valori ...
`TOP() ... COUNT(*)`	Restituisce i primi record max_records per 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 del campione dei valori ...

AVG(numeric_expr)

Restituisci la media dei valori per un gruppo di righe calcolato da numeric_expr. Le righe con un valore NULL non vengono incluse nel calcolo.

BIT_AND(numeric_expr)

Restituisci il risultato di un'operazione AND a livello di bit tra ogni istanza di numeric_expr in tutte le righe. I valori NULL vengono ignorati. Questa funzione restituisce NULL se tutte le istanze di numeric_expr restituiscono NULL.

BIT_OR(numeric_expr)

Restituisci il risultato di un'operazione OR a livello di bit tra ogni istanza di numeric_expr in tutte le righe. I valori NULL vengono ignorati. Questa funzione restituisce NULL se tutte le istanze di numeric_expr restituiscono NULL.

BIT_XOR(numeric_expr)

Restituisci il risultato di un'operazione XOR a livello di bit tra ogni istanza di numeric_expr in tutte le righe. I valori NULL vengono ignorati. Questa funzione restituisce NULL se tutte le istanze di numeric_expr restituiscono NULL.

CORR(numeric_expr, numeric_expr)

Restituisci il coefficiente di correlazione Pearson di un insieme di coppie di numeri.

COUNT(*)

Restituisce il numero totale di valori (NULL e non-NULL) nell'ambito della funzione. A meno che non utilizzi COUNT(*) con la funzione TOP, è meglio specificare esplicitamente il campo da conteggiare.

COUNT([DISTINCT] field [, n])

Restituisce 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 per DISTINCT è un'approssimazione statistica e non è garantito che sia esatto.

Usa EXACT_COUNT_DISTINCT() per una risposta esatta.

Se richiedi una maggiore precisione da COUNT(DISTINCT), puoi specificare un secondo parametro, n, che fornisce la soglia al di sotto della quale sono garantiti risultati esatti. Per impostazione predefinita, n è 1000, ma se fornisci un valore n più grande, riceverai risultati esatti per COUNT(DISTINCT) fino a quel valore di n. Tuttavia, fornire valori più elevati di n ridurrà la scalabilità di questo operatore e potrebbe aumentare notevolmente i tempi 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, ti consigliamo di utilizzare GROUP EACH BY nei campi pertinenti e poi di applicare COUNT(*). L'approccio GROUP EACH BY è più scalabile, ma potrebbe comportare una leggera penalizzazione iniziale in termini di prestazioni.

COVAR_POP(numeric_expr1, numeric_expr2)

Calcola la covarianza della popolazione dei valori calcolati da numeric_expr1 e numeric_expr2.

COVAR_SAMP(numeric_expr1, numeric_expr2)

Calcola la covarianza del campione dei valori calcolati da numeric_expr1 e numeric_expr2.

EXACT_COUNT_DISTINCT(field)

Restituisce il numero esatto di valori distinti, diversi da NULL, per il campo specificato. Per migliorare la scalabilità e le prestazioni, utilizza COUNT(DISTINCT field).

FIRST(expr)

Restituisce il primo valore sequenziale nell'ambito della funzione.

GROUP_CONCAT('str' [, separator])

Concatena più stringhe in un'unica stringa, dove ogni valore è separato dal parametro facoltativo separator. Se separator 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 le virgolette doppie. Ad esempio, la stringa a"b restituisce "a""b". Usa GROUP_CONCAT_UNQUOTED se preferisci che queste stringhe non vengano restituite con le 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, dove ogni valore è separato dal parametro facoltativo separator. Se separator 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 stringa a"b restituisce a"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)

Restituisce il valore massimo nell'ambito della funzione.

MIN(field)

Restituisce 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 valore x distinto e contiene un campo ripetuto per tutti i valori y accoppiati a x nell'input della query. La funzione NEST richiede una clausola GROUP 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 funzione NEST quando usi una selezione secondaria che produce risultati intermedi per l'uso immediato dalla stessa query.

NTH(n, field)

Restituisce il n° valore sequenziale nell'ambito della funzione, dove n è una costante. La funzione NTH inizia a contare da 1, quindi non esiste un termine zero. Se l'ambito della funzione ha meno di n valori, la funzione restituisce NULL.

QUANTILES(expr[, buckets])

Calcola il valore minimo, massimo e i quantili approssimativi per l'espressione di input. I valori di input NULL vengono ignorati. Un input vuoto o NULL restituisce un output di tipo NULL. Il numero di quantili calcolati è controllato con il parametro facoltativo buckets, che include il numero minimo e massimo nel conteggio. Per calcolare approssimativamente N-riquadro, utilizza N+1 buckets. Il valore predefinito di buckets è 100. Nota: il valore predefinito 100 non stima i percentili. Per stimare i percentili, utilizza almeno 101 buckets. Se specificato esplicitamente, buckets deve essere almeno pari a 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 determinato quantile, ma ricorda che NTH è basato su 1 e che QUANTILES restituisce il quantile minimo ("0°") nella prima posizione e il massimo ("100° percentile" o "n-tile" N) nell'ultima posizione. Ad esempio, NTH(11, QUANTILES(expr, 21)) stima la mediana di expr, mentre NTH(20, QUANTILES(expr, 21)) stima il 19° vigintile (95° percentile) di expr. 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%, utilizza 1001 bucket invece di 21 e modifica l'argomento in base alla funzione NTH di conseguenza. Per calcolare la mediana con un errore di 0,1%, utilizza NTH(501, QUANTILES(expr, 1001)); per il 95° percentile con un errore di 0,1%, utilizza NTH(951, QUANTILES(expr, 1001)).

STDDEV(numeric_expr)

Restituisci la deviazione standard dei valori calcolati da numeric_expr. Le righe con valore NULL non vengono incluse nel calcolo. La funzione STDDEV è un alias di STDDEV_SAMP.

STDDEV_POP(numeric_expr)

Calcola la deviazione standard della popolazione del valore calcolato da numeric_expr. Utilizza STDDEV_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 invece STDDEV_SAMP(). Per ulteriori informazioni sulla differenza tra la popolazione e la deviazione standard del campione, consulta la pagina dedicata alla deviazione standard su Wikipedia.

STDDEV_SAMP(numeric_expr)

Calcola la deviazione standard del campione del valore calcolato da numeric_expr. Utilizza STDDEV_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 invece STDDEV_POP(). Per ulteriori informazioni sulla differenza tra la popolazione e la deviazione standard del campione, consulta la pagina dedicata alla deviazione standard su Wikipedia.

SUM(field)

Restituisce la somma totale dei valori nell'ambito della funzione. Da utilizzare solo con tipi di dati numerici.

TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)

Restituisce i primi max_records record per frequenza. Vedi la descrizione SUPERIORE qui sotto per i dettagli.

UNIQUE(expr)

Restituisci l'insieme di valori univoci non NULL nell'ambito della funzione in un ordine non definito. Analogamente a una clausola GROUP BY di grandi dimensioni senza la parola chiave EACH, la query restituisce un errore di tipo "Risorse superate" se sono presenti troppi valori distinti. Tuttavia, a differenza di GROUP BY, la funzione UNIQUE può essere applicata con l'aggregazione mirata, consentendo un funzionamento efficiente sui campi nidificati con un numero limitato di valori.

VARIANCE(numeric_expr)

Calcola la varianza dei valori calcolata da numeric_expr. Le righe con valore NULL non vengono incluse nel calcolo. La funzione VARIANCE è un alias di VAR_SAMP.

VAR_POP(numeric_expr)

Calcola la varianza della popolazione dei valori calcolati da numeric_expr. Per ulteriori informazioni sulla differenza tra la popolazione e la deviazione standard del campione, consulta la pagina dedicata alla 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 la popolazione e la deviazione standard del campione, consulta la pagina dedicata alla deviazione standard su Wikipedia.

Funzione TOP()

TOP è una funzione che è un'alternativa alla clausola GROUP BY. Viene utilizzato come sintassi semplificata per GROUP BY ... ORDER BY ... LIMIT .... In genere, la funzione TOP è più veloce rispetto alla query ... GROUP BY ... ORDER BY ... LIMIT ... completa, ma potrebbe restituire solo risultati approssimativi. Di seguito è riportata la sintassi per la funzione TOP:

TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)

Quando utilizzi TOP in una clausola SELECT, devi includere COUNT(*) come 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 di TOP()

Esempi di query 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() con GROUP BY...ORDER BY...LIMIT

La query restituisce in ordine le prime 10 parole utilizzate più di frequente contenenti "th" e il numero di documenti in cui sono state utilizzate le parole. La query TOP 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';
```

Utilizzare il 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 al mese in Wyoming. La seconda query utilizza il parametro multiplier per moltiplicare i valori di cnt 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 utilizzare TOP, devi includere COUNT(*) nella clausola SELECT.

Esempi avanzati

Deviazione media e 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), utilizza la funzione HAVING. HAVING confronta un valore con un risultato determinato da una funzione di aggregazione, anziché WHERE, 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, restituisce NULL.

Sintassi

Operatore	Descrizione	Esempio
+	Aggiunta	`SELECT 6 + (5 - 1);` Resi: 10
-	Sottrazione	`SELECT 6 - (4 + 1);` Resi: 1
*	Moltiplicazione	`SELECT 6 * (5 - 1);` Resi: 24
/	Divisione	`SELECT 6 / (2 + 2);` Resi: 1.5
%	Modulo	`SELECT 6 % (2 + 2);` Resi: 2

Funzioni a livello di bit

Le funzioni a livello di bit operano a livello di singoli bit e richiedono argomenti numerici. Per ulteriori informazioni sulle funzioni a livello di bit, consulta Operazioni bit.

Altre tre funzioni a livello di bit, BIT_AND, BIT_OR e BIT_XOR, sono documentate nelle funzioni aggregate.

Sintassi

Operatore	Descrizione	Esempio
&	A livello di bit E	`SELECT (1 + 3) & 1` Restituisce: 0
\|	O a livello di bit	`SELECT 24 \| 12` Resi: 28
^	XOR a livello di bit	`SELECT 1 ^ 0` Resi: 1
<<	Spostamento a sinistra a livello di bit	`SELECT 1 << (2 + 2)` Resi: 16
>>	Spostamento a destra a livello di bit	`SELECT (6 + 2) >> 2` Resi: 2
~	A livello di bit, NON	`SELECT ~2` Resi: -3
`BIT_COUNT(<numeric_expr>)`	Restituisce il numero di bit impostati in `<numeric_expr>`.	`SELECT BIT_COUNT(29);` 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 in valore booleano.
`BYTES()`	Trasmetti in byte.
`CAST(expr AS type)`	Converte `expr` in una variabile di tipo `type`.
`FLOAT()`	Trasmetti per raddoppiare.
`HEX_STRING()`	Converti in stringa esadecimale.
`INTEGER()`	Trasmetti a numero intero.
`STRING()`	Trasmetti alla stringa.

BOOLEAN(<numeric_expr>)

Restituisce true se <numeric_expr> è diverso da 0 e non da NULL.
Restituisce false se <numeric_expr> è 0.
Restituisce NULL se <numeric_expr> è NULL.

BYTES(string_expr)

Restituisci string_expr come valore di tipo bytes.

CAST(expr AS type)

Converti expr in una variabile di tipo type.

FLOAT(expr)

Restituisce expr come doppio. expr può essere una stringa come '45.78', ma la funzione restituisce NULL 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 di Unix se expr è un timestamp.

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:

Un confronto tra due espressioni.
Confronto di un'espressione o di un insieme di espressioni con un criterio specifico, ad esempio l'essere in un elenco specificato, essere NULL o essere 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.

Per le funzioni di confronto, puoi utilizzare espressioni numeriche o stringa come argomenti. Le costanti stringa 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 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 a `expr1`, `expr2` o a qualsiasi valore tra parentesi.
`COALESCE()`	Restituisce il primo argomento diverso da NULL.
`GREATEST()`	Restituisce il parametro `numeric_expr` più grande.
`IFNULL()`	Se l'argomento non è null, restituisce l'argomento.
`IS_INF()`	Restituisce `true` se infinito positivo o negativo.
`IS_NAN()`	Restituisce `true` se l'argomento è `NaN`.
`IS_EXPLICITLY_DEFINED()`	deprecato: utilizza `expr IS NOT NULL`.
`LEAST()`	Restituisce il parametro `numeric_expr` dell'argomento più piccolo.
`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 se expr1 è maggiore di expr2.

expr1 < expr2

Restituisce true se expr1 è inferiore a expr2.

expr1 >= expr2

Restituisce true se expr1 è maggiore 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 è maggiore o uguale a expr2 e minore o uguale a expr3.

expr IS NULL

Restituisce true se expr è NULL.

expr IN(expr1, expr2, ...)

Restituisce true se expr corrisponde a expr1, expr2 o a qualsiasi valore tra parentesi. La parola chiave IN è un'abbreviazione efficiente per (expr = expr1 || expr = expr2 || ...). Le espressioni utilizzate con la parola chiave IN devono essere costanti e devono corrispondere al tipo di dati expr. La clausola IN può essere utilizzata anche per creare semi-join e anti-join. Per ulteriori informazioni, vedi Semi-join e Anti-join.

COALESCE(<expr1>, <expr2>, ...)

Restituisci il primo argomento diverso da 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 restituisce NULL.

Per ignorare i valori NULL, utilizza la funzione IFNULL per sostituire i valori NULL con un valore che non influisca sul confronto. Nel seguente esempio di codice, la funzione IFNULL viene utilizzata per modificare i valori di NULL 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, restituisce expr, altrimenti restituisce null_default.

IS_INF(numeric_expr)

Restituisci true se numeric_expr è infinito positivo o negativo.

IS_NAN(numeric_expr)

Restituisci true se numeric_expr è il valore numerico speciale NaN.

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 restituisce NULL

NVL(expr, null_default)

Se expr non è null, restituisce expr, altrimenti restituisce null_default. La funzione NVL è un alias di IFNULL.

Funzioni di data e ora

Le seguenti funzioni consentono la modifica di data e ora per timestamp UNIX, stringhe di data e tipi di dati TIMESTAMP. Per ulteriori informazioni sull'utilizzo del tipo di dati TIMESTAMP, consulta la sezione Utilizzo di TIMESTAMP.

Le funzioni di data e ora compatibili con i timestamp UNIX operano in base al fuso orario UNIX. Le funzioni di data e ora restituiscono i 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 corrente del server nel formato `%H:%M:%S`.
`CURRENT_TIMESTAMP()`	Restituisce l'ora corrente 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 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 numero intero compreso tra 0 e 23.
`MINUTE()`	Restituisce i minuti di un TIMESTAMP come numero intero compreso tra 0 e 59.
`MONTH()`	Restituisce il mese di un TIMESTAMP come 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 di data in un timestamp UNIX in microsecondi.
`QUARTER()`	Restituisce il trimestre dell'anno di un TIMESTAMP come numero intero compreso tra 1 e 4.
`SEC_TO_TIMESTAMP()`	Converte un timestamp UNIX di secondi in un TIMESTAMP.
`SECOND()`	Restituisce i secondi di TIMESTAMP come numero intero compreso tra 0 e 59.
`STRFTIME_UTC_USEC()`	Restituisce una stringa di data nel formato date_format_str.
`TIME()`	Restituisce un elemento TIMESTAMP nel formato `%H:%M:%S`.
`TIMESTAMP()`	Converti una stringa di data in TIMESTAMP.
`TIMESTAMP_TO_MSEC()`	Converte TIMESTAMP in timestamp UNIX in millisecondi.
`TIMESTAMP_TO_SEC()`	Converte TIMESTAMP in timestamp UNIX in secondi.
`TIMESTAMP_TO_USEC()`	Converte TIMESTAMP in timestamp UNIX in microsecondi.
`USEC_TO_TIMESTAMP()`	Converte un timestamp UNIX in microsecondi in TIMESTAMP.
`UTC_USEC_TO_DAY()`	Sposta un timestamp UNIX in microsecondi dall'inizio del giorno in cui si verifica.
`UTC_USEC_TO_HOUR()`	Sposta un timestamp UNIX in microsecondi all'inizio dell'ora in cui si trova.
`UTC_USEC_TO_MONTH()`	Sposta un timestamp UNIX in microsecondi all'inizio del mese in cui si trova.
`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 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: 2013-02-01

CURRENT_TIME()

Restituisce una stringa leggibile dell'ora corrente del server nel formato %H:%M:%S.

Esempio:

SELECT CURRENT_TIME();

Restituisce: 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: 2013-02-01 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: 2012-10-01

DATE_ADD(<timestamp>,<interval>, <interval_units>)

Aggiunge l'intervallo specificato a un tipo di dati TIMESTAMP. I possibili valori interval_units sono YEAR, MONTH, DAY, HOUR, MINUTE e SECOND. Se interval è 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: 2017-10-01 02:03:04 UTC

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), -5, "YEAR");

Resi: 2007-10-01 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'));

Restituisce: 466

Esempio:

SELECT DATEDIFF(TIMESTAMP('2011-06-24 12:18:35'), TIMESTAMP('2012-10-02 05:23:48'));

Restituisce: -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 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 come numero intero compreso tra 1 e 366 inclusi. Il numero intero 1 si riferisce all'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);

Restituisce: 2010-05-19 08:58:01.071200

HOUR(<timestamp>)

Restituisce l'ora di un tipo di dati TIMESTAMP come 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 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>)

Converti un timestamp UNIX in millisecondi in un tipo di dati TIMESTAMP.

Esempio:

SELECT MSEC_TO_TIMESTAMP(1349053323000);

Resi: 2012-10-01 01:02:03 UTC

SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)

Resi: 2012-10-01 01:02:04 UTC

NOW()

Restituisce il timestamp UNIX corrente in microsecondi.

Esempio:

SELECT NOW();

Restituisce: 1359685811687920

PARSE_UTC_USEC(<date_string>)

Converte una stringa di data in un timestamp UNIX in microsecondi. date_string deve avere il formato YYYY-MM-DD HH:MM:SS[.uuuuuu]. La parte frazionaria del secondo può contenere fino a sei cifre o può 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 inclusi.

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: 2012-12-20 02:03:07 UTC

SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));

Resi: 2012-12-20 02:03:07 UTC

SECOND(<timestamp>)

Restituisce i secondi di un tipo di dati TIMESTAMP come numero intero compreso tra 0 e 59 inclusi.

Durante un secondo intercalare, l'intervallo di numeri 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 alla data (come / e -) e caratteri speciali accettati dalla 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 per intervalli di tempo, ad esempio recuperare tutti i dati per un determinato mese, perché le funzioni sono più efficienti.

Esempio:

SELECT STRFTIME_UTC_USEC(1274259481071200, "%Y-%m-%d");

Resi: 2010-05-19

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 di data in un tipo di dati TIMESTAMP.

Esempio:

SELECT TIMESTAMP("2012-10-01 01:02:03");

Resi: 2012-10-01 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>)

Converti un tipo di dati TIMESTAMP in un timestamp UNIX in secondi.

Esempio:

SELECT TIMESTAMP_TO_SEC(TIMESTAMP("2012-10-01 01:02:03"));

Restituisce: 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 invece di 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: 2012-10-01 01:02:03 UTC

SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)

Resi: 2012-10-01 01:02:04 UTC

UTC_USEC_TO_DAY(<unix_timestamp>)

Sposta un timestamp UNIX in microsecondi dall'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);

Restituisce: 1274227200000000

UTC_USEC_TO_HOUR(<unix_timestamp>)

Sposta un timestamp UNIX in microsecondi all'inizio dell'ora in cui si trova.

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);

Restituisce: 1274256000000000

UTC_USEC_TO_MONTH(<unix_timestamp>)

Sposta un timestamp UNIX in microsecondi all'inizio del mese in cui si trova.

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);

Restituisce: 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 imposti day_of_week su 2 (martedì), la funzione restituisce un timestamp UNIX per martedì 08-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 si verifica nel 2010, la funzione restituisce 1274259481071200, la rappresentazione in microsecondi di 2010-01-01 00:00.

Esempio:

SELECT UTC_USEC_TO_YEAR(1274259481071200);

Restituisce: 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 domenica, la settimana 1 ha meno di 7 giorni, mentre la prima domenica dell'anno è il primo giorno della 2.

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 del timestamp con numeri interi in formato leggibile

La seguente query trova i primi cinque momenti in cui è stata eseguita la maggior parte delle revisioni di Wikipedia. Per visualizzare i risultati in un formato leggibile, utilizza la funzione FORMAT_UTC_USEC() di BigQuery, che prende un timestamp, in microsecondi, come input. Questa query moltiplica i timestamp (in secondi) del formato POSIX di Wikipedia 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 |
+----------------------------+----------------+

Bucketing dei risultati per timestamp

È utile utilizzare le funzioni di data e ora per raggruppare i risultati delle query in bucket corrispondenti ad anni, mesi o giorni specifici. L'esempio che segue utilizza la funzione UTC_USEC_TO_MONTH() per visualizzare il numero di caratteri utilizzati da ogni collaboratore di Wikipedia nei commenti delle revisioni al mese.

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;

Resi (troncati):

+--------------------------------+---------+-----------------------+
|      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 e da un formato leggibile.

Sintassi

Funzioni IP
`FORMAT_IP()`	Converte i 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 formato `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 i 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 supportano l'analisi degli indirizzi da e verso il formato leggibile. Queste funzioni funzionano solo sui campi stringa con IP.

Sintassi

FORMAT_PACKED_IP(packed_ip)

Restituisce un indirizzo IP leggibile, nel formato 10.1.5.23 o 2620: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 ti consentono di trovare valori all'interno dei dati JSON archiviati utilizzando espressioni simili a JSONPath.

L'archiviazione dei dati JSON può essere più flessibile rispetto alla 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 il costo di scansione dell'intera stringa, il che è più costoso rispetto a se ogni campo si trova in una colonna separata. Inoltre, la query è più lenta poiché l'intera stringa deve essere analizzata al momento della query. Tuttavia, per gli 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 anziché le funzioni di espressione regolare di BigQuery, poiché le funzioni JSON sono più facili da utilizzare.

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 uno scalare JSON.

JSON_EXTRACT(json, json_path): Seleziona un valore in json in base all'espressione JSONPath json_path. json_path deve essere una costante stringa. Restituisce il valore in formato stringa JSON.
JSON_EXTRACT_SCALAR(json, json_path): Seleziona un valore in json in base all'espressione JSONPath json_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 ospita 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 è false.

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 se un'espressione è vera e l'altra è NULL.

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 se un'espressione è falsa e l'altra è NULL.

NOT expr

Restituisce true se l'espressione è false.
Restituisce false se l'espressione è true.
Restituisce NULL se l'espressione è NULL.

Puoi utilizzare NOT con altre funzioni come operatore di negazione. Ad esempio, NOT IN(expr1, expr2) o IS NOT NULL.

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 indefinito, l'operazione restituisce NULL.

Sintassi

Funzioni matematiche
`ABS()`	Restituisce il valore assoluto dell'argomento.
`ACOS()`	Restituisce l'arco coseno dell'argomento.
`ACOSH()`	Restituisce l'arco coseno iperbolico dell'argomento.
`ASIN()`	Restituisce l'arcoseno dell'argomento.
`ASINH()`	Restituisce il seno iperbolico dell'arco dell'argomento.
`ATAN()`	Restituisce l'arco tangente dell'argomento.
`ATANH()`	Restituisce la tangente iperbolica ad arco dell'argomento.
`ATAN2()`	Restituisce l'arco tangente dei due argomenti.
`CEIL()`	Arrotonda per eccesso 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 per difetto l'argomento 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 Base10 dell'argomento.
`PI()`	Restituisce la costante π.
`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): Restituisce il coseno iperbolico ad arco dell'argomento.
ASIN(numeric_expr): Restituisci l'arcoseno dell'argomento.
ASINH(numeric_expr): Restituisci l'arco seno iperbolico dell'argomento.
ATAN(numeric_expr): Restituisce l'arco tangente dell'argomento.
ATANH(numeric_expr): Restituisci la tangente arcata iperbolica dell'argomento.
ATAN2(numeric_expr1, numeric_expr2): Restituisce l'arco tangente dei due argomenti.
CEIL(numeric_expr): Arrotonda per eccesso 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): Restituisce il risultato dell'elevamento della costante "e", la base del logaritmo naturale, alla potenza di numeric_expr.
FLOOR(numeric_expr): Arrotonda per difetto l'argomento 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): Restituisce il logaritmo in Base-2 dell'argomento.
LOG10(numeric_expr): Restituisce il logaritmo in Base10 dell'argomento.
PI(): Restituisci la costante π. La funzione PI() richiede parentesi per indicare che è una funzione, ma non accetta argomenti tra queste parentesi. Puoi usare PI() come una costante con le funzioni matematiche e aritmetiche.
POW(numeric_expr1, numeric_expr2): Restituisce il risultato dell'elevamento di numeric_expr1 alla potenza di numeric_expr2.
RADIANS(numeric_expr): Restituisci numeric_expr, convertito da gradi a radianti. (nota che π radianti equivale a 180 gradi).
RAND([int32_seed]): Restituisci un valore in virgola mobile casuale compreso 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 clausola LIMIT. Se int32_seed non è specificato, BigQuery utilizza il timestamp attuale 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 su 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 approssimativa del cerchio di delimitazione

Restituisci una raccolta di massimo 100 punti all'interno di un cerchio approssimativo determinato dall'utilizzo della Spherical Law of Cosines, incentrata sul Denver Colorado (39,73, -104,98). Questa query utilizza le funzioni matematiche e trigonometriche di BigQuery, come PI(), SIN() e COS().

Poiché la Terra non è una sfera assoluta e longitudine e latitudine convergeno 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 di espressione regolare

BigQuery fornisce il supporto delle espressioni regolari tramite la libreria re2. Consulta la documentazione per conoscere la sintassi delle espressioni regolari.

Tieni presente che le espressioni regolari sono corrispondenze globali; per iniziare la corrispondenza all'inizio di una parola devi utilizzare il carattere ^.

Sintassi

Funzioni di espressione regolare
`REGEXP_MATCH()`	Restituisce true se l'argomento corrisponde all'espressione regolare.
`REGEXP_EXTRACT()`	Restituisce la parte dell'argomento che corrisponde al gruppo di acquisizione all'interno dell'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 all'interno dell'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 che corrisponde 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

Risultato del filtro impostato in base alla corrispondenza con espressione regolare

Le funzioni di espressione regolare di BigQuery possono essere utilizzate per filtrare i risultati in una clausola WHERE e per visualizzare i risultati in SELECT. L'esempio seguente combina entrambi questi casi d'uso di 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

Mentre le funzioni di espressione regolare di BigQuery funzionano solo per i dati di tipo 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 intero corpus_date a una stringa, che viene poi modificata da REGEXP_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 delle stringhe. Le costanti stringa devono essere racchiuse tra virgolette singole o doppie. Le funzioni stringa sono sensibili alle maiuscole per impostazione predefinita. Puoi aggiungere IGNORE CASE alla fine di una query per abilitare la corrispondenza senza distinzione tra maiuscole e minuscole. 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 la funzionalità di espressione regolare, utilizza le funzioni di espressione regolare.

Sintassi

Funzioni di stringa
`CONCAT()`	Restituisce la concatenazione di due o più stringhe oppure NULL se uno qualsiasi dei valori è NULL.
`expr CONTAINS 'str'`	Restituisce `true` se `expr` contiene l'argomento di stringa specificato.
`INSTR()`	Restituisce l'indice in base una 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 i 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 e str2 è Script, CONCAT restituisce JavaScript.

expr CONTAINS 'str'

Restituisce true se expr contiene l'argomento di stringa specificato. Questo è un confronto sensibile alle maiuscole.

INSTR('str1', 'str2')

Restituisce l'indice in base una sola 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) restituisce sea.

LENGTH('str')

Restituisce un valore numerico per la lunghezza della stringa. Esempio: se str è '123456', LENGTH restituisce 6.

LOWER('str')

Restituisce la stringa originale con tutti i caratteri in minuscolo.

LPAD('str1', numeric_expr, 'str2')

Inserisci i tamponi str1 a sinistra con str2, ripetendo il comando str2 finché la stringa del risultato non corrisponde esattamente a numeric_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) restituisce land.

RPAD('str1', numeric_expr, 'str2')

Inserisci str1 a destra con str2, ripetendo il comando str2 finché la stringa del risultato non corrisponde esattamente a numeric_expr caratteri. Esempio: RPAD('1', 7, '?') restituisce 1??????.

RTRIM('str1' [, str2])

Rimuove i caratteri finali dal lato destro di str1. Se str2 viene omesso, RTRIM rimuove gli spazi finali da str1. Altrimenti, 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'])

Divide una stringa in sottostringhe ripetute. Se delimiter è specificato, la funzione SPLIT suddivide str in sottostringhe, utilizzando delimiter come delimitatore.

SUBSTR('str', index [, max_len])

Restituisce una sottostringa di str, a partire da index. Se viene utilizzato il parametro facoltativo max_len, la stringa restituita può contenere al massimo max_len caratteri. Il conteggio inizia da 1, quindi il primo carattere della stringa si trova nella posizione 1 (non zero). Se index è 5, la sottostringa inizia con il quinto carattere da sinistra in str. Se index è -4, la sottostringa inizia con il quarto carattere da destra in str. Esempio: SUBSTR('awesome', -4, 4) restituisce la sottostringa some.

UPPER('str')

Restituisce la stringa originale con tutti i caratteri in maiuscolo.

Utilizzare l'escape dei caratteri speciali nelle stringhe

Per eseguire l'escape 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 prima delle barre, delle virgolette singole e delle virgolette doppie.
Usa sequenze in stile C ('\a', '\b', '\f', '\n', '\r', '\t', e '\v') per 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 nella tabella

Le funzioni con caratteri jolly nelle tabelle consentono di eseguire agevolmente query sui dati di un insieme specifico di tabelle. Una funzione con caratteri jolly in una tabella equivale a un'unione separata da virgole di tutte le tabelle corrispondenti alla funzione con caratteri jolly. Quando utilizzi una funzione con caratteri jolly, BigQuery accede e addebita solo le tabelle che corrispondono al carattere jolly. Le funzioni con caratteri jolly nella tabella sono specificate nella clausola FROM della query.

Se in una query utilizzi funzioni con caratteri jolly, queste non devono più essere incluse tra parentesi. Ad esempio, alcuni degli esempi riportati di seguito usano le parentesi, mentre altri no.

I risultati memorizzati nella cache non sono supportati per le query su più tabelle che utilizzano una funzione con caratteri jolly (anche se è selezionata l'opzione Usa risultati memorizzati nella cache). Se si esegue la stessa query con caratteri jolly più volte, ti viene addebitato un costo per ogni query.

Sintassi

Funzioni con caratteri jolly nella 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()`	Esegue query nelle tabelle i cui nomi corrispondono a un predicato specificato.

TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)

Esegue query sulle tabelle giornaliere che si sovrappongono con l'intervallo di tempo compreso tra <timestamp1> e <timestamp2>.

I nomi delle tabelle devono avere il seguente formato: <prefix><day>, dove <day> è nel formato YYYYMMDD.

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: recupero di 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: genera le tabelle in un intervallo di due giorni fino a "ora"

Questo esempio 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 una tabella giornaliera non è presente nella sequenza, l'azione TABLE_DATE_RANGE_STRICT non riesce e restituisce un errore Not Found: Table <table_name>.

Esempio: errore su 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 un errore "Non trovato" per la tabella "people20140326".

TABLE_QUERY(dataset, expr)

Query sulle tabelle i cui nomi corrispondono al valore expr specificato. Il parametro expr 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

Questo esempio 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()`	Data un URL, restituisce il nome host come stringa.
`DOMAIN()`	Data 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'): Specificato un URL, restituisce il nome host come stringa. Esempio: HOST('http://www.google.com:80/index.html') restituisce "www.google.com"
DOMAIN('url_str'): Specificato un URL, restituisce il dominio come stringa. Esempio: DOMAIN('http://www.google.com:80/index.html') restituisce "google.com".
TLD('url_str'): Specificando un URL, restituisce il dominio di primo livello più il dominio di qualsiasi 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 la ricerca DNS inversa, quindi se le chiami utilizzando un indirizzo IP, le funzioni restituiranno segmenti dell'indirizzo IP anziché segmenti del nome host.
Tutte le funzioni di analisi degli URL prevedono caratteri minuscoli. I caratteri maiuscoli nell'URL generano un risultato NULL o comunque errato. Potresti passare l'input a questa funzione tramite LOWER() se i tuoi dati presentano un uso misto di lettere maiuscole e minuscole.

Esempio avanzato

Analizzare i nomi di dominio dai dati degli URL

Questa query utilizza la funzione DOMAIN() per restituire i domini più popolari elencati come home page del repository su GitHub. Nota l'utilizzo di HAVING per filtrare i record utilizzando il risultato della funzione DOMAIN(). Questa è una funzione utile per determinare le informazioni dei referrer 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 relative ai domini di primo livello, utilizza la funzione TLD(). Questo esempio mostra i principali domini di primo livello che non sono 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 di finestra

Le funzioni di finestra, note anche come funzioni di analisi, consentono i calcoli su un sottoinsieme specifico, o "finestra", di un set di risultati. Le funzioni a finestra semplificano la creazione di report che includono analisi complesse come medie finali e totali correnti.

Ogni funzione finestra richiede una clausola OVER che specifichi la parte superiore e inferiore della finestra. I tre componenti della clausola OVER (partizionamento, ordinamento e framing) forniscono un controllo aggiuntivo sulla finestra. Il partizionamento consente di suddividere i dati di input in gruppi logici che hanno una caratteristica comune. L'ordinamento consente di ordinare i risultati all'interno di una partizione. L'inquadratura consente di creare un frame di finestra scorrevole all'interno di una partizione che si sposta in base alla riga corrente. Puoi configurare le dimensioni del frame 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; verrà creata una partizione per ogni insieme di valori distinto per queste colonne, in modo simile a una clausola GROUP BY. Se PARTITION BY viene omesso, la partizione di base corrisponderà a tutte le righe nell'input della funzione finestra.

La clausola PARTITION BY consente inoltre alle funzioni di finestra di partizionare i dati e caricare in contemporanea l'esecuzione. Se vuoi utilizzare una funzione di finestra con allowLargeResults o se intendi applicare ulteriori unioni o aggregazioni all'output della funzione finestra, usa PARTITION BY per caricare in contemporanea l'esecuzione.

JOIN EACH e GROUP EACH BY non possono essere utilizzate nell'output delle funzioni di finestra. Per generare risultati della query di grandi dimensioni quando utilizzi le funzioni di finestra, devi usare PARTITION BY.

ORDER BY

Ordina la partizione. Se ORDER BY non è presente, non è garantito alcun ordinamento predefinito. L'ordinamento avviene a livello di partizione, prima che venga applicata qualsiasi clausola relativa al frame della finestra. Se specifichi una finestra RANGE, devi aggiungere una clausola ORDER BY. L'ordine predefinito è ASC.

ORDER BY è facoltativo in alcuni casi, ma alcune funzioni di finestra, come rank() o dense_rank(), richiedono la clausola.

Se utilizzi ORDER BY senza specificare ROWS o RANGE, ORDER BY implica che la finestra si estende dall'inizio della partizione alla riga corrente. In assenza di una clausola ORDER BY, la finestra è l'intera partizione.

<window-frame-clause>

{ROWS | RANGE} {BETWEEN <start> AND <end> | <start> | <end>}

Un sottoinsieme della partizione su cui operare. Può essere delle stesse dimensioni della partizione o di dimensioni inferiori. Se utilizzi ORDER BY senza window-frame-clause, il frame della finestra predefinito è RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Se ometti sia ORDER BY sia window-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 5 righe precedenti di valori degli stipendi, dovrai eseguire la query SUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW). L'insieme di righe in genere include la riga corrente, ma non è obbligatorio.
RANGE: definisce una finestra in termini di un intervallo di valori in una determinata colonna, rispetto al valore della colonna nella riga corrente. Funziona solo su numeri e date, dove i valori della data sono numeri interi semplici (microsecondi dall'epoca). Le righe vicine con lo stesso valore sono chiamate righe peer. Le righe peer di CURRENT ROW sono incluse in un frame di finestra che specifica CURRENT ROW. Ad esempio, se specifichi la fine della finestra come CURRENT ROW e la riga successiva nella finestra ha lo stesso valore, questa verrà inclusa nel calcolo della funzione.
BETWEEN <start> AND <end>: un intervallo, che include le righe di inizio e fine. L'intervallo non deve includere la riga corrente, ma <start> deve precedere o uguale a <end>.
<start> - Specifica l'offset iniziale per questa finestra, in relazione alla riga corrente. Sono supportate le seguenti opzioni:
```
{UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
```
dove <expr> è un numero intero positivo, PRECEDING indica un numero di riga o un valore di intervallo precedente e FOLLOWING indica un numero di riga o un valore di intervallo successivo. UNBOUNDED PRECEDING indica la prima riga della partizione. Se l'inizio precede la finestra, verrà impostato sulla prima riga della partizione.
<end> - Specifica l'offset finale per questa finestra, in relazione alla riga corrente. Sono supportate le seguenti opzioni:
```
{UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
```
dove <expr> è un numero intero positivo, PRECEDING indica un numero di riga o un valore di intervallo precedente e FOLLOWING indica un numero di riga o un valore di intervallo successivo. UNBOUNDED FOLLOWING indica l'ultima riga della partizione. Se la fine è oltre la fine della finestra, verrà impostata sull'ultima riga della partizione.

A differenza delle funzioni di aggregazione, che comprimono molte righe di input in una riga di output, le funzioni di finestra restituiscono una riga di output per ogni riga di input. Questa funzionalità semplifica la creazione di query che calcolano i totali correnti e le 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 nella riga corrente e nella riga precedente. Il frame della finestra comprende 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 di finestra
`AVG()` `COUNT(*)` `COUNT([DISTINCT])` `MAX()` `MIN()` `STDDEV()` `SUM()`	Si tratta della stessa operazione delle funzioni di aggregazione corrispondenti, ma che vengono calcolate in base a 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 frame 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 del percentile rispetto alla finestra ...
`PERCENTILE_DISC()`	Restituisce il valore più vicino al percentile dell'argomento sulla finestra.
`RANK()`	Restituisce il rango intero di un valore in un gruppo di valori.
`RATIO_TO_REPORT()`	Restituisce il rapporto di ogni 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 di finestra eseguono la stessa operazione delle funzioni di aggregazione corrispondenti, ma vengono calcolate in una finestra definita dalla clausola OVER.

Un'altra differenza significativa è che la funzione COUNT([DISTINCT] field) produce risultati esatti quando viene utilizzata come funzione finestra, con un comportamento simile alla funzione aggregata di EXACT_COUNT_DISTINCT().

Nella query di esempio, la clausola ORDER BY determina il calcolo della finestra dall'inizio della partizione alla riga attuale, il 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	2 Kinghenryvi	18	18
1590	1 Kinghenryvi	24	42
1590	3Khenryvi	40	82

CUME_DIST()

Restituisce un doppio che indica la distribuzione cumulativa di un valore in un gruppo di valori, calcolata utilizzando la formula <number of rows preceding or tied with the current row> / <total rows>. I valori uguali restituiscono lo stesso valore di distribuzione cumulativo.

Questa funzione finestra richiede ORDER BY nella clausola OVER.

#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
dispiacere	4	0,8
strumenti	4	0,8
circumstance	3	1,0

DENSE_RANK()

Restituisce il rango intero di un valore in un gruppo di valori. Il ranking viene calcolato in base a confronti con altri valori del gruppo.

I valori legati vengono visualizzati con la stessa posizione. La posizione del valore successivo viene incrementata di 1. Ad esempio, se due valori corrispondono al ranking 2, il successivo valore di ranking sarà 3. Se preferisci inserire un divario nell'elenco di ranking, utilizza rank().

Questa funzione finestra richiede ORDER BY nella clausola OVER.

#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
dispiacere	4	3
strumenti	4	3
circumstance	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	fv
imperfetta	1	imperfetta

LAG(<expr>[, <offset>[, <default_value>]])

Consente di leggere i dati di una riga precedente all'interno di una finestra. In particolare, LAG() restituisce il valore <expr> per la riga posizionata <offset> prima della riga corrente. Se la riga non esiste, viene restituito <default_value>.

#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	ritardo
fazzoletto	29	null
soddisfazione	5	fazzoletto
dispiacere	4	soddisfazione
strumenti	4	dispiacere
circumstance	3	strumenti

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
imperfetta	1	imperfetta

LEAD(<expr>[, <offset>[, <default_value>]])

Consente di leggere i dati di una riga successiva all'interno di una finestra. In particolare, LEAD() restituisce il valore <expr> per la riga situata <offset> righe dopo quella corrente. Se la riga non esiste, viene restituito <default_value>.

#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	dispiacere
dispiacere	4	strumenti
strumenti	4	circumstance
circumstance	3	null

NTH_VALUE(<expr>, <n>)

Restituisce il valore di <expr> nella posizione <n> del frame della finestra, dove <n> è un indice a base una.

NTILE(<num_buckets>)

Divide una sequenza di righe in <num_buckets> bucket e assegna un numero di bucket corrispondente, sotto forma di numero intero, a ogni riga. La funzione ntile() assegna i numeri dei bucket nel modo più uniforme 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
dispiacere	4	1
strumenti	4	2
circumstance	3	2

PERCENT_RANK()

Restituisce il ranking della riga corrente rispetto alle altre righe della partizione. I valori restituiti sono compresi tra 0 e 1 inclusi. Il primo valore restituito è 0,0.

Questa funzione finestra richiede ORDER BY nella clausola OVER.

#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
dispiacere	4	0.5
strumenti	4	0.5
circumstance	3	1,0

PERCENTILE_CONT(<percentile>)

Restituisce un valore interpolato che verrebbe mappato all'argomento percentile rispetto alla finestra, dopo l'ordinamento in base alla clausola ORDER BY.

Il valore <percentile> deve essere compreso tra 0 e 1.

Questa funzione finestra richiede ORDER BY nella clausola OVER.

#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
dispiacere	4	4
strumenti	4	4
circumstance	3	4

PERCENTILE_DISC(<percentile>)

Restituisce il valore più vicino al percentile dell'argomento sulla finestra.

Il valore <percentile> deve essere compreso tra 0 e 1.

Questa funzione finestra richiede ORDER BY nella clausola OVER.

#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
dispiacere	4	4
strumenti	4	4
circumstance	3	4

RANK()

Restituisce il rango intero di un valore in un gruppo di valori. Il ranking viene calcolato in base a confronti con altri valori del gruppo.

I valori legati vengono visualizzati con la stessa posizione. Il ranking del valore successivo viene incrementato in base al numero di valori associati che si sono verificati prima di questo valore. Ad esempio, se due valori corrispondono al ranking 2, il successivo valore di ranking sarà 4, non 3. Se preferisci che non ci siano lacune nell'elenco di ranking, utilizza dense_rank().

Questa funzione finestra richiede ORDER BY nella clausola OVER.

#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	classifica
fazzoletto	29	1
soddisfazione	5	2
dispiacere	4	3
strumenti	4	3
circumstance	3	5

RATIO_TO_REPORT(<column>)

Restituisce il rapporto di ogni valore alla somma dei valori, come un doppio compreso 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
dispiacere	4	0,08888888888888889
strumenti	4	0,08888888888888889
circumstance	3	0,06666666666666667

ROW_NUMBER()

Restituisce il numero della riga corrente del risultato della query sulla 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
dispiacere	4	3
strumenti	4	4
circumstance	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 è true 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 firmata a 64 bit ...
`IF()`	Se il primo argomento è true, restituisce il secondo argomento, altrimenti il terzo argomento.
`POSITION()`	Restituisce la posizione sequenziale in base una sola dell'argomento.
`SHA1()`	Restituisce un hash SHA1 nel formato BYTES.
`SOME()`	Restituisce true se l'argomento è true per almeno uno dei relativi input.
`TO_BASE64()`	Converte l'argomento BYTES in una stringa codificata in Base64.

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 tua query. Le espressioni WHEN devono essere booleane e tutte le espressioni nelle clausole THEN e ELSE devono essere di tipi compatibili.

CURRENT_USER()

Restituisci l'indirizzo email dell'utente che esegue la query.

EVERY(<condition>)

Restituisci true se condition è true per tutti i suoi input. Quando viene utilizzata con la clausola OMIT IF, questa funzione è utile per le query che coinvolgono campi ripetuti.

FROM_BASE64(<str>)

Converti la stringa di input con codifica 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 definito dalla libreria CityHash (versione 1.0.3). Sono supportate qualsiasi stringa o espressione intera e la funzione rispetta IGNORE CASE per le stringhe, restituendo valori non variabili tra maiuscole e minuscole.

FARM_FINGERPRINT(expr)

Calcola e restituisce un valore di fingerprint firmato a 64 bit dell'input STRING o BYTES utilizzando la funzione Fingerprint64 della libreria open source FarmHash. L'output di questa funzione per un determinato input non cambierà mai e corrisponderà all'output della funzione FARM_FINGERPRINT quando utilizzi GoogleSQL. Consente di rispettare IGNORE CASE per le stringhe, restituendo valori non varianti con maiuscole e minuscole.

IF(condition, true_return, false_return)

Restituisce true_return o false_return, a seconda che condition sia true o false. I valori restituiti possono essere valori letterali o valori derivati dai campi, ma devono essere dello stesso tipo di dati. I valori derivati dal campo non devono essere inclusi nella clausola SELECT.

POSITION(field)

Restituisci la posizione sequenziale singola di field all'interno di un insieme di campi ripetuti.

SHA1(<str>)

Restituisci un hash SHA1, in 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 se condition è true per almeno uno dei suoi input. Quando viene utilizzata con la clausola OMIT IF, questa funzione è utile per le query che coinvolgono campi ripetuti.

TO_BASE64(<bin_data>)

Converti 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 utilizza un blocco CASE/WHEN per raggruppare i risultati nelle categorie "region" in base a un elenco di stati. Se lo stato non compare tra le opzioni in una delle istruzioni WHEN, il valore predefinito dello stato sarà "Nessuno".

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 di selezione secondaria in righe e colonne. Nell'esempio che segue, i risultati della ricerca della maggior parte degli articoli di Wikipedia revisionati che iniziano con il valore "Google" sono organizzati in colonne in cui vengono visualizzati i conteggi delle revisioni se soddisfano vari 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 del set di risultati. Per recuperare un campionamento casuale di valori, usa la funzione HASH per restituire risultati in cui il modulo "n" dell'hash è uguale a zero.

Ad esempio, la seguente query troverà il HASH() del valore di "title" e poi verifica se quel valore modulo "2" è zero. Ciò dovrebbe comportare l'etichettatura "campionata" di circa il 50% dei valori. Per campionare un numero inferiore di valori, aumenta il valore dell'operazione modulo da "2" a un valore maggiore. La query utilizza la funzione ABS in combinazione con HASH, perché HASH può restituire valori negativi e l'operatore modulo con un valore negativo restituisce 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;
```

Funzioni e operatori SQL legacy

Funzioni e operatori supportati

Sintassi delle query

Clausola SELECT

Esempio

Modificatore WITHIN per funzioni aggregate

Esempio

Clausola FROM

Fare riferimento alle tabelle

Esempio

Esempi

Utilizzo delle sottoquery

Virgola come UNION ALL

Funzioni con caratteri jolly nella tabella

Operatore FLATTEN

Operatore JOIN

Tipi JOIN

CIASCUNO modificatore

Semi-join e Anti-join

Esempi

Clausola WHERE

Esempio

Clausola OMIT RECORD IF

Esempio

Clausola GROUP BY

Esempio

Modificatore EACH

La funzione ROLLUP

Esempio

Esempio

Clausola HAVING

Esempio

Clausola ORDER BY

Clausola LIMIT

Grammatica delle query

Funzioni di aggregazione

Sintassi

Funzione TOP()

Esempi di TOP()

Esempi avanzati

Operatori aritmetici

Sintassi

Funzioni a livello di bit

Sintassi

Funzioni di trasmissione

Sintassi

Funzioni di confronto

Sintassi

Funzioni di data e ora

Sintassi

Esempi avanzati

Funzioni IP

Sintassi

Sintassi

Funzioni JSON

Sintassi

Operatori logici

Sintassi

Funzioni matematiche

Sintassi

Esempi avanzati

Funzioni di espressione regolare

Sintassi

Esempi avanzati

Funzioni di stringa

Sintassi

Funzioni con caratteri jolly nella tabella

Sintassi

Funzioni URL

Sintassi

Esempio avanzato

Funzioni di finestra

Sintassi

Altre funzioni

Sintassi

Esempi avanzati

Virgola come `UNION ALL`

Modificatore `EACH`

La funzione `ROLLUP`