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

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 dla kryteriów wyszukiwania określonych w zapytaniu.

Informacje o progu agregacji

Standardowe zapytania SQL uruchamiane bezpośrednio na nieprzetworzonych tabelach wymuszają rygorystyczny próg agregacji. Wiersz wyjściowy jest generowany tylko wtedy, gdy obliczona liczba agregacji wynosi co najmniej 5.

Jeśli filtry zapytania dają wynik 0, 1, 2, 3 lub 4, cały wiersz wyników jest pomijany w odpowiedzi. Jeśli logika aplikacji wymaga dokładnych niskich wartości (w tym 0) lub musi pobierać poszczególne identyfikatory miejsc, musisz wysłać zapytanie do zbioru danych za pomocą 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.

Wskazówka: BigQuery udostępnia też dokumentację i przykłady dotyczące wykonywania zapytań na danych geoprzestrzennych. Więcej informacji znajdziesz w artykule Wprowadzenie do analizy geoprzestrzennej.

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

W zapytaniu możesz opcjonalnie podać 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)

Uwaga: miejsce jest uwzględniane w wynikach tylko wtedy, gdy jego punkt środkowy mieści się w ograniczeniu lokalizacji. Miejsce, takie jak park, którego punkt centralny znajduje się poza ograniczeniem lokalizacji, nie jest uwzględniane w wynikach.

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 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 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.

Wskazówka: dodaj filtr business_status, aby mieć pewność, że otrzymasz liczbę firm OPERATIONAL. W przeciwnym razie liczba będzie obejmować zamknięte firmy.

W poniższym przykładzie zwracane są dane zbiorcze dotyczące miejsc typu tourist_attraction z business_status o wartości OPERATIONAL, które mają rating większą lub równą 4, 0, a allows_dogs jest ustawione 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_restaurant lub steak_house. Użyj primary_type w 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żyj types w 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

Filtruj według identyfikatora miejsca

W przykładzie poniżej obliczana jest średnia ocena 5 miejsc. Miejsca są identyfikowane na podstawie 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 poniższym przykładzie zapytanie znajduje liczbę kawiarni w Sydney w Australii, które mają kod pocztowy 2000 i 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_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

Uwaga: zwykle dodaje się filtr business_status, aby uzyskać liczbę firm OPERATIONAL. W przeciwnym razie liczba będzie obejmować zamknięte firmy.

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)

Filtrowanie według godzin otwarcia

W tym przykładzie zwracana jest liczba wszystkich miejsc na danym 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);

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 nazwę kodu tekstowego (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;

Ten przykład zlicza 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 w polu snapshot_date.

Uwaga: pole snapshot_date ma format RRRR-MM-01 (np. „2024-01-01”). Chociaż wartość dnia (01) jest obowiązkowa w ciągu daty, każda migawka reprezentuje dane zagregowane dla całego miesiąca, a nie dla pojedynczego dnia.

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.

Uwaga: operacja GROUP BY jest niedozwolona w przypadku postal_code_names w Wielkiej Brytanii.

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: