Yerler Analizleri sorgusu yazma

Places Insights verilerine erişmek için BigQuery'de SQL sorguları yazarsınız. Bu sorgular, yerlerle ilgili toplu analizler döndürür. Sonuçlar, sorguda belirtilen arama ölçütleri için veri kümesinden döndürülür.

Sorgularla ilgili temel bilgiler

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

Sorgunun temel biçimi.

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

Sorgu şartları

Sorgudaki SELECT ifadesi WITH AGGREGATION_THRESHOLD içermeli ve veri kümesini belirtmelidir. Örneğin:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us___sample.places_sample`

Bu örnekte, ABD için FROM veri kümesini belirtmek üzere PROJECT_NAME.places_insights___us___sample.places_sample kullanıyorsunuz.

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

İsteğe bağlı olarak sorguya proje adınızı ekleyebilirsiniz. Proje adı belirtmezseniz sorgunuz varsayılan olarak etkin projeye yönlendirilir.

Farklı projelerde aynı ada sahip veri kümelerini bağladıysanız veya etkin proje dışındaki bir tabloya sorgu gönderiyorsanız proje adınızı eklemek isteyebilirsiniz.

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

Toplama işlevi belirtme

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

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___sample.places_sample`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
  AND business_status = "OPERATIONAL"

Konum kısıtlaması belirtme

Konum kısıtlaması belirtmezseniz veri toplama işlemi tüm veri kümesine uygulanır. Genellikle belirli bir alanı aramak için aşağıdaki örnekte gösterildiği gibi bir konum kısıtlaması belirtirsiniz:

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

Bu örnekte sorgunuz, New York'taki Empire State Binası'nı merkez alan ve 1.000 metre yarıçaplı bir hedef kısıtlama belirtiyor.

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___sample.places_sample`
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)

Bir sonraki örnekte, arama alanını bağlı noktalarla oluşturulan bir çizgi kullanarak tanımlıyorsunuz. Çizgi, Routes API tarafından hesaplanan bir seyahat rotasına benzer. Rota bir 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___sample.places_sample`
WHERE
  ST_DWITHIN(route, point, 100)

Bu örnekte, arama yarıçapını çizginin etrafında 100 metre olarak ayarlarsınız.

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:

Örneğin, sorgunuzda sorgu için filtreleme ölçütlerini tanımlayan bir WHERE koşulu olabilir. Aşağıdaki örnekte, restaurant türündeki yerler için toplama verileri döndürüyorsunuz. Bu yerlerin OPERATIONAL değeri business_status, rating değeri 4, 0'a eşit veya daha büyük ve allows_dogs değeri true olarak ayarlanmış.

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us___sample.places_sample`
WHERE
ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
AND 'restaurant' 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 olan yerlerle ilgili sonuçlar döndürür:

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

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

Veri kümesindeki her yer şunlara sahip olabilir:

  • Yer türleri tarafından tanımlanan türler arasından tek bir birincil tür ile ilişkilendirilmelidir. Örneğin, birincil tür mexican_restaurant veya steak_house olabilir. Bir sorguda primary_type kullanarak sonuçları bir yerin birincil türüne göre filtreleyin.

  • Yer türleri tarafından tanımlanan türlerden birden fazla tür değeri ile ilişkilendirilmiş olmalıdır. Örneğin, bir restoranın şu türleri olabilir: seafood_restaurant, restaurant, food, point_of_interest, establishment. Bir sorguda types kullanarak yerle ilişkili türler listesindeki sonuçları filtreleyin.

Aşağıdaki sorgu, birincil türü bar olan ancak restaurant olarak da işlev gören tüm yerlerle ilgili sonuçlar döndürür:

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us___sample.places_sample`
WHERE
  'restaurant' IN UNNEST(types)
  AND 'bar' = primary_type

Ö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ı aşağıdaki önceden tanımlanmış değerleri destekler:

    • PRICE_LEVEL_FREE
    • PRICE_LEVEL_INEXPENSIVE
    • PRICE_LEVEL_MODERATE
    • PRICE_LEVEL_EXPENSIVE
    • PRICE_LEVEL_VERY_EXPENSIVE

  • business_status alanı aşağıdaki önceden tanımlanmış değerleri destekler:

    • OPERATIONAL
    • CLOSED_TEMPORARILY
    • CLOSED_PERMANENTLY

Bu örnekte sorgu, New York City'deki Empire State Binası'nın 1.000 metre yarıçapındaki business_status değeri OPERATIONAL olan tüm restoranların sayısını döndürür:

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

Çalışma saatlerine göre filtreleme

Bu örnekte, bir coğrafi bölgedeki 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___sample.places_sample`,
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);

Sonuç gruplarını döndürme

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

Ö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___sample.places_sample`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.99992071622756, 40.71818785986936), point, 1000)
GROUP BY primary_type

Aşağıdaki resimde bu sorguya ilişkin örnek bir çıkış gösterilmektedir:

Sonuçları birincil türe göre gruplandırmak için sorgu sonuçları.

Bu örnekte, konumların bulunduğu bir tablo tanımlıyorsunuz. Ardından, her konum için yakındaki restoranların sayısını (1.000 metre içindeki restoranlar) 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___sample_sample` 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

Aşağıdaki resimde bu sorguya ilişkin örnek bir çıkış gösterilmektedir:

Sonuçları konuma göre gruplandırmak için sorgu sonuçları.