Aby uzyskać bezpośredni dostęp do danych Statystyk miejsc, piszesz zapytania SQL w BigQuery, 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
Podczas wysyłania zapytań bezpośrednio do tabel obowiązuje próg agregacji wynoszący 1. Oznacza to, że wiersz wyjściowy jest generowany tylko wtedy, gdy agreguje co najmniej 1 miejsce. Jeśli filtry zapytania dają wynik 0, cały wiersz wyników jest pomijany w odpowiedzi. Aby wyświetlić 0 w przypadku pominiętych wierszy, użyj funkcji zliczania miejsc.
Podstawowe informacje na temat zapytań
Poniższy obraz przedstawia podstawowy format zapytania:
Poniżej znajdziesz szczegółowe informacje o poszczególnych częściach zapytania.
Wymagania dotyczące zapytań
Zapytania SQL wykonywane bezpośrednio na zbiorze danych muszą określać zbiór danych i zawierać WITH AGGREGATION_THRESHOLD w klauzuli SELECT. Bez tego zapytanie się nie powiedzie.
W tym przykładzie określono 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`
Podaj nazwę projektu (opcjonalnie).
Opcjonalnie możesz uwzględnić w zapytaniu nazwę projektu. Jeśli nie podasz nazwy projektu, zapytanie zostanie domyślnie wykonane w aktywnym projekcie.
Możesz uwzględnić nazwę projektu, 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 pokazuje 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 wygenerować 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. Zwykle określasz ograniczenie lokalizacji, aby wyszukiwać w określonym obszarze.
1. Filtrowanie przy użyciu buforów wyszukiwania promieniowego (ST_DWITHIN)
To przykładowe zapytanie określa ograniczenie celu skoncentrowane na Empire State Building w Nowym Jorku, z promieniem 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 przy użyciu granic wielokąta (ST_CONTAINS)
Możesz użyć wielokąta, aby określić obszar wyszukiwania. W przypadku wielokąta 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 według buforów podróży na trasie (LINESTRING)
W następnym przykładzie obszar wyszukiwania definiujesz za pomocą linii połączonych punktów i ustawiasz promień wyszukiwania na 100 metrów wokół linii. Linia jest podobna do trasy 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 miejsc
Zawęź wyszukiwanie na podstawie pól zdefiniowanych przez schemat zbioru danych. Filtruj wyniki na podstawie pól zbioru danych, takich jak miejsce regular_opening_hours, price_level i klient rating.
Możesz odwoływać się do dowolnych pól w zbiorze danych zdefiniowanych przez schemat zbioru danych dla kraju, który Cię interesuje. 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 dla konkretnego kraju, który definiuje komponenty schematu specyficzne dla danego kraju.
Na przykład zapytanie może zawierać klauzulę WHERE, która definiuje kryteria filtrowania zapytania.
W tym przykładzie zwracane są dane zbiorcze dotyczące miejsc typu tourist_attraction z parametrem business_status o wartości OPERATIONAL, które mają parametr rating o wartości co najmniej 4,0, a parametr allows_dogs jest 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 dotyczące 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 głównego typu miejsca i typu miejsca
Każde miejsce w zbiorze danych może mieć:
- pojedynczy typ podstawowy powiązany z miejscem, wybrany spośród typów zdefiniowanych w sekcji Typy miejsc; Na przykład typem głównym może być
mexican_restaurantlubsteak_house. Użyjprimary_typew zapytaniu, aby filtrować wyniki według głównego typu miejsca. - Wiele wartości typu powiązanych z nim z typów zdefiniowanych przez typy miejsc. Na przykład restauracja może mieć te typy:
seafood_restaurant,restaurant,food,point_of_interest,establishment. Użyjtypesw zapytaniu, aby filtrować wyniki na liście typów powiązanych z miejscem.
To zapytanie zwraca wyniki dla wszystkich miejsc, których głównym typem jest 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
W przykładzie poniżej obliczana jest średnia ocena 5 miejsc. Miejsca są identyfikowane za pomocą 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ą wyszukiwarki identyfikatorów miejsc lub automatycznie za pomocą interfejsu Places API, aby wykonać żądanie wyszukiwania tekstu (nowe).
W tym przykładzie zapytanie znajduje liczbę kawiarni w kodzie pocztowym 2000 w Sydney w Australii, które nie występują w tablicy excluded_cafes.
Takie zapytanie może być przydatne dla właściciela firmy, który chce wykluczyć swoje firmy z obliczeń.
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_levelobsługuje te wstępnie zdefiniowane wartości:PRICE_LEVEL_FREEPRICE_LEVEL_INEXPENSIVEPRICE_LEVEL_MODERATEPRICE_LEVEL_EXPENSIVEPRICE_LEVEL_VERY_EXPENSIVE
- Pole
business_statusobsługuje te wstępnie zdefiniowane wartości:OPERATIONALCLOSED_TEMPORARILYCLOSED_PERMANENTLYFUTURE_OPENING
W tym przykładzie zapytanie zwraca liczbę wszystkich kwiaciarni z business_status o wartości 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)
Filtruj według godzin otwarcia
W tym przykładzie zwracana jest liczba wszystkich miejsc na danym obszarze geograficznym, w których w piątek 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);
Filtruj według regionu (komponenty adresu)
Nasz zbiór danych o miejscach zawiera też zestaw komponentów adresowych, które są przydatne do filtrowania wyników na podstawie granic politycznych. Każdy komponent adresu jest identyfikowany przez tekstową nazwę kodową (10002 dla kodu pocztowego w Nowym Jorku) lub identyfikator miejsca (ChIJm5NfgIBZwokR6jLqucW0ipg) dla odpowiedniego 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;
W tym przykładzie zliczamy miejsca, 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 zrzutu danych
Zbiór danych o miejscach zawiera dane o liczbie miejsc w poszczególnych miesiącach od stycznia 2024 r. Aby wysłać zapytanie o zrzut danych o miejscach z danego miesiąca, użyj filtra snapshot_date.
Na przykład to zapytanie zwraca wyniki pogrupowane według głównego typu dla 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 zgrupowanych wierszy wyników
Pokazane do tej pory zapytania zwracają w wyniku pojedynczy wiersz, który zawiera liczbę agregacji dla zapytania. Możesz też użyć operatora GROUP BY, aby zwrócić w odpowiedzi wiele wierszy na podstawie kryteriów grupowania.
Na przykład to zapytanie zwraca wyniki pogrupowane według głównego 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:

W tym przykładzie zdefiniujesz tabelę lokalizacji. Dla każdej lokalizacji obliczasz liczbę restauracji w pobliżu, czyli 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
Ten obraz przedstawia przykładowe dane wyjściowe tego zapytania:
