Sintassi, funzioni e operatori SQL precedenti

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

Sintassi delle query

Nota:le parole chiave non fanno distinzione tra maiuscole e minuscole. In questo documento, le parole chiave come SELECT sono scritte 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 campi, valori letterali e chiamate di funzioni (incluse le funzioni aggregate e le funzioni finestra), nonché combinazioni dei tre. L'elenco di espressioni è separato da virgole.

A ogni espressione è possibile assegnare un alias aggiungendo uno spazio seguito da un identificatore dopo l'espressione. La parola chiave facoltativa AS può essere aggiunta tra l'espressione e l'alias per una maggiore 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 né in altre espressioni nella stessa clausola SELECT.

Note:

• Se utilizzi una funzione di aggregazione nella clausola SELECT, devi utilizzare una funzione di aggregazione 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'escapismo delle parole riservate in modo da poterle utilizzare come nome del campo e alias. Ad esempio, se hai una colonna denominata "partition", che è una parola riservata nella sintassi di BigQuery, le query che fanno riferimento a quel campo non vanno a buon fine con messaggi di errore oscuri, a meno che non la escludi con parentesi quadre:

  SELECT [partition] FROM ...

Esempio

Questo esempio definisce gli alias nella clausola SELECT e poi fa riferimento a uno di questi nella clausola ORDER BY. Tieni presente che non è possibile fare riferimento alla colonna parola utilizzando parola_alias nella clausola WHERE; deve essere fatto riferimento al nome. Anche l'alias len non è visibile nella clausola WHERE. Sarà 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 le funzioni aggregate

aggregate_function WITHIN RECORD [ [ AS ] alias ]

La parola chiave WITHIN consente alla funzione di aggregazione di eseguire l'aggregazione in base ai valori ripetuti all'interno di ogni record. Per ogni record di input verrà prodotto esattamente un output aggregato. Questo tipo di aggregazione è chiamato aggregazione basata sugli ambiti. Poiché l'aggregazione basata su ambito produce output per ogni record, le espressioni non aggregate possono essere selezionate insieme alle espressioni aggregate basate su ambito senza utilizzare una clausola GROUP BY.

In genere, utilizzerai l'ambito RECORD quando utilizzi l'aggregazione basata sugli ambiti. Se hai uno schema ripetuto e nidificato molto complesso, potresti dover eseguire aggregazioni all'interno degli ambiti dei subrecord. Per farlo, sostituisci la parola chiave RECORD nella sintassi sopra con il nome del nodo nello schema in cui vuoi eseguire l'aggregazione. Per ulteriori informazioni su questo comportamento avanzato, consulta Gestire i dati.

Esempio

Questo esempio esegue un'aggregazione COUNT basata su 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 su tabelle, sottoquery, tabelle unite e tabelle modificate da operatori speciali descritti di seguito. È possibile eseguire query sulle combinazioni di queste origini dati utilizzando la virgola, ovvero l'operatore UNION ALL in BigQuery.

Tabelle di riferimento

Quando fai riferimento a una tabella, devono essere specificati sia datasetId sia tableId. project_name è facoltativo. Se project_name non è specificato, BigQuery utilizzerà per impostazione predefinita il progetto corrente. 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 maggiore 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, o datasetId e tableId, a condizione che non sia stato specificato project_name. project_name non può essere incluso nel prefisso della colonna perché il carattere due punti non è consentito nei nomi dei campi.

Esempi

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

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

In questo esempio, il nome della colonna è preceduto da datasetId e tableId. Tieni presente che project_name non può essere incluso in questo esempio. Questo metodo funziona solo se il set di dati si trova nel progetto predefinito corrente.

#legacySQL
SELECT
  samples.shakespeare.word
FROM
  samples.shakespeare;

In questo esempio il nome della colonna è preceduto da un alias di tabella.

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

Tabelle partizionate con intervallo di numeri interi

SQL precedente supporta l'utilizzo di decoratori di tabella per indirizzare una partizione specifica in una tabella partizionata con intervallo di interi. La chiave per indirizzare una partizione di intervallo è l'inizio dell'intervallo.

L'esempio seguente esegue query sulla partizione dell'intervallo che inizia con 30:

#legacySQL
SELECT
  *
FROM
  dataset.table$30;

Tieni presente che non puoi utilizzare SQL precedente per eseguire query su un'intera tabella partizionata con intervallo di numeri interi. La query restituisce invece un errore come il seguente:

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, così come le colonne di una tabella.

Le sottoquery possono essere utilizzate per calcolare aggregazioni e altre espressioni. Nella sottoquery è disponibile l'intera gamma di operatori SQL. Ciò significa che una sottoquery può contenere altre sottoquery, che possono eseguire join e aggregazioni di raggruppamento e così via.

Virgola come UNION ALL

A differenza di GoogleSQL, SQL precedente utilizza la virgola come operatore UNION ALL anziché CROSS JOIN. Si tratta di un comportamento precedente che si è evoluto perché, in passato, 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 verbose. L'utilizzo della virgola come operatore di unione consente di scrivere queste query 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 rispetto alle query che elaborano la stessa quantità di dati da una singola tabella. La differenza di rendimento può essere fino a 50 ms per tabella aggiuntiva. Una singola query può unire al massimo 1000 tabelle.

Funzioni con caratteri jolly per le tabelle

Il termine funzione di carattere jolly per tabelle si riferisce a un tipo speciale di funzione esclusiva di BigQuery. Queste funzioni vengono utilizzate nella clausola FROM per associare una raccolta di nomi di tabelle utilizzando uno di diversi 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 di caratteri jolly per le tabelle.

Operatore FLATTEN

(FLATTEN([project_name:]datasetId.tableId, field_to_be_flattened))
(FLATTEN((subquery), field_to_be_flattened))

A differenza dei sistemi di elaborazione SQL tradizionali, BigQuery è progettato per gestire i 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 dello schema da ripetuto a facoltativo. Dati 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 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 viene specificato, il valore predefinito è INNER.

Le operazioni CROSS JOIN non consentono clausole ON. CROSS JOIN può restituire una grande quantità di dati e potrebbe comportare una query lenta e inefficiente o una query che supera le risorse massime consentite per query. Queste query non andranno a buon fine e restituiranno un errore. Se possibile, preferisci le query che non utilizzano CROSS JOIN. Ad esempio, CROSS JOIN viene spesso utilizzato in luoghi in cui le funzioni finestra sarebbero più efficienti.

Modificatore EACH

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

EACH era incoraggiato in molti casi, ma ora non è più così. Se possibile, utilizza JOIN senza il modificatore EACH per migliorare il rendimento. Utilizza JOIN EACH quando la query non è riuscita con un messaggio di errore relativo al superamento delle risorse.

Semijoin e antijoin

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

Esempi

La seguente query utilizza una semijoin per trovare n-gram in cui la prima parola dell'n-gram è anche la seconda parola di un altro n-gram che ha "AND" come terza parola.

#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 una semijoin 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 relativi agli altri 40 stati, puoi utilizzare un'unione anti. 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 le semijoin o le anti-semijoin correlate. La sottoquery non può fare riferimento a nessun campo della query esterna.
• La sottoquery utilizzata in una semijoin o anti-semijoin deve selezionare esattamente un campo.
• I tipi del campo selezionato e del campo utilizzato dalla query esterna nella clausola WHERE devono corrispondere esattamente. BigQuery non eseguirà alcuna coercizione di tipo per le semijoin o le anti-semijoin.

Clausola WHERE

La clausola WHERE, a volte chiamata predicato, filtra i record prodotti dalla clausola FROM utilizzando un'espressione booleana. Più condizioni possono essere unite da clausole booleane AND e OR, eventualmente racchiuse tra parentesi tonde (), 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 di aggregazione.

Esempio

L'esempio seguente utilizza una disgiunzione di espressioni booleane nella clausola WHERE , ovvero le due espressioni unite da un operatore OR. Un record di input viene applicato al 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 esclusivo di BigQuery. È particolarmente utile per gestire schemi nidificati e ripetuti. È simile a una clausola WHERE , ma è diversa in due modi importanti. 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 funzioni aggregate con ambito nella condizione.

Oltre a filtrare i record completi, OMIT...IF può specificare un ambito più ristretto per filtrare solo parti 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 nel link alla documentazione di WITHIN riportato sopra.

Se utilizzi OMIT...IF per escludere una parte di un record in un campo ripetuto e la query seleziona anche altri campi ripetuti 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 Eseguire la migrazione a GoogleSQL.

Esempio

Facendo riferimento all'esempio utilizzato per il modificatore WITHIN, OMIT RECORD IF può essere utilizzato per ottenere lo stesso risultato ottenuto con WITHIN e HAVING nell'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 le aggregazioni di campi correlati. Il raggruppamento avviene dopo l'applicazione del filtro nella clausola WHERE, ma prima del calcolo delle 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 dieci prime parole più comuni nel set di dati di esempio di trigrammi. Oltre a mostrare l'utilizzo della clausola GROUP BY, illustra come gli indici di posizione possono essere utilizzati al posto dei nomi di campo 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 raggruppata . A differenza dell'aggregazione basata sugli ambiti, l'aggregazione raggruppata è comune nella maggior parte dei sistemi di elaborazione SQL.

Il modificatore EACH

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

EACH era incoraggiato in molti casi, ma ora non è più così. L'utilizzo di GROUP BY senza il modificatore EACH in genere offre un rendimento migliore. 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 righe aggiuntive che rappresentano aggregazioni aggregate. Tutti i campi elencati dopo ROLLUP devono essere racchiusi in un unico insieme di parentesi. Nelle righe aggiunte a causa della funzione ROLLUP, NULL indica le colonne per le quali viene eseguita l'aggregazione.

Esempio

Questa query genera conteggi per anno di nascite di bambini e bambine dal set di dati di natalità di esempio.

#legacySQL
SELECT
  year,
  is_male,
  COUNT(1) as count
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year >= 2000
  AND year <= 2002
GROUP BY
  ROLLUP(year, is_male)
ORDER BY
  year,
  is_male;

Questi sono i risultati della query. Nota che ci sono 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 utilizzare la funzione GROUPING per distinguere le righe aggiunte a causa della funzione ROLLUP dalle 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 a causa della funzione ROLLUP.

#legacySQL
SELECT
  year,
  GROUPING(year) as rollup_year,
  is_male,
  GROUPING(is_male) as rollup_gender,
  COUNT(1) as count
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year >= 2000
  AND year <= 2002
GROUP BY
  ROLLUP(year, is_male)
ORDER BY
  year,
  is_male;

Questi sono i risultati restituiti dalla nuova query.

+------+-------------+---------+---------------+----------+
| year | rollup_year | is_male | rollup_gender |  count   |
+------+-------------+---------+---------------+----------+
| NULL |           1 |    NULL |             1 | 12122730 |
| 2000 |           0 |    NULL |             1 |  4063823 |
| 2000 |           0 |   false |             0 |  1984255 |
| 2000 |           0 |    true |             0 |  2079568 |
| 2001 |           0 |    NULL |             1 |  4031531 |
| 2001 |           0 |   false |             0 |  1970770 |
| 2001 |           0 |    true |             0 |  2060761 |
| 2002 |           0 |    NULL |             1 |  4027376 |
| 2002 |           0 |   false |             0 |  1966519 |
| 2002 |           0 |    true |             0 |  2060857 |
+------+-------------+---------+---------------+----------+

Note:

• I campi non aggregati nella 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 in base a valori float e double non è supportato, perché la funzione di uguaglianza per questi tipi non è ben definita.
• Poiché il sistema è interattivo, le query che producono un numero elevato di gruppi potrebbero non riuscire. L' utilizzo della TOP funzione 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, pertanto i risultati di tutte le espressioni calcolate sono visibili alla clausola HAVING. La clausola HAVING può fare riferimento solo agli output della clausola SELECTcorrispondente.

Esempio

Questa query calcola le parole iniziali più comuni nel set di dati di esempio di n-gram che contengono la lettera a e si verificano al massimo 10.000 volte.

#legacySQL
SELECT
  first,
  COUNT(ngram) ngram_count
FROM
  [bigquery-public-data:samples.trigrams]
GROUP BY
  1
HAVING
  first contains "a"
  AND ngram_count < 10000
ORDER BY
  2 DESC
LIMIT 10;

Clausola ORDER BY

La clausola ORDER BY ordina i risultati di una query in ordine crescente o decrescente utilizzando uno o più campi chiave. Per ordinare in base a più campi o alias, inseriscili come elenco separato da virgole. I risultati vengono ordinati in base ai campi nell'ordine in cui sono elencati. Utilizza DESC (decrescente) o ASC (crescente) per specificare la direzione di ordinamento. ASC è il valore predefinito. È possibile specificare un'altra direzione di ordinamento per ogni chiave di ordinamento.

La clausola ORDER BY viene valutata dopo la clausola SELECT, pertanto può 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 numeri molto elevati di righe, LIMIT è un buon modo per evitare query in esecuzione prolungata elaborando solo un sottoinsieme di righe.

Note:

• La clausola LIMIT interrompe l'elaborazione e restituisce i risultati quando soddisfa i tuoi requisiti. In questo modo, è possibile ridurre il tempo di elaborazione di alcune query, ma quando specifichi funzioni aggregate come COUNT o clausole ORDER BY, il set di risultati completo deve comunque essere elaborato prima di restituire i risultati. La clausola LIMIT è l'ultima da valutare.
• Una query con una clausola LIMIT può comunque essere non deterministica se nella query non è presente un operatore che garantisca l'ordinamento del set di risultati di output. Questo perché BigQuery esegue l'elaborazione utilizzando un numero elevato di worker in parallelo. L'ordine in cui vengono restituiti i job paralleli non è garantito.
• La clausola LIMIT non può contenere funzioni; accetta solo una costante numerica.
• Quando viene utilizzata la clausola LIMIT, i byte totali elaborati e i byte fatturati possono variare per la stessa query.

Grammatica delle query

Le singole clausole degli enunciati SELECT di BigQuery sono descritte in dettaglio sopra. Qui presentiamo la grammatica completa delle SELECT affermazioni in un formato compatto con link 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 tra parentesi quadre "[, ... ]" indica che l'elemento precedente può ripetersi in un elenco con il separatore specificato.
• Le parentesi tonde "( )" indicano parentesi letterali.

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. Pertanto, la seguente query è valida, anche se il campo clicks non viene visualizzato direttamente:

#legacySQL
SELECT country, SUM(clicks) FROM table GROUP BY country;

Funzioni di aggregazione

Le funzioni aggregate restituiscono valori che rappresentano riepiloghi di insiemi di dati più grandi, il che le rende particolarmente utili per analizzare i log. Una funzione di aggregazione opera su una raccolta di valori e restituisce un singolo valore per tabella, gruppo o ambito:

• Aggregazione delle tabelle

  Utilizza una funzione di aggregazione per riepilogare tutte le righe idonee della tabella. Ad esempio:

  SELECT COUNT(f1) FROM ds.Table;

• Aggregazione di 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 basata sugli ambiti

  Questa funzionalità si applica solo alle tabelle con campi nidificati.
  Utilizza una funzione di aggregazione 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 all'intera riga, o un nodo (campo ripetuto in una riga). Le funzioni di aggregazione operano sui valori nell'ambito e restituiscono risultati aggregati per ogni record o nodo.

Puoi applicare una limitazione a una funzione di aggregazione utilizzando una delle seguenti opzioni:

• Un alias in una query di sottoselezionate. La restrizione è 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

AVG(numeric_expr)

Restituisce la media dei valori per un gruppo di righe calcolata da numeric_expr. Le righe con un valore NULL non sono incluse nel calcolo.

BIT_AND(numeric_expr)

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

BIT_OR(numeric_expr)

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

BIT_XOR(numeric_expr)

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

CORR(numeric_expr, numeric_expr)

Restituisce il coefficiente di correlazione di 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 tu 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 diversi per il campo specificato. Tieni presente che il valore restituito per DISTINCT è un'approssimazione statistica e non è garantito che sia esatto.

Utilizza EXACT_COUNT_DISTINCT() per una risposta esatta.

Se hai bisogno di una maggiore precisione da COUNT(DISTINCT), puoi specificare un secondo parametro, n, che indica la soglia al di sotto della quale sono garantiti risultati esatti. Per impostazione predefinita, n è 1000, ma se specifichi un valore maggiore, otterrai risultati esatti per COUNT(DISTINCT) fino a quel valore.nn Tuttavia, valori più elevati di n ridurranno la scalabilità di questo operatore e potrebbero aumentare notevolmente il tempo di esecuzione della query o causare il suo fallimento.

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 un lieve calo delle prestazioni iniziali.

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 campione dei valori calcolati da numeric_expr1 e numeric_expr2.

EXACT_COUNT_DISTINCT(field)

Restituisce il numero esatto di valori distinti non null per il campo specificato. Per una maggiore scalabilità e 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 un carattere di virgoletta doppia, GROUP_CONCAT restituisce la stringa con le virgolette doppie aggiunte. Ad esempio, la stringa a"b viene restituita come "a""b". Utilizza GROUP_CONCAT_UNQUOTED se preferisci che queste stringhe non vengano restituite con le virgolette doppie aggiunte.

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 un carattere di virgolette doppie. Ad esempio, la stringa a"b viene restituita come a"b.

Esempio:

#legacySQL
SELECT
  GROUP_CONCAT_UNQUOTED(x)
FROM (
  SELECT
    'a"b' AS x),
  (
  SELECT
    'cd' AS x);

LAST(field)

Restituisce 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 corrente 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 con x nell'input della query. La funzione NEST richiede una clausola GROUP BY.

BigQuery appiattisce automaticamente i risultati delle query, pertanto se utilizzi la funzione NEST nella query di primo livello, i risultati non conterranno campi ripetuti. Utilizza la funzione NEST quando utilizzi una sottosezione che produce risultati intermedi per l'uso immediato dalla stessa query.

NTH(n, field)

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

QUANTILES(expr[, buckets])

Calcola valori minimi, massimi e quantili approssimativi per l'espressione di input. I valori di input NULL vengono ignorati. Un input vuoto o composto esclusivamente da NULL genera un output NULL. Il numero di quantili calcolati è controllato con il parametro facoltativo buckets, che include il minimo e il massimo nel conteggio. Per calcolare N-tiles approssimativi, utilizza N+1 buckets. Il valore predefinito di buckets è 100. Nota: il valore predefinito 100 non stima i percentile. Per stimare i percentile, 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 valore minimo ("quantile 0") nella prima posizione e il valore massimo ("percentile 100" 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 la precisione, utilizza più bucket. Ad esempio, per ridurre il margine di errore per i calcoli precedenti dal 5% allo 0,1%, utilizza 1001 bucket anziché 21 e modifica di conseguenza l'argomento della funzione NTH. Per calcolare la mediana con un errore del 0,1%, utilizza NTH(501, QUANTILES(expr, 1001)); per il 95° percentile con un errore del 0,1%, utilizza NTH(951, QUANTILES(expr, 1001)).

STDDEV(numeric_expr)

Restituisce la deviazione standard dei valori calcolati da numeric_expr. Le righe con un valore NULL non sono 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 tuo set di dati comprende solo un campione rappresentativo della popolazione, utilizza STDDEV_SAMP(). Per ulteriori informazioni sulla deviazione standard della popolazione rispetto a quella del campione, consulta la pagina 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 STDDEV_POP(). Per ulteriori informazioni sulla deviazione standard della popolazione rispetto a quella del campione, consulta la pagina 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 in base alla frequenza. Per maggiori dettagli, consulta la descrizione TOP di seguito.

UNIQUE(expr)

Restituisce l'insieme di valori univoci non null nell'ambito della funzione in un ordine non definito. Come per una clausola GROUP BY di grandi dimensioni senza la parola chiave EACH, la query non andrà a buon fine con un errore "Risorse superate" se sono presenti troppi valori distinti. Tuttavia, a differenza di GROUP BY, la funzione UNIQUE può essere applicata con l'aggregazione basata sugli ambiti, consentendo un'operazione efficiente sui campi nidificati con un numero limitato di valori.

VARIANCE(numeric_expr)

Calcola la varianza dei valori calcolati da numeric_expr. Le righe con un valore NULL non sono 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 deviazione standard della popolazione rispetto a quella del campione, consulta la pagina Deviazione standard su Wikipedia.

VAR_SAMP(numeric_expr)

Calcola la varianza del campione dei valori calcolati da numeric_expr. Per ulteriori informazioni sulla deviazione standard della popolazione rispetto a quella del campione, consulta la pagina Deviazione standard su Wikipedia.

Funzione TOP()

TOP è una funzione che rappresenta un'alternativa alla clausola GROUP BY. Viene utilizzata come sintassi semplificata per GROUP BY ... ORDER BY ... LIMIT .... In genere, la funzione TOP è più veloce della query ... GROUP BY ... ORDER BY ... LIMIT ... completa, ma potrebbe restituire solo risultati approssimativi. Di seguito è riportata la sintassi della 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(*) per il moltiplicatore 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 10 parole più utilizzate contenenti "th" e il numero di documenti in cui sono state utilizzate. La query TOP verrà eseguita molto più rapidamente:

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

• Utilizzando 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 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";

  Restituisce:

  +-------+-------+
  | 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";

  Restituisce:

  +-------+---------+
  | month |   cnt   |
  +-------+---------+
  |   7   | 1959400 |
  |   5   | 1903800 |
  |   8   | 1903000 |
  +-------+---------+
  

Nota: per utilizzare TOP, devi includere COUNT(*) nella clausola SELECT.

Esempi avanzati

• Media e deviazione standard raggruppate per condizione

  La seguente query restituisce la media e la deviazione standard dei pesi alla nascita in Ohio nel 2003, raggruppati in base alle madri che fumano e a quelle che 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 della query utilizzando un valore aggregato

  Per filtrare i risultati della query utilizzando un valore aggregato (ad es. applicando un filtro in base al valore di un SUM), utilizza la funzione HAVING. HAVING confronta un valore con un risultato determinato da una funzione di aggregazione, diversamente da 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

  Restituisce:

  +-------+--------+---------+
  | 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 numerico letterale o un valore numerico restituito da una query. Se l'operazione aritmetica restituisce un risultato non definito, l'operazione restituisce NULL.

Sintassi

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 Operazione a livello di bit.

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

Sintassi

Funzioni di trasmissione

Le funzioni di conversione cambiano il tipo di dati di un'espressione numerica. Le funzioni di conversione sono particolarmente utili per garantire che gli argomenti di una funzione di confronto abbiano lo stesso tipo di dati.

Sintassi

BOOLEAN(<numeric_expr>)

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

BYTES(string_expr)

Restituisce string_expr come valore di tipo bytes.

CAST(expr AS type)

Converte expr in una variabile di tipo type.

FLOAT(expr)

Restituisce expr come numero 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)

Trasforma expr in un numero intero a 64 bit.

• Restituisce NULL se expr è una stringa che non corrisponde a un valore intero.
• Restituisce il numero di microsecondi dall'epoca Unix se expr è un timestamp.

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.
• Un confronto tra un'espressione o un insieme di espressioni e criteri specifici, ad esempio l'appartenenza a un elenco specificato, il valore NULL o un valore facoltativo non predefinito.

Alcune delle funzioni elencate di seguito restituiscono valori diversi da true o false, ma i valori restituiti si basano su operazioni di confronto.

Puoi utilizzare espressioni numeriche o di stringa come argomenti per le funzioni di confronto. Le costanti di stringa devono essere racchiuse tra virgolette singole o doppie. Le espressioni possono essere letterali o valori recuperati da una query. Le funzioni di confronto vengono utilizzate più spesso come condizioni di filtro nelle clausole WHERE, ma possono essere utilizzate anche in altre clausole.

Sintassi

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 efficace per (expr = expr1 || expr = expr2 || ...). Le espressioni utilizzate con la parola chiave IN devono essere costanti e devono corrispondere al tipo di dati di expr. La clausola IN può essere utilizzata anche per creare semijoin e antijoin. Per ulteriori informazioni, consulta Semijoin e antijoin.

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

Restituisce il primo argomento che non è NULL.

GREATEST(numeric_expr1, numeric_expr2, ...)

Restituisce il parametro numeric_expr più grande. Tutti i parametri devono essere numerici e dello stesso tipo. Se un parametro è NULL, questa funzione restituisce NULL.

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

IS_INF(numeric_expr)

Restituisce true se numeric_expr è infinito positivo o negativo.

IS_NAN(numeric_expr)

Restituisce true se numeric_expr è il valore numerico speciale NaN.

IS_EXPLICITLY_DEFINED(expr)

Questa funzione è deprecata. Utilizza invece expr IS NOT NULL.

LEAST(numeric_expr1, numeric_expr2, ...)

Restituisce il parametro numeric_expr più piccolo. Tutti i parametri devono essere numerici e dello stesso tipo. Se un parametro è NULL, questa funzione restituisce NULL

NVL(expr, null_default)

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

Funzioni di data e ora

Le seguenti funzioni consentono la manipolazione di date e ore per timestamp UNIX, stringhe di date e tipi di dati TIMESTAMP. Per ulteriori informazioni sull'utilizzo del tipo di dato TIMESTAMP, consulta Utilizzare TIMESTAMP.

Le funzioni di data e ora che funzionano con i timestamp UNIX operano su ora UNIX. Le funzioni relative a data e ora restituiscono valori in base al fuso orario UTC.

Sintassi

CURRENT_DATE()

Restituisce una stringa leggibile della data corrente nel formato %Y-%m-%d.

Esempio:

SELECT CURRENT_DATE();

Restituisce: 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 corrente del server nel formato %Y-%m-%d %H:%M:%S.

Esempio:

SELECT CURRENT_TIMESTAMP();

Restituisce: 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'));

Restituisce: 2012-10-01

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

Aggiunge l'intervallo specificato a un tipo di dati TIMESTAMP. I possibili valori di interval_units includono YEAR, MONTH, DAY, HOUR, MINUTE e SECOND. Se interval è un numero negativo, l'intervallo viene sottratto al tipo di dati TIMESTAMP.

Esempio:

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

Restituisce: 2017-10-01 02:03:04 UTC

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

Restituisce: 2007-10-01 02:03:04 UTC

DATEDIFF(<timestamp1>,<timestamp2>)

Restituisce il numero di giorni compresi tra due tipi di dati TIMESTAMP. Il risultato è positivo se il primo tipo di dati TIMESTAMP segue il secondo tipo di dati TIMESTAMP, altrimenti è negativo.

Esempio:

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

Resi: 466

Esempio:

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

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

Restituisce: 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"));

Restituisce: 2

DAYOFYEAR(<timestamp>)

Restituisce il giorno dell'anno di un tipo di dati TIMESTAMP come numero intero compreso tra 1 e 366. Il numero intero 1 si riferisce al 1° gennaio.

Esempio:

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

Restituisce: 275

FORMAT_UTC_USEC(<unix_timestamp>)

Restituisce una rappresentazione di 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'));

Restituisce: 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'));

Restituisce: 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'));

Restituisce: 10

MSEC_TO_TIMESTAMP(<expr>)

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

Esempio:

SELECT MSEC_TO_TIMESTAMP(1349053323000);

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

SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)

Restituisce: 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ò essere lunga fino a 6 cifre o può essere omessa.

TIMESTAMP_TO_USEC è una funzione equivalente che converte un argomento di tipo di dati TIMESTAMP anziché una stringa di data.

Esempio:

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

Restituisce: 1349056984000000

QUARTER(<timestamp>)

Restituisce il trimestre dell'anno di un tipo di dati TIMESTAMP come numero intero compreso tra 1 e 4.

Esempio:

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

Restituisce: 4

SEC_TO_TIMESTAMP(<expr>)

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

Esempio:

SELECT SEC_TO_TIMESTAMP(1355968987);

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

SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));

Restituisce: 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'));

Restituisce: 48

STRFTIME_UTC_USEC(<unix_timestamp>,
                  <date_format_str>)

Restituisce una stringa della data leggibile nel formato date_format_str. date_format_str può includere i caratteri di punteggiatura relativi alla data (ad esempio / e -) e i 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 per ottenere tutti i dati di un determinato mese, perché sono più efficienti.

Esempio:

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

Restituisce: 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'));

Restituisce: 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");

Restituisce: 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"));

Restituisce: 1349053323000

TIMESTAMP_TO_SEC(<timestamp>)

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

Esempio:

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

Resi: 1349053323

TIMESTAMP_TO_USEC(<timestamp>)

Converte un tipo di dati TIMESTAMP in un timestamp UNIX in microsecondi.

PARSE_UTC_USEC è una funzione equivalente che converte un argomento stringa di dati anziché un tipo di dati TIMESTAMP.

Esempio:

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

Restituisce: 1349053323000000

USEC_TO_TIMESTAMP(<expr>)

Converte un timestamp UNIX in microsecondi in un tipo di dati TIMESTAMP.

Esempio:

SELECT USEC_TO_TIMESTAMP(1349053323000000);

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

SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)

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

UTC_USEC_TO_DAY(<unix_timestamp>)

Sposta un timestamp UNIX in microsecondi all'inizio del giorno in cui si verifica.

Ad esempio, se unix_timestamp si verifica il 19 maggio alle 08:58, questa funzione restituisce un timestamp UNIX per il 19 maggio alle 00:00 (mezzanotte).

Esempio:

SELECT UTC_USEC_TO_DAY(1274259481071200);

Restituisce: 1274227200000000

UTC_USEC_TO_HOUR(<unix_timestamp>)

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

Ad esempio, se unix_timestamp si verifica alle 08:58, questa funzione restituisce un timestamp UNIX per le 08:00 dello stesso giorno.

Esempio:

SELECT UTC_USEC_TO_HOUR(1274259481071200);

Restituisce: 1274256000000000

UTC_USEC_TO_MONTH(<unix_timestamp>)

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

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 compreso tra 0 (domenica) e 6 (sabato).

Ad esempio, se unix_timestamp si verifica il 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;

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

Esempio:

SELECT WEEK(TIMESTAMP('2014-12-31'));

Resi: 53

YEAR(<timestamp>)

Restituisce 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 dei timestamp interi in un formato leggibile

  La seguente query trova i 5 momenti più importanti in cui si sono verificate il maggior numero di revisioni di Wikipedia. Per visualizzare i risultati in un formato leggibile da persone, utilizza la funzione FORMAT_UTC_USEC() di BigQuery, che accetta come input un timestamp in microsecondi. Questa query moltiplica i timestamp nel formato POSIX di Wikipedia (in secondi) per 1000000 per convertire il valore in microsecondi.

  Esempio:

  #legacySQL
  SELECT
    /* Multiply timestamp by 1000000 and convert */
    /* into a more human-readable format. */
    TOP (FORMAT_UTC_USEC(timestamp * 1000000), 5)
      AS top_revision_time,
    COUNT (*) AS revision_count
  FROM
    [bigquery-public-data:samples.wikipedia];

  Restituisce:

  +----------------------------+----------------+
  |     top_revision_time      | revision_count |
  +----------------------------+----------------+
  | 2002-02-25 15:51:15.000000 |          20976 |
  | 2002-02-25 15:43:11.000000 |          15974 |
  | 2010-02-02 03:34:51.000000 |              3 |
  | 2010-02-02 01:04:59.000000 |              3 |
  | 2010-02-01 23:55:05.000000 |              3 |
  +----------------------------+----------------+
  

• Raggruppamento dei risultati per timestamp

  È utile utilizzare le funzioni di data e ora per raggruppare i risultati delle query in bucket corrispondenti a anni, mesi o giorni specifici. Il seguente esempio utilizza la funzione UTC_USEC_TO_MONTH() per mostrare il numero di caratteri utilizzati ogni mese da ciascun collaboratore di Wikipedia nei commenti delle revisioni.

  Esempio:

  #legacySQL
  SELECT
    contributor_username,
    /* Return the timestamp shifted to the
     * start of the month, formatted in
     * a human-readable format. Uses the
     * 'LEFT()' string function to return only
     * the first 7 characters of the formatted timestamp.
     */
    LEFT (FORMAT_UTC_USEC(
      UTC_USEC_TO_MONTH(timestamp * 1000000)),7)
      AS month,
    SUM(LENGTH(comment)) as total_chars_used
  FROM
    [bigquery-public-data:samples.wikipedia]
  WHERE
    (contributor_username != '' AND
     contributor_username IS NOT NULL)
    AND timestamp > 1133395200
    AND timestamp < 1157068800
  GROUP BY
    contributor_username, month
  ORDER BY
    total_chars_used DESC;

  Risultati (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 formato leggibile e viceversa.

Sintassi

FORMAT_IP(integer_value)

Converte i 32 bit meno significativi di integer_value in una stringa di indirizzo IPv4 leggibile. Ad esempio, FORMAT_IP(1) restituirà la stringa '0.0.0.1'.

PARSE_IP(readable_ip)

Converte una stringa che rappresenta l'indirizzo IPv4 in un valore intero non firmato. Ad esempio, PARSE_IP('0.0.0.1') restituisce 1. Se la stringa non è un indirizzo IPv4 valido, PARSE_IP restituirà NULL.

BigQuery supporta la scrittura di indirizzi IPv4 e IPv6 in stringhe compresse, come dati binari di 4 o 16 byte nell'ordine di byte di rete. Le funzioni descritte di seguito supportano l'analisi degli indirizzi da e verso un formato leggibile da persone. Queste funzioni funzionano solo sui campi di 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 della scansione dell'intera stringa, che è più costoso rispetto al caso in cui ogni campo si trovi in una colonna separata. Inoltre, la query è più lenta perché 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 utilizzi dati strutturati, utilizza le funzioni JSON anziché le funzioni di espressioni regolari di BigQuery, in quanto sono più facili da usare.

Sintassi

JSON_EXTRACT(json, json_path)

Seleziona un valore in json in base all'espressione JSONPath json_path. json_path deve essere una costante di 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 di 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 ammette valori NULL e restituisce true, false o NULL.

Sintassi

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 è falsa.
• Restituisce false se l'espressione è vera.
• Restituisce NULL se l'espressione è NULL.

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

Funzioni matematiche

Le funzioni matematiche accettano argomenti numerici e restituiscono un risultato numerico. Ogni argomento può essere un valore numerico letterale o un valore numerico restituito da una query. Se la funzione matematica restituisce un risultato non definito, l'operazione restituisce NULL.

Sintassi

ABS(numeric_expr)

Restituisce il valore assoluto dell'argomento.

ACOS(numeric_expr)

Restituisce l'arcocoseno dell'argomento.

ACOSH(numeric_expr)

Restituisce l'arco coseno iperbolico dell'argomento.

ASIN(numeric_expr)

Restituisce l'arco seno dell'argomento.

ASINH(numeric_expr)

Restituisce l'arco seno iperbolico dell'argomento.

ATAN(numeric_expr)

Restituisce l'arcotangente dell'argomento.

ATANH(numeric_expr)

Restituisce l'arcotangente iperbolica dell'argomento.

ATAN2(numeric_expr1, numeric_expr2)

Restituisce l'arcotangente dei due argomenti.

CEIL(numeric_expr)

Arrotonda l'argomento per eccesso al numero intero più vicino e restituisce il valore arrotondato.

COS(numeric_expr)

Restituisce il coseno dell'argomento.

COSH(numeric_expr)

Restituisce il coseno iperbolico dell'argomento.

DEGREES(numeric_expr)

Restituisce numeric_expr, convertito da radianti in gradi.

EXP(numeric_expr)

Restituisce il risultato dell'elevazione della costante "e", la base del logaritmo naturale, alla potenza di numeric_expr.

FLOOR(numeric_expr)

Arrotonda l'argomento per difetto al numero intero più vicino e restituisce il valore arrotondato.

LN(numeric_expr)
LOG(numeric_expr)

Restituisce il logaritmo naturale dell'argomento.

LOG2(numeric_expr)

Restituisce il logaritmo in base 2 dell'argomento.

LOG10(numeric_expr)

Restituisce il logaritmo in base 10 dell'argomento.

PI()

Restituisce la costante π. La funzione PI() richiede le parentesi per indicare che si tratta di una funzione, ma non accetta argomenti al loro interno. Puoi utilizzare PI() come una costante con 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)

Restituisce numeric_expr, convertito da gradi a radianti. Tieni presente che π radianti corrispondono a 180 gradi.

RAND([int32_seed])

Restituisce un valore float casuale nell'intervallo 0,0 <= valore < 1,0. Ogni valore int32_seed genera sempre la stessa sequenza di numeri casuali all'interno di una determinata query, a condizione che non venga utilizzata una clausola LIMIT. Se int32_seed non è specificato, BigQuery utilizza il timestamp corrente come valore iniziale.

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)

Restituisce il seno dell'argomento.

SINH(numeric_expr)

Restituisce il seno iperbolico dell'argomento.

SQRT(numeric_expr)

Restituisce la radice quadrata dell'espressione.

TAN(numeric_expr)

Restituisce la tangente dell'argomento.

TANH(numeric_expr)

Restituisce la tangente iperbolica dell'argomento.

Esempi avanzati

• Query sul riquadro delimitatore

  La seguente query restituisce una raccolta di punti all'interno di un riquadro di delimitazione delimitante 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 cerchio delimitante approssimativo

  Restituisce una raccolta di massimo 100 punti all'interno di un cerchio approssimato determinato dall'uso della legge sferica dei coseni, centrato su Denver, Colorado (39,73, -104,98). Questa query utilizza le funzioni matematiche e trigonometriche di BigQuery, ad esempio PI(), SIN() e COS().

  Poiché la Terra non è una sfera assoluta e la longitudine + la latitudine convergono 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 espressioni regolari

BigQuery fornisce il supporto delle espressioni regolari utilizzando la libreria re2. consulta la documentazione per la sintassi delle espressioni regolari.

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

Sintassi

REGEXP_MATCH('str', 'reg_exp')

Restituisce true se str corrisponde all'espressione regolare. Per la corrispondenza di stringhe senza espressioni regolari, utilizza CONTIENE 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;

Restituisce:

+-------+-------+
| 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 cattura 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;

Restituisce:

+----------+
| fragment |
+----------+
| NULL     |
| Al'ce    |
| As'es    |
+----------+

REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')

Restituisce una stringa in cui qualsiasi sottostringa di orig_str che corrisponde a reg_exp viene sostituita con replace_str. Ad esempio, REGEXP_REPLACE ("Un saluto"; "lo"; "p") restituisce Un saluto.

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;

Restituisce:

+---------------+
| expanded_word |
+---------------+
| Whenever      |
| never         |
| nevertheless  |
| whenever      |
+---------------+

Esempi avanzati

• Filtrare il set di risultati in base alla corrispondenza con un'espressione regolare

  Le funzioni di espressioni regolari di BigQuery possono essere utilizzate per filtrare i risultati in una clausola WHERE, nonché per visualizzare i risultati in SELECT. L'esempio seguente combina entrambi i casi d'uso delle 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 con virgola mobile

  Sebbene le funzioni di espressioni regolari di BigQuery funzionino solo per i dati di stringa, è possibile utilizzare la funzione STRING() per convertire i dati interi o in virgola mobile in formato di stringa. In questo esempio, STRING() viene utilizzato per eseguire il casting del valore intero corpus_date in 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 di stringa. Le costanti stringa devono essere racchiuse tra virgolette singole o doppie. Per impostazione predefinita, le funzioni di stringa sono sensibili alle maiuscole. Puoi aggiungere IGNORE CASE alla fine di una query per attivare 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à delle espressioni regolari, utilizza le funzioni di espressione regolare.

Sintassi

CONCAT('str1', 'str2', '...')
str1 + str2 + ...

Restituisce la concatenazione di due o più stringhe o 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 stringa specificato. Questo è un confronto sensibile alle maiuscole.

INSTR('str1', 'str2')

Restituisce l'indice a partire da 1 della prima occorrenza di str2 in str1 oppure 0 se str2 non si verifica in str1.

LEFT('str', numeric_expr)

Restituisce i caratteri numeric_expr più a 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')

Aggiunge str1 a sinistra con str2, ripetendo str2 finché la stringa risultante non è composta esattamente da 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, verrà restituita l'intera stringa. Esempio: RIGHT('kirkland', 4) restituisce land.

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

Aggiunge str1 a destra con str2, ripetendo str2 finché la stringa risultante non è composta esattamente da 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. In caso contrario, RTRIM rimuove tutti i caratteri in str2 dal lato destro di str1 (sensibile alle maiuscole).

Esempi:

SELECT RTRIM("Say hello", "leo") restituisce "Say h".

SELECT RTRIM("Say hello ", " hloe") restituisce "Say".

SPLIT('str' [, 'delimiter'])

Suddivide una stringa in sottostringhe ripetute. Se viene specificato delimiter, 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 ha una lunghezza massima di max_len caratteri. Il conteggio inizia da 1, quindi il primo carattere della stringa si trova in 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.

'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 per le tabelle

Le funzioni con caratteri jolly per le tabelle sono un modo pratico per eseguire query sui dati di un insieme specifico di tabelle. Una funzione carattere jolly della tabella è equivalente a un'unione distinta da virgola di tutte le tabelle corrispondenti alla funzione carattere jolly. Quando utilizzi una funzione di carattere jolly per le tabelle, BigQuery accede e addebita solo le tabelle che corrispondono al carattere jolly. Le funzioni di caratteri jolly delle tabelle vengono specificate nella clausola FROM della query.

Se utilizzi funzioni carattere jolly della tabella in una query, non è più necessario che siano racchiuse tra parentesi. Ad esempio, alcuni dei seguenti esempi utilizzano le parentesi, mentre altri no.

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

Sintassi

TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)

Esegui query sulle tabelle giornaliere che si sovrappongono all'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 di timestamp. Ad esempio:

• TIMESTAMP('2012-10-01 02:03:04')
• DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')

Esempio: ottieni le tabelle tra due giorni

Questo esempio presuppone che esistano le 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: ottieni le tabelle in un intervallo di due giorni fino a "ora"

Questo esempio presuppone che le seguenti tabelle esistano in un progetto denominato myproject-1234:

• mydata.people20140323
• mydata.people20140324
• mydata.people20140325

#legacySQL
SELECT
  name
FROM
  (TABLE_DATE_RANGE([myproject-1234:mydata.people],
                    DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'),
                    CURRENT_TIMESTAMP()))
WHERE
  age >= 35

Corrisponde alle seguenti tabelle:

• mydata.people20140323
• mydata.people20140324
• mydata.people20140325

TABLE_DATE_RANGE_STRICT(prefix, timestamp1, timestamp2)

Questa funzione è equivalente a TABLE_DATE_RANGE. L'unica differenza è che se nella sequenza manca una tabella giornaliera, TABLE_DATE_RANGE_STRICT non riesce e restituisce un errore Not Found: Table <table_name>.

Esempio: errore relativo alla tabella mancante

Questo esempio presuppone che esistano le 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 riportato sopra restituisce un errore "Non trovato" per la tabella "people20140326".

TABLE_QUERY(dataset, expr)

Esegue query sulle tabelle i cui nomi corrispondono a expr specificato. Il parametro expr deve essere rappresentato come stringa e deve contenere un'espressione da valutare. Ad esempio: 'length(table_id) < 3'.

Esempio: abbina le tabelle i cui nomi contengono "oo" e hanno una lunghezza maggiore di 4

Questo esempio presuppone che esistano le 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: abbina le tabelle i cui nomi iniziano con "boo", seguite da 3-5 cifre numeriche

Questo esempio presuppone che le seguenti tabelle esistano 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

HOST('url_str')

Dato un URL, restituisce il nome host come stringa. Esempio: HOST('http://www.google.com:80/index.html') restituisce "www.google.com"

DOMAIN('url_str')

Dato un URL, restituisce il dominio come stringa. Esempio: DOMAIN('http://www.google.com:80/index.html') restituisce "google.com".

TLD('url_str')

Dato un URL, restituisce il dominio di primo livello più eventuali domini di paese presenti 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 ricerche DNS inverse, pertanto se le chiami utilizzando un indirizzo IP, restituiranno i segmenti dell'indirizzo IP anziché i segmenti del nome host.
• Tutte le funzioni di analisi dell'URL prevedono caratteri in minuscolo. I caratteri maiuscoli nell'URL produrranno un risultato NULL o comunque errato. Valuta la possibilità di passare l'input a questa funzione tramite LOWER() se i dati hanno lettere maiuscole e minuscole.

Esempio avanzato

Eseguire l'analisi dei nomi di dominio dai dati dell'URL

Questa query utilizza la funzione DOMAIN() per restituire i domini più popolari elencati come home page dei repository su GitHub. Nota l' utilizzo di HAVING per filtrare i record utilizzando il risultato della funzione DOMAIN(). Si tratta di una funzione utile per determinare le informazioni sui 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;

Restituisce:

+-----------------+----------------+
|   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 sui TLD, utilizza la funzione TLD(). Questo esempio mostra i principali domini di primo livello che non sono inclusi in un elenco di esempi comuni.

#legacySQL
SELECT
  TLD(repository_homepage) AS user_tld,
  COUNT(*) AS activity_count
FROM
  [bigquery-public-data:samples.github_timeline]
GROUP BY
  user_tld
HAVING
  /* Only consider TLDs that are NOT NULL */
  /* or in our list of common TLDs */
  user_tld IS NOT NULL AND NOT user_tld
  IN ('','.com','.net','.org','.info','.edu')
ORDER BY
  activity_count DESC
LIMIT 5;

Restituisce:

+----------+----------------+
| user_tld | activity_count |
+----------+----------------+
| .de      |          22934 |
| .io      |          17528 |
| .me      |          13652 |
| .fr      |          12895 |
| .co.uk   |           9135 |
+----------+----------------+

Funzioni finestra

Le funzioni finestra, note anche come funzioni analitiche, consentono di eseguire calcoli su un insieme specifico, o "finestra", di un insieme di risultati. Le funzioni finestra semplificano la creazione di report che includono analisi complesse come medie cumulative 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 inquadratura) forniscono un controllo aggiuntivo sulla finestra. La partizione ti 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. La funzionalità Framing consente di creare un riquadro della finestra scorrevole all'interno di una partizione che si sposta rispetto alla riga corrente. Puoi configurare le dimensioni dell'intervallo 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 colonne separati da virgole. Verrà creata una partizione per ogni insieme distinto di valori per queste colonne, in modo simile a una clausola GROUP BY. Se PARTITION BY viene omesso, la partizione di base è costituita da tutte le righe dell'input alla funzione finestra.

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

Le clausole

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

ORDER BY

Ordina la partizione. Se ORDER BY non è presente, non è garantito alcun ordine di ordinamento predefinito. L'ordinamento avviene a livello di partizione, prima dell'applicazione di qualsiasi clausola del 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 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 estenda 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ò avere le stesse dimensioni della partizione o essere più piccola. Se utilizzi ORDER BY senza un window-frame-clause, il riquadro della finestra predefinito è RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Se ometti sia ORDER BY sia window-frame-clause, il riquadro 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 mostri la somma delle 5 righe precedenti di valori di stipendio, esegui una query su SUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW). L'insieme di righe include in genere la riga corrente, ma non è obbligatorio.
• RANGE: definisce una finestra in termini di intervallo di valori in una determinata colonna, rispetto al valore della colonna nella riga corrente. Opera solo su numeri e date, dove i valori delle date sono semplici numeri interi (microsecondi dall'epoca). Le righe adiacenti con lo stesso valore sono chiamate righe peer. Le righe peer di CURRENT ROW sono incluse in un frame della finestra che specifica CURRENT ROW. Ad esempio, se specifichi che la fine della finestra è CURRENT ROW e la riga successiva nella finestra ha lo stesso valore, verrà inclusa nel calcolo della funzione.
• BETWEEN <start> AND <end>: un intervallo, incluse le righe iniziale e finale. L'intervallo non deve includere la riga corrente, ma <start> deve precedere o essere uguale <end>.
• <start>: specifica l'offset iniziale per questa finestra rispetto 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 valore di riga o intervallo precedente e FOLLOWING indica un valore di riga o 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 di fine per questa finestra rispetto alla riga corrente. Sono supportate le seguenti opzioni:

  {UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}

  dove <expr> è un numero intero positivo, PRECEDING indica un numero di riga precedente o un valore di intervallo e FOLLOWING indica un numero di riga successivo o un valore di intervallo. UNBOUNDED FOLLOWING indica l'ultima riga della partizione. Se fine è oltre la fine della finestra, verrà impostato sull'ultima riga della partizione.

A differenza delle funzioni di aggregazione, che uniscono molte righe di input in una sola riga di output, le funzioni 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 progressivo per un piccolo set di dati di cinque righe definito da 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);