Lanjutan: Mengkueri set data secara langsung

Untuk mengakses data Places Insights secara langsung, Anda menulis SQL kueri di BigQuery yang menampilkan insight gabungan tentang tempat. Hasil ditampilkan dari set data untuk kriteria penelusuran yang ditentukan dalam kueri.

Memahami nilai minimum agregasi

Kueri SQL standar yang dijalankan langsung pada tabel mentah menerapkan nilai minimum agregasi yang ketat. Baris output dibuat hanya jika jumlah agregasi yang dihitung adalah 5 atau lebih tinggi.

Jika filter kueri Anda menghasilkan jumlah 0, 1, 2, 3, atau 4, seluruh baris hasil akan dihilangkan dari respons. Jika logika aplikasi Anda memerlukan jumlah rendah yang akurat (termasuk 0) atau perlu mengambil ID Tempat individual, Anda harus membuat kueri set data menggunakan Fungsi Jumlah Tempat sebagai gantinya.

Dasar-dasar kueri

Gambar berikut menunjukkan format dasar kueri:

Setiap bagian kueri dijelaskan secara lebih mendetail di bawah.

Tips: BigQuery juga menyediakan dokumentasi dan contoh untuk menjalankan kueri pada data geospasial. Untuk mengetahui informasi selengkapnya, lihat Pengantar analisis geospasial.

Persyaratan kueri

Kueri SQL yang dibuat langsung pada set data harus menentukan set data dan menyertakan WITH AGGREGATION_THRESHOLD dalam klausa SELECT. Jika tidak, kueri akan gagal.

Contoh ini menentukan places_insights___us.places untuk membuat kueri set data untuk Amerika Serikat.

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`

Menentukan nama project (opsional)

Anda dapat menyertakan nama project dalam kueri secara opsional. Jika Anda tidak menentukan nama project, kueri akan ditetapkan secara default ke project aktif.

Anda mungkin ingin menyertakan nama project jika telah menautkan set data dengan nama yang sama di project yang berbeda, atau jika Anda membuat kueri tabel di luar project aktif.

Misalnya, [project name].[dataset name].places.

Menentukan fungsi agregasi

Contoh di bawah menunjukkan fungsi agregasi BigQuery yang didukung. Kueri ini menggabungkan rating semua tempat yang terletak dalam radius 1.000 meter dari Empire State Building di New York City untuk menghasilkan statistik rating:

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(id) AS place_count,
  APPROX_COUNT_DISTINCT(rating) as distinct_ratings,
  COUNTIF(rating > 4.0) as good_rating_count,
  LOGICAL_AND(rating <= 5) as all_ratings_equal_or_below_five,
  LOGICAL_OR(rating = 5) as any_rating_exactly_five,
  AVG(rating) as avg_rating,
  SUM(user_rating_count) as rating_count,
  COVAR_POP(rating, user_rating_count) as rating_covar_pop,
  COVAR_SAMP(rating, user_rating_count) as rating_covar_samp,
  STDDEV_POP(rating) as rating_stddev_pop,
  STDDEV_SAMP(rating) as rating_stddev_samp,
  VAR_POP(rating) as rating_var_pop,
  VAR_SAMP(rating) as rating_var_samp,
FROM
  `PROJECT_NAME.places_insights___us.places`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
  AND business_status = "OPERATIONAL"

Memfilter hasil menggunakan poligon dan garis geografis

Jika Anda tidak menentukan batasan lokasi, agregasi data akan diterapkan ke seluruh set data. Biasanya, Anda menentukan batasan lokasi untuk menelusuri area tertentu.

1. Memfilter menggunakan buffer penelusuran radial (ST_DWITHIN)

Kueri contoh ini menentukan batasan target yang berpusat di Empire State Building di New York City, dengan radius 1.000 meter.

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`
WHERE
ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)

Catatan: Tempat hanya disertakan dalam hasil jika titik tengah tempat tersebut berada dalam batasan lokasi. Tempat, seperti taman, yang titik pusatnya berada di luar batasan lokasi tidak disertakan dalam hasil.

2. Memfilter menggunakan batas poligon (ST_CONTAINS)

Anda dapat menggunakan poligon untuk menentukan area penelusuran. Saat menggunakan poligon, titik poligon harus menentukan loop tertutup dengan titik pertama dalam poligon sama dengan titik terakhir:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`
WHERE
ST_CONTAINS(ST_GEOGFROMTEXT("""POLYGON((-73.985708 40.75773,-73.993324 40.750298,
                                      -73.9857 40.7484,-73.9785 40.7575,
                                      -73.985708 40.75773))"""), point)

3. Memfilter di sepanjang buffer perjalanan rute (LINESTRING)

Pada contoh berikutnya, Anda menentukan area penelusuran menggunakan garis titik yang terhubung dan menetapkan radius penelusuran ke 100 meter di sekitar garis. Garis ini mirip dengan rute perjalanan yang dihitung oleh Routes API. Rute ini dapat digunakan untuk kendaraan, sepeda, atau pejalan kaki:

DECLARE route GEOGRAPHY;

SET route = ST_GEOGFROMTEXT("""LINESTRING(-73.98903537033028 40.73655649223003,
                                          -73.93580216278471 40.80955538843361)""");

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`
WHERE
  ST_DWITHIN(route, point, 100)

Memfilter menurut kolom set data tempat

Persempit penelusuran Anda berdasarkan kolom yang ditentukan oleh skema set data. Filter hasil berdasarkan kolom set data seperti regular_opening_hours, price_level, dan rating pelanggan.

Referensi kolom apa pun dalam set data yang ditentukan oleh skema set data untuk negara yang Anda minati. Skema set data untuk setiap negara terdiri dari dua bagian:

• Skema inti yang umum untuk set data semua negara.
• Skema khusus negara yang menentukan komponen skema khusus untuk negara tersebut.

Misalnya, kueri Anda dapat menyertakan klausa WHERE yang menentukan kriteria pemfilteran untuk kueri.

Tips: Sertakan filter business_status untuk memastikan Anda mendapatkan jumlah bisnis OPERATIONAL. Jika tidak, jumlahnya akan mencakup bisnis yang tutup.

Dalam contoh berikut, Anda menampilkan data agregasi untuk tempat berjenis tourist_attraction dengan business_status OPERATIONAL, yang memiliki rating lebih besar dari atau sama dengan 4,0, dan dengan allows_dogs ditetapkan ke true:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`
WHERE
ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
AND 'tourist_attraction' IN UNNEST(types)
AND business_status = "OPERATIONAL"
AND rating >= 4.0
AND allows_dogs = true

Kueri berikutnya menampilkan hasil untuk tempat yang memiliki setidaknya delapan stasiun pengisian kendaraan listrik:

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`
WHERE
  ev_charge_options.connector_count > 8;

Memfilter berdasarkan jenis utama tempat dan jenis tempat

Setiap tempat dalam set data dapat memiliki:

• Satu jenis utama yang terkait dengannya dari jenis yang ditentukan oleh Jenis tempat. Misalnya, jenis utama dapat berupa mexican_restaurant atau steak_house. Gunakan primary_type dalam kueri untuk memfilter hasil berdasarkan jenis utama tempat.
• Beberapa nilai jenis yang terkait dengannya dari jenis yang ditentukan oleh Jenis tempat. Misalnya, restoran mungkin memiliki jenis berikut: seafood_restaurant, restaurant, food, point_of_interest, establishment. Gunakan types dalam kueri untuk memfilter hasil berdasarkan daftar jenis yang terkait dengan tempat tersebut.

Kueri berikut menampilkan hasil untuk semua tempat dengan jenis utama skin_care_clinic yang juga berfungsi sebagai spa:

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`
WHERE
  'spa' IN UNNEST(types)
  AND 'skin_care_clinic' = primary_type

Memfilter berdasarkan ID tempat

Contoh di bawah menghitung rating rata-rata untuk 5 tempat. Tempat diidentifikasi berdasarkan place_id.

DECLARE place_ids ARRAY<STRING>;
SET place_ids = ['ChIJPQOh8YVZwokRE2WsbZI4tOk', 'ChIJibtT3ohZwokR7tX0gp0nG8U',
                 'ChIJdfD8moVZwokRO6vxjXAtoWs', 'ChIJsdNONuFbwokRLM-yuifjb8k',
                 'ChIJp0gKoClawokR0txqrcaEkFc'];
SELECT WITH AGGREGATION_THRESHOLD
 AVG(rating) as avg_rating,
FROM
  `<var>PROJECT_NAME</var>.places_insights___us.places`,
  UNNEST(place_ids) place_id
WHERE
  id = place_id;

Mengecualikan ID Tempat tertentu dari hasil kueri

Anda juga dapat mengecualikan array ID Tempat dari kueri.

Anda dapat menemukan ID Tempat yang dicari menggunakan alat pencari ID Tempat, atau secara terprogram menggunakan Places API untuk melakukan permintaan Text Search (Baru).

Dalam contoh berikut, kueri menemukan jumlah kafe di kode pos 2000 Sydney, Australia, yang tidak muncul dalam array excluded_cafes. Kueri seperti ini mungkin berguna bagi pemilik bisnis yang ingin mengecualikan bisnisnya sendiri dari jumlah.

WITH excluded_cafes AS (
  -- List the specific place IDs to exclude from the final count
  SELECT * FROM UNNEST([
    'ChIJLTcYGz-uEmsRmazk9oMnP5w', 'ChIJeWDDDNOvEmsRF8SMPUwPbhw',
    'ChIJKdaKHbmvEmsRSdxq_1O05bU'
  ]) AS place_id
)

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `places_insights___au.places` AS places
-- Perform a LEFT JOIN to identify which places are in the exclusion list
LEFT JOIN
  excluded_cafes ON places.id = excluded_cafes.place_id
WHERE
  -- Filter for specific place type and postal code
  places.primary_type = 'cafe'
  AND '2000' IN UNNEST(places.postal_code_names)
  -- Keep only the rows where the join failed (meaning the ID was NOT in the list)
  AND excluded_cafes.place_id IS NULL;

Memfilter berdasarkan nilai data yang telah ditentukan

Banyak kolom set data memiliki nilai yang telah ditentukan. Misalnya:

• Kolom price_level mendukung nilai yang telah ditentukan berikut:
  • PRICE_LEVEL_FREE
  • PRICE_LEVEL_INEXPENSIVE
  • PRICE_LEVEL_MODERATE
  • PRICE_LEVEL_EXPENSIVE
  • PRICE_LEVEL_VERY_EXPENSIVE
• Kolom business_status mendukung nilai yang telah ditentukan berikut:
  • OPERATIONAL
  • CLOSED_TEMPORARILY
  • CLOSED_PERMANENTLY
  • FUTURE_OPENING

Catatan: Biasanya, Anda menyertakan filter business_status untuk memastikan Anda mendapatkan jumlah bisnis OPERATIONAL. Jika tidak, jumlahnya akan mencakup bisnis yang tutup.

Dalam contoh ini, kueri menampilkan jumlah semua toko bunga dengan business_status OPERATIONAL dalam radius 1.000 meter dari Empire State Building di New York City:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`
WHERE
ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
AND business_status = "OPERATIONAL"
AND 'florist' IN UNNEST(types)

Memfilter berdasarkan jam operasional

Dalam contoh ini, tampilkan jumlah semua tempat di area geografis dengan happy hour pada hari Jumat:

SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`,
UNNEST(regular_opening_hours_happy_hour.friday) AS friday_hours
WHERE '17:00:00' BETWEEN friday_hours.start_time AND friday_hours.end_time
AND ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000);

Memfilter berdasarkan Wilayah (komponen alamat)

Set data tempat kami juga berisi kumpulan komponen alamat yang berguna untuk memfilter hasil berdasarkan batas politik. Setiap komponen alamat diidentifikasi berdasarkan nama kode teksnya (10002 untuk kode pos di NYC) atau ID tempat (ChIJm5NfgIBZwokR6jLqucW0ipg) untuk ID kode pos yang setara.

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`
WHERE
  '10002' IN UNNEST(postal_code_names)
  -- 'ChIJm5NfgIBZwokR6jLqucW0ipg' IN UNNEST(postal_code_ids) -- same filter as above using postal code ID

Memfilter berdasarkan konfigurasi pengisian kendaraan listrik

Contoh ini memberikan jumlah tempat yang memiliki setidaknya 8 pengisi daya kendaraan listrik:

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`
WHERE
  ev_charge_options.connector_count > 8;

Contoh ini menghitung jumlah tempat yang memiliki setidaknya 10 pengisi daya Tesla yang mendukung pengisian daya cepat:

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`, UNNEST(ev_charge_options.connector_aggregation) as connectors
WHERE
  connectors.type ='EV_CONNECTOR_TYPE_TESLA'
  AND connectors.max_charge_rate_kw >= 50
  AND connectors.count >= 10

Menampilkan baris hasil yang dikelompokkan

Kueri yang ditampilkan sejauh ini menampilkan satu baris dalam hasil yang berisi jumlah agregasi untuk kueri. Anda juga dapat menggunakan operator GROUP BY untuk menampilkan beberapa baris dalam respons berdasarkan kriteria pengelompokan.

Catatan: Melakukan operasi GROUP BY tidak diizinkan pada postal_code_names di GB.

Misalnya, kueri berikut menampilkan hasil yang dikelompokkan berdasarkan jenis utama setiap tempat di area penelusuran:

SELECT WITH AGGREGATION_THRESHOLD
  primary_type,
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.99992071622756, 40.71818785986936), point, 1000)
GROUP BY primary_type

Gambar ini menunjukkan contoh output untuk kueri ini:

Dalam contoh ini, Anda menentukan tabel lokasi. Untuk setiap lokasi, Anda kemudian menghitung jumlah restoran terdekat, yang berarti restoran dalam jarak 1.000 meter:

WITH my_locations AS (
  SELECT 'Location 1' AS name, ST_GEOGPOINT(-74.00776440888504, 40.70932825380786) AS location
  UNION ALL
  SELECT 'Location 2' AS name, ST_GEOGPOINT(-73.98257192833559, 40.750738934863215) AS location
  UNION ALL
  SELECT 'Location 3' AS name, ST_GEOGPOINT(-73.94701794263223, 40.80792954838445)  AS location
)
SELECT WITH AGGREGATION_THRESHOLD
  l.name,
  COUNT(*) as count
FROM
  `PROJECT_NAME.places_insights___us.places` AS p
JOIN
   my_locations l
ON
  ST_DWITHIN(l.location, p.point, 1000)
WHERE
  primary_type = "restaurant"
  AND business_status = "OPERATIONAL"
GROUP BY
  l.name

Gambar ini menunjukkan contoh output untuk kueri ini: