Ikhtisar fitur utama Google BigQuery — berlatih menulis permintaan untuk analisis pemasaran
Diterbitkan: 2022-04-12Semakin banyak informasi yang dikumpulkan suatu bisnis, semakin akut pertanyaan tentang di mana harus menyimpannya. Jika Anda tidak memiliki kemampuan atau keinginan untuk memelihara server Anda sendiri, Google BigQuery (GBQ) dapat membantu. BigQuery menyediakan penyimpanan yang cepat, hemat biaya, dan skalabel untuk bekerja dengan data besar, dan memungkinkan Anda menulis kueri menggunakan sintaks seperti SQL serta fungsi standar dan yang ditentukan pengguna.
Dalam artikel ini, kita melihat fungsi utama BigQuery dan menunjukkan kemungkinannya menggunakan contoh spesifik. Anda akan belajar cara menulis kueri dasar dan mengujinya pada data demo.
Buat laporan tentang data GBQ tanpa pelatihan teknis atau pengetahuan tentang SQL.
Apakah Anda secara teratur membutuhkan laporan tentang kampanye iklan tetapi tidak punya waktu untuk mempelajari SQL atau menunggu tanggapan dari analis Anda? Dengan OWOX BI, Anda dapat membuat laporan tanpa perlu memahami bagaimana data Anda terstruktur. Cukup pilih parameter dan metrik yang ingin Anda lihat di laporan Data Cerdas Anda. OWOX BI Smart Data akan langsung memvisualisasikan data Anda dengan cara yang dapat Anda pahami.
Daftar Isi
- Apa itu SQL dan dialek apa yang didukung BigQuery
- Mulai dari mana
- Fitur Google BigQuery
- Fungsi agregat
- Fungsi tanggal
- Fungsi string
- Fungsi jendela
- Kesimpulan
Apa itu SQL dan dialek apa yang didukung BigQuery
Structured Query Language (SQL) memungkinkan Anda untuk mengambil data dari, menambahkan data, dan memodifikasi data dalam array besar. Google BigQuery mendukung dua dialek SQL: SQL Standar dan SQL Lama yang sudah ketinggalan zaman.
Dialek mana yang harus dipilih bergantung pada preferensi Anda, tetapi Google merekomendasikan penggunaan SQL Standar untuk manfaat berikut:
- Fleksibilitas dan fungsionalitas untuk bidang bersarang dan berulang
- Dukungan untuk bahasa DML dan DDL, memungkinkan Anda mengubah data dalam tabel serta mengelola tabel dan tampilan dalam GBQ
- Pemrosesan data dalam jumlah besar lebih cepat dibandingkan dengan Legacy SQL
- Dukungan untuk semua pembaruan BigQuery di masa mendatang
Anda dapat mempelajari lebih lanjut tentang perbedaan dialek di dokumentasi BigQuery.
Lihat juga: Apa kelebihan dialek SQL Standar baru Google BigQuery dibandingkan SQL Lama, dan tugas bisnis apa yang dapat Anda selesaikan dengannya?
Secara default, kueri Google BigQuery berjalan di SQL Lama.
Anda dapat beralih ke SQL Standar dengan beberapa cara:
- Di antarmuka BigQuery, di jendela pengeditan kueri, pilih Tampilkan Opsi dan hapus tanda centang di samping Gunakan SQL Lama :
- Sebelum membuat kueri, tambahkan baris #standardSQL dan mulai kueri Anda dengan baris baru:
Mulai dari mana
Agar Anda dapat berlatih dan menjalankan kueri bersama kami, kami telah menyiapkan tabel dengan data demo. Isi formulir di bawah ini dan kami akan mengirimkannya ke email Anda.
Data demo untuk latihan kueri SQL
Unduh Untuk memulai, unduh tabel data demo Anda dan unggah ke proyek Google BigQuery Anda. Cara termudah untuk melakukannya adalah dengan add-on OWOX BI BigQuery Reports.
- Buka Google Spreadsheet dan instal add-on OWOX BI BigQuery Reports.
- Buka tabel yang Anda unduh yang berisi data demo dan pilih Laporan BigQuery OWOX BI -> Unggah Data ke BigQuery :
- Di jendela yang terbuka, pilih proyek Google BigQuery Anda, kumpulan data, dan pikirkan nama untuk tabel tempat data yang dimuat akan disimpan.
- Tentukan format untuk data yang dimuat (seperti yang ditunjukkan pada tangkapan layar):
Jika Anda tidak memiliki proyek di Google BigQuery, buatlah. Untuk melakukannya, Anda memerlukan akun penagihan aktif di Google Cloud Platform. Jangan biarkan hal itu membuat Anda takut bahwa Anda perlu menautkan kartu bank: Anda tidak akan dikenakan biaya apa pun tanpa sepengetahuan Anda. Selain itu, saat mendaftar, Anda akan menerima $300 selama 12 bulan yang dapat digunakan untuk penyimpanan dan pemrosesan data.
OWOX BI membantu Anda menggabungkan data dari sistem yang berbeda ke dalam BigQuery: data tentang tindakan pengguna di situs web Anda, panggilan telepon, pesanan dari CRM Anda, email, biaya iklan. Anda dapat menggunakan OWOX BI untuk menyesuaikan analitik tingkat lanjut dan mengotomatiskan laporan dengan kompleksitas apa pun.
Sebelum berbicara tentang fitur Google BigQuery, mari kita ingat seperti apa kueri dasar dalam dialek SQL Lama dan SQL Standar:
| Pertanyaan | SQL lama | SQL standar |
|---|---|---|
| Pilih bidang dari tabel | PILIH bidang1,bidang2 | PILIH bidang1,bidang2 |
| Pilih tabel untuk memilih bidang | DARI [projectID:dataSet.tableName] | DARI `projectID.dataSet.tableName` |
| Pilih parameter yang digunakan untuk memfilter nilai | WHERE bidang1=nilai | kolom WHERE1=nilai |
| Pilih bidang yang digunakan untuk mengelompokkan hasil | KELOMPOK BERDASAR bidang1, bidang2 | KELOMPOK BERDASAR bidang1, bidang2 |
| Pilih cara memesan hasil | ORDER BY field1 ASC (naik) atau DESC (turun) | ORDER BY field1 ASC (naik) atau DESC (turun) |
Fitur Google BigQuery
Saat membuat kueri, Anda akan paling sering menggunakan fungsi agregat , tanggal, string, dan jendela. Mari kita lihat lebih dekat masing-masing kelompok fungsi ini.
Lihat juga: Cara mulai bekerja dengan penyimpanan cloud — buat set data dan tabel serta konfigurasikan pengimporan data ke Google BigQuery.
Fungsi agregat
Fungsi agregat memberikan nilai ringkasan untuk seluruh tabel. Misalnya, Anda dapat menggunakannya untuk menghitung ukuran cek rata-rata atau pendapatan total per bulan, atau Anda dapat menggunakannya untuk memilih segmen pengguna yang melakukan jumlah pembelian maksimum.
Ini adalah fungsi agregat yang paling populer:
| SQL lama | SQL standar | Apa fungsinya? |
|---|---|---|
| AVG (bidang) | AVG([BERBEDA] (bidang)) | Mengembalikan nilai rata-rata kolom bidang. Di SQL Standar, saat Anda menambahkan kondisi DISTINCT, rata-rata dianggap hanya untuk baris dengan nilai unik (tidak berulang) di kolom bidang. |
| MAX (bidang) | MAX (bidang) | Mengembalikan nilai maksimum dari kolom bidang. |
| MIN (bidang) | MIN (bidang) | Mengembalikan nilai minimum dari kolom bidang. |
| SUM(bidang) | SUM(bidang) | Mengembalikan jumlah nilai dari kolom bidang. |
| JUMLAH(bidang) | JUMLAH(bidang) | Mengembalikan jumlah baris dalam kolom bidang. |
| EXACT_COUNT_DISTINCT(bidang) | JUMLAH([BERBEDA] (bidang)) | Mengembalikan jumlah baris unik di kolom bidang. |
Untuk daftar semua fungsi agregat, lihat dokumentasi SQL Legacy dan SQL Standar.
Mari kita lihat data demo untuk melihat bagaimana fungsi-fungsi ini bekerja. Kami dapat menghitung pendapatan rata-rata untuk transaksi, pembelian untuk jumlah tertinggi dan terendah, total pendapatan, total transaksi, dan jumlah transaksi unik (untuk memeriksa apakah pembelian digandakan). Untuk melakukan ini, kami akan menulis kueri di mana kami menentukan nama proyek Google BigQuery kami, kumpulan data, dan tabel.
#SQL lama
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, EXACT_COUNT_DISTINCT(transactionId) as unique_transactions FROM [owox-analytics:t_kravchenko.Demo_data]
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, EXACT_COUNT_DISTINCT(transactionId) as unique_transactions FROM [owox-analytics:t_kravchenko.Demo_data]#SQL standar
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, COUNT(DISTINCT(transactionId)) as unique_transactions FROM `owox-analytics.t_kravchenko.Demo_data`
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, COUNT(DISTINCT(transactionId)) as unique_transactions FROM `owox-analytics.t_kravchenko.Demo_data`Hasilnya, kita akan mendapatkan yang berikut:
Anda dapat memeriksa hasil perhitungan ini di tabel asli dengan data demo menggunakan fungsi Google Spreadsheet standar (SUM, AVG, dan lainnya) atau menggunakan tabel pivot.
Seperti yang Anda lihat dari tangkapan layar di atas, jumlah transaksi dan transaksi unik berbeda. Ini menunjukkan ada dua transaksi di tabel kami dengan transactionId yang sama:
Jika Anda tertarik dengan transaksi unik, gunakan fungsi yang menghitung string unik. Atau, Anda dapat mengelompokkan data menggunakan fungsi GROUP BY untuk menghilangkan duplikat sebelum menerapkan fungsi agregat.
Data demo untuk latihan kueri SQL
UnduhFungsi tanggal
Fungsi-fungsi ini memungkinkan Anda untuk memproses tanggal: mengubah formatnya, memilih bidang yang diperlukan (hari, bulan, atau tahun), atau menggeser tanggal dengan interval tertentu.
Mereka mungkin berguna ketika:
- mengonversi tanggal dan waktu dari sumber yang berbeda ke satu format untuk menyiapkan analitik lanjutan
- membuat laporan yang diperbarui secara otomatis atau memicu pengiriman surat (misalnya, saat Anda membutuhkan data selama dua jam, minggu, atau bulan terakhir)
- membuat laporan kohort yang diperlukan untuk memperoleh data selama beberapa hari, minggu, atau bulan
Ini adalah fungsi tanggal yang paling umum digunakan:
| SQL lama | SQL standar | Deskripsi fungsi |
|---|---|---|
| TANGGAL SEKARANG() | TANGGAL SEKARANG() | Mengembalikan tanggal saat ini dalam format % YYYY -% MM-% DD. |
| TANGGAL(stempel waktu) | TANGGAL(stempel waktu) | Mengonversi tanggal dari % YYYY -% MM-% DD% H:% M:% C. ke format % YYYY -% MM-% DD. |
| DATE_ADD(stempel waktu, interval, interval_units) | DATE_ADD(stempel waktu, INTERVAL interval interval_units) | Mengembalikan tanggal cap waktu, meningkatkannya dengan interval interval yang ditentukan.interval_units.Dalam SQL Legacy, dapat mengambil nilai YEAR, MONTH, DAY, HOUR, MINUTE, dan SECOND, dan dalam Standard SQL dapat mengambil YEAR, QUARTER, MONTH, MINGGU, dan HARI. |
| DATE_ADD(stempel waktu, - interval, interval_units) | DATE_SUB(stempel waktu, INTERVAL interval interval_units) | Mengembalikan tanggal stempel waktu, menguranginya dengan interval yang ditentukan. |
| DATEDIFF(stempel waktu1, stempel waktu2) | DATE_DIFF(timestamp1, timestamp2, date_part) | Mengembalikan perbedaan antara tanggal timestamp1 dan timestamp2. Di SQL Legacy, mengembalikan perbedaan dalam hari, dan dalam SQL Standar, mengembalikan perbedaan tergantung pada nilai date_part yang ditentukan (hari, minggu, bulan, kuartal, tahun). |
| HARI (cap waktu) | EKSTRAK(HARI DARI stempel waktu) | Mengembalikan hari dari tanggal stempel waktu. Mengambil nilai dari 1 hingga 31 inklusif. |
| BULAN (stempel waktu) | EKSTRAK(BULAN DARI stempel waktu) | Mengembalikan nomor urut bulan dari tanggal stempel waktu. Mengambil nilai dari 1 hingga 12 inklusif. |
| TAHUN (stempel waktu) | EKSTRAK (TAHUN DARI stempel waktu) | Mengembalikan tahun dari tanggal stempel waktu. |
Untuk daftar semua fungsi tanggal, lihat dokumentasi SQL Legacy dan SQL Standar.
Mari kita lihat data demo kami untuk melihat bagaimana masing-masing fungsi ini bekerja. Misalnya, kita akan mendapatkan tanggal saat ini, mengubah tanggal dari tabel asli ke dalam format % YYYY -% MM-% DD, menghapusnya, dan menambahkan satu hari ke dalamnya. Kemudian kami akan menghitung perbedaan antara tanggal saat ini dan tanggal dari tabel sumber dan membagi tanggal saat ini menjadi bidang tahun, bulan, dan hari yang terpisah. Untuk melakukannya, Anda dapat menyalin kueri sampel di bawah dan mengganti nama proyek, kumpulan data, dan tabel data dengan milik Anda sendiri.
#SQL lama
SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC,1, 'DAY') AS date_UTC_plus_one_day, DATE_ADD( date_UTC,-1, 'DAY') AS date_UTC_minus_one_day, DATEDIFF(CURRENT_DATE(), date_UTC ) AS difference_between_date, DAY( CURRENT_DATE() ) AS the_day, MONTH( CURRENT_DATE()) AS the_month, YEAR( CURRENT_DATE()) AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]
SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC,1, 'DAY') AS date_UTC_plus_one_day, DATE_ADD( date_UTC,-1, 'DAY') AS date_UTC_minus_one_day, DATEDIFF(CURRENT_DATE(), date_UTC ) AS difference_between_date, DAY( CURRENT_DATE() ) AS the_day, MONTH( CURRENT_DATE()) AS the_month, YEAR( CURRENT_DATE()) AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]#SQL standar
SELECT today, date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC_in_YYYYMMDD, INTERVAL 1 DAY) AS date_UTC_plus_one_day, DATE_SUB( date_UTC_in_YYYYMMDD,INTERVAL 1 DAY) AS date_UTC_minus_one_day, DATE_DIFF(today, date_UTC_in_YYYYMMDD, DAY) AS difference_between_date, EXTRACT(DAY FROM today ) AS the_day, EXTRACT(MONTH FROM today ) AS the_month, EXTRACT(YEAR FROM today ) AS the_year FROM ( SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD FROM `owox-analytics.t_kravchenko.Demo_data`)
SELECT today, date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC_in_YYYYMMDD, INTERVAL 1 DAY) AS date_UTC_plus_one_day, DATE_SUB( date_UTC_in_YYYYMMDD,INTERVAL 1 DAY) AS date_UTC_minus_one_day, DATE_DIFF(today, date_UTC_in_YYYYMMDD, DAY) AS difference_between_date, EXTRACT(DAY FROM today ) AS the_day, EXTRACT(MONTH FROM today ) AS the_month, EXTRACT(YEAR FROM today ) AS the_year FROM ( SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD FROM `owox-analytics.t_kravchenko.Demo_data`)Setelah menjalankan kueri, Anda akan menerima laporan ini:
Lihat juga: Contoh laporan yang dapat dibuat menggunakan kueri SQL pada data di Google BigQuery dan metrik unik apa yang dapat Anda tambahkan ke data Google Analytics dengan OWOX BI.
Fungsi string
Fungsi string memungkinkan Anda untuk menghasilkan string, memilih dan mengganti substring, dan menghitung panjang string dan urutan indeks substring dalam string asli. Misalnya, dengan fungsi string, Anda dapat:
- filter laporan dengan tag UTM yang diteruskan ke URL halaman
- membawa data ke dalam satu format jika sumber dan nama kampanye ditulis dalam register yang berbeda
- mengganti data yang salah dalam laporan (misalnya, jika nama kampanye salah cetak)
Ini adalah fungsi paling populer untuk bekerja dengan string:
| SQL lama | SQL standar | Deskripsi fungsi |
|---|---|---|
| CONCAT('str1', 'str2') atau 'str1'+ 'str2' | CONCAT('str1', 'str2') | Menggabungkan 'str1' dan 'str2' menjadi satu string. |
| 'str1' BERISI 'str2' | REGEXP_CONTAINS('str1', 'str2') atau 'str1' LIKE '%str2%' | Mengembalikan nilai true jika string 'str1' berisi string 'str2.'Dalam SQL Standar, string 'str2' dapat ditulis sebagai ekspresi reguler menggunakan pustaka re2 . |
| PANJANG('str' ) | CHAR_LENGTH('str' )atau CHARACTER_LENGTH('str' ) | Mengembalikan panjang string 'str' (jumlah karakter). |
| SUBSTR('str', indeks [, max_len]) | SUBSTR('str', indeks [, max_len]) | Mengembalikan substring dengan panjang max_len yang dimulai dengan karakter indeks dari string 'str'. |
| LOWER('str') | LOWER('str') | Mengonversi semua karakter dalam string 'str menjadi huruf kecil. |
| ATAS(str) | ATAS(str) | Mengonversi semua karakter dalam string 'str' menjadi huruf besar. |
| INSTR('str1', 'str2') | STRPOS('str1', 'str2') | Mengembalikan indeks kemunculan pertama string 'str2' ke string 'str1'; jika tidak, mengembalikan 0. |
| GANTI('str1', 'str2', 'str3') | GANTI('str1', 'str2', 'str3') | Mengganti 'str1' dengan 'str2' dengan 'str3'. |
Anda dapat mempelajari lebih lanjut tentang semua fungsi string di Legacy SQL dan dokumentasi SQL Standar.
Mari kita lihat data demo untuk melihat cara menggunakan fungsi yang dijelaskan. Misalkan kita memiliki tiga kolom terpisah yang berisi nilai hari, bulan, dan tahun:
Bekerja dengan tanggal dalam format ini sangat tidak nyaman, jadi kami dapat menggabungkan nilai ke dalam satu kolom. Untuk melakukannya, gunakan kueri SQL di bawah dan jangan lupa untuk mengganti nama proyek, set data, dan tabel Anda di Google BigQuery.
#SQL lama
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY mix_string1
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY mix_string1#SQL standar
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM owox-analytics.t_kravchenko.Demo_data) GROUP BY mix_string1
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM owox-analytics.t_kravchenko.Demo_data) GROUP BY mix_string1Setelah menjalankan kueri, kami menerima tanggal dalam satu kolom:
Seringkali, ketika Anda mengunduh halaman di situs web, URL mencatat nilai variabel yang dipilih pengguna. Ini bisa berupa metode pembayaran atau pengiriman, nomor transaksi, indeks toko fisik tempat pembeli ingin mengambil barang, dll. Dengan menggunakan kueri SQL, Anda dapat memilih parameter ini dari alamat halaman. Pertimbangkan dua contoh bagaimana dan mengapa Anda melakukan ini.
Contoh 1 . Misalkan kita ingin mengetahui jumlah pembelian dimana pengguna mengambil barang dari toko fisik. Untuk melakukan ini, kita perlu menghitung jumlah transaksi yang dikirim dari halaman di URL yang berisi substring shop_id (indeks untuk toko fisik). Kita dapat melakukan ini dengan pertanyaan berikut:
#SQL lama
SELECT COUNT(transactionId) AS transactions, check FROM ( SELECT transactionId, page CONTAINS 'shop_id' AS check FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY check
SELECT COUNT(transactionId) AS transactions, check FROM ( SELECT transactionId, page CONTAINS 'shop_id' AS check FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY check#SQL standar
SELECT COUNT(transactionId) AS transactions, check1, check2 FROM ( SELECT transactionId, REGEXP_CONTAINS( page, 'shop_id') AS check1, page LIKE '%shop_id%' AS check2 FROM `owox-analytics.t_kravchenko.Demo_data`) GROUP BY check1, check2
SELECT COUNT(transactionId) AS transactions, check1, check2 FROM ( SELECT transactionId, REGEXP_CONTAINS( page, 'shop_id') AS check1, page LIKE '%shop_id%' AS check2 FROM `owox-analytics.t_kravchenko.Demo_data`) GROUP BY check1, check2Dari tabel yang dihasilkan, terlihat bahwa 5502 transaksi (cek = true) dikirim dari halaman yang berisi shop_id:
Contoh 2 . Anda telah menetapkan delivery_id untuk setiap metode pengiriman, dan Anda menentukan nilai parameter ini di URL halaman. Untuk mengetahui metode pengiriman yang telah dipilih pengguna, Anda harus memilih delivery_id di kolom terpisah.
Kita dapat menggunakan kueri berikut untuk ini:
#SQL lama
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') as delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, LENGTH(page_lower_case) AS page_length, INSTR(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM [owox-analytics:t_kravchenko.Demo_data]))) ORDER BY page_lower_case ASC
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') as delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, LENGTH(page_lower_case) AS page_length, INSTR(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM [owox-analytics:t_kravchenko.Demo_data]))) ORDER BY page_lower_case ASC#SQL standar
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') AS delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, CHAR_LENGTH(page_lower_case) AS page_length, STRPOS(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM `owox-analytics.t_kravchenko.Demo_data`))) ORDER BY page_lower_case ASC
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') AS delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, CHAR_LENGTH(page_lower_case) AS page_length, STRPOS(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM `owox-analytics.t_kravchenko.Demo_data`))) ORDER BY page_lower_case ASCHasilnya, kami mendapatkan tabel seperti ini di Google BigQuery:
Data demo untuk latihan kueri SQL
UnduhFungsi jendela
Fungsi-fungsi ini mirip dengan fungsi agregat yang telah kita bahas di atas. Perbedaan utama adalah bahwa fungsi jendela tidak melakukan perhitungan pada seluruh kumpulan data yang dipilih menggunakan kueri tetapi hanya pada sebagian data tersebut — subset atau jendela .
Menggunakan fungsi jendela, Anda dapat menggabungkan data di bagian grup tanpa menggunakan fungsi GABUNG untuk menggabungkan beberapa kueri. Misalnya, Anda dapat menghitung pendapatan rata-rata per kampanye iklan atau jumlah transaksi per perangkat. Dengan menambahkan bidang lain ke laporan, Anda dapat dengan mudah mengetahui, misalnya, bagian pendapatan dari kampanye iklan di Black Friday atau bagian transaksi yang dilakukan dari aplikasi seluler.
Bersama dengan setiap fungsi dalam kueri, Anda harus mengeja ekspresi OVER yang mendefinisikan batas jendela. OVER berisi tiga komponen yang dapat Anda gunakan:
- PARTITION BY — Mendefinisikan karakteristik yang Anda gunakan untuk membagi data asli menjadi subset, seperti clientId atau DayTime
- ORDER BY — Mendefinisikan urutan baris dalam subset, seperti jam DESC
- WINDOW FRAME — Memungkinkan Anda untuk memproses baris dalam subset fitur tertentu (misalnya, hanya lima baris sebelum baris saat ini)
Dalam tabel ini, kami telah mengumpulkan fungsi jendela yang paling sering digunakan:
| SQL lama | SQL standar | Deskripsi fungsi |
|---|---|---|
| AVG (bidang) JUMLAH(bidang) COUNT(bidang BERBEDA) MAKS () MIN() JUMLAH() | AVG([BERBEDA] (bidang)) JUMLAH(bidang) JUMLAH([BERBEDA] (bidang)) MAX (bidang) MIN (bidang) SUM(bidang) | Mengembalikan nilai rata-rata, angka, maksimum, minimum, dan total dari kolom bidang dalam subset yang dipilih.DISTINCT digunakan untuk menghitung hanya nilai unik (tidak berulang). |
| PADAT_RANK() | PADAT_RANK() | Mengembalikan nomor baris dalam subset. |
| FIRST_VALUE(bidang) | NILAI_PERTAMA (bidang[{RESPECT | IGNORE} NULL]) | Mengembalikan nilai baris pertama dari kolom bidang dalam subset. Secara default, baris dengan nilai kosong dari kolom bidang disertakan dalam penghitungan. RESPECT atau IGNORE NULLS menentukan apakah akan menyertakan atau mengabaikan string NULL. |
| LAST_VALUE(bidang) | NILAI_TERAKHIR (bidang [{RESPECT | ABAIKAN} NULL]) | Mengembalikan nilai baris terakhir dalam subset dari kolom bidang. Secara default, baris dengan nilai kosong di kolom bidang disertakan dalam penghitungan. RESPECT atau IGNORE NULLS menentukan apakah akan menyertakan atau mengabaikan string NULL. |
| LAG (bidang) | LAG (bidang[, offset [, default_expression]]) | Mengembalikan nilai baris sebelumnya sehubungan dengan kolom bidang saat ini di dalam subset.Offset adalah bilangan bulat yang menentukan jumlah baris yang akan di-offset dari baris saat ini.Default_expression adalah nilai yang akan dikembalikan fungsi jika tidak diperlukan string dalam subset. |
| LEAD (bidang) | LEAD (bidang[, offset [, default_expression]]) | Mengembalikan nilai baris berikutnya relatif terhadap kolom bidang saat ini dalam subset. Offset adalah bilangan bulat yang menentukan jumlah baris yang ingin Anda naikkan sehubungan dengan baris saat ini. Default_expression adalah nilai yang akan dikembalikan fungsi jika tidak ada string yang diperlukan dalam subset saat ini. |
Anda dapat melihat daftar semua fungsi analitik agregat dan fungsi navigasi dalam dokumentasi untuk SQL Lama dan SQL Standar.
Contoh 1 . Katakanlah kita ingin menganalisis aktivitas pelanggan selama jam kerja dan non-kerja. Untuk melakukan ini, kita perlu membagi transaksi menjadi dua kelompok dan menghitung metrik bunga:
- Grup 1 — Pembelian selama jam kerja dari 9:00 hingga 18:00
- Grup 2 — Pembelian setelah jam kerja dari pukul 00:00 hingga 9:00 dan dari pukul 18:00 hingga 23:59
Selain jam kerja dan non-kerja, variabel lain untuk membentuk jendela adalah clientId. Artinya, untuk setiap pengguna, kita akan memiliki dua jendela:
| jendela | id klien | Siang hari |
|---|---|---|
| jendela 1 | ID klien 1 | jam kerja |
| jendela 2 | ID klien 2 | jam tidak bekerja |
| jendela 3 | ID klien 3 | jam kerja |
| jendela 4 | ID klien 4 | jam tidak bekerja |
| jendela N | ID klien N | jam kerja |
| jendela N+1 | clientId N+1 | jam tidak bekerja |
Mari kita gunakan data demo untuk menghitung pendapatan rata-rata, maksimum, minimum, dan total, jumlah total transaksi, dan jumlah transaksi unik per pengguna selama jam kerja dan non-kerja. Permintaan di bawah ini akan membantu kami melakukan ini.
#SQL lama
SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESC
SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESC#SQL standar
#standardSQL SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESC
#standardSQL SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESCMari kita lihat apa yang terjadi sebagai hasilnya menggunakan contoh pengguna dengan clientId 102041117.1428132012. Di tabel asli untuk pengguna ini, kami memiliki data berikut:
Dengan menjalankan kueri, kami menerima laporan yang berisi pendapatan rata-rata, minimum, maksimum, dan total dari pengguna ini serta jumlah total transaksi pengguna. Seperti yang dapat Anda lihat pada tangkapan layar di bawah, kedua transaksi tersebut dilakukan oleh pengguna selama jam kerja:
Contoh 2 . Sekarang untuk tugas yang lebih rumit:
- Letakkan nomor urut untuk semua transaksi di jendela tergantung pada waktu eksekusinya. Ingatlah bahwa kita mendefinisikan jendela menurut pengguna dan slot waktu kerja/non-kerja.
- Laporkan pendapatan dari transaksi berikutnya/sebelumnya (relatif terhadap saat ini) di dalam jendela.
- Tampilkan pendapatan dari transaksi pertama dan terakhir di jendela.
Untuk melakukannya, kami akan menggunakan kueri berikut:
#SQL lama
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour#SQL standar
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hourKami dapat memeriksa hasil perhitungan menggunakan contoh pengguna yang sudah kami ketahui: clientId 102041117.1428132012:
Dari tangkapan layar di atas, kita dapat melihat bahwa:
- transaksi pertama jam 15:00 dan transaksi kedua jam 16:00
- setelah transaksi jam 15:00, terjadi transaksi jam 16:00 dengan pendapatan 25066 (kolom lead_revenue)
- sebelum transaksi jam 16:00 terjadi transaksi jam 15:00 dengan pendapatan 3699 (kolom lag_revenue)
- transaksi pertama dalam jendela adalah pukul 15:00, dan pendapatan untuk transaksi ini adalah 3699 (kolom first_revenue_by_hour)
- kueri memproses data baris demi baris, jadi untuk transaksi yang dimaksud, transaksi terakhir di jendela akan menjadi dirinya sendiri dan nilai di kolom last_revenue_by_hour dan pendapatan akan sama
Artikel bermanfaat tentang Google BigQuery:
- 6 Alat Visualisasi BigQuery Teratas
- Cara Mengunggah Data ke Google BigQuery
- Cara Mengupload Data Mentah dari Google Ads ke Google BigQuery
- Konektor Google Spreadsheet Google BigQuery
- Mengotomatiskan Laporan di Google Spreadsheet Menggunakan Data dari Google BigQuery
- Mengotomatiskan laporan di Google Data Studio Berdasarkan Data dari Google BigQuery
Jika Anda ingin mengumpulkan data tanpa sampel dari situs Anda di Google BigQuery tetapi tidak tahu harus mulai dari mana, pesan demo. Kami akan memberi tahu Anda tentang semua kemungkinan yang Anda dapatkan dengan BigQuery dan OWOX BI.
Klien kami
tumbuh 22% lebih cepat
Tumbuh lebih cepat dengan mengukur apa yang paling berhasil dalam pemasaran Anda
Analisis efisiensi pemasaran Anda, temukan area pertumbuhan, tingkatkan ROI
Dapatkan demoKesimpulan
Dalam artikel ini, kita telah melihat grup fungsi yang paling populer: agregat, tanggal, string, dan jendela. Namun, Google BigQuery memiliki banyak fungsi yang lebih berguna, termasuk:
- fungsi casting yang memungkinkan Anda mengonversi data ke format tertentu
- fungsi wildcard tabel yang memungkinkan Anda mengakses beberapa tabel dalam kumpulan data
- fungsi ekspresi reguler yang memungkinkan Anda mendeskripsikan model kueri penelusuran dan bukan nilai pastinya
Kami pasti akan menulis tentang fungsi-fungsi ini di blog kami. Sementara itu, Anda dapat mencoba semua fungsi yang dijelaskan dalam artikel ini menggunakan data demo kami.
Data demo untuk latihan kueri SQL
Unduh