Gelişmiş: Veri kümesini doğrudan sorgulama

Places Insights verilerine doğrudan erişmek için BigQuery'de yerler hakkında toplu analizler döndüren SQL sorguları yazarsınız. Sorguda belirtilen arama ölçütleri için veri kümesinden sonuçlar döndürülür.

Toplama eşiğini anlama

Doğrudan ham tablolar üzerinde çalıştırılan standart SQL sorguları, katı bir toplama eşiği uygular. Yalnızca hesaplanan toplama sayısı 5 veya daha yüksekse bir çıkış satırı oluşturulur.

Sorgu filtreleriniz 0, 1, 2, 3 veya 4 sonucunu verirse sonuç satırının tamamı yanıttan çıkarılır. Uygulama mantığınız, düşük sayıların (0 dahil) tam olarak bilinmesini gerektiriyorsa veya tek tek yer kimliklerini almanız gerekiyorsa veri kümesini yer sayısı işlevlerini kullanarak sorgulamanız gerekir.

Sorguyla ilgili temel bilgiler

Aşağıdaki resimde bir sorgunun temel biçimi gösterilmektedir:

Sorgunun her bir bölümü aşağıda daha ayrıntılı olarak açıklanmıştır.

İpucu: BigQuery, coğrafi veriler üzerinde sorgu çalıştırmayla ilgili dokümanlar ve örnekler de sağlar. Daha fazla bilgi için Coğrafi analizlere giriş başlıklı makaleyi inceleyin.

Sorgu şartları

Doğrudan veri kümesi üzerinde yapılan SQL sorguları, veri kümesini belirtmeli ve SELECT yan tümcesinde WITH AGGREGATION_THRESHOLD ifadesini içermelidir. Bu olmadan sorgu başarısız olur.

Bu örnekte, ABD için veri kümesini sorgulamak üzere places_insights___us.places belirtilmiştir.

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

Proje adı belirtin (isteğe bağlı)

İsterseniz sorguya proje adınızı da ekleyebilirsiniz. Proje adı belirtmezseniz sorgunuz varsayılan olarak etkin projeyi kullanır.

Farklı projelerde aynı ada sahip bağlı veri kümeleriniz varsa veya etkin projenin dışındaki bir tabloyu sorguluyorsanız proje adınızı eklemeniz gerekebilir.

Örneğin, [project name].[dataset name].places.

Toplama işlevi belirtme

Aşağıdaki örnekte, desteklenen BigQuery toplama işlevleri gösterilmektedir. Bu sorgu, puan istatistikleri oluşturmak için New York'taki Empire State Binası'nın 1.000 metre yarıçapındaki tüm yerlerin puanlarını toplar:

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"

Coğrafi poligonları ve çizgileri kullanarak sonuçları filtreleme

Konum kısıtlaması belirtmezseniz veri toplama işlemi tüm veri kümesine uygulanır. Belirli bir bölgede arama yapmak için genellikle bir konum kısıtlaması belirtirsiniz.

1. Radyal arama tamponlarını kullanarak filtreleme (ST_DWITHIN)

Bu örnek sorgu, New York'taki Empire State Binası'nı merkez alan ve 1.000 metrelik bir yarıçapa sahip hedef kısıtlamasını belirtir.

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)

Not: Bir yer, yalnızca merkez noktası konum kısıtlaması kapsamındaysa sonuçlara dahil edilir. Merkez noktası konum kısıtlamasının dışında olan park gibi yerler sonuçlara dahil edilmez.

2. Poligon sınırlarını kullanarak filtreleme (ST_CONTAINS)

Arama alanını belirtmek için poligon kullanabilirsiniz. Çokgen kullanırken çokgenin noktaları, çokgendeki ilk noktanın son noktayla aynı olduğu kapalı bir döngü tanımlamalıdır:

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. Rota boyunca seyahat tamponlarına göre filtreleme (LINESTRING)

Bir sonraki örnekte, bağlı noktalardan oluşan bir çizgi kullanarak arama alanını tanımlayacak ve arama yarıçapını çizginin etrafında 100 metre olarak ayarlayacaksınız. Çizgi, Routes API tarafından hesaplanan bir seyahat rotasına benzer. Rota; araç, bisiklet veya yaya için olabilir:

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)

Yer veri kümesi alanlarına göre filtreleme

Aramanızı veri kümesi şeması tarafından tanımlanan alanlara göre hassaslaştırın. Sonuçları yer regular_opening_hours, price_level ve müşteri rating gibi veri kümesi alanlarına göre filtreleyin.

İlgilendiğiniz ülke için veri kümesi şeması tarafından tanımlanan veri kümesindeki tüm alanlara referans verin. Her ülkenin veri kümesi şeması iki bölümden oluşur:

• Tüm ülkelerin veri kümelerinde ortak olan temel şema.
• Ülkeye özgü şema bileşenlerini tanımlayan ülkeye özgü şema.

Örneğin, sorgunuzda sorgu için filtreleme ölçütlerini tanımlayan bir WHERE ifadesi bulunabilir.

İpucu: OPERATIONAL işletmelerin sayısını aldığınızdan emin olmak için business_status filtresini ekleyin. Aksi takdirde, kapalı işletmeler de sayıya dahil edilir.

Aşağıdaki örnekte, OPERATIONAL business_status değerine sahip tourist_attraction türündeki Yerler için birleştirilmiş veri döndürülür.Bu Yerlerin rating değeri 4,0'a eşit veya daha büyük ve allows_dogs değeri true olarak ayarlanmıştır:

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

Aşağıdaki sorgu, en az sekiz elektrikli araç şarj istasyonu bulunan yerlerle ilgili sonuçları döndürür:

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

Yerin birincil türüne ve yer türüne göre filtreleme

Veri kümesindeki her yer şunları içerebilir:

• Yer türleri tarafından tanımlanan türlerden kendisiyle ilişkili tek bir birincil tür. Örneğin, birincil tür mexican_restaurant veya steak_house olabilir. Bir yerin birincil türüne göre sonuçları filtrelemek için sorguda primary_type kullanın.
• Yer türleri tarafından tanımlanan türlerden, yerle ilişkili birden çok tür değeri. Örneğin, bir restoran şu türlere sahip olabilir: seafood_restaurant, restaurant, food, point_of_interest, establishment. Sorguda types kullanarak sonuçları, yerle ilişkili türlerin listesine göre filtreleyin.

Aşağıdaki sorgu, birincil türü skin_care_clinic olan ve aynı zamanda spa olarak da işlev gören tüm yerler için sonuç döndürür:

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

Yer kimliğine göre filtreleme

Aşağıdaki örnekte 5 yerin ortalama puanı hesaplanmaktadır. Yerler, place_id ile tanımlanır.

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

Belirli yer kimliklerini sorgu sonuçlarından hariç tutma

Bir sorgudan bir dizi yer kimliğini de hariç tutabilirsiniz.

Aradığınız yer kimliklerini Yer Kimliği Bulucu'yu kullanarak veya Places API'yi kullanarak programatik olarak Metin Arama (Yeni) isteği göndererek bulabilirsiniz.

Aşağıdaki örnekte sorgu, Avustralya'nın Sidney şehrindeki 2000 posta kodunda bulunan ve excluded_cafes dizisinde görünmeyen kafelerin sayısını bulur. Bu tür bir sorgu, kendi işletmelerini bir sayımdan hariç tutmak isteyen işletme sahipleri için yararlı olabilir.

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;

Önceden tanımlanmış veri değerlerine göre filtreleme

Birçok veri kümesi alanında önceden tanımlanmış değerler bulunur. Örneğin:

• price_level alanı, önceden tanımlanmış aşağıdaki değerleri destekler:
  • PRICE_LEVEL_FREE
  • PRICE_LEVEL_INEXPENSIVE
  • PRICE_LEVEL_MODERATE
  • PRICE_LEVEL_EXPENSIVE
  • PRICE_LEVEL_VERY_EXPENSIVE
• business_status alanı, önceden tanımlanmış aşağıdaki değerleri destekler:
  • OPERATIONAL
  • CLOSED_TEMPORARILY
  • CLOSED_PERMANENTLY
  • FUTURE_OPENING

Not: OPERATIONAL işletmelerin sayısını öğrenmek için genellikle business_status filtresini eklersiniz. Aksi takdirde sayılara kapalı işletmeler de dahil edilir.

Bu örnekte sorgu, New York'taki Empire State Binası'nın 1.000 metrelik yarıçapı içinde business_status değeri OPERATIONAL olan tüm çiçekçilerin sayısını döndürür:

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)

Çalışma saatlerine göre filtreleme

Bu örnekte, bir coğrafi bölgede cuma günleri happy hour olan tüm yerlerin sayısını döndürün:

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

Bölgeye göre filtreleme (adres bileşenleri)

Yerler veri kümemiz, sonuçları siyasi sınırlara göre filtrelemek için kullanışlı olan bir dizi adres bileşeni de içerir. Her adres bileşeni, metin kod adıyla (NYC'deki posta kodu için 10002) veya eşdeğer posta kodu kimliği için yer kimliğiyle (ChIJm5NfgIBZwokR6jLqucW0ipg) tanımlanır.

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

Elektrikli araç şarj yapılandırmalarına göre filtreleme

Bu örnekte, en az 8 elektrikli araç şarj ünitesi bulunan yerlerin sayısı verilmiştir:

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

Bu örnekte, hızlı şarjı destekleyen en az 10 Tesla şarj cihazının bulunduğu yerlerin sayısı sayılmaktadır:

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

Veri anlık görüntüsü tarihine göre filtreleme

Yerler veri kümesi, Ocak 2024'e kadar her ayın yer sayısı verilerini içerir. Belirli bir ayın yer verilerinin anlık görüntüsünü sorgulamak için snapshot_date alanını kullanarak filtreleyin.

Not: snapshot_date alanı YYYY-AA-01 (örneğin, "2024-01-01") olarak biçimlendirilir. Tarih dizesinde gün değeri (01) zorunlu olsa da her anlık görüntü, tek bir gün yerine tüm ay için toplanan verileri temsil eder.

Örneğin, aşağıdaki sorgu, Ocak 2024'te arama alanındaki her yer için birincil türe göre gruplandırılmış sonuçlar döndürür:

SELECT WITH AGGREGATION_THRESHOLD
  primary_type,
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places_historical`
WHERE
  snapshot_date = '2024-01-01'
  AND ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
GROUP BY
  primary_type;

Gruplandırılmış sonuç satırlarını döndürme

Şimdiye kadar gösterilen sorgular, sonuçta sorgunun toplama sayısını içeren tek bir satır döndürür. Gruplandırma ölçütüne göre yanıtta birden fazla satır döndürmek için GROUP BY operatörünü de kullanabilirsiniz.

Not: Birleşik Krallık'ta postal_code_names için GROUP BY işlemi yapılmasına izin verilmez.

Örneğin, aşağıdaki sorgu, arama alanındaki her yerin birincil türüne göre gruplandırılmış sonuçlar döndürür:

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

Bu resimde, bu sorgunun örnek bir çıkışı gösterilmektedir:

Bu örnekte bir konum tablosu tanımlayacaksınız. Ardından her konum için yakındaki restoranların (1.000 metre mesafedeki restoranlar) sayısını hesaplarsınız:

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

Bu resimde, bu sorgunun örnek bir çıkışı gösterilmektedir: