Google używa technologii AI do tłumaczenia treści na Twój preferowany język. Tłumaczenia wygenerowane przez AI mogą zawierać błędy.

Zaawansowane: bezpośrednie wysyłanie zapytań do zbioru danych

Aby uzyskać bezpośredni dostęp do danych Statystyk miejsc, napisz w BigQuery zapytania SQL, które zwracają zagregowane statystyki dotyczące miejsc. Wyniki są zwracane ze zbioru danych na podstawie kryteriów wyszukiwania określonych w zapytaniu.

Informacje o progu agregacji

Zapytania w standardowej wersji SQL wykonywane bezpośrednio w tabelach z danymi pierwotnymi wymuszają stosowanie ścisłego progu agregacji. Wiersz wyjściowy jest generowany tylko wtedy, gdy obliczona liczba agregacji wynosi co najmniej 5.

Jeśli filtry zapytania zwracają liczbę 0, 1, 2, 3 lub 4, cały wiersz wyników jest pomijany w odpowiedzi. Jeśli logika aplikacji wymaga dokładnych małych liczb (w tym 0) lub pobierania poszczególnych identyfikatorów miejsc, musisz wysłać zapytanie do zbioru danych za pomocą funkcji zliczających miejsca.

Podstawowe informacje na temat zapytań

Poniższy obraz przedstawia podstawowy format zapytania:

Podstawowy format zapytania.

Każda część zapytania jest opisana bardziej szczegółowo poniżej.

Wymagania dotyczące zapytań

Zapytania SQL wysyłane bezpośrednio do zbioru danych muszą określać zbiór danych i zawierać w klauzuli SELECT klauzulę WITH AGGREGATION_THRESHOLD. W przeciwnym razie zapytanie się nie powiedzie.

Ten przykład określa places_insights___us.places, aby wysłać zapytanie do zbioru danych dotyczącego Stanów Zjednoczonych.

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

Określanie nazwy projektu (opcjonalne)

W zapytaniu możesz opcjonalnie uwzględnić nazwę projektu. Jeśli nie określisz nazwy projektu, zapytanie domyślnie będzie dotyczyć aktywnego projektu.

Nazwę projektu warto uwzględnić, jeśli masz połączone zbiory danych o tej samej nazwie w różnych projektach lub jeśli wysyłasz zapytanie do tabeli spoza aktywnego projektu.

Na przykład [project name].[dataset name].places.

Określanie funkcji agregującej

Poniższy przykład przedstawia obsługiwane funkcje agregujące BigQuery. To zapytanie agreguje oceny wszystkich miejsc znajdujących się w promieniu 1000 metrów od Empire State Building w Nowym Jorku, aby uzyskać statystyki ocen:

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"

Filtrowanie wyników za pomocą wielokątów i linii geograficznych

Jeśli nie określisz ograniczenia lokalizacji, agregacja danych zostanie zastosowana do całego zbioru danych. Zazwyczaj określasz ograniczenie lokalizacji, aby przeszukać określony obszar.

1. Filtrowanie za pomocą buforów wyszukiwania promieniowego (ST_DWITHIN)

Ten przykładowy kod zapytania określa ograniczenie docelowe wyśrodkowane na Empire State Building w Nowym Jorku o promieniu 1000 metrów.

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)

2. Filtrowanie za pomocą granic wielokąta (ST_CONTAINS)

Do określenia obszaru wyszukiwania możesz użyć wielokąta. W przypadku wielokąta jego punkty muszą tworzyć zamkniętą pętlę, w której pierwszy punkt jest taki sam jak ostatni:

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. Filtrowanie wzdłuż buforów podróży (LINESTRING)

W następnym przykładzie obszar wyszukiwania jest definiowany za pomocą linii połączonych punktów, a promień wyszukiwania jest ustawiony na 100 metrów wokół linii. Linia jest podobna do trasy podróży obliczonej przez interfejs Routes API. Trasa może być przeznaczona dla pojazdu, roweru lub pieszego:

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)

Filtrowanie według pól zbioru danych o miejscach

Zawęź wyszukiwanie na podstawie pól zdefiniowanych przez schemat zbioru danych. Filtruj wyniki na podstawie pól zbioru danych, takich jak regular_opening_hours, price_level i rating.

Odwołuj się do dowolnych pól w zbiorze danych zdefiniowanych przez schemat zbioru danych dla interesującego Cię kraju. Schemat zbioru danych dla każdego kraju składa się z 2 części:

Schemat podstawowy, który jest wspólny dla zbiorów danych we wszystkich krajach.
Schemat specyficzny dla danego kraju, który określa komponenty schematu charakterystyczne dla tego kraju.

Na przykład zapytanie może zawierać klauzulę WHERE, która określa kryteria filtrowania zapytania.

W tym przykładzie zwracasz dane agregacji dla miejsc typu tourist_attraction ze statusem business_status ustawionym na OPERATIONAL, które mają rating większy lub równy 4,0 oraz allows_dogs ustawiony na 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

Następne zapytanie zwraca wyniki dla miejsc, które mają co najmniej 8 stacji ładowania EV:

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

Filtrowanie według podstawowego typu miejsca i typu miejsca

Każde miejsce w zbiorze danych może mieć:

Jeden podstawowy typ powiązany z typami zdefiniowanymi w sekcji Typy miejsc. Na przykład podstawowym typem może być mexican_restaurant lub steak_house. Aby filtrować wyniki według podstawowego typu miejsca, użyj w zapytaniu parametru primary_type.
Wiele wartości typu powiązanych z typami zdefiniowanymi w sekcji Typy miejsc. Na przykład restauracja może mieć te typy: seafood_restaurant, restaurant, food, point_of_interest, establishment. Aby filtrować wyniki według listy typów powiązanych z miejscem, użyj w zapytaniu parametru types.

To zapytanie zwraca wyniki dla wszystkich miejsc, których podstawowy typ to skin_care_clinic, a które działają też jako 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

Filtrowanie według identyfikatora miejsca

Poniższy przykład oblicza średnią ocenę 5 miejsc. Miejsca są identyfikowane za pomocą parametru 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
  `PROJECT_NAME.places_insights___us.places`,
  UNNEST(place_ids) place_id
WHERE
  id = place_id;

Wykluczanie określonych identyfikatorów miejsc z wyników zapytania

Możesz też wykluczyć z zapytania tablicę identyfikatorów miejsc.

Identyfikatory miejsc, których szukasz, możesz znaleźć za pomocą narzędzia do wyszukiwania identyfikatorów miejsc lub programowo za pomocą interfejsu Places API, wysyłając żądanie wyszukiwania tekstowego (nowe).

W tym przykładzie zapytanie znajduje liczbę kawiarni w kodzie pocztowym 2000 w Sydney w Australii, które nie znajdują się w tablicy excluded_cafes. Takie zapytanie może być przydatne dla właściciela firmy, który chce wykluczyć własne firmy z liczby.

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;

Filtrowanie według wstępnie zdefiniowanych wartości danych

Wiele pól zbioru danych ma wstępnie zdefiniowane wartości. Na przykład:

Pole price_level obsługuje te wstępnie zdefiniowane wartości:
- PRICE_LEVEL_FREE
- PRICE_LEVEL_INEXPENSIVE
- PRICE_LEVEL_MODERATE
- PRICE_LEVEL_EXPENSIVE
- PRICE_LEVEL_VERY_EXPENSIVE
Pole business_status obsługuje te wstępnie zdefiniowane wartości:
- OPERATIONAL
- CLOSED_TEMPORARILY
- CLOSED_PERMANENTLY
- FUTURE_OPENING

W tym przykładzie zapytanie zwraca liczbę wszystkich kwiaciarni ze statusem business_status ustawionym na OPERATIONAL w promieniu 1000 metrów od Empire State Building w Nowym Jorku:

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)

Filtrowanie według godzin otwarcia

W tym przykładzie zwracasz liczbę wszystkich miejsc w obszarze geograficznym, w których w piątki obowiązują happy hours:

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

Filtrowanie według regionu (składniki adresu)

Nasz zbiór danych o miejscach zawiera też zestaw składników adresu, które są przydatne do filtrowania wyników na podstawie granic politycznych. Każdy składnik adresu jest identyfikowany za pomocą nazwy tekstowej (10002 w przypadku kodu pocztowego w Nowym Jorku) lub identyfikatora miejsca (ChIJm5NfgIBZwokR6jLqucW0ipg) dla równoważnego identyfikatora kodu pocztowego.

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

Filtrowanie według konfiguracji ładowania EV

Ten przykład podaje liczbę miejsc z co najmniej 8 ładowarkami EV:

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

Ten przykład zlicza liczbę miejsc, które mają co najmniej 10 ładowarek Tesla obsługujących szybkie ładowanie:

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

Filtrowanie według daty migawki danych

Zbiór danych o miejscach zawiera dane o liczbie miejsc z każdego miesiąca od stycznia 2024 r. Aby wysłać zapytanie o migawkę danych o miejscach z danego miesiąca, przefiltruj dane za pomocą pola snapshot_date.

Na przykład to zapytanie zwraca wyniki pogrupowane według podstawowego typu każdego miejsca w obszarze wyszukiwania w styczniu 2024 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;

Zwracanie pogrupowanych wierszy wyników

Pokazane dotychczas zapytania zwracają w wyniku jeden wiersz, który zawiera liczbę agregacji dla zapytania. Możesz też użyć operatora GROUP BY, aby zwracać w odpowiedzi wiele wierszy na podstawie kryteriów grupowania.

Na przykład to zapytanie zwraca wyniki pogrupowane według podstawowego typu każdego miejsca w obszarze wyszukiwania:

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

Ten obraz przedstawia przykładowe dane wyjściowe tego zapytania:

Wyniki zapytania dotyczące grupowania wyników według typu podstawowego.

W tym przykładzie definiujesz tabelę lokalizacji. Następnie dla każdej lokalizacji obliczasz liczbę pobliskich restauracji, czyli tych, które znajdują się w promieniu 1000 metrów:

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