Sintaksis, Fungsi, dan Operator SQL Lama

Dokumen ini menjelaskan sintaksis, fungsi, dan operator kueri SQL lama. Sintaksis kueri yang lebih disukai untuk BigQuery adalah GoogleSQL. Untuk informasi tentang GoogleSQL, lihat Sintaksis kueri GoogleSQL.

Sintaksis kueri

Catatan: Kata kunci tidak peka huruf besar/kecil. Dalam dokumen ini, kata kunci seperti SELECT menggunakan huruf besar sebagai ilustrasi saja.

Klausa SELECT

Klausa SELECT menentukan daftar ekspresi yang akan dihitung. Ekspresi dalam klausa SELECT dapat berisi nama kolom, literal, dan panggilan fungsi (termasuk fungsi agregat dan fungsi jendela) serta kombinasi dari ketiganya. Daftar ekspresi dipisahkan koma.

Setiap ekspresi dapat diberi alias dengan menambahkan spasi yang diikuti dengan ID setelah ekspresi. Kata kunci AS opsional dapat ditambahkan antara ekspresi dan alias agar lebih mudah dibaca. Alias yang ditentukan dalam klausa SELECT dapat direferensikan dalam klausa GROUP BY, HAVING, dan ORDER BY pada kueri, tetapi tidak oleh klausa FROM, WHERE, atau OMIT RECORD IF, atau oleh ekspresi lain dalam klausa SELECT yang sama.

Catatan:

  • Jika Anda menggunakan fungsi agregat dalam klausa SELECT, Anda harus menggunakan fungsi agregat di semua ekspresi atau kueri Anda harus memiliki klausa GROUP BY yang menyertakan semua kolom non-agregat di klausa SELECT sebagai kunci pengelompokan. Contoh: 
    #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. */
  • Anda dapat menggunakan tanda kurung siku untuk meng-escape kata yang dicadangkan sehingga Anda dapat menggunakannya sebagai nama kolom dan alias. Misalnya, jika Anda memiliki kolom bernama "partition", yang merupakan kata khusus untuk sistem dalam sintaksis BigQuery, kueri yang mereferensikan ke kolom tersebut akan gagal dengan pesan error yang tidak jelas, kecuali jika Anda meng-escape kolom itu dengan tanda kurung siku: 
    SELECT [partition] FROM ...
Contoh

Contoh ini menentukan alias dalam klausa SELECT, lalu mereferensikan salah satunya dalam klausa ORDER BY. Perhatikan bahwa kolom word tidak dapat direferensikan menggunakan word_alias dalam klausa WHERE; kolom itu harus direferensikan dengan nama. Alias len juga tidak terlihat dalam klausa WHERE. Alias tersebut akan terlihat dalam klausa HAVING. 

#legacySQL
SELECT
  word AS word_alias,
  LENGTH(word) AS len
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS 'th'
ORDER BY
  len;

Pengubah WITHIN untuk fungsi agregat

aggregate_function WITHIN RECORD [ [ AS ] alias ]

Kata kunci WITHIN menyebabkan fungsi agregat menggabungkan seluruh nilai berulang dalam setiap kumpulan data. Untuk setiap kumpulan data input, hanya satu output gabungan yang akan dihasilkan. Jenis agregasi ini disebut sebagai agregasi cakupan. Karena agregasi cakupan menghasilkan output untuk setiap kumpulan data, ekspresi non-gabungan dapat dipilih bersama dengan ekspresi agregasi cakupan tanpa menggunakan klausa GROUP BY.

Biasanya Anda akan menggunakan cakupan RECORD saat menggunakan agregasi cakupan. Jika memiliki skema berulang dan bertingkat yang sangat kompleks, Anda mungkin perlu melakukan agregasi dalam cakupan sub-data. Hal ini dapat dilakukan dengan mengganti kata kunci RECORD dalam sintaksis di atas dengan nama node dalam skema tempat Anda ingin agregasi dilakukan. Untuk mengetahui informasi selengkapnya tentang perilaku lanjutan tersebut, lihat Menangani data.

Contoh

Contoh ini melakukan agregasi COUNT yang tercakup, lalu memfilter dan mengurutkan kumpulan data berdasarkan nilai gabungan. 

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

Klausa FROM

FROM
  [project_name:]datasetId.tableId [ [ AS ] alias ] |
  (subquery) [ [ AS ] alias ] |
  JOIN clause |
  FLATTEN clause |
  table wildcard function

Klausa FROM menentukan data sumber yang akan dikueri. Kueri BigQuery dapat dijalankan langsung melalui tabel, subkueri, tabel gabungan, dan tabel yang dimodifikasi oleh operator dengan tujuan khusus seperti yang dijelaskan di bawah ini. Kombinasi sumber data ini dapat dikueri menggunakan koma, yang merupakan operator UNION ALL di BigQuery.

Mereferensikan tabel

Saat mereferensikan tabel, datasetId dan tableId harus ditentukan; project_name bersifat opsional. Jika project_name tidak ditentukan, BigQuery akan menetapkannya secara default ke project saat ini. Jika nama project Anda menyertakan tanda hubung, Anda harus mengapit seluruh referensi tabel dengan tanda kurung siku.

Contoh
[my-dashed-project:dataset1.tableName]

Tabel dapat diberi alias dengan menambahkan spasi diikuti dengan ID setelah nama tabel. Kata kunci AS opsional dapat ditambahkan antara tableId dan alias agar lebih mudah dibaca.

Saat mereferensikan kolom dari tabel, Anda dapat menggunakan nama kolom sederhana atau memberikan awalan pada nama kolom dengan alias, jika Anda menentukannya, atau dengan datasetId dan tableId selama tidak ada project_name yang ditentukan. project_name tidak dapat disertakan dalam awalan kolom karena karakter titik dua tidak diizinkan dalam nama kolom.

Contoh

Contoh ini mereferensikan kolom tanpa awalan tabel. 

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

Contoh ini memberi awalan pada nama kolom dengan datasetId dan tableId. Perhatikan bahwa project_name tidak dapat disertakan dalam contoh ini. Metode ini hanya akan berfungsi jika set data berada dalam project default Anda saat ini. 

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

Contoh ini memberi awalan pada nama kolom dengan alias tabel. 

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

Tabel berpartisi berdasarkan rentang bilangan bulat

SQL lama mendukung penggunaan dekorator tabel untuk menangani partisi tertentu dalam tabel berpartisi berdasarkan rentang bilangan bulat. Kunci untuk menangani partisi rentang adalah awal dari rentang.

Contoh berikut mengkueri partisi rentang yang dimulai dengan 30: 

#legacySQL
SELECT
  *
FROM
  dataset.table$30;

Perhatikan bahwa Anda tidak dapat menggunakan SQL lama untuk membuat kueri di seluruh tabel berpartisi berdasarkan rentang bilangan bulat. Sebagai gantinya, kueri akan menampilkan error seperti berikut:

Querying tables partitioned on a field is not supported in Legacy SQL

Menggunakan subkueri

Subkueri adalah pernyataan SELECT bertingkat yang digabungkan dalam tanda kurung. Ekspresi yang dihitung dalam klausa SELECT pada subkueri tersedia untuk kueri outer, sama seperti kolom tabel yang akan tersedia.

Subkueri dapat digunakan untuk menghitung agregasi dan ekspresi lainnya. Berbagai operator SQL tersedia di subkueri. Artinya, subkueri itu sendiri dapat berisi subkueri lain, subkueri dapat melakukan penggabungan dan mengelompokkan agregasi, dll.

Koma sebagai UNION ALL

Tidak seperti GoogleSQL, legacy SQL menggunakan koma sebagai operator UNION ALL, bukan operator CROSS JOIN. Ini adalah perilaku lama yang berkembang karena selama ini BigQuery tidak mendukung CROSS JOIN, dan pengguna BigQuery harus menulis kueri UNION ALL secara rutin. Di GoogleSQL, kueri yang menjalankan penggabungan biasanya sangat panjang. Penggunaan koma sebagai operator gabungan memungkinkan penulisan kueri semacam itu menjadi jauh lebih efisien. Misalnya, kueri ini dapat digunakan untuk menjalankan satu kueri terhadap log dari beberapa hari. 

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

Kueri yang menggabungkan sejumlah besar tabel biasanya berjalan lebih lambat daripada kueri yang memproses jumlah data yang sama dari satu tabel. Perbedaan performa dapat mencapai 50 md per tabel tambahan. Satu kueri dapat menggabungkan maksimal 1.000 tabel.

Fungsi karakter pengganti tabel

Istilah fungsi karakter pengganti tabel merujuk pada jenis fungsi khusus yang unik untuk BigQuery. Fungsi ini digunakan dalam klausa FROM untuk mencocokkan kumpulan nama tabel menggunakan salah satu dari beberapa jenis filter. Misalnya, fungsi TABLE_DATE_RANGE hanya dapat digunakan untuk membuat kueri terhadap kumpulan tabel harian tertentu. Untuk mengetahui informasi selengkapnya tentang fungsi ini, lihat Fungsi karakter pengganti tabel.

Operator FLATTEN

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

Tidak seperti sistem pemrosesan SQL biasa, BigQuery dirancang untuk menangani data berulang. Karena itu, pengguna BigQuery terkadang perlu menulis kueri yang memanipulasi struktur kumpulan data berulang. Salah satu cara untuk melakukannya adalah menggunakan operator FLATTEN.

FLATTEN mengonversi satu node dalam skema dari berulang menjadi opsional. Dengan mempertimbangkan kumpulan data dengan satu atau beberapa nilai untuk kolom berulang, FLATTEN akan membuat beberapa kumpulan data, satu untuk setiap nilai di kolom berulang. Semua kolom lain yang dipilih dari kumpulan data akan diduplikasi di setiap kumpulan data output baru. FLATTEN dapat diterapkan berulang kali untuk menghapus beberapa tingkat pengulangan.

Untuk mengetahui informasi dan contoh selengkapnya, lihat Menangani data.

Operator JOIN

BigQuery mendukung beberapa operator JOIN di setiap klausa FROM. Operasi JOIN berikutnya menggunakan hasil dari operasi JOIN sebelumnya sebagai input JOIN kiri. Kolom dari input JOIN sebelumnya dapat digunakan sebagai kunci dalam klausa ON dari operator JOIN berikutnya.

Jenis JOIN

BigQuery mendukung operasi INNER, [FULL|RIGHT|LEFT] OUTER, dan CROSS JOIN. Jika tidak ditentukan, defaultnya adalah INNER.

Operasi CROSS JOIN tidak mengizinkan klausa ON. CROSS JOIN dapat menampilkan data dalam jumlah besar serta dapat menghasilkan kueri yang lambat dan tidak efisien, atau kueri yang melebihi resource per kueri maksimum yang diizinkan. Kueri tersebut akan gagal dengan pesan error. Jika memungkinkan, pilih kueri yang tidak menggunakan CROSS JOIN. Misalnya, CROSS JOIN sering digunakan di berbagai tempat fungsi jendela akan lebih efisien.

Pengubah EACH

Pengubah EACH adalah petunjuk yang memberi tahu BigQuery untuk menjalankan JOIN menggunakan beberapa partisi. Hal ini sangat berguna jika Anda mengetahui bahwa kedua sisi JOIN berukuran besar. Pengubah EACH tidak dapat digunakan dalam klausa CROSS JOIN.

Penggunaan EACH sebelumnya disarankan dalam banyak kasus, tetapi sekarang tidak lagi. Jika memungkinkan, gunakan JOIN tanpa pengubah EACH untuk mendapatkan performa yang lebih baik. Gunakan JOIN EACH saat kueri Anda gagal dengan pesan error yang menyatakan bahwa resource terlampaui.

Semi-join dan Anti-join

Selain mendukung JOIN dalam klausa FROM, BigQuery juga mendukung dua jenis join dalam klausa WHERE: semi-join dan anti-semi-join. Semi-join ditentukan menggunakan kata kunci IN dengan subkueri; anti-join ditentukan menggunakan kata kunci NOT IN.

Contoh

Kueri berikut menggunakan semi-join untuk menemukan n-gram dengan kata pertama dalam n-gram juga merupakan kata kedua dalam n-gram lain yang memiliki "AND" sebagai kata ketiga dalam n-gram. 

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

Kueri berikut menggunakan semi-join untuk menampilkan jumlah perempuan berusia di atas 50 tahun yang melahirkan di 10 negara bagian dengan kelahiran terbanyak. 

#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

Guna melihat jumlah perempuan tersebut untuk 40 negara bagian lainnya, Anda dapat menggunakan anti-join. Kueri berikut hampir identik dengan contoh sebelumnya, tetapi menggunakan NOT IN dan bukan IN untuk menampilkan jumlah perempuan berusia di atas 50 tahun yang melahirkan di 40 negara bagian dengan angka kelahiran paling sedikit. 

#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

Catatan:

  • BigQuery tidak mendukung semi-join atau anti-semi-join yang berkorelasi. Subkueri tidak dapat mereferensikan kolom apa pun dari kueri outer.
  • Subkueri yang digunakan dalam semi-join atau anti-semi-join harus memilih satu kolom.
  • Jenis kolom yang dipilih dan kolom yang digunakan dari kueri outer dalam klausa WHERE harus sama persis. BigQuery tidak akan melakukan pemaksaan jenis apa pun untuk semi-join atau anti-semi-join.

Klausa WHERE

Klausa WHERE, yang terkadang disebut predikat, memfilter kumpulan data yang dihasilkan oleh klausa FROM menggunakan ekspresi boolean. Beberapa kondisi dapat digabungkan dengan klausa AND dan OR boolean, yang secara opsional diapit oleh tanda kurung—()— untuk mengelompokkannya. Kolom yang tercantum dalam klausa WHERE tidak perlu dipilih dalam klausa SELECT yang terkait dan ekspresi klausa WHERE tidak dapat merujuk ke ekspresi yang dihitung dalam klausa SELECT pada kueri yang memiliki klausa WHERE.

Catatan: Fungsi agregat tidak dapat digunakan dalam klausa WHERE. Gunakan klausa HAVING dan kueri outer jika Anda perlu memfilter output fungsi agregat.

Contoh

Contoh berikut menggunakan disjungsi ekspresi boolean dalam klausa WHERE — dua ekspresi yang digabungkan oleh operator OR. Kumpulan data input akan diteruskan melalui filter WHERE jika salah satu ekspresi menampilkan 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');

Klausa OMIT RECORD IF

Klausa OMIT RECORD IF adalah konstruksi yang unik untuk BigQuery. Klausa ini sangat berguna untuk menangani skema berulang yang bertingkat. Ini mirip dengan klausa WHERE , tetapi berbeda karena dua hal penting. Pertama, metode ini menggunakan kondisi pengecualian, yang berarti bahwa kumpulan data dihilangkan jika ekspresi menampilkan true, tetapi disimpan jika ekspresi menampilkan false atau null. Kedua, klausa OMIT RECORD IF dapat (dan biasanya) menggunakan fungsi agregat cakupan dalam kondisinya.

Selain memfilter kumpulan data lengkap, OMIT...IF dapat menentukan cakupan yang lebih sempit untuk memfilter sebagian data saja. Hal ini dilakukan dengan menggunakan nama node non-leaf di skema Anda, bukan RECORD di klausa OMIT...IF. Fungsi ini jarang digunakan oleh pengguna BigQuery. Anda dapat menemukan dokumentasi selengkapnya tentang perilaku lanjutan ini yang ditautkan dari dokumentasi WITHIN di atas.

Jika Anda menggunakan OMIT...IF untuk mengecualikan sebagian data dalam kolom berulang, dan kueri juga memilih kolom berulang secara independen lainnya, BigQuery akan menghilangkan sebagian dari kumpulan data berulang lainnya dalam kueri. Jika Anda melihat error Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>,, sebaiknya beralih ke GoogleSQL. Untuk mengetahui informasi tentang cara memigrasikan pernyataan OMIT...IF ke GoogleSQL, lihat Bermigrasi ke GoogleSQL.

Contoh

Mengacu kembali ke contoh yang digunakan untuk pengubah WITHIN, OMIT RECORD IF dapat digunakan untuk melakukan hal yang sama seperti yang digunakan WITHIN dan HAVING dalam contoh tersebut. 

#legacySQL
SELECT
  repository.url
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  COUNT(payload.pages.page_name) <= 80;

Klausa GROUP BY

Dengan klausa GROUP BY, Anda dapat mengelompokkan baris yang memiliki nilai sama untuk kolom atau kumpulan kolom tertentu sehingga Anda dapat menghitung agregasi kolom terkait. Pengelompokan terjadi setelah pemfilteran yang dilakukan dalam klausa WHERE, tetapi sebelum ekspresi dalam klausa SELECT dihitung. Hasil ekspresi tidak dapat digunakan sebagai kunci grup dalam klausa GROUP BY.

Contoh

Kueri ini menemukan sepuluh kata pertama teratas yang paling umum dalam set data sampel trigram. Selain menunjukkan penggunaan klausa GROUP BY, contoh ini juga menunjukkan bagaimana indeks posisi dapat digunakan sebagai ganti nama kolom dalam klausa GROUP BY dan ORDER BY. 

#legacySQL
SELECT
  first,
  COUNT(ngram)
FROM
  [bigquery-public-data:samples.trigrams]
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 10;

Agregasi yang dilakukan menggunakan klausa GROUP BY disebut agregasi yang dikelompokkan . Tidak seperti agregasi cakupan, agregasi yang dikelompokkan biasa terjadi di sebagian besar sistem pemrosesan SQL.

Pengubah EACH

Pengubah EACH adalah petunjuk yang memberi tahu BigQuery untuk menjalankan GROUP BY menggunakan beberapa partisi. Hal ini sangat berguna jika Anda tahu bahwa set data berisi sejumlah besar nilai yang berbeda untuk kunci grup.

Penggunaan EACH sebelumnya disarankan dalam banyak kasus, tetapi sekarang tidak lagi. Menggunakan GROUP BY tanpa pengubah EACH biasanya memberikan performa yang lebih baik. Gunakan GROUP EACH BY saat kueri Anda gagal dengan pesan error yang menyatakan bahwa resource terlampaui.

Fungsi ROLLUP

Saat fungsi ROLLUP digunakan, BigQuery akan menambahkan baris tambahan ke hasil kueri yang merepresentasikan agregasi yang digabungkan. Semua kolom yang tercantum setelah ROLLUP harus diapit dalam satu set tanda kurung. Dalam baris yang ditambahkan karena fungsi ROLLUP, NULL menunjukkan kolom yang agregasinya digabungkan.

Contoh

Kueri ini menghasilkan jumlah kelahiran laki-laki dan perempuan per tahun dari sampel set data kelahiran. 

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

Ini hasil kuerinya. Perhatikan bahwa ada baris yang salah satu atau kedua kunci grupnya adalah NULL. Baris tersebut merupakan baris rollup. 

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

Saat menggunakan fungsi ROLLUP, Anda dapat menggunakan fungsi GROUPING untuk membedakan antara baris yang ditambahkan karena fungsi ROLLUP dan baris yang benar-benar memiliki nilai NULL untuk kunci grup.

Contoh

Kueri ini menambahkan fungsi GROUPING ke contoh sebelumnya untuk mengidentifikasi baris yang ditambahkan karena fungsi ROLLUP dengan lebih baik. 

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

Ini hasil yang ditampilkan pada kueri baru. 

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

Catatan:

  • Kolom yang tidak digabungkan dalam klausa SELECT harus dicantumkan dalam klausa 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. */
  • Ekspresi yang dihitung dalam klausa SELECT tidak dapat digunakan dalam klausa GROUP BY yang terkait. 
    #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. */
  • Pengelompokan menurut nilai float dan ganda tidak didukung, karena fungsi kesetaraan untuk jenis tersebut tidak ditentukan dengan baik.
  • Karena sistem ini bersifat interaktif, kueri yang menghasilkan grup dalam jumlah besar mungkin gagal. Penggunaan fungsi TOP, bukan GROUP BY, dapat menyelesaikan beberapa masalah penskalaan.

Klausa HAVING

Klausa HAVING berperilaku sama persis seperti klausa WHERE, kecuali klausa ini dinilai setelah klausa SELECT sehingga hasil dari semua ekspresi yang dihitung dapat dilihat oleh klausa HAVING. Klausa HAVING hanya dapat merujuk ke output dari klausa SELECT yang terkait.

Contoh

Kueri ini menghitung kata pertama yang paling umum dalam set data sampel n-gram yang berisi huruf a dan muncul maksimal 10.000 kali. 

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

Klausa ORDER BY

Klausa ORDER BY mengurutkan hasil kueri dalam urutan menaik atau menurun menggunakan satu atau beberapa kolom kunci. Untuk mengurutkan berdasarkan beberapa kolom atau alias, masukkan kolom atau alias tersebut sebagai daftar yang dipisahkan koma. Hasilnya diurutkan berdasarkan kolom sesuai urutan yang tercantum. Gunakan DESC (menurun) atau ASC (menaik) untuk menentukan arah pengurutan. ASC adalah defaultnya. Arah pengurutan yang berbeda dapat ditentukan untuk setiap kunci pengurutan.

Klausa ORDER BY dievaluasi setelah klausa SELECT, sehingga dapat mereferensikan output dari ekspresi apa pun yang dihitung dalam SELECT. Jika kolom diberi alias dalam klausa SELECT, alias tersebut harus digunakan dalam klausa ORDER BY.

Klausa LIMIT

Klausa LIMIT membatasi jumlah baris dalam kumpulan hasil yang ditampilkan. Karena kueri BigQuery secara rutin beroperasi pada baris yang sangat besar, LIMIT adalah cara yang baik untuk menghindari kueri yang berjalan lama karena hanya memproses sebagian baris.

Catatan:

  • Klausa LIMIT akan berhenti memproses dan menampilkan hasilnya jika telah memenuhi persyaratan Anda. Hal ini dapat mengurangi waktu pemrosesan untuk beberapa kueri, tetapi saat Anda menentukan fungsi agregat seperti klausa COUNT atau ORDER BY, kumpulan hasil lengkap masih harus diproses sebelum menampilkan hasil. Klausa LIMIT adalah klausa terakhir yang akan dievaluasi.
  • Kueri dengan klausa LIMIT mungkin masih non-deterministik jika tidak ada operator dalam kueri yang menjamin pengurutan kumpulan hasil output. Hal ini karena BigQuery dijalankan menggunakan sejumlah besar worker paralel. Urutan kemunculan tugas paralel tidak dijamin.
  • Klausa LIMIT tidak boleh berisi fungsi apa pun; kueri hanya menggunakan konstanta numerik.
  • Saat klausa LIMIT digunakan, total byte yang diproses dan byte yang ditagih dapat bervariasi untuk kueri yang sama.

Tata bahasa kueri

Setiap klausa dari pernyataan SELECT BigQuery dijelaskan secara mendetail di atas. Di sini kami menyajikan tata bahasa lengkap dari pernyataan SELECT dalam bentuk yang ringkas dengan link kembali ke setiap bagian. 

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
    }

Notasi:

  • Tanda kurung siku "[ ]" menunjukkan klausa opsional.
  • Tanda kurung kurawal "{ }" mencakup serangkaian opsi.
  • Batang vertikal "|" menunjukkan logika OR.
  • Koma atau kata kunci yang diikuti elipsis dalam tanda kurung siku "[, ... ]" menunjukkan bahwa item sebelumnya dapat diulang dalam daftar dengan pemisah yang ditentukan.
  • Tanda kurung "( )" menunjukkan tanda kurung literal.

Fungsi dan operator yang didukung

Sebagian besar klausa pernyataan SELECT mendukung fungsi. Kolom yang direferensikan dalam fungsi tidak perlu dicantumkan dalam klausa SELECT apa pun. Oleh karena itu, kueri berikut valid, meskipun kolom clicks tidak ditampilkan secara langsung:

#legacySQL
SELECT country, SUM(clicks) FROM table GROUP BY country;
Fungsi agregat
AVG() Menampilkan rata-rata nilai untuk sekelompok baris ...
BIT_AND() Menampilkan hasil operasi bitwise AND ...
BIT_OR() Menampilkan hasil operasi bitwise OR ...
BIT_XOR() Menampilkan hasil operasi bitwise XOR ...
CORR() Menampilkan koefisien korelasi Pearson dari sekumpulan pasangan angka.
COUNT() Menampilkan jumlah total nilai ...
COUNT([DISTINCT]) Menampilkan jumlah total nilai non-NULL ...
COVAR_POP() Menghitung kovarians populasi dari nilai ...
COVAR_SAMP() Menghitung kovarians sampel dari nilai ...
EXACT_COUNT_DISTINCT() Menampilkan jumlah tepat nilai non-NULL yang berbeda untuk kolom yang ditentukan.
FIRST() Menampilkan nilai berurutan pertama dalam cakupan fungsi.
GROUP_CONCAT() Menggabungkan beberapa string menjadi satu string ...
GROUP_CONCAT_UNQUOTED() Menggabungkan beberapa string menjadi satu string ... tidak akan menambahkan tanda kutip ganda ...
LAST() Menampilkan nilai berurutan terakhir ...
MAX() Menampilkan nilai maksimum ...
MIN() Menampilkan nilai minimum ...
NEST() Menggabungkan semua nilai dalam cakupan agregasi saat ini ke dalam kolom berulang.
NTH() Menampilkan nilai berurutan ke-n ...
QUANTILES() Menghitung perkiraan minimum, maksimum, dan kuantil ...
STDDEV() Menampilkan simpangan baku ...
STDDEV_POP() Menghitung simpangan baku populasi ...
STDDEV_SAMP() Menghitung simpangan baku sampel ...
SUM() Menampilkan jumlah total nilai ...
TOP() ... COUNT(*) Menampilkan kumpulan data max_records teratas menurut frekuensi.
UNIQUE() Menampilkan kumpulan nilai unik non-NULL ...
VARIANCE() Menghitung varians dari nilai ...
VAR_POP() Menghitung varians populasi dari nilai ...
VAR_SAMP() Menghitung varians sampel dari nilai ...
Operator aritmetika
+ Penambahan
- Pengurangan
* Perkalian
/ Pembagian
% Modulus
Fungsi bitwise
& Bitwise AND
| Bitwise OR
^ Bitwise XOR
<< Bitwise Shift Left
>> Bitwise Shift Right
~ Bitwise NOT
BIT_COUNT() Menampilkan jumlah bit ...
Fungsi transmisi
BOOLEAN() Melakukan transmisi ke boolean.
BYTES() Melakukan transmisi ke byte.
CAST(expr AS type) Mengonversi expr menjadi variabel jenis type.
FLOAT() Melakukan transmisi ke nilai ganda.
HEX_STRING() Melakukan transmisi ke string heksadesimal.
INTEGER() Melakukan transmisi ke bilangan bulat.
STRING() Melakukan transmisi ke string.
Fungsi perbandingan
expr1 = expr2 Menampilkan true jika ekspresi sama.
expr1 != expr2
expr1 <> expr2 		Menampilkan true jika ekspresi tidak sama.
expr1 > expr2 Menampilkan true jika expr1 lebih besar dari expr2.
expr1 < expr2 Menampilkan true jika expr1 kurang dari expr2.
expr1 >= expr2 Menampilkan true jika expr1 lebih besar dari atau sama dengan expr2.
expr1 <= expr2 Menampilkan true jika expr1 kurang dari atau sama dengan expr2.
expr1 BETWEEN expr2 AND expr3 Menampilkan true jika nilai expr1 antara expr2 dan expr3, inklusif.
expr IS NULL Menampilkan true jika expr adalah NULL.
expr IN() Menampilkan true jika expr cocok dengan expr1, expr2, atau nilai apa pun dalam tanda kurung.
COALESCE() Menampilkan argumen pertama yang bukan NULL.
GREATEST() Menampilkan parameter numeric_expr terbesar.
IFNULL() Jika argumen tidak bernilai null, argumen akan ditampilkan.
IS_INF() Menampilkan true jika tak terhingga positif atau negatif.
IS_NAN() Menampilkan true jika argumen adalah NaN.
IS_EXPLICITLY_DEFINED() tidak digunakan lagi: Gunakan expr IS NOT NULL sebagai gantinya.
LEAST() Menampilkan parameter numeric_expr argumen terkecil.
NVL() Jika expr bukan null, expr akan ditampilkan. Jika tidak, null_default akan ditampilkan.
Fungsi tanggal dan waktu
CURRENT_DATE() Menampilkan tanggal saat ini dalam format %Y-%m-%d.
CURRENT_TIME() Menampilkan waktu server saat ini dalam format %H:%M:%S.
CURRENT_TIMESTAMP() Menampilkan waktu server saat ini dalam format %Y-%m-%d %H:%M:%S.
DATE() Menampilkan tanggal dalam format %Y-%m-%d.
DATE_ADD() Menambahkan interval yang ditentukan ke jenis data TIMESTAMP.
DATEDIFF() Menampilkan jumlah hari antara dua jenis data TIMESTAMP.
DAY() Menampilkan hari dalam sebulan sebagai bilangan bulat antara 1 dan 31.
DAYOFWEEK() Menampilkan hari dalam seminggu sebagai bilangan bulat antara 1 (Minggu) dan 7 (Sabtu).
DAYOFYEAR() Menampilkan hari dalam setahun sebagai bilangan bulat antara 1 dan 366.
FORMAT_UTC_USEC() Menampilkan stempel waktu UNIX dalam format YYYY-MM-DD HH:MM:SS.uuuuuu.
HOUR() Menampilkan jam pada TIMESTAMP sebagai bilangan bulat antara 0 dan 23.
MINUTE() Menampilkan menit pada TIMESTAMP sebagai bilangan bulat antara 0 dan 59.
MONTH() Menampilkan bulan pada TIMESTAMP sebagai bilangan bulat antara 1 dan 12.
MSEC_TO_TIMESTAMP() Mengonversi stempel waktu UNIX dalam milidetik menjadi TIMESTAMP.
NOW() Menampilkan stempel waktu UNIX saat ini dalam mikrodetik.
PARSE_UTC_USEC() Mengonversi string tanggal menjadi stempel waktu UNIX dalam mikrodetik.
QUARTER() Menampilkan kuartal dalam setahun pada TIMESTAMP sebagai bilangan bulat antara 1 dan 4.
SEC_TO_TIMESTAMP() Mengonversi stempel waktu UNIX dalam detik menjadi TIMESTAMP.
SECOND() Menampilkan detik pada TIMESTAMP sebagai bilangan bulat antara 0 dan 59.
STRFTIME_UTC_USEC() Menampilkan string tanggal dalam format date_format_str.
TIME() Menampilkan TIMESTAMP dalam format %H:%M:%S.
TIMESTAMP() Mengonversi string tanggal menjadi TIMESTAMP.
TIMESTAMP_TO_MSEC() Mengonversi TIMESTAMP menjadi stempel waktu UNIX dalam milidetik.
TIMESTAMP_TO_SEC() Mengonversi TIMESTAMP menjadi stempel waktu UNIX dalam detik.
TIMESTAMP_TO_USEC() Mengonversi TIMESTAMP menjadi stempel waktu UNIX dalam mikrodetik.
USEC_TO_TIMESTAMP() Mengonversi stempel waktu UNIX dalam mikrodetik menjadi TIMESTAMP.
UTC_USEC_TO_DAY() Menggeser stempel waktu UNIX dalam mikrodetik ke awal hari saat peristiwa terjadi.
UTC_USEC_TO_HOUR() Menggeser stempel waktu UNIX dalam mikrodetik ke awal jam saat peristiwa terjadi.
UTC_USEC_TO_MONTH() Menggeser stempel waktu UNIX dalam mikrodetik ke awal bulan saat peristiwa terjadi.
UTC_USEC_TO_WEEK() Menampilkan stempel waktu UNIX dalam mikrodetik yang mewakili hari dalam seminggu.
UTC_USEC_TO_YEAR() Menampilkan stempel waktu UNIX dalam mikrodetik yang mewakili tahun.
WEEK() Menampilkan minggu pada TIMESTAMP sebagai bilangan bulat antara 1 dan 53.
YEAR() Menampilkan tahun pada TIMESTAMP.
Fungsi IP
FORMAT_IP() Mengonversi 32 bit integer_value yang paling tidak signifikan menjadi string alamat IPv4 yang dapat dibaca manusia.
PARSE_IP() Mengonversi string yang mewakili alamat IPv4 menjadi nilai bilangan bulat yang tidak bertanda tangan.
FORMAT_PACKED_IP() Menampilkan alamat IP yang dapat dibaca manusia dalam bentuk 10.1.5.23 atau 2620:0:1009:1:216:36ff:feef:3f.
PARSE_PACKED_IP() Menampilkan alamat IP dalam BYTES.
Fungsi JSON
JSON_EXTRACT() Memilih nilai berdasarkan ekspresi JSONPath dan menampilkan string JSON.
JSON_EXTRACT_SCALAR() Memilih nilai berdasarkan ekspresi JSONPath dan menampilkan skalar JSON.
Operator logika
expr AND expr Menampilkan true jika kedua ekspresi benar.
expr OR expr Menampilkan true jika salah satu atau kedua ekspresi benar.
NOT expr Menampilkan true jika ekspresi salah.
Fungsi matematika
ABS() Menampilkan nilai absolut dari argumen.
ACOS() Menampilkan kosinus terbalik dari argumen.
ACOSH() Menampilkan kosinus hiperbolik terbalik dari argumen.
ASIN() Menampilkan sinus terbalik dari argumen.
ASINH() Menampilkan sinus hiperbolik terbalik dari argumen.
ATAN() Menampilkan tangen terbalik dari argumen.
ATANH() Menampilkan tangen hiperbolik terbalik dari argumen.
ATAN2() Menampilkan tangen terbalik dari kedua argumen.
CEIL() Membulatkan argumen ke atas ke bilangan bulat terdekat dan menampilkan nilai yang dibulatkan.
COS() Menampilkan kosinus dari argumen.
COSH() Menampilkan kosinus hiperbolik dari argumen.
DEGREES() Mengonversi dari radian ke derajat.
EXP() Menampilkan e pangkat dari argumen.
FLOOR() Membulatkan argumen ke bawah ke bilangan bulat terdekat.
LN()
LOG() 		Menampilkan logaritma natural dari argumen.
LOG2() Menampilkan logaritma basis 2 dari argumen.
LOG10() Menampilkan logaritma basis 10 dari argumen.
PI() Menampilkan konstanta π.
POW() Menampilkan argumen pertama pangkat argumen kedua.
RADIANS() Mengonversi dari derajat ke radian.
RAND() Menampilkan nilai float acak dalam rentang 0,0 <= nilai < 1,0.
ROUND() Membulatkan argumen ke atas atau ke bawah ke bilangan bulat terdekat.
SIN() Menampilkan sinus dari argumen.
SINH() Menampilkan sinus hiperbolik dari argumen.
SQRT() Menampilkan akar kuadrat dari ekspresi.
TAN() Menampilkan tangen dari argumen.
TANH() Menampilkan tangen hiperbolik dari argumen.
Fungsi ekspresi reguler
REGEXP_MATCH() Menampilkan true jika argumen cocok dengan ekspresi reguler.
REGEXP_EXTRACT() Menampilkan bagian argumen yang cocok dengan grup tangkapan dalam ekspresi reguler.
REGEXP_REPLACE() Menggantikan substring yang cocok dengan ekspresi reguler.
Fungsi string
CONCAT() Menampilkan penggabungan dua string atau lebih, atau NULL jika salah satu nilainya adalah NULL.
expr CONTAINS 'str' Menampilkan true jika expr berisi argumen string yang ditentukan.
INSTR() Menampilkan indeks berbasis satu dari kemunculan pertama string.
LEFT() Menampilkan karakter paling kiri dari string.
LENGTH() Menampilkan panjang string.
LOWER() Menampilkan string asli dengan semua karakter dalam huruf kecil.
LPAD() Menyisipkan karakter di sebelah kiri string.
LTRIM() Menghapus karakter dari sisi kiri string.
REPLACE() Mengganti semua kemunculan substring.
RIGHT() Menampilkan karakter paling kanan dari string.
RPAD() Menyisipkan karakter ke sisi kanan string.
RTRIM() Menghapus karakter di akhir dari sisi kanan string.
SPLIT() Memisahkan string menjadi beberapa substring berulang.
SUBSTR() Menampilkan substring ...
UPPER() Menampilkan string asli dengan semua karakter dalam huruf besar.
Fungsi karakter pengganti tabel
TABLE_DATE_RANGE() Membuat kueri beberapa tabel harian yang mencakup rentang tanggal.
TABLE_DATE_RANGE_STRICT() Membuat kueri beberapa tabel harian yang mencakup rentang tanggal, tanpa tanggal yang hilang.
TABLE_QUERY() Membuat kueri tabel yang namanya cocok dengan predikat yang ditentukan.
Fungsi URL
HOST() Pada URL yang diberikan, fungsi ini menampilkan nama host sebagai string.
DOMAIN() Pada URL yang diberikan, fungsi ini menampilkan domain sebagai string.
TLD() Pada URL yang diberikan, fungsi ini menampilkan domain level teratas plus domain negara apa pun di URL tersebut.
Fungsi jendela
AVG()
COUNT(*)
COUNT([DISTINCT])
MAX()
MIN()
STDDEV()
SUM()
 		Operasi yang sama dengan Fungsi agregat yang sesuai, tetapi dihitung melalui jendela yang ditentukan oleh klausa OVER.
CUME_DIST() Menampilkan nilai ganda yang menunjukkan distribusi kumulatif nilai dalam grup nilai ...
DENSE_RANK() Menampilkan peringkat bilangan bulat nilai dalam grup nilai.
FIRST_VALUE() Menampilkan nilai pertama pada kolom yang ditentukan di jendela.
LAG() Memungkinkan Anda membaca data dari baris sebelumnya dalam jendela.
LAST_VALUE() Menampilkan nilai terakhir pada kolom yang ditentukan di jendela.
LEAD() Memungkinkan Anda membaca data dari baris selanjutnya dalam jendela.
NTH_VALUE() Menampilkan nilai <expr> di posisi <n> pada bingkai jendela ...
NTILE() Membagi jendela ke dalam jumlah bucket yang ditentukan.
PERCENT_RANK() Menampilkan peringkat baris saat ini, relatif terhadap baris lain dalam partisi.
PERCENTILE_CONT() Menampilkan nilai interpolasi yang akan dipetakan ke argumen persentil sehubungan dengan jendela ...
PERCENTILE_DISC() Menampilkan nilai yang paling dekat dengan persentil argumen pada jendela.
RANK() Menampilkan peringkat bilangan bulat nilai dalam grup nilai.
RATIO_TO_REPORT() Menampilkan rasio setiap nilai terhadap jumlah nilai.
ROW_NUMBER() Menampilkan nomor baris saat ini dari hasil kueri pada jendela.
Fungsi lainnya
CASE WHEN ... THEN Gunakan CASE untuk memilih di antara dua atau beberapa ekspresi alternatif dalam kueri Anda.
CURRENT_USER() Menampilkan alamat email pengguna yang menjalankan kueri.
EVERY() Menampilkan true jika argumen benar untuk semua inputnya.
FROM_BASE64() Mengonversi string input berenkode base-64 ke format BYTES.
HASH() Menghitung dan menampilkan nilai hash yang ditandatangani 64-bit ...
FARM_FINGERPRINT() Menghitung dan menampilkan nilai sidik jari 64-bit yang ditandatangani ...
IF() Jika argumen pertama benar, argumen kedua akan ditampilkan. Jika tidak, argumen ketiga akan ditampilkan.
POSITION() Menampilkan posisi argumen berbasis satu dan berurutan.
SHA1() Menampilkan hash SHA1, dalam format BYTES.
SOME() Menampilkan true jika argumen benar untuk setidaknya salah satu inputnya.
TO_BASE64() Mengonversi argumen BYTES kei string berenkode base-64.

Fungsi agregat

Fungsi agregat menampilkan nilai yang mewakili ringkasan set data yang lebih besar, yang membuat fungsi ini sangat berguna untuk menganalisis log. Fungsi agregat beroperasi terhadap kumpulan nilai dan menampilkan satu nilai per tabel, grup, atau cakupan:

  • Agregasi tabel

    Menggunakan fungsi agregat untuk meringkas semua baris yang memenuhi syarat dalam tabel. Contoh:

    SELECT COUNT(f1) FROM ds.Table;

  • Agregasi grup

    Menggunakan fungsi agregat dan klausa GROUP BY yang menentukan kolom non-gabungan untuk meringkas baris berdasarkan grup. Contoh:

    SELECT COUNT(f1) FROM ds.Table GROUP BY b1;

    Fungsi TOP mewakili kasus khusus agregasi grup.

  • Agregasi cakupan

    Fitur ini hanya berlaku untuk tabel yang memiliki kolom bertingkat.
    Menggunakan fungsi agregat dan kata kunci WITHIN untuk menggabungkan nilai berulang dalam cakupan yang ditentukan. Contoh:

    SELECT COUNT(m1.f2) WITHIN RECORD FROM Table;

    Cakupannya dapat berupa RECORD, yang sesuai dengan seluruh baris, atau node (kolom berulang dalam satu baris). Fungsi agregasi beroperasi pada nilai-nilai dalam cakupan dan menampilkan hasil gabungan untuk setiap kumpulan data atau node.

Anda dapat menerapkan batasan ke fungsi agregat menggunakan salah satu opsi berikut:

  • Alias dalam kueri subpilihan. Batasan ditentukan dalam klausa WHERE luar.

    #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

  • Alias dalam klausa HAVING.

    #legacySQL
SELECT corpus, count(word) AS count_corpus_words
FROM [bigquery-public-data:samples.shakespeare]
GROUP BY corpus
HAVING count_corpus_words > 4000;

Anda juga dapat merujuk ke alias dalam klausa GROUP BY atau ORDER BY.

Sintaks

Fungsi agregat
AVG() Menampilkan rata-rata nilai untuk sekelompok baris ...
BIT_AND() Menampilkan hasil operasi bitwise AND ...
BIT_OR() Menampilkan hasil operasi bitwise OR ...
BIT_XOR() Menampilkan hasil operasi bitwise XOR ...
CORR() Menampilkan koefisien korelasi Pearson dari sekumpulan pasangan angka.
COUNT() Menampilkan jumlah total nilai ...
COUNT([DISTINCT]) Menampilkan jumlah total nilai non-NULL ...
COVAR_POP() Menghitung kovarians populasi dari nilai ...
COVAR_SAMP() Menghitung kovarians sampel dari nilai ...
EXACT_COUNT_DISTINCT() Menampilkan jumlah tepat nilai non-NULL yang berbeda untuk kolom yang ditentukan.
FIRST() Menampilkan nilai berurutan pertama dalam cakupan fungsi.
GROUP_CONCAT() Menggabungkan beberapa string menjadi satu string ...
GROUP_CONCAT_UNQUOTED() Menggabungkan beberapa string menjadi satu string ... tidak akan menambahkan tanda kutip ganda ...
LAST() Menampilkan nilai berurutan terakhir ...
MAX() Menampilkan nilai maksimum ...
MIN() Menampilkan nilai minimum ...
NEST() Menggabungkan semua nilai dalam cakupan agregasi saat ini ke dalam kolom berulang.
NTH() Menampilkan nilai berurutan ke-n ...
QUANTILES() Menghitung perkiraan minimum, maksimum, dan kuantil ...
STDDEV() Menampilkan simpangan baku ...
STDDEV_POP() Menghitung simpangan baku populasi ...
STDDEV_SAMP() Menghitung simpangan baku sampel ...
SUM() Menampilkan jumlah total nilai ...
TOP() ... COUNT(*) Menampilkan kumpulan data max_records teratas menurut frekuensi.
UNIQUE() Menampilkan kumpulan nilai unik non-NULL ...
VARIANCE() Menghitung varians dari nilai ...
VAR_POP() Menghitung varians populasi dari nilai ...
VAR_SAMP() Menghitung varians sampel dari nilai ...
AVG(numeric_expr)
Menampilkan rata-rata nilai untuk sekelompok baris yang dihitung oleh numeric_expr. Baris dengan nilai NULL tidak disertakan dalam penghitungan.
BIT_AND(numeric_expr)
Menampilkan hasil operasi bitwise AND antara setiap instance numeric_expr di semua baris. Nilai NULL diabaikan. Fungsi ini menampilkan NULL jika semua instance numeric_expr bernilai NULL.
BIT_OR(numeric_expr)
Menampilkan hasil operasi bitwise OR antara setiap instance numeric_expr di semua baris. Nilai NULL diabaikan. Fungsi ini menampilkan NULL jika semua instance numeric_expr bernilai NULL.
BIT_XOR(numeric_expr)
Menampilkan hasil operasi bitwise XOR antara setiap instance numeric_expr di semua baris. Nilai NULL diabaikan. Fungsi ini menampilkan NULL jika semua instance numeric_expr bernilai NULL.
CORR(numeric_expr, numeric_expr)
Menampilkan koefisien korelasi Pearson dari sekumpulan pasangan angka.
COUNT(*)
Menampilkan jumlah total nilai (NULL dan non-NULL) dalam cakupan fungsi. Sebaiknya tentukan kolom yang akan dihitung secara eksplisit, kecuali jika Anda menggunakan COUNT(*) dengan fungsi TOP.
COUNT([DISTINCT] field [, n])
Menampilkan jumlah total nilai non-NULL dalam cakupan fungsi.

Jika Anda menggunakan kata kunci DISTINCT, fungsi tersebut akan menampilkan jumlah nilai yang berbeda untuk kolom yang ditentukan. Perhatikan bahwa nilai yang ditampilkan untuk DISTINCT adalah perkiraan statistik dan tidak dijamin tepat.

Gunakan EXACT_COUNT_DISTINCT() untuk mendapatkan jawaban yang tepat.

Jika memerlukan akurasi yang lebih tinggi dari COUNT(DISTINCT), Anda dapat menentukan parameter kedua, n, yang memberikan nilai minimum. Jika nilainya kurang dari nilai minimum tersebut, hasil yang tepat akan dijamin. Secara default, n adalah 1.000, tetapi jika Anda memberikan n yang lebih besar, Anda akan mendapatkan hasil yang tepat untuk COUNT(DISTINCT) hingga nilai n tersebut. Namun, memberikan nilai n yang lebih besar akan mengurangi skalabilitas operator ini dan dapat meningkatkan waktu eksekusi kueri secara signifikan atau menyebabkan kueri gagal.

Untuk menghitung jumlah nilai yang berbeda secara tepat, gunakan EXACT_COUNT_DISTINCT. Atau, untuk pendekatan yang lebih skalabel, pertimbangkan untuk menggunakan GROUP EACH BY di kolom yang relevan, lalu terapkan COUNT(*). Pendekatan GROUP EACH BY lebih skalabel, tetapi mungkin menimbulkan sedikit penalti performa di awal.

COVAR_POP(numeric_expr1, numeric_expr2)
Menghitung kovarians populasi dari nilai yang dihitung oleh numeric_expr1 dan numeric_expr2.
COVAR_SAMP(numeric_expr1, numeric_expr2)
Menghitung kovarians sampel dari nilai yang dihitung oleh numeric_expr1 dan numeric_expr2.
EXACT_COUNT_DISTINCT(field)
Menampilkan jumlah tepat nilai non-NULL yang berbeda untuk kolom yang ditentukan. Untuk skalabilitas dan performa yang lebih baik, gunakan COUNT(DISTINCT field).
FIRST(expr)
Menampilkan nilai berurutan pertama dalam cakupan fungsi.
GROUP_CONCAT('str' [, separator])

Menggabungkan beberapa string menjadi satu string, dengan setiap nilai dipisahkan oleh parameter separator opsional. Jika separator dihilangkan, BigQuery akan menampilkan string yang dipisahkan koma.

Jika string dalam data sumber berisi karakter tanda kutip ganda, GROUP_CONCAT akan menampilkan string dengan penambahan tanda kutip ganda. Misalnya, string a"b akan ditampilkan sebagai "a""b". Gunakan GROUP_CONCAT_UNQUOTED jika Anda lebih suka string ini tidak ditampilkan dengan penambahan tanda kutip ganda.

Contoh:

#legacySQL
SELECT
  GROUP_CONCAT(x)
FROM (
  SELECT
    'a"b' AS x),
  (
  SELECT
    'cd' AS x);
GROUP_CONCAT_UNQUOTED('str' [, separator])

Menggabungkan beberapa string menjadi satu string, dengan setiap nilai dipisahkan oleh parameter separator opsional. Jika separator dihilangkan, BigQuery akan menampilkan string yang dipisahkan koma.

Tidak seperti GROUP_CONCAT, fungsi ini tidak akan menambahkan tanda kutip ganda ke nilai yang ditampilkan yang menyertakan karakter tanda kutip ganda. Misalnya, string a"b akan ditampilkan sebagai a"b.

Contoh:

#legacySQL
SELECT
  GROUP_CONCAT_UNQUOTED(x)
FROM (
  SELECT
    'a"b' AS x),
  (
  SELECT
    'cd' AS x);
LAST(field)
Menampilkan nilai berurutan terakhir dalam cakupan fungsi.
MAX(field)
Menampilkan nilai maksimum dalam cakupan fungsi.
MIN(field)
Menampilkan nilai minimum dalam cakupan fungsi.
NEST(expr)

Menggabungkan semua nilai dalam cakupan agregasi saat ini ke dalam kolom berulang. Misalnya, kueri "SELECT x, NEST(y) FROM ... GROUP BY x" menampilkan satu kumpulan data output untuk setiap nilai x yang berbeda, dan berisi kolom berulang untuk semua nilai y yang dipasangkan dengan x di input kueri. Fungsi NEST memerlukan klausa GROUP BY.

BigQuery secara otomatis akan meratakan hasil kueri, sehingga jika Anda menggunakan fungsi NEST pada kueri tingkat teratas, hasilnya tidak akan berisi kolom berulang. Gunakan fungsi NEST saat menggunakan subpilihan yang memberikan hasil menengah agar dapat langsung digunakan oleh kueri yang sama.

NTH(n, field)
Menampilkan nilai berurutan ke-n dalam cakupan fungsi, dengan n sebagai konstanta. Fungsi NTH mulai menghitung dari 1, sehingga tidak ada suku nol. Jika cakupan fungsi memiliki nilai kurang dari n, fungsi ini akan menampilkan NULL.
QUANTILES(expr[, buckets])

Menghitung perkiraan minimum, maksimum, dan kuantil untuk ekspresi input. Nilai input NULL diabaikan. Input kosong atau NULL saja akan menghasilkan output NULL. Jumlah kuantil yang dihitung dikontrol dengan parameter buckets opsional, yang mencakup jumlah minimum dan maksimum. Untuk menghitung perkiraan N-tile, gunakan N+1 buckets. Nilai default buckets adalah 100. (Catatan: Nilai default 100 tidak memperkirakan persentil. Untuk memperkirakan persentil, gunakan minimum 101 buckets.) Jika ditentukan secara eksplisit, buckets minimal harus 2.

Error pecahan per kuantil adalah epsilon = 1/buckets, yang berarti error berkurang seiring dengan bertambahnya jumlah bucket. Contoh:

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.

Fungsi NTH dapat digunakan untuk memilih kuantil tertentu, tetapi perlu diingat bahwa NTH didasarkan pada angka 1, dan bahwa QUANTILES menampilkan nilai minimum (kuantil "ke-0") di posisi pertama, dan nilai maksimum (persentil "ke-100" atau N-tile "ke-N") di posisi terakhir. Misalnya, NTH(11, QUANTILES(expr, 21)) memperkirakan median expr, sedangkan NTH(20, QUANTILES(expr, 21)) memperkirakan vigintil ke-19 (persentil ke-95) dari expr. Kedua estimasi tersebut memiliki margin error 5%.

Untuk meningkatkan akurasi, gunakan lebih banyak bucket. Misalnya, untuk mengurangi margin error pada penghitungan sebelumnya dari 5% menjadi 0,1%, gunakan 1.001 bucket, bukan 21, dan sesuaikan argumen ke fungsi NTH. Untuk menghitung median dengan error 0,1%, gunakan NTH(501, QUANTILES(expr, 1001)); untuk persentil ke-95 dengan error 0,1%, gunakan NTH(951, QUANTILES(expr, 1001)).

STDDEV(numeric_expr)
Menampilkan simpangan baku dari nilai yang dihitung oleh numeric_expr. Baris dengan nilai NULL tidak disertakan dalam penghitungan. Fungsi STDDEV adalah alias untuk STDDEV_SAMP.
STDDEV_POP(numeric_expr)
Menghitung simpangan baku populasi dari nilai yang dihitung oleh numeric_expr. Gunakan STDDEV_POP() untuk menghitung simpangan baku set data yang mencakup seluruh populasi yang diinginkan. Jika set data Anda hanya terdiri dari sampel perwakilan populasi, gunakan STDDEV_SAMP() sebagai gantinya. Untuk mengetahui informasi selengkapnya tentang populasi versus simpangan baku, lihat Simpangan baku di Wikipedia.
STDDEV_SAMP(numeric_expr)
Menghitung simpangan baku sampel dari nilai yang dihitung oleh numeric_expr. Gunakan STDDEV_SAMP() untuk menghitung simpangan baku seluruh populasi berdasarkan sampel perwakilan populasi. Jika set data Anda terdiri dari seluruh populasi, gunakan STDDEV_POP(). Untuk mengetahui informasi selengkapnya tentang populasi versus simpangan baku, lihat Simpangan baku di Wikipedia.
SUM(field)
Menampilkan jumlah total nilai dalam cakupan fungsi. Hanya untuk digunakan dengan jenis data numerik.
TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)
Menampilkan kumpulan data max_records teratas menurut frekuensi. Lihat deskripsi fungsi TOP di bawah untuk mengetahui detailnya.
UNIQUE(expr)
Menampilkan kumpulan nilai unik non-NULL dalam cakupan fungsi dengan urutan yang tidak ditentukan. Serupa dengan klausa GROUP BY besar tanpa kata kunci EACH, kueri akan gagal dengan error "Resources Exceeded" jika ada terlalu banyak nilai yang berbeda. Namun, tidak seperti GROUP BY, fungsi UNIQUE dapat diterapkan dengan agregasi cakupan, sehingga memungkinkan operasi yang efisien pada kolom bertingkat dengan jumlah nilai yang terbatas.
VARIANCE(numeric_expr)
Menghitung varians dari nilai yang dihitung oleh numeric_expr. Baris dengan nilai NULL tidak disertakan dalam penghitungan. Fungsi VARIANCE adalah alias untuk VAR_SAMP.
VAR_POP(numeric_expr)
Menghitung varians populasi dari nilai yang dihitung oleh numeric_expr. Untuk mengetahui informasi selengkapnya tentang populasi versus simpangan baku, lihat Simpangan baku di Wikipedia.
VAR_SAMP(numeric_expr)
Menghitung varians sampel dari nilai yang dihitung oleh numeric_expr. Untuk mengetahui informasi selengkapnya tentang populasi versus simpangan baku, lihat Simpangan baku di Wikipedia.

Fungsi TOP()

TOP adalah fungsi yang merupakan alternatif dari klausa GROUP BY. Fungsi ini digunakan sebagai sintaksis yang disederhanakan untuk GROUP BY ... ORDER BY ... LIMIT .... Umumnya, fungsi TOP berperforma lebih cepat dibandingkan kueri ... GROUP BY ... ORDER BY ... LIMIT ... lengkap, tetapi hanya dapat menampilkan hasil perkiraan. Berikut adalah sintaksis untuk fungsi TOP:

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

Saat menggunakan fungsi TOP dalam klausa SELECT, Anda harus menyertakan COUNT(*) sebagai salah satu kolom.

Kueri yang menggunakan fungsi TOP() hanya dapat menampilkan dua kolom: kolom TOP dan nilai COUNT(*).

field|alias
Kolom atau alias yang akan ditampilkan.
max_values
[Opsional] Jumlah hasil maksimum yang akan ditampilkan. Defaultnya adalah 20.
multiplier
Bilangan bulat positif yang meningkatkan nilai yang ditampilkan oleh COUNT(*) berdasarkan kelipatan yang ditentukan.

Contoh fungsi TOP()

  • Contoh kueri dasar yang menggunakan TOP()

    Kueri berikut menggunakan TOP() untuk menampilkan 10 baris.

    Contoh 1:

    #legacySQL
SELECT
  TOP(word, 10) as word, COUNT(*) as cnt
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS "th";

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

  • Bandingkan TOP() dengan GROUP BY...ORDER BY...LIMIT

    Kueri akan menampilkan, secara berurutan, 10 kata teratas yang paling sering digunakan yang berisi kata "th", dan jumlah dokumen yang digunakan untuk menggunakan kata tersebut. Kueri TOP akan dijalankan jauh lebih cepat:

    Contoh tanpa TOP():

    #legacySQL
SELECT
  word, COUNT(*) AS cnt
FROM
  ds.Table
WHERE
  word CONTAINS 'th'
GROUP BY
  word
ORDER BY
  cnt DESC LIMIT 10;

    Contoh dengan TOP():

    #legacySQL
SELECT
  TOP(word, 10), COUNT(*)
FROM
  ds.Table
WHERE
  word contains 'th';

  • Menggunakan parameter multiplier.

    Kueri berikut menunjukkan bagaimana parameter multiplier memengaruhi hasil kueri. Kueri pertama menampilkan jumlah kelahiran per bulan di Wyoming. Kueri kedua menggunakan parameter multiplier untuk mengalikan nilai cnt dengan 100.

    Contoh tanpa parameter multiplier:

    #legacySQL
SELECT
  TOP(month,3) as month, COUNT(*) as cnt
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state = "WY";

    Hasil:

    +-------+-------+
| month |  cnt  |
+-------+-------+
|   7   | 19594 |
|   5   | 19038 |
|   8   | 19030 |
+-------+-------+

    Contoh dengan parameter multiplier:

    #legacySQL
SELECT
  TOP(month,3,100) as month, COUNT(*) as cnt
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state = "WY";

    Hasil:

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

Catatan: Anda harus menyertakan COUNT(*) dalam klausa SELECT untuk menggunakan TOP.

Contoh lanjutan

  • Simpangan rata-rata dan simpangan baku yang dikelompokkan menurut kondisi

    Kueri berikut menampilkan simpangan rata-rata dan simpangan baku dari berat bayi baru lahir di Ohio pada tahun 2003, yang dikelompokkan menurut ibu yang merokok dan tidak merokok.

    Contoh:

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

  • Memfilter hasil kueri menggunakan nilai gabungan

    Untuk memfilter hasil kueri menggunakan nilai gabungan (misalnya, memfilter menurut nilai SUM), gunakan fungsi HAVING. HAVING membandingkan nilai dengan hasil yang ditentukan oleh fungsi agregasi, bukan WHERE, yang beroperasi di setiap baris sebelum agregasi.

    Contoh:

    #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

    Hasil:

    +-------+--------+---------+
| state |  sex   |   cnt   |
+-------+--------+---------+
| CA    | Male   | 7060826 |
| CA    | Female | 6733288 |
| TX    | Male   | 5107542 |
| TX    | Female | 4879247 |
| NY    | Male   | 4442246 |
| NY    | Female | 4227891 |
| IL    | Male   | 3089555 |
+-------+--------+---------+

Operator aritmetika

Operator aritmetika mengambil argumen numerik dan menampilkan hasil numerik. Setiap argumen dapat berupa literal numerik atau nilai numerik yang ditampilkan oleh kueri. Jika operasi aritmetika bernilai hasil yang tidak ditentukan, operasi tersebut akan menampilkan NULL.

Sintaks

Operator Deskripsi Contoh
+ Penambahan

SELECT 6 + (5 - 1);

Hasil: 10
- Pengurangan

SELECT 6 - (4 + 1);

Hasil: 1
* Perkalian

SELECT 6 * (5 - 1);

Hasil: 24
/ Pembagian

SELECT 6 / (2 + 2);

Hasil: 1,5
% Modulus

SELECT 6 % (2 + 2);

Hasil: 2

Fungsi bitwise

Fungsi bitwise beroperasi pada tingkat bit individual dan memerlukan argumen numerik. Untuk mengetahui informasi selengkapnya tentang fungsi bitwise, lihat Operasi bitwise.

Tiga fungsi bitwise tambahan, BIT_AND, BIT_OR, dan BIT_XOR, didokumentasikan dalam fungsi agregat.

Sintaks

Operator Deskripsi Contoh
& Bitwise AND

SELECT (1 + 3) & 1

Hasil: 0
| Bitwise OR

SELECT 24 | 12

Hasil: 28
^ Bitwise XOR

SELECT 1 ^ 0

Hasil: 1
<< Bitwise Shift Left

SELECT 1 << (2 + 2)

Hasil: 16
>> Bitwise Shift Right

SELECT (6 + 2) >> 2

Hasil: 2
~ Bitwise NOT

SELECT ~2

Hasil: -3
BIT_COUNT(<numeric_expr>)

Menampilkan jumlah bit yang ditetapkan di <numeric_expr>.

SELECT BIT_COUNT(29);

Hasil: 4

Fungsi transmisi

Fungsi transmisi mengubah jenis data ekspresi numerik. Fungsi transmisi sangat berguna untuk memastikan bahwa argumen dalam fungsi perbandingan memiliki jenis data yang sama.

Sintaks

Fungsi transmisi
BOOLEAN() Melakukan transmisi ke boolean.
BYTES() Melakukan transmisi ke byte.
CAST(expr AS type) Mengonversi expr menjadi variabel jenis type.
FLOAT() Melakukan transmisi ke nilai ganda.
HEX_STRING() Melakukan transmisi ke string heksadesimal.
INTEGER() Melakukan transmisi ke bilangan bulat.
STRING() Melakukan transmisi ke string.
BOOLEAN(<numeric_expr>)
  • Menampilkan true jika <numeric_expr> bukan 0 dan bukan NULL.
  • Menampilkan false jika <numeric_expr> adalah 0.
  • Menampilkan NULL jika <numeric_expr> adalah NULL.
BYTES(string_expr)
Menampilkan string_expr sebagai nilai jenis bytes.
CAST(expr AS type)
Mengonversi expr menjadi variabel jenis type.
FLOAT(expr)
Menampilkan expr sebagai nilai ganda. expr dapat berupa string seperti '45.78', tetapi fungsi ini akan menampilkan NULL untuk nilai non-numerik.
HEX_STRING(numeric_expr)
Menampilkan numeric_expr sebagai string heksadesimal.
INTEGER(expr)
Mentransmisikan expr ke bilangan bulat 64-bit.
  • Menampilkan NULL jika expr adalah string yang tidak sesuai dengan nilai bilangan bulat.
  • Menampilkan jumlah mikrodetik sejak epoch Unix jika expr adalah stempel waktu.
STRING(numeric_expr)
Menampilkan numeric_expr sebagai string.

Fungsi perbandingan

Fungsi perbandingan menampilkan true atau false, berdasarkan jenis perbandingan berikut:

  • Perbandingan dua ekspresi.
  • Perbandingan ekspresi atau kumpulan ekspresi dengan kriteria tertentu, seperti berada di daftar yang ditentukan, menjadi NULL, atau menjadi nilai opsional non-default.

Beberapa fungsi yang tercantum di bawah menampilkan nilai selain true atau false, tetapi nilai yang ditampilkan didasarkan pada operasi perbandingan.

Anda dapat menggunakan ekspresi numerik atau string sebagai argumen untuk fungsi perbandingan. (Konstanta string harus diapit oleh tanda kutip tunggal atau ganda.) Ekspresi dapat berupa literal atau nilai yang diambil oleh kueri. Fungsi perbandingan paling sering digunakan sebagai kondisi pemfilteran di klausa WHERE, tetapi dapat digunakan dalam klausa lain.

Sintaks

Fungsi perbandingan
expr1 = expr2 Menampilkan true jika ekspresi sama.
expr1 != expr2
expr1 <> expr2 		Menampilkan true jika ekspresi tidak sama.
expr1 > expr2 Menampilkan true jika expr1 lebih besar dari expr2.
expr1 < expr2 Menampilkan true jika expr1 kurang dari expr2.
expr1 >= expr2 Menampilkan true jika expr1 lebih besar dari atau sama dengan expr2.
expr1 <= expr2 Menampilkan true jika expr1 kurang dari atau sama dengan expr2.
expr1 BETWEEN expr2 AND expr3 Menampilkan true jika nilai expr1 antara expr2 dan expr3, inklusif.
expr IS NULL Menampilkan true jika expr adalah NULL.
expr IN() Menampilkan true jika expr cocok dengan expr1, expr2, atau nilai apa pun dalam tanda kurung.
COALESCE() Menampilkan argumen pertama yang bukan NULL.
GREATEST() Menampilkan parameter numeric_expr terbesar.
IFNULL() Jika argumen tidak bernilai null, argumen akan ditampilkan.
IS_INF() Menampilkan true jika tak terhingga positif atau negatif.
IS_NAN() Menampilkan true jika argumen adalah NaN.
IS_EXPLICITLY_DEFINED() tidak digunakan lagi: Gunakan expr IS NOT NULL sebagai gantinya.
LEAST() Menampilkan parameter numeric_expr argumen terkecil.
NVL() Jika expr bukan null, expr akan ditampilkan. Jika tidak, null_default akan ditampilkan.
expr1 = expr2
Menampilkan true jika ekspresi sama.
expr1 != expr2
expr1 <> expr2
Menampilkan true jika ekspresi tidak sama.
expr1 > expr2
Menampilkan true jika expr1 lebih besar dari expr2.
expr1 < expr2
Menampilkan true jika expr1 kurang dari expr2.
expr1 >= expr2
Menampilkan true jika expr1 lebih besar dari atau sama dengan expr2.
expr1 <= expr2
Menampilkan true jika expr1 kurang dari atau sama dengan expr2.
expr1 BETWEEN expr2 AND expr3

Menampilkan true jika nilai expr1 lebih besar dari atau sama dengan expr2, dan kurang dari atau sama dengan expr3.

expr IS NULL
Menampilkan true jika expr adalah NULL.
expr IN(expr1, expr2, ...)
Menampilkan true jika expr cocok dengan expr1, expr2, atau nilai apa pun dalam tanda kurung. Kata kunci IN adalah penyederhanaan yang efisien untuk (expr = expr1 || expr = expr2 || ...). Ekspresi yang digunakan dengan kata kunci IN harus berupa konstanta dan harus cocok dengan jenis data expr. Klausa IN juga dapat digunakan untuk membuat semi-join dan anti-join. Untuk mengetahui informasi selengkapnya, lihat Semi-join dan Anti-join.
COALESCE(<expr1>, <expr2>, ...)
Menampilkan argumen pertama yang bukan NULL.
GREATEST(numeric_expr1, numeric_expr2, ...)

Menampilkan parameter numeric_expr terbesar. Semua parameter harus berupa angka, dan semua parameter harus berjenis sama. Jika parameter apa pun bernilai NULL, fungsi ini akan menampilkan NULL.

Untuk mengabaikan nilai NULL, gunakan fungsi IFNULL untuk mengubah nilai NULL ke nilai yang tidak memengaruhi perbandingan. Pada contoh kode berikut, fungsi IFNULL digunakan untuk mengubah nilai NULL menjadi -1, yang tidak memengaruhi perbandingan antara angka positif.

SELECT GREATEST(IFNULL(a,-1), IFNULL(b,-1)) FROM (SELECT 1 as a, NULL as b);
IFNULL(expr, null_default)
Jika expr bukan null, expr akan ditampilkan. Jika tidak, null_default akan ditampilkan.
IS_INF(numeric_expr)
Menampilkan true jika numeric_expr adalah tak terhingga positif atau negatif.
IS_NAN(numeric_expr)
Menampilkan true jika numeric_expr adalah nilai numerik NaN khusus.
IS_EXPLICITLY_DEFINED(expr)

Fungsi ini tidak digunakan lagi. Sebagai gantinya, gunakan expr IS NOT NULL.

LEAST(numeric_expr1, numeric_expr2, ...)

Menampilkan parameter numeric_expr terkecil. Semua parameter harus berupa angka, dan semua parameter harus berjenis sama. Jika parameter apa pun bernilai NULL, fungsi ini akan menampilkan NULL

NVL(expr, null_default)
Jika expr bukan null, expr akan ditampilkan. Jika tidak, null_default akan ditampilkan. Fungsi NVL adalah alias untuk IFNULL.

Fungsi tanggal dan waktu

Fungsi berikut memungkinkan manipulasi tanggal dan waktu untuk stempel waktu UNIX, string tanggal, dan jenis data TIMESTAMP. Untuk mengetahui informasi selengkapnya tentang cara menangani jenis data TIMESTAMP, lihat Menggunakan TIMESTAMP.

Fungsi tanggal dan waktu yang berfungsi dengan stempel waktu UNIX beroperasi pada waktu UNIX. Fungsi tanggal dan waktu menampilkan nilai berdasarkan zona waktu UTC.

Sintaks

Fungsi tanggal dan waktu
CURRENT_DATE() Menampilkan tanggal saat ini dalam format %Y-%m-%d.
CURRENT_TIME() Menampilkan waktu server saat ini dalam format %H:%M:%S.
CURRENT_TIMESTAMP() Menampilkan waktu server saat ini dalam format %Y-%m-%d %H:%M:%S.
DATE() Menampilkan tanggal dalam format %Y-%m-%d.
DATE_ADD() Menambahkan interval yang ditentukan ke jenis data TIMESTAMP.
DATEDIFF() Menampilkan jumlah hari antara dua jenis data TIMESTAMP.
DAY() Menampilkan hari dalam sebulan sebagai bilangan bulat antara 1 dan 31.
DAYOFWEEK() Menampilkan hari dalam seminggu sebagai bilangan bulat antara 1 (Minggu) dan 7 (Sabtu).
DAYOFYEAR() Menampilkan hari dalam setahun sebagai bilangan bulat antara 1 dan 366.
FORMAT_UTC_USEC() Menampilkan stempel waktu UNIX dalam format YYYY-MM-DD HH:MM:SS.uuuuuu.
HOUR() Menampilkan jam pada TIMESTAMP sebagai bilangan bulat antara 0 dan 23.
MINUTE() Menampilkan menit pada TIMESTAMP sebagai bilangan bulat antara 0 dan 59.
MONTH() Menampilkan bulan pada TIMESTAMP sebagai bilangan bulat antara 1 dan 12.
MSEC_TO_TIMESTAMP() Mengonversi stempel waktu UNIX dalam milidetik menjadi TIMESTAMP.
NOW() Menampilkan stempel waktu UNIX saat ini dalam mikrodetik.
PARSE_UTC_USEC() Mengonversi string tanggal menjadi stempel waktu UNIX dalam mikrodetik.
QUARTER() Menampilkan kuartal dalam setahun pada TIMESTAMP sebagai bilangan bulat antara 1 dan 4.
SEC_TO_TIMESTAMP() Mengonversi stempel waktu UNIX dalam detik menjadi TIMESTAMP.
SECOND() Menampilkan detik pada TIMESTAMP sebagai bilangan bulat antara 0 dan 59.
STRFTIME_UTC_USEC() Menampilkan string tanggal dalam format date_format_str.
TIME() Menampilkan TIMESTAMP dalam format %H:%M:%S.
TIMESTAMP() Mengonversi string tanggal menjadi TIMESTAMP.
TIMESTAMP_TO_MSEC() Mengonversi TIMESTAMP menjadi stempel waktu UNIX dalam milidetik.
TIMESTAMP_TO_SEC() Mengonversi TIMESTAMP menjadi stempel waktu UNIX dalam detik.
TIMESTAMP_TO_USEC() Mengonversi TIMESTAMP menjadi stempel waktu UNIX dalam mikrodetik.
USEC_TO_TIMESTAMP() Mengonversi stempel waktu UNIX dalam mikrodetik menjadi TIMESTAMP.
UTC_USEC_TO_DAY() Menggeser stempel waktu UNIX dalam mikrodetik ke awal hari saat peristiwa terjadi.
UTC_USEC_TO_HOUR() Menggeser stempel waktu UNIX dalam mikrodetik ke awal jam saat peristiwa terjadi.
UTC_USEC_TO_MONTH() Menggeser stempel waktu UNIX dalam mikrodetik ke awal bulan saat peristiwa terjadi.
UTC_USEC_TO_WEEK() Menampilkan stempel waktu UNIX dalam mikrodetik yang mewakili hari dalam seminggu.
UTC_USEC_TO_YEAR() Menampilkan stempel waktu UNIX dalam mikrodetik yang mewakili tahun.
WEEK() Menampilkan minggu pada TIMESTAMP sebagai bilangan bulat antara 1 dan 53.
YEAR() Menampilkan tahun pada TIMESTAMP.

CURRENT_DATE()

Menampilkan string tanggal saat ini yang dapat dibaca manusia dalam format %Y-%m-%d.

Contoh:

SELECT CURRENT_DATE();

Hasil: 2013-02-01

CURRENT_TIME()

Menampilkan string waktu server saat ini yang dapat dibaca manusia dalam format %H:%M:%S.

Contoh:

SELECT CURRENT_TIME();

Hasil: 01:32:56

CURRENT_TIMESTAMP()

Menampilkan jenis data TIMESTAMP dari waktu server saat ini dalam format %Y-%m-%d %H:%M:%S.

Contoh:

SELECT CURRENT_TIMESTAMP();

Hasil: 2013-02-01 01:33:35 UTC

DATE(<timestamp>)

Menampilkan string jenis data TIMESTAMP yang dapat dibaca manusia dalam format %Y-%m-%d.

Contoh:

SELECT DATE(TIMESTAMP('2012-10-01 02:03:04'));

Hasil: 2012-10-01

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

Menambahkan interval yang ditentukan ke jenis data TIMESTAMP. Nilai interval_units yang mungkin mencakup YEAR, MONTH, DAY, HOUR, MINUTE, dan SECOND. Jika interval adalah angka negatif, interval akan dikurangi dari jenis data TIMESTAMP.

Contoh:

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

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

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

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

DATEDIFF(<timestamp1>,<timestamp2>)

Menampilkan jumlah hari antara dua jenis data TIMESTAMP. Hasilnya positif jika jenis data TIMESTAMP pertama muncul setelah jenis data TIMESTAMP kedua. Jika kebalikannya, hasilnya akan negatif.

Contoh:

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

Hasil: 466

Contoh:

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

Hasil: -466

DAY(<timestamp>)

Menampilkan hari dalam sebulan dari jenis data TIMESTAMP sebagai bilangan bulat antara 1 dan 31, secara inklusif.

Contoh:

SELECT DAY(TIMESTAMP('2012-10-02 05:23:48'));

Hasil: 2

DAYOFWEEK(<timestamp>)

Menampilkan hari dalam seminggu dari jenis data TIMESTAMP sebagai bilangan bulat antara 1 (Minggu) dan 7 (Sabtu), secara inklusif.

Contoh:

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

Hasil: 2

DAYOFYEAR(<timestamp>)

Menampilkan hari dalam setahun dari jenis data TIMESTAMP sebagai bilangan bulat antara 1 dan 366, secara inklusif. Bilangan bulat 1 merujuk pada 1 Januari.

Contoh:

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

Hasil: 275

FORMAT_UTC_USEC(<unix_timestamp>)

Menampilkan representasi string stempel waktu UNIX yang dapat dibaca manusia dalam format YYYY-MM-DD HH:MM:SS.uuuuuu.

Contoh:

SELECT FORMAT_UTC_USEC(1274259481071200);

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

HOUR(<timestamp>)

Menampilkan jam dari jenis data TIMESTAMP sebagai bilangan bulat antara 0 dan 23, secara inklusif.

Contoh:

SELECT HOUR(TIMESTAMP('2012-10-02 05:23:48'));

Hasil: 5

MINUTE(<timestamp>)

Menampilkan menit dari jenis data TIMESTAMP sebagai bilangan bulat antara 0 dan 59, secara inklusif.

Contoh:

SELECT MINUTE(TIMESTAMP('2012-10-02 05:23:48'));

Hasil: 23

MONTH(<timestamp>)

Menampilkan bulan dari jenis data TIMESTAMP sebagai bilangan bulat antara 1 dan 12, secara inklusif.

Contoh:

SELECT MONTH(TIMESTAMP('2012-10-02 05:23:48'));

Hasil: 10

MSEC_TO_TIMESTAMP(<expr>)
Mengonversi stempel waktu UNIX dalam milidetik menjadi jenis data TIMESTAMP.

Contoh:

SELECT MSEC_TO_TIMESTAMP(1349053323000);

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

SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)

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

NOW()

Menampilkan stempel waktu UNIX saat ini dalam mikrodetik.

Contoh:

SELECT NOW();

Hasil: 1359685811687920

PARSE_UTC_USEC(<date_string>)

Mengonversi string tanggal menjadi stempel waktu UNIX dalam mikrodetik. date_string harus memiliki format YYYY-MM-DD HH:MM:SS[.uuuuuu]. Bagian pecahan kedua dapat memiliki panjang hingga 6 digit atau dapat dihilangkan.

TIMESTAMP_TO_USEC adalah fungsi setara yang mengonversi argumen jenis data TIMESTAMP, bukan string tanggal.

Contoh:

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

Hasil: 1349056984000000

QUARTER(<timestamp>)

Menampilkan kuartal dalam setahun dari jenis data TIMESTAMP sebagai bilangan bulat antara 1 dan 4, secara inklusif.

Contoh:

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

Hasil: 4

SEC_TO_TIMESTAMP(<expr>)

Mengonversi stempel waktu UNIX dalam detik menjadi jenis data TIMESTAMP.

Contoh:

SELECT SEC_TO_TIMESTAMP(1355968987);

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

SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));

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

SECOND(<timestamp>)

Menampilkan detik dari jenis data TIMESTAMP sebagai bilangan bulat antara 0 dan 59, secara inklusif.

Selama detik kabisat, rentang bilangan bulat adalah antara 0 dan 60, secara inklusif.

Contoh:

SELECT SECOND(TIMESTAMP('2012-10-02 05:23:48'));

Hasil: 48

STRFTIME_UTC_USEC(<unix_timestamp>,
                  <date_format_str>)

Menampilkan string tanggal yang dapat dibaca manusia dalam format date_format_str. date_format_str dapat menyertakan karakter tanda baca yang terkait dengan tanggal (seperti / dan -) serta karakter khusus yang diterima oleh fungsi strftime dalam bahasa C++ (seperti %d untuk hari dalam sebulan).

Gunakan fungsi UTC_USEC_TO_<function_name> jika Anda berencana untuk mengelompokkan data kueri berdasarkan interval waktu, seperti mendapatkan semua data untuk bulan tertentu, karena fungsinya lebih efisien.

Contoh:

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

Hasil: 2010-05-19

TIME(<timestamp>)

Menampilkan string jenis data TIMESTAMP yang dapat dibaca manusia dalam format %H:%M:%S.

Contoh:

SELECT TIME(TIMESTAMP('2012-10-01 02:03:04'));

Hasil: 02:03:04

TIMESTAMP(<date_string>)

Mengonversi string tanggal menjadi jenis data TIMESTAMP.

Contoh:

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

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

TIMESTAMP_TO_MSEC(<timestamp>)

Mengonversi jenis data TIMESTAMP ke stempel waktu UNIX dalam milidetik.

Contoh:

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

Hasil: 1349053323000

TIMESTAMP_TO_SEC(<timestamp>)
Mengonversi jenis data TIMESTAMP menjadi stempel waktu UNIX dalam detik.

Contoh:

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

Hasil: 1349053323

TIMESTAMP_TO_USEC(<timestamp>)

Mengonversi jenis data TIMESTAMP menjadi stempel waktu UNIX dalam mikrodetik.

PARSE_UTC_USEC adalah fungsi padanan yang mengonversi argumen string data, bukan jenis data TIMESTAMP.

Contoh:

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

Hasil: 1349053323000000

USEC_TO_TIMESTAMP(<expr>)

Mengonversi stempel waktu UNIX dalam mikrodetik menjadi jenis data TIMESTAMP.

Contoh:

SELECT USEC_TO_TIMESTAMP(1349053323000000);

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

SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)

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

UTC_USEC_TO_DAY(<unix_timestamp>)

Menggeser stempel waktu UNIX dalam mikrodetik ke awal hari saat peristiwa terjadi.

Misalnya, jika unix_timestamp terjadi pada 19 Mei pukul 08.58, fungsi ini akan menampilkan stempel waktu UNIX untuk 19 Mei pukul 00:00 (tengah malam).

Contoh:

SELECT UTC_USEC_TO_DAY(1274259481071200);

Hasil: 1274227200000000

UTC_USEC_TO_HOUR(<unix_timestamp>)

Menggeser stempel waktu UNIX dalam mikrodetik ke awal jam saat peristiwa terjadi.

Misalnya, jika unix_timestamp terjadi pada pukul 08.58, fungsi ini akan menampilkan stempel waktu UNIX untuk pukul 08:00 pada hari yang sama.

Contoh:

SELECT UTC_USEC_TO_HOUR(1274259481071200);

Hasil: 1274256000000000

UTC_USEC_TO_MONTH(<unix_timestamp>)

Menggeser stempel waktu UNIX dalam mikrodetik ke awal bulan saat peristiwa terjadi.

Misalnya, jika unix_timestamp terjadi pada 19 Maret, fungsi ini akan menampilkan stempel waktu UNIX untuk 1 Maret pada tahun yang sama.

Contoh:

SELECT UTC_USEC_TO_MONTH(1274259481071200);

Hasil: 1272672000000000

UTC_USEC_TO_WEEK(<unix_timestamp>,
                 <day_of_week>)

Menampilkan stempel waktu UNIX dalam mikrodetik yang mewakili hari dalam seminggu dari argumen unix_timestamp. Fungsi ini menggunakan dua argumen: stempel waktu UNIX dalam mikrodetik, dan hari dalam seminggu dari 0 (Minggu) hingga 6 (Sabtu).

Misalnya, jika unix_timestamp terjadi pada hari Jumat, 11-04-2008, dan Anda menetapkan day_of_week ke 2 (Selasa), fungsi ini akan menampilkan stempel waktu UNIX untuk hari Selasa, 08-04-2008.

Contoh:

SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;

Hasil: 1207612800000000

UTC_USEC_TO_YEAR(<unix_timestamp>)

Menampilkan stempel waktu UNIX dalam mikrodetik yang mewakili tahun dari argumen unix_timestamp.

Misalnya, jika unix_timestamp terjadi pada tahun 2010, fungsi akan menampilkan 1274259481071200, yang merupakan representasi mikrodetik dari 2010-01-01 00:00.

Contoh:

SELECT UTC_USEC_TO_YEAR(1274259481071200);

Hasil: 1262304000000000

WEEK(<timestamp>)

Menampilkan minggu dari jenis data TIMESTAMP sebagai bilangan bulat antara 1 dan 53, secara inklusif.

Minggu dimulai pada hari Minggu, sehingga jika 1 Januari adalah hari selain Minggu, minggu ke-1 memiliki kurang dari 7 hari, dan Minggu pertama dalam setiap tahun adalah hari pertama minggu ke-2.

Contoh:

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

Hasil: 53

YEAR(<timestamp>)
Menampilkan tahun dari jenis data TIMESTAMP.

Contoh:

SELECT YEAR(TIMESTAMP('2012-10-02 05:23:48'));

Hasil: 2012

Contoh lanjutan

  • Mengonversi hasil stempel waktu bilangan bulat menjadi format yang dapat dibaca manusia

    Kueri berikut menemukan 5 momen teratas saat revisi Wikipedia paling banyak terjadi. Untuk menampilkan hasil dalam format yang dapat dibaca manusia, gunakan fungsi FORMAT_UTC_USEC() BigQuery, yang mengambil stempel waktu, dalam mikrodetik, sebagai input. Kueri ini mengalikan stempel waktu format POSIX Wikipedia (dalam detik) dengan 1.000.000 untuk mengonversi nilainya menjadi mikrodetik.

    Contoh:

    #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];

    Hasil:

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

  • Mengelompokkan Hasil berdasarkan Stempel Waktu

    Sebaiknya gunakan fungsi tanggal dan waktu untuk mengelompokkan hasil kueri ke dalam bucket yang sesuai dengan tahun, bulan, atau hari tertentu. Contoh berikut menggunakan fungsi UTC_USEC_TO_MONTH() untuk menampilkan jumlah karakter yang digunakan setiap kontributor Wikipedia dalam komentar revisi mereka per bulan.

    Contoh:

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

    Hasil (terpotong):

    +--------------------------------+---------+-----------------------+
|      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 ...

Fungsi IP

Fungsi IP mengonversi alamat IP ke dan dari bentuk yang dapat dibaca manusia.

Sintaks

Fungsi IP
FORMAT_IP() Mengonversi 32 bit integer_value yang paling tidak signifikan menjadi string alamat IPv4 yang dapat dibaca manusia.
PARSE_IP() Mengonversi string yang mewakili alamat IPv4 menjadi nilai bilangan bulat yang tidak bertanda tangan.
FORMAT_PACKED_IP() Menampilkan alamat IP yang dapat dibaca manusia dalam bentuk 10.1.5.23 atau 2620:0:1009:1:216:36ff:feef:3f.
PARSE_PACKED_IP() Menampilkan alamat IP dalam BYTES.
FORMAT_IP(integer_value)
Mengonversi 32 bit integer_value paling tidak signifikan menjadi string alamat IPv4 yang dapat dibaca manusia. Misalnya, FORMAT_IP(1) akan menampilkan string '0.0.0.1'.
PARSE_IP(readable_ip)
Mengonversi string yang mewakili alamat IPv4 menjadi nilai bilangan bulat yang tidak bertanda tangan. Misalnya, PARSE_IP('0.0.0.1') akan menampilkan 1. Jika string bukan alamat IPv4 yang valid, PARSE_IP akan menampilkan NULL.

BigQuery mendukung penulisan alamat IPv4 dan IPv6 dalam string yang dikemas, sebagai data biner 4 atau 16 byte dalam urutan byte jaringan. Fungsi yang dijelaskan di bawah mendukung penguraian alamat ke dan dari bentuk yang dapat dibaca manusia. Fungsi ini hanya berfungsi pada kolom string dengan IP.

Sintaks

FORMAT_PACKED_IP(packed_ip)

Menampilkan alamat IP yang dapat dibaca manusia, dalam bentuk 10.1.5.23 atau 2620:0:1009:1:216:36ff:feef:3f. Contoh:

  • FORMAT_PACKED_IP('0123456789@ABCDE') akan menampilkan '3031:3233:3435:3637:3839:4041:4243:4445'
  • FORMAT_PACKED_IP('0123') akan menampilkan '48.49.50.51'
PARSE_PACKED_IP(readable_ip)

Menampilkan alamat IP dalam BYTES. Jika string input bukan alamat IPv4 atau IPv6 yang valid, PARSE_PACKED_IP akan menampilkan NULL. Contoh:

  • PARSE_PACKED_IP('48.49.50.51') akan menampilkan 'MDEyMw=='
  • PARSE_PACKED_IP('3031:3233:3435:3637:3839:4041:4243:4445') akan menampilkan 'MDEyMzQ1Njc4OUBBQkNERQ=='

Fungsi JSON

Fungsi JSON BigQuery memberi Anda kemampuan untuk menemukan nilai dalam data JSON yang tersimpan, menggunakan ekspresi seperti JSONPath.

Menyimpan data JSON dapat lebih fleksibel daripada mendeklarasikan semua kolom individual dalam skema tabel, tetapi dapat menyebabkan biaya yang lebih tinggi. Saat memilih data dari string JSON, Anda akan dikenai biaya untuk pemindaian seluruh string, yang lebih mahal dibandingkan jika setiap kolom berada dalam kolom terpisah. Kueri juga lebih lambat karena seluruh string perlu diurai pada waktu kueri. Namun, untuk skema ad-hoc atau yang cepat berubah, fleksibilitas JSON sepadan dengan biaya tambahannya.

Jika bekerja dengan data terstruktur, gunakan fungsi JSON, bukan fungsi ekspresi reguler BigQuery, karena fungsi JSON lebih mudah digunakan.

Sintaks

Fungsi JSON
JSON_EXTRACT() Memilih nilai berdasarkan ekspresi JSONPath dan menampilkan string JSON.
JSON_EXTRACT_SCALAR() Memilih nilai berdasarkan ekspresi JSONPath dan menampilkan skalar JSON.
JSON_EXTRACT(json, json_path)

Memilih nilai di json sesuai dengan ekspresi JSONPath json_path. json_path harus berupa konstanta string. Menampilkan nilai dalam format string JSON.

JSON_EXTRACT_SCALAR(json, json_path)

Memilih nilai di json sesuai dengan ekspresi JSONPath json_path. json_path harus berupa konstanta string. Menampilkan nilai JSON skalar.

Operator logika

Operator logika menjalankan logika biner atau ternary pada ekspresi. Logika biner menampilkan true atau false. Logika ternary mengakomodasi nilai NULL dan menampilkan true, false, atau NULL.

Sintaks

Operator logika
expr AND expr Menampilkan true jika kedua ekspresi benar.
expr OR expr Menampilkan true jika salah satu atau kedua ekspresi benar.
NOT expr Menampilkan true jika ekspresi salah.
expr AND expr
  • Menampilkan true jika kedua ekspresi benar.
  • Menampilkan false jika salah satu atau kedua ekspresi salah.
  • Menampilkan NULL jika kedua ekspresi adalah NULL atau satu ekspresi benar dan ekspresi lainnya adalah NULL.
expr OR expr
  • Menampilkan true jika salah satu atau kedua ekspresi benar.
  • Menampilkan false jika kedua ekspresi salah.
  • Menampilkan NULL jika kedua ekspresi adalah NULL, atau satu ekspresi salah dan ekspresi lainnya adalah NULL.
NOT expr
  • Menampilkan true jika ekspresi salah.
  • Menampilkan false jika ekspresi jika benar.
  • Menampilkan NULL jika ekspresi adalah NULL.

Anda dapat menggunakan NOT dengan fungsi lain sebagai operator negasi. Misalnya, NOT IN(expr1, expr2) atau IS NOT NULL.

Fungsi matematika

Fungsi matematika mengambil argumen numerik dan menampilkan hasil numerik. Setiap argumen dapat berupa literal numerik atau nilai numerik yang ditampilkan oleh kueri. Jika fungsi matematika bernilai hasil yang tidak ditentukan, operasi akan menampilkan NULL.

Sintaks

Fungsi matematika
ABS() Menampilkan nilai absolut dari argumen.
ACOS() Menampilkan kosinus terbalik dari argumen.
ACOSH() Menampilkan kosinus hiperbolik terbalik dari argumen.
ASIN() Menampilkan sinus terbalik dari argumen.
ASINH() Menampilkan sinus hiperbolik terbalik dari argumen.
ATAN() Menampilkan tangen terbalik dari argumen.
ATANH() Menampilkan tangen hiperbolik terbalik dari argumen.
ATAN2() Menampilkan tangen terbalik dari kedua argumen.
CEIL() Membulatkan argumen ke atas ke bilangan bulat terdekat dan menampilkan nilai yang dibulatkan.
COS() Menampilkan kosinus dari argumen.
COSH() Menampilkan kosinus hiperbolik dari argumen.
DEGREES() Mengonversi dari radian ke derajat.
EXP() Menampilkan e pangkat dari argumen.
FLOOR() Membulatkan argumen ke bawah ke bilangan bulat terdekat.
LN()
LOG() 		Menampilkan logaritma natural dari argumen.
LOG2() Menampilkan logaritma basis 2 dari argumen.
LOG10() Menampilkan logaritma basis 10 dari argumen.
PI() Menampilkan konstanta π.
POW() Menampilkan argumen pertama pangkat argumen kedua.
RADIANS() Mengonversi dari derajat ke radian.
RAND() Menampilkan nilai float acak dalam rentang 0,0 <= nilai < 1,0.
ROUND() Membulatkan argumen ke atas atau ke bawah ke bilangan bulat terdekat.
SIN() Menampilkan sinus dari argumen.
SINH() Menampilkan sinus hiperbolik dari argumen.
SQRT() Menampilkan akar kuadrat dari ekspresi.
TAN() Menampilkan tangen dari argumen.
TANH() Menampilkan tangen hiperbolik dari argumen.
ABS(numeric_expr)
Menampilkan nilai absolut argumen.
ACOS(numeric_expr)
Menampilkan kosinus terbalik dari argumen.
ACOSH(numeric_expr)
Menampilkan kosinus hiperbolik terbalik dari argumen.
ASIN(numeric_expr)
Menampilkan sinus terbalik dari argumen.
ASINH(numeric_expr)
Menampilkan sinus hiperbolik terbalik dari argumen.
ATAN(numeric_expr)
Menampilkan tangen terbalik dari argumen.
ATANH(numeric_expr)
Menampilkan tangen hiperbolik terbalik dari argumen.
ATAN2(numeric_expr1, numeric_expr2)
Menampilkan tangen terbalik dari kedua argumen.
CEIL(numeric_expr)
Membulatkan argumen ke atas ke bilangan bulat terdekat dan menampilkan nilai yang dibulatkan.
COS(numeric_expr)
Menampilkan kosinus dari argumen.
COSH(numeric_expr)
Menampilkan kosinus hiperbolik dari argumen.
DEGREES(numeric_expr)
Menampilkan numeric_expr, yang dikonversi dari radian ke derajat.
EXP(numeric_expr)
Menampilkan hasil pemangkatan konstanta "e" - basis logaritma natural - ke pangkat numeric_expr.
FLOOR(numeric_expr)
Membulatkan argumen ke bawah ke bilangan bulat terdekat dan menampilkan nilai yang dibulatkan.
LN(numeric_expr)
LOG(numeric_expr)
Menampilkan logaritma natural dari argumen.
LOG2(numeric_expr)
Menampilkan logaritma basis 2 dari argumen.
LOG10(numeric_expr)
Menampilkan logaritma basis 10 dari argumen.
PI()
Menampilkan konstanta π. Fungsi PI() memerlukan tanda kurung untuk menunjukkan bahwa itu adalah sebuah fungsi, tetapi tidak membutuhkan argumen dalam tanda kurung tersebut. Anda dapat menggunakan PI() seperti konstanta dengan fungsi matematika dan aritmetika.
POW(numeric_expr1, numeric_expr2)
Menampilkan hasil pemangkatan numeric_expr1 pangkat numeric_expr2.
RADIANS(numeric_expr)
Menampilkan numeric_expr, yang dikonversi dari derajat ke radian. (Perhatikan bahwa π radian sama dengan 180 derajat.)
RAND([int32_seed])
Menampilkan nilai float acak dalam rentang 0,0 <= nilai < 1,0. Setiap nilai int32_seed selalu menghasilkan urutan angka acak yang sama dalam kueri tertentu, selama Anda tidak menggunakan klausa LIMIT. Jika int32_seed tidak ditentukan, BigQuery akan menggunakan stempel waktu saat ini sebagai nilai seed.
ROUND(numeric_expr [, digits])
Membulatkan argumen ke atas atau ke bawah ke bilangan bulat terdekat (atau jika ditentukan, ke jumlah digit tertentu) dan menampilkan nilai yang dibulatkan.
SIN(numeric_expr)
Menampilkan sinus dari argumen.
SINH(numeric_expr)
Menampilkan sinus hiperbolik dari argumen.
SQRT(numeric_expr)
Menampilkan akar kuadrat dari ekspresi.
TAN(numeric_expr)
Menampilkan tangen dari argumen.
TANH(numeric_expr)
Menampilkan tangen hiperbolik dari argumen.

Contoh lanjutan

  • Kueri kotak pembatas

    Kueri berikut menampilkan kumpulan titik dalam kotak pembatas persegi panjang yang berpusat di San Francisco (37.46, -122.50).

    Contoh:

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

  • Perkiraan Kueri Lingkaran Pembatas

    Menampilkan kumpulan hingga 100 titik dalam perkiraan lingkaran yang ditentukan menggunakan Spherical Law of Cosines, yang berpusat di Denver, Colorado (39,73, -104,98). Kueri ini menggunakan fungsi matematika dan trigonometri BigQuery, seperti PI(), SIN(), dan COS().

    Karena Bumi tidak berbentuk bulat absolut, dan bujur+lintang bertemu di kutub, kueri ini menampilkan perkiraan yang dapat berguna untuk berbagai jenis data.

    Contoh:

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

Fungsi ekspresi reguler

BigQuery menyediakan dukungan ekspresi reguler menggunakan library re2; lihat dokumentasi tersebut untuk mengetahui sintaksis ekspresi regulernya.

Perhatikan bahwa ekspresi reguler adalah pencocokan global. Untuk mulai mencocokkan di awal kata, Anda harus menggunakan karakter ^.

Sintaks

Fungsi ekspresi reguler
REGEXP_MATCH() Menampilkan true jika argumen cocok dengan ekspresi reguler.
REGEXP_EXTRACT() Menampilkan bagian argumen yang cocok dengan grup tangkapan dalam ekspresi reguler.
REGEXP_REPLACE() Menggantikan substring yang cocok dengan ekspresi reguler.
REGEXP_MATCH('str', 'reg_exp')

Menampilkan true jika str cocok dengan ekspresi reguler. Untuk pencocokan string tanpa ekspresi reguler, gunakan CONTAINS, bukan REGEXP_MATCH.

Contoh:

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

Hasil:

+-------+-------+
| word  | count |
+-------+-------+
| ne'er |    42 |
| we'll |    35 |
| We'll |    33 |
+-------+-------+
REGEXP_EXTRACT('str', 'reg_exp')

Menampilkan bagian str yang cocok dengan grup tangkapan dalam ekspresi reguler.

Contoh:

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

Hasil:

+----------+
| fragment |
+----------+
| NULL     |
| Al'ce    |
| As'es    |
+----------+
REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')

Menampilkan string dengan setiap substring orig_str yang cocok dengan reg_exp diganti dengan replace_str. Misalnya, REGEXP_REPLACE ('Hello', 'lo', 'p') akan menampilkan Help.

Contoh:

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

Hasil:

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

Contoh lanjutan

  • Memfilter hasil yang ditetapkan oleh pencocokan ekspresi reguler

    Fungsi ekspresi reguler BigQuery dapat digunakan untuk memfilter hasil dalam klausa WHERE, serta untuk menampilkan hasil dalam SELECT. Contoh berikut menggabungkan kedua kasus penggunaan ekspresi reguler ke dalam satu kueri.

    Contoh:

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

  • Menggunakan ekspresi reguler pada data float atau bilangan bulat

    Meskipun fungsi ekspresi reguler BigQuery hanya berfungsi untuk data string, fungsi STRING() dapat digunakan untuk mentransmisikan data bilangan bulat atau data float ke dalam format string. Dalam contoh ini, STRING() digunakan untuk mentransmisikan nilai bilangan bulat corpus_date ke string, yang kemudian diubah oleh REGEXP_REPLACE.

    Contoh:

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

Fungsi string

Fungsi string beroperasi pada data string. Konstanta string harus diapit oleh tanda kutip tunggal atau ganda. Fungsi string peka huruf besar/kecil secara default. Anda dapat menambahkan IGNORE CASE ke akhir kueri untuk mengaktifkan pencocokan yang tidak peka huruf besar/kecil. IGNORE CASE hanya berfungsi pada karakter ASCII dan hanya pada tingkat teratas kueri.

Karakter pengganti tidak didukung dalam fungsi ini. Untuk mengetahui fungsi ekspresi reguler, lihat bagian fungsi ekspresi reguler.

Sintaks

Fungsi string
CONCAT() Menampilkan penggabungan dua string atau lebih, atau NULL jika salah satu nilainya adalah NULL.
expr CONTAINS 'str' Menampilkan true jika expr berisi argumen string yang ditentukan.
INSTR() Menampilkan indeks berbasis satu dari kemunculan pertama string.
LEFT() Menampilkan karakter paling kiri dari string.
LENGTH() Menampilkan panjang string.
LOWER() Menampilkan string asli dengan semua karakter dalam huruf kecil.
LPAD() Menyisipkan karakter di sebelah kiri string.
LTRIM() Menghapus karakter dari sisi kiri string.
REPLACE() Mengganti semua kemunculan substring.
RIGHT() Menampilkan karakter paling kanan dari string.
RPAD() Menyisipkan karakter ke sisi kanan string.
RTRIM() Menghapus karakter di akhir dari sisi kanan string.
SPLIT() Memisahkan string menjadi beberapa substring berulang.
SUBSTR() Menampilkan substring ...
UPPER() Menampilkan string asli dengan semua karakter dalam huruf besar.
CONCAT('str1', 'str2', '...')
str1 + str2 + ...
Menampilkan penggabungan dua string atau lebih, atau NULL jika salah satu nilainya NULL. Contoh: jika str1 adalah Java dan str2 adalah Script, CONCAT akan menampilkan JavaScript.
expr CONTAINS 'str'
Menampilkan true jika expr berisi argumen string yang ditentukan. Perbandingan ini peka huruf besar/kecil.
INSTR('str1', 'str2')
Menampilkan indeks berbasis satu dari kemunculan str2 pertama di str1, atau menampilkan 0 jika str2 tidak muncul di str1.
LEFT('str', numeric_expr)
Menampilkan karakter numerik_expr paling kiri dari str. Jika angkanya lebih panjang dari str, string lengkap akan ditampilkan. Contoh: LEFT('seattle', 3) akan menampilkan sea.
LENGTH('str')
Menampilkan nilai numerik untuk panjang string. Contoh: jika str adalah '123456', LENGTH akan menampilkan 6.
LOWER('str')
Menampilkan string asli dengan semua karakter dalam huruf kecil.
LPAD('str1', numeric_expr, 'str2')
Mengisi str1 di sebelah kiri dengan str2, mengulangi str2 hingga string hasil tepat numeric_expr karakter. Contoh: LPAD('1', 7, '?') akan menampilkan ??????1.
LTRIM('str1' [, str2])

Menghapus karakter dari sisi kiri str1. Jika str2 dihilangkan, LTRIM akan menghapus spasi dari sisi kiri str1. Jika tidak, LTRIM akan menghapus karakter apa pun dalam str2 dari sisi kiri str1 (peka huruf besar/kecil).

Contoh:

SELECT LTRIM("Say hello", "yaS") akan menampilkan " hello".

SELECT LTRIM("Say hello", " ySa") akan menampilkan "hello".

REPLACE('str1', 'str2', 'str3')

Mengganti semua hasil temuan dari str2 dalam str1 dengan str3.

Menampilkan karakter numerik_expr paling kanan dari str. Jika angkanya lebih panjang dari string, fungsi ini akan menampilkan seluruh string. Contoh: RIGHT('kirkland', 4) akan menampilkan land.
RPAD('str1', numeric_expr, 'str2')
Mengisi str1 di sebelah kanan dengan str2, mengulangi str2 hingga string hasil tepat numeric_expr karakter. Contoh: RPAD('1', 7, '?') akan menampilkan 1??????.
RTRIM('str1' [, str2])

Menghapus karakter di akhir dari sisi kanan str1. Jika str2 dihilangkan, RTRIM akan menghapus spasi di akhir dari str1. Jika tidak, RTRIM akan menghapus karakter apa pun dalam str2 dari sisi kanan str1 (peka huruf besar/kecil).

Contoh:

SELECT RTRIM("Say hello", "leo") akan menampilkan "Say h".

SELECT RTRIM("Say hello ", " hloe") akan menampilkan "Say".

SPLIT('str' [, 'delimiter'])
Membagi string menjadi beberapa substring berulang. Jika delimiter ditentukan, fungsi SPLIT akan membagi str menjadi substring, menggunakan delimiter sebagai pembatas.
SUBSTR('str', index [, max_len])
Menampilkan substring str, mulai dari index. Jika parameter max_len opsional digunakan, panjang string yang ditampilkan maksimal max_len karakter. Penghitungan dimulai dari 1, jadi karakter pertama dalam string berada di posisi 1 (bukan nol). Jika index adalah 5, substring dimulai dengan karakter ke-5 dari kiri pada str. Jika index adalah -4, substring dimulai dengan karakter ke-4 dari kanan pada str. Contoh: SUBSTR('awesome', -4, 4) akan menampilkan substring some.
UPPER('str')
Menampilkan string asli dengan semua karakter dalam huruf besar.

Meng-escape karakter khusus dalam string

Untuk meng-escape karakter khusus, gunakan salah satu metode berikut:

  • Gunakan notasi '\xDD', dengan '\x' diikuti dengan representasi heksadesimal dua digit dari karakter tersebut.
  • Gunakan garis miring escape di depan garis miring, tanda kutip tunggal, dan tanda kutip ganda.
  • Gunakan urutan gaya C ('\a', '\b', '\f', '\n', '\r', '\t', dan '\v') untuk karakter lainnya.

Berikut beberapa contoh escape: 

'this is a space: \x20'
'this string has \'single quote\' inside it'
'first line \n second line'
"double quotes are also ok"
'\070' -> ERROR: octal escaping is not supported

Fungsi karakter pengganti tabel

Fungsi karakter pengganti tabel adalah cara yang mudah untuk membuat kueri data dari kumpulan tabel tertentu. Fungsi karakter pengganti tabel setara dengan gabungan yang dipisahkan koma untuk semua tabel yang cocok dengan fungsi karakter pengganti. Jika Anda menggunakan fungsi karakter pengganti pada tabel, BigQuery hanya akan mengakses dan mengenakan biaya untuk tabel yang cocok dengan karakter pengganti tersebut. Fungsi karakter pengganti tabel ditentukan dalam klausa FROM kueri.

Jika Anda menggunakan fungsi karakter pengganti tabel dalam kueri, fungsi tersebut tidak perlu lagi berada di dalam tanda kurung. Misalnya, sebagian contoh berikut menggunakan tanda kurung, sedangkan yang lainnya tidak.

Hasil yang disimpan dalam cache tidak didukung untuk kueri terhadap beberapa tabel yang menggunakan fungsi karakter pengganti (meskipun jika opsi Use Cached Results dicentang). Jika menjalankan kueri karakter pengganti yang sama beberapa kali, Anda akan ditagih untuk setiap kueri.

Sintaks

Fungsi karakter pengganti tabel
TABLE_DATE_RANGE() Membuat kueri beberapa tabel harian yang mencakup rentang tanggal.
TABLE_DATE_RANGE_STRICT() Membuat kueri beberapa tabel harian yang mencakup rentang tanggal, tanpa tanggal yang hilang.
TABLE_QUERY() Membuat kueri tabel yang namanya cocok dengan predikat yang ditentukan.
TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)

Membuat kueri tabel harian yang tumpang tindih dengan rentang waktu antara <timestamp1> dan <timestamp2>.

Nama tabel harus memiliki format berikut: <prefix><day>, dengan <day> dalam format YYYYMMDD.

Anda dapat menggunakan fungsi tanggal dan waktu untuk menghasilkan parameter stempel waktu. Contoh:

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

Contoh: dapatkan tabel antara dua hari

Contoh ini mengasumsikan bahwa tabel berikut ada:

  • 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

Cocok dengan tabel berikut:

  • mydata.people20140325
  • mydata.people20140326
  • mydata.people20140327

Contoh: dapatkan tabel dalam rentang dua hari hingga "sekarang"

Contoh ini mengasumsikan bahwa tabel berikut ada dalam project bernama 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

Cocok dengan tabel berikut:

  • mydata.people20140323
  • mydata.people20140324
  • mydata.people20140325
TABLE_DATE_RANGE_STRICT(prefix, timestamp1, timestamp2)

Fungsi ini setara dengan TABLE_DATE_RANGE. Satu-satunya perbedaan adalah jika ada tabel harian yang hilang dalam urutan, TABLE_DATE_RANGE_STRICT akan gagal dan menampilkan error Not Found: Table <table_name>.

Contoh: error pada tabel yang hilang

Contoh ini mengasumsikan bahwa tabel berikut ada:

  • 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

Contoh di atas menampilkan error "Not Found" untuk tabel "people20140326".

TABLE_QUERY(dataset, expr)

Tabel kueri yang namanya cocok dengan expr yang diberikan. Parameter expr harus direpresentasikan sebagai string dan harus berisi ekspresi yang akan dinilai. Misalnya, 'length(table_id) < 3'.

Contoh: cocokkan tabel yang namanya berisi "oo" dan memiliki panjang lebih dari 4

Contoh ini mengasumsikan bahwa tabel berikut ada:

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

Cocok dengan tabel berikut:

  • mydata.ooze
  • mydata.spoon

Contoh: cocokkan tabel yang namanya diawali dengan "boo", diikuti oleh 3-5 digit numerik

Contoh ini mengasumsikan bahwa tabel berikut ada dalam project bernama 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}")')

Cocok dengan tabel berikut:

  • mydata.book418
  • mydata.boom12345

Fungsi URL

Sintaks

Fungsi URL
HOST() Pada URL yang diberikan, fungsi ini menampilkan nama host sebagai string.
DOMAIN() Pada URL yang diberikan, fungsi ini menampilkan domain sebagai string.
TLD() Pada URL yang diberikan, fungsi ini menampilkan domain level teratas plus domain negara apa pun di URL tersebut.
HOST('url_str')
Pada URL yang diberikan, fungsi ini menampilkan nama host sebagai string. Contoh: HOST('http://www.google.com:80/index.html') akan menampilkan 'www.google.com'
DOMAIN('url_str')
Pada URL yang diberikan, fungsi ini menampilkan domain sebagai string. Contoh: DOMAIN('http://www.google.com:80/index.html') akan menampilkan 'google.com'.
TLD('url_str')
Pada URL yang diberikan, fungsi ini menampilkan domain level teratas plus domain negara apa pun di URL tersebut. Contoh: TLD('http://www.google.com:80/index.html') akan menampilkan '.com'. TLD('http://www.google.co.uk:80/index.html') akan menampilkan '.co.uk'.

Catatan:

  • Fungsi ini tidak melakukan pencarian balik DNS, jadi jika Anda memanggil fungsi ini menggunakan alamat IP, fungsi tersebut akan menampilkan segmen alamat IP, bukan segmen nama host.
  • Semua fungsi penguraian URL memerlukan karakter huruf kecil. Karakter huruf besar di URL akan menghasilkan hasil NULL atau salah. Pertimbangkan untuk meneruskan input ke fungsi ini melalui LOWER() jika data Anda memiliki penggunaan huruf campuran.

Contoh lanjutan

Mengurai nama domain dari data URL

Kueri ini menggunakan fungsi DOMAIN() untuk menampilkan domain paling populer yang tercantum sebagai halaman beranda repositori di GitHub. Perhatikan penggunaan HAVING untuk memfilter data menggunakan hasil dari fungsi DOMAIN(). Fungsi ini berguna untuk menentukan informasi perujuk dari data URL.

Contoh:

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

Hasil:

+-----------------+----------------+
|   user_domain   | activity_count |
+-----------------+----------------+
| github.com      |         281879 |
| google.com      |          34769 |
| khanacademy.org |          17316 |
| sourceforge.net |          15103 |
| mozilla.org     |          14091 |
+-----------------+----------------+

Untuk melihat informasi TLD secara khusus, gunakan fungsi TLD(). Contoh ini menampilkan TLD teratas yang tidak ada dalam daftar contoh umum.

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

Hasil:

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

Fungsi jendela

Fungsi jendela, yang juga dikenal sebagai fungsi analisis, mengaktifkan penghitungan pada subset tertentu, atau "jendela", dari kumpulan hasil. Fungsi jendela mempermudah pembuatan laporan yang menyertakan analisis kompleks seperti rata-rata akhir dan total berjalan.

Setiap fungsi jendela memerlukan klausa OVER yang menentukan bagian atas dan bawah jendela. Tiga komponen klausa OVER (partisi, pengurutan, dan pembingkaian) memberikan kontrol tambahan pada jendela. Partisi memungkinkan Anda membagi data input menjadi grup logika yang memiliki karakteristik sama. Dengan pengurutan, Anda dapat mengurutkan hasil dalam partisi. Pembingkaian memungkinkan Anda membuat bingkai jendela geser dalam partisi yang bergerak relatif terhadap baris saat ini. Anda dapat mengonfigurasi ukuran perpindahan bingkai jendela berdasarkan jumlah baris atau rentang nilai, seperti interval waktu. 

#legacySQL
SELECT <window_function>
  OVER (
      [PARTITION BY <expr>]
      [ORDER BY <expr> [ASC | DESC]]
      [<window-frame-clause>]
     )
PARTITION BY
Menentukan partisi dasar tempat fungsi ini dioperasikan. Tentukan satu atau beberapa nama kolom yang dipisahkan koma. Satu partisi akan dibuat untuk setiap kumpulan nilai yang berbeda untuk kolom ini, mirip dengan klausa GROUP BY. Jika PARTITION BY dihilangkan, partisi dasar adalah semua baris dalam input ke fungsi jendela.
Klausa PARTITION BY juga memungkinkan fungsi jendela untuk mempartisi data dan memparalelkan eksekusi. Jika Anda ingin menggunakan fungsi jendela dengan allowLargeResults, atau jika Anda ingin menerapkan penggabungan atau agregasi lebih lanjut ke output fungsi jendela, gunakan PARTITION BY untuk memparalelkan eksekusi.
Klausa
JOIN EACH dan GROUP EACH BY tidak dapat digunakan pada output fungsi jendela. Untuk menghasilkan hasil kueri yang besar saat menggunakan fungsi jendela, Anda harus menggunakan PARTITION BY.
ORDER BY
Mengurutkan partisi. Jika ORDER BY tidak ada, tidak ada jaminan untuk tata urutan default apa pun. Pengurutan terjadi di tingkat partisi, sebelum klausa bingkai jendela diterapkan. Jika menentukan jendela RANGE, Anda harus menambahkan klausa ORDER BY. Urutan defaultnya adalah ASC.
ORDER BY bersifat opsional dalam beberapa kasus, tetapi fungsi jendela tertentu, seperti rank() atau dense_rank(), memerlukan klausa tersebut.
Jika Anda menggunakan ORDER BY tanpa menentukan ROWS atau RANGE, ORDER BY menyiratkan bahwa jendela diperluas dari awal partisi ke baris saat ini. Jika klausa ORDER BY tidak ada, jendela merupakan seluruh partisi.
<window-frame-clause>
{ROWS | RANGE} {BETWEEN <start> AND <end> | <start> | <end>}
Subset partisi yang akan digunakan untuk beroperasi. Ukurannya bisa sama dengan partisi atau lebih kecil. Jika Anda menggunakan ORDER BY tanpa window-frame-clause, bingkai jendela defaultnya adalah RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Jika Anda menghilangkan ORDER BY dan window-frame-clause, bingkai jendela default adalah seluruh partisi.
  • ROWS - Mendefinisikan jendela dalam hal posisi baris, relatif terhadap baris saat ini. Misalnya, untuk menambahkan kolom yang menunjukkan jumlah dari 5 baris nilai gaji sebelumnya, Anda harus menjalankan kueri SUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW). Kumpulan baris biasanya mencakup baris saat ini, tetapi hal itu tidak wajib.
  • RANGE - Mendefinisikan jendela dalam hal rentang nilai di kolom tertentu, relatif terhadap nilai kolom tersebut di baris saat ini. Hanya beroperasi pada angka dan tanggal, dengan nilai tanggal berupa bilangan bulat sederhana (mikrodetik sejak epoch). Baris yang berdekatan dengan nilai yang sama disebut baris peer. Baris peer CURRENT ROW disertakan dalam bingkai jendela yang menentukan CURRENT ROW. Misalnya, jika Anda menentukan bagian ujung jendela adalah CURRENT ROW dan baris berikutnya dalam jendela tersebut memiliki nilai yang sama, nilai tersebut akan disertakan dalam penghitungan fungsi.
  • BETWEEN <start> AND <end> - Rentang, termasuk baris awal dan akhir. Rentang tidak perlu menyertakan baris saat ini, tetapi <start> harus mendahului atau sama dengan <end>.
  • <start> - Menentukan offset awal untuk jendela ini, relatif terhadap baris saat ini. Opsi berikut didukung: 
    {UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
     dengan <expr> adalah bilangan bulat positif, PRECEDING menunjukkan nilai rentang atau nomor baris sebelumnya, dan FOLLOWING menunjukkan nilai rentang atau nomor baris berikutnya. UNBOUNDED PRECEDING berarti baris pertama partisi. Jika bagian awalnya mendahului jendela, bagian tersebut akan ditetapkan ke baris pertama partisi.
  • <end> - Menentukan offset akhir untuk jendela ini, relatif terhadap baris saat ini. Opsi berikut didukung: 
    {UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
     dengan <expr> adalah bilangan bulat positif, PRECEDING menunjukkan nilai rentang atau nomor baris sebelumnya, dan FOLLOWING menunjukkan nilai rentang atau nomor baris berikutnya. UNBOUNDED FOLLOWING berarti baris terakhir partisi. Jika bagian ujungnya berada di luar bagian akhir jendela, bagian tersebut akan ditetapkan ke baris terakhir partisi.

Tidak seperti fungsi agregasi, yang menciutkan banyak baris input menjadi satu baris output, fungsi jendela menampilkan satu baris output untuk setiap baris input. Fitur ini mempermudah pembuatan kueri yang menghitung total berjalan dan rata-rata perpindahan. Misalnya, kueri berikut menampilkan total berjalan untuk set data kecil yang terdiri dari lima baris yang ditentukan oleh pernyataan 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);

Nilai yang ditampilkan:

+------+-------+--------------+
| name | value | RunningTotal |
+------+-------+--------------+
| a    |     0 |            0 |
| b    |     1 |            1 |
| c    |     2 |            3 |
| d    |     3 |            6 |
| e    |     4 |           10 |
+------+-------+--------------+

Contoh berikut menghitung rata-rata perpindahan nilai dalam baris saat ini dan baris sebelumnya. Bingkai jendela terdiri dari dua baris yang berpindah mengikuti baris saat ini.

#legacySQL
SELECT
  name,
  value,
  AVG(value)
    OVER (ORDER BY value
          ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
    AS MovingAverage
FROM
  (SELECT "a" AS name, 0 AS value),
  (SELECT "b" AS name, 1 AS value),
  (SELECT "c" AS name, 2 AS value),
  (SELECT "d" AS name, 3 AS value),
  (SELECT "e" AS name, 4 AS value);

Nilai yang ditampilkan:

+------+-------+---------------+
| name | value | MovingAverage |
+------+-------+---------------+
| a    |     0 |           0.0 |
| b    |     1 |           0.5 |
| c    |     2 |           1.5 |
| d    |     3 |           2.5 |
| e    |     4 |           3.5 |
+------+-------+---------------+

Sintaks

Fungsi jendela
AVG()
COUNT(*)
COUNT([DISTINCT])
MAX()
MIN()
STDDEV()
SUM()
 		Operasi yang sama dengan Fungsi agregat yang sesuai, tetapi dihitung melalui jendela yang ditentukan oleh klausa OVER.
CUME_DIST() Menampilkan nilai ganda yang menunjukkan distribusi kumulatif nilai dalam grup nilai ...
DENSE_RANK() Menampilkan peringkat bilangan bulat nilai dalam grup nilai.
FIRST_VALUE() Menampilkan nilai pertama pada kolom yang ditentukan di jendela.
LAG() Memungkinkan Anda membaca data dari baris sebelumnya dalam jendela.
LAST_VALUE() Menampilkan nilai terakhir pada kolom yang ditentukan di jendela.
LEAD() Memungkinkan Anda membaca data dari baris selanjutnya dalam jendela.
NTH_VALUE() Menampilkan nilai <expr> di posisi <n> pada bingkai jendela ...
NTILE() Membagi jendela ke dalam jumlah bucket yang ditentukan.
PERCENT_RANK() Menampilkan peringkat baris saat ini, relatif terhadap baris lain dalam partisi.
PERCENTILE_CONT() Menampilkan nilai interpolasi yang akan dipetakan ke argumen persentil sehubungan dengan jendela ...
PERCENTILE_DISC() Menampilkan nilai yang paling dekat dengan persentil argumen pada jendela.
RANK() Menampilkan peringkat bilangan bulat nilai dalam grup nilai.
RATIO_TO_REPORT() Menampilkan rasio setiap nilai terhadap jumlah nilai.
ROW_NUMBER() Menampilkan nomor baris saat ini dari hasil kueri pada jendela.
AVG(numeric_expr)
COUNT(*)
COUNT([DISTINCT] field)
MAX(field)
MIN(field)
STDDEV(numeric_expr)
SUM(field)
Fungsi jendela ini menjalankan operasi yang sama dengan Fungsi agregat yang sesuai, tetapi dihitung melalui jendela yang ditentukan oleh klausa OVER.

Perbedaan signifikan lainnya adalah bahwa fungsi COUNT([DISTINCT] field) memberikan hasil yang tepat saat digunakan sebagai fungsi jendela, dengan perilaku yang mirip dengan fungsi agregat EXACT_COUNT_DISTINCT().

Dalam contoh kueri, klausa ORDER BY menyebabkan jendela dihitung dari awal partisi ke baris saat ini, yang menghasilkan jumlah kumulatif untuk tahun tersebut.

#legacySQL
SELECT
   corpus_date,
   corpus,
   word_count,
   SUM(word_count) OVER (
     PARTITION BY corpus_date
     ORDER BY word_count) annual_total
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   word='love'
ORDER BY
   corpus_date, word_count

Hasil:

corpus_date corpus word_count annual_total
0 various 37 37
0 sonnets 157 194
1590 2kinghenryvi 18 18
1590 1kinghenryvi 24 42
1590 3kinghenryvi 40 82
CUME_DIST()

Menampilkan nilai ganda yang menunjukkan distribusi kumulatif nilai dalam grup nilai, yang dihitung menggunakan formula <number of rows preceding or tied with the current row> / <total rows>. Nilai seri akan menampilkan nilai distribusi kumulatif yang sama.

Fungsi jendela ini memerlukan ORDER BY dalam klausa OVER.

#legacySQL
SELECT
   word,
   word_count,
   CUME_DIST() OVER (PARTITION BY corpus ORDER BY word_count DESC) cume_dist,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Hasil:

word word_count cume_dist
handkerchief 29 0,2
satisfaction 5 0,4
displeasure 4 0,8
instruments 4 0,8
circumstance 3 1.0
DENSE_RANK()

Menampilkan peringkat bilangan bulat nilai dalam grup nilai. Peringkat tersebut dihitung berdasarkan perbandingan dengan nilai lain dalam grup.

Nilai seri akan ditampilkan sebagai peringkat yang sama. Peringkat nilai berikutnya bertambah 1. Misalnya, jika dua nilai seri untuk peringkat 2, peringkat nilai berikutnya adalah 3. Jika Anda menginginkan perbedaan dalam daftar peringkat, gunakan rank().

Fungsi jendela ini memerlukan ORDER BY dalam klausa OVER.

#legacySQL
SELECT
   word,
   word_count,
   DENSE_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) dense_rank,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 Menampilkan:
word word_count dense_rank
handkerchief 29 1
satisfaction 5 2
displeasure 4 3
instruments 4 3
circumstance 3 4
FIRST_VALUE(<field_name>)

Menampilkan nilai pertama <field_name> di jendela.

#legacySQL
SELECT
   word,
   word_count,
   FIRST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) fv,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 1
 Menampilkan:
word word_count fv
imperfectly 1 imperfectly
LAG(<expr>[, <offset>[, <default_value>]])

Memungkinkan Anda membaca data dari baris sebelumnya dalam jendela. Secara khusus, LAG() menampilkan nilai <expr> untuk baris yang terletak pada baris <offset> sebelum baris saat ini. Jika baris tidak ada, <default_value> akan ditampilkan.

#legacySQL
SELECT
   word,
   word_count,
   LAG(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lag,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Hasil:

word word_count lag
handkerchief 29 null
satisfaction 5 handkerchief
displeasure 4 satisfaction
instruments 4 displeasure
circumstance 3 instruments
LAST_VALUE(<field_name>)

Menampilkan nilai terakhir <field_name> di jendela.

#legacySQL
SELECT
   word,
   word_count,
   LAST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) lv,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 1

Menampilkan:

word word_count lv
imperfectly 1 imperfectly

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

Memungkinkan Anda membaca data dari baris selanjutnya dalam jendela. Secara khusus, LEAD() menampilkan nilai <expr> untuk baris yang terletak pada baris <offset> setelah baris saat ini. Jika baris tidak ada, <default_value> akan ditampilkan.

#legacySQL
SELECT
   word,
   word_count,
   LEAD(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lead,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 Menampilkan:
word word_count lead
handkerchief 29 satisfaction
satisfaction 5 displeasure
displeasure 4 instruments
instruments 4 circumstance
circumstance 3 null
NTH_VALUE(<expr>, <n>)

Menampilkan nilai <expr> di posisi <n> pada bingkai jendela, dengan <n> sebagai indeks berbasis satu.

NTILE(<num_buckets>)

Membagi urutan baris menjadi <num_buckets> bucket dan menetapkan nomor bucket yang sesuai, sebagai bilangan bulat, untuk setiap baris. Fungsi ntile() menetapkan nomor bucket secukupnya dan menampilkan nilai dari 1 hingga <num_buckets> untuk setiap baris.

#legacySQL
SELECT
   word,
   word_count,
   NTILE(2) OVER (PARTITION BY corpus ORDER BY word_count DESC) ntile,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 Menampilkan:
word word_count ntile
handkerchief 29 1
satisfaction 5 1
displeasure 4 1
instruments 4 2
circumstance 3 2
PERCENT_RANK()

Menampilkan peringkat baris saat ini, relatif terhadap baris lain dalam partisi. Nilai yang ditampilkan berkisar antara 0 dan 1, secara inklusif. Nilai pertama yang ditampilkan adalah 0,0.

Fungsi jendela ini memerlukan ORDER BY dalam klausa OVER.

#legacySQL
SELECT
   word,
   word_count,
   PERCENT_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) p_rank,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 Menampilkan:
word word_count p_rank
handkerchief 29 0.0
satisfaction 5 0,25
displeasure 4 0,5
instruments 4 0,5
circumstance 3 1.0
PERCENTILE_CONT(<percentile>)

Menampilkan nilai interpolasi yang akan dipetakan ke argumen persentil sehubungan dengan jendela, setelah mengurutkannya per klausa ORDER BY.

<percentile> harus antara 0 dan 1.

Fungsi jendela ini memerlukan ORDER BY dalam klausa OVER.

#legacySQL
SELECT
   word,
   word_count,
   PERCENTILE_CONT(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_cont,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 Menampilkan:
word word_count p_cont
handkerchief 29 4
satisfaction 5 4
displeasure 4 4
instruments 4 4
circumstance 3 4
PERCENTILE_DISC(<percentile>)

Menampilkan nilai yang paling dekat dengan persentil argumen pada jendela.

<percentile> harus antara 0 dan 1.

Fungsi jendela ini memerlukan ORDER BY dalam klausa OVER.

#legacySQL
SELECT
   word,
   word_count,
   PERCENTILE_DISC(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_disc,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 Menampilkan:
word word_count p_disc
handkerchief 29 4
satisfaction 5 4
displeasure 4 4
instruments 4 4
circumstance 3 4
RANK()

Menampilkan peringkat bilangan bulat nilai dalam grup nilai. Peringkat tersebut dihitung berdasarkan perbandingan dengan nilai lain dalam grup.

Nilai seri akan ditampilkan sebagai peringkat yang sama. Peringkat nilai berikutnya bertambah sesuai dengan berapa banyak nilai seri yang terjadi sebelumnya. Misalnya, jika dua nilai seri untuk peringkat 2, peringkat nilai berikutnya adalah 4, bukan 3. Jika Anda tidak menginginkan kekosongan dalam daftar peringkat, gunakan dense_rank().

Fungsi jendela ini memerlukan ORDER BY dalam klausa OVER.

#legacySQL
SELECT
   word,
   word_count,
   RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) rank,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 Menampilkan:
word word_count rank
handkerchief 29 1
satisfaction 5 2
displeasure 4 3
instruments 4 3
circumstance 3 5
RATIO_TO_REPORT(<column>)

Menampilkan rasio setiap nilai terhadap jumlah nilai, sebagai ganda antara 0 dan 1.

#legacySQL
SELECT
   word,
   word_count,
   RATIO_TO_REPORT(word_count) OVER (PARTITION BY corpus ORDER BY word_count DESC) r_to_r,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 Menampilkan:
word word_count r_to_r
handkerchief 29 0.6444444444444445
satisfaction 5 0.1111111111111111
displeasure 4 0.08888888888888889
instruments 4 0.08888888888888889
circumstance 3 0.06666666666666667
ROW_NUMBER()

Menampilkan nomor baris saat ini dari hasil kueri pada jendela, dimulai dengan 1.

#legacySQL
SELECT
   word,
   word_count,
   ROW_NUMBER() OVER (PARTITION BY corpus ORDER BY word_count DESC) row_num,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 Menampilkan:
word word_count row_num
handkerchief 29 1
satisfaction 5 2
displeasure 4 3
instruments 4 4
circumstance 3 5

Fungsi lainnya

Sintaks

Fungsi lainnya
CASE WHEN ... THEN Gunakan CASE untuk memilih di antara dua atau beberapa ekspresi alternatif dalam kueri Anda.
CURRENT_USER() Menampilkan alamat email pengguna yang menjalankan kueri.
EVERY() Menampilkan true jika argumen benar untuk semua inputnya.
FROM_BASE64() Mengonversi string input berenkode base-64 ke format BYTES.
HASH() Menghitung dan menampilkan nilai hash yang ditandatangani 64-bit ...
FARM_FINGERPRINT() Menghitung dan menampilkan nilai sidik jari 64-bit yang ditandatangani ...
IF() Jika argumen pertama benar, argumen kedua akan ditampilkan. Jika tidak, argumen ketiga akan ditampilkan.
POSITION() Menampilkan posisi argumen berbasis satu dan berurutan.
SHA1() Menampilkan hash SHA1, dalam format BYTES.
SOME() Menampilkan true jika argumen benar untuk setidaknya salah satu inputnya.
TO_BASE64() Mengonversi argumen BYTES kei string berenkode base-64.
CASE WHEN when_expr1 THEN then_expr1
  WHEN when_expr2 THEN then_expr2 ...
  ELSE else_expr END
Gunakan CASE untuk memilih di antara dua atau beberapa ekspresi alternatif dalam kueri Anda. Ekspresi WHEN harus berupa boolean, dan semua ekspresi dalam klausa THEN dan klausa ELSE harus berupa jenis yang kompatibel.
CURRENT_USER()
Menampilkan alamat email pengguna yang menjalankan kueri.
EVERY(<condition>)
Menampilkan true jika condition benar untuk semua inputnya. Saat digunakan dengan klausa OMIT IF, fungsi ini berguna untuk kueri yang melibatkan kolom berulang.
FROM_BASE64(<str>)
Mengonversi str string input berenkode base-64 ke format BYTES. Untuk mengonversi BYTES ke string berenkode base-64, gunakan TO_BASE64().
HASH(expr)
Menghitung dan menampilkan nilai hash 64-bit yang ditandatangani untuk byte expr seperti yang ditentukan oleh library CityHash (versi 1.0.3). Semua ekspresi string atau bilangan bulat didukung dan fungsi ini mengikuti IGNORE CASE untuk string, yang menampilkan nilai invarian untuk huruf besar/kecil.
FARM_FINGERPRINT(expr)
Menghitung dan menampilkan nilai sidik jari yang ditandatangani 64-bit dari input STRING atau BYTES menggunakan fungsi Fingerprint64 dari library FarmHash open source. Output fungsi ini untuk input tertentu tidak akan pernah berubah dan cocok dengan output fungsi FARM_FINGERPRINT saat menggunakan GoogleSQL. Mengikuti IGNORE CASE untuk string, yang menampilkan nilai invarian untuk huruf besar/kecil.
IF(condition, true_return, false_return)
Menampilkan true_return atau false_return, tergantung pada apakah condition benar atau salah. Nilai yang ditampilkan dapat berupa literal atau nilai yang berasal dari kolom, tetapi harus berupa jenis data yang sama. Nilai yang berasal dari kolom tidak perlu disertakan dalam klausa SELECT.
POSITION(field)
Menampilkan posisi kolom berbasis satu dan berurutan dalam kumpulan kolom berulang.
SHA1(<str>)
Menampilkan hash SHA1, dalam format BYTES, dari string input str. Anda dapat mengonversi hasilnya ke base-64 menggunakan TO_BASE64(). Contoh: 
#legacySQL
SELECT
  TO_BASE64(SHA1(corpus))
FROM
  [bigquery-public-data:samples.shakespeare]
LIMIT
  100;
SOME(<condition>)
Menampilkan true jika condition bernilai benar untuk setidaknya salah satu inputnya. Saat digunakan dengan klausa OMIT IF, fungsi ini berguna untuk kueri yang melibatkan kolom berulang.
TO_BASE64(<bin_data>)
Mengonversi input BYTES bin_data ke string berenkode base-64. Contoh: 
#legacySQL
SELECT
  TO_BASE64(SHA1(title))
FROM
  [bigquery-public-data:samples.wikipedia]
LIMIT
  100;
 Untuk mengonversi string berenkode base64 ke BYTES, gunakan FROM_BASE64().

Contoh lanjutan

  • Mengelompokkan hasil ke dalam kategori menggunakan kondisional

    Kueri berikut menggunakan blok CASE/WHEN untuk mengelompokkan hasil ke dalam kategori "region" berdasarkan daftar status. Jika status tidak muncul sebagai opsi di salah satu pernyataan WHEN, nilai status akan ditetapkan secara default ke "None".

    Contoh:

    #legacySQL
SELECT
  CASE
    WHEN state IN ('WA', 'OR', 'CA', 'AK', 'HI', 'ID',
                   'MT', 'WY', 'NV', 'UT', 'CO', 'AZ', 'NM')
      THEN 'West'
    WHEN state IN ('OK', 'TX', 'AR', 'LA', 'TN', 'MS', 'AL',
                   'KY', 'GA', 'FL', 'SC', 'NC', 'VA', 'WV',
                   'MD', 'DC', 'DE')
      THEN 'South'
    WHEN state IN ('ND', 'SD', 'NE', 'KS', 'MN', 'IA',
                   'MO', 'WI', 'IL', 'IN', 'MI', 'OH')
      THEN 'Midwest'
    WHEN state IN ('NY', 'PA', 'NJ', 'CT',
                   'RI', 'MA', 'VT', 'NH', 'ME')
      THEN 'Northeast'
    ELSE 'None'
  END as region,
  average_mother_age,
  average_father_age,
  state, year
FROM
  (SELECT
     year, state,
     SUM(mother_age)/COUNT(mother_age) as average_mother_age,
     SUM(father_age)/COUNT(father_age) as average_father_age
   FROM
     [bigquery-public-data:samples.natality]
   WHERE
     father_age < 99
   GROUP BY
     year, state)
ORDER BY
  year
LIMIT 5;

    Hasil:

    +--------+--------------------+--------------------+-------+------+
| region | average_mother_age | average_father_age | state | year |
+--------+--------------------+--------------------+-------+------+
| South  | 24.342600163532296 | 27.683769419460344 | AR    | 1969 |
| West   | 25.185041908446163 | 28.268214055448098 | AK    | 1969 |
| West   | 24.780776677578217 | 27.831181063905248 | CA    | 1969 |
| West   | 25.005834769924412 | 27.942978384829598 | AZ    | 1969 |
| South  | 24.541730952905738 | 27.686430093306885 | AL    | 1969 |
+--------+--------------------+--------------------+-------+------+

  • Menyimulasikan Tabel Pivot

    Gunakan pernyataan kondisional untuk menyusun hasil kueri subpilihan ke dalam baris dan kolom. Dalam contoh di bawah ini, hasil penelusuran untuk sebagian besar artikel Wikipedia yang direvisi yang dimulai dengan nilai 'Google' akan disusun ke dalam kolom yang menampilkan jumlah revisi jika memenuhi berbagai kriteria.

    Contoh:

    #legacySQL
SELECT
  page_title,
  /* Populate these columns as True or False, */
  /*  depending on the condition */
  IF (page_title CONTAINS 'search',
      INTEGER(total), 0) AS search,
  IF (page_title CONTAINS 'Earth' OR
      page_title CONTAINS 'Maps', INTEGER(total), 0) AS geo,
FROM
  /* Subselect to return top revised Wikipedia articles */
  /* containing 'Google', followed by additional text. */
  (SELECT
    TOP (title, 5) as page_title,
    COUNT (*) as total
   FROM
     [bigquery-public-data:samples.wikipedia]
   WHERE
     REGEXP_MATCH (title, r'^Google.+') AND wp_namespace = 0
  );

    Hasil:

    +---------------+--------+------+
|  page_title   | search | geo  |
+---------------+--------+------+
| Google search |   4261 |    0 |
| Google Earth  |      0 | 3874 |
| Google Chrome |      0 |    0 |
| Google Maps   |      0 | 2617 |
| Google bomb   |      0 |    0 |
+---------------+--------+------+

  • Menggunakan HASH untuk memilih sampel acak dari data Anda

    Beberapa kueri dapat memberikan hasil yang berguna menggunakan subsampling acak dari kumpulan hasil. Untuk mengambil sampel nilai secara acak, gunakan fungsi HASH untuk menampilkan hasil di mana modulus "n" dari hash tersebut sama dengan nol.

    Misalnya, kueri berikut akan menemukan HASH() dari nilai "title", lalu memeriksa apakah nilai modulus "2" adalah nol. Ini akan mengakibatkan sekitar 50% nilai diberi label sebagai "sampled". Untuk mengambil sampel nilai yang lebih sedikit, naikkan nilai operasi modulus dari "2" ke nilai yang lebih besar. Kueri menggunakan fungsi ABS yang dikombinasikan dengan HASH, karena HASH dapat menampilkan nilai negatif, dan operator modulus pada nilai negatif menghasilkan nilai negatif.

    Contoh:

    #legacySQL
SELECT
  title,
  HASH(title) AS hash_value,
  IF(ABS(HASH(title)) % 2 == 1, 'True', 'False')
    AS included_in_sample
FROM
  [bigquery-public-data:samples.wikipedia]
WHERE
  wp_namespace = 0
LIMIT 5;