장소 통계 쿼리 작성

Places Insights 데이터에 액세스하려면 BigQuery에서 장소에 관한 집계된 유용한 정보를 반환하는 SQL 쿼리를 작성하세요. 결과는 쿼리에 지정된 검색 기준의 데이터 세트에서 반환됩니다.

참고: 집계 수는 5 이상인 경우에만 반환됩니다. 예를 들어 검색 조건의 결과가 1, 2, 3 또는 4인 경우 결과 집계가 결과에서 생략됩니다. 장소 개수 함수는 0을 포함한 모든 개수를 반환할 수 있지만 최소 검색 영역은 40.0m x 40.0m (1,600m²)로 적용됩니다.

성공: BigQuery는 지리 공간 데이터에 대한 쿼리 실행을 위한 문서와 예시도 제공합니다. 자세한 내용은 지리정보 분석 소개를 참고하세요.

쿼리 요구사항

쿼리의 SELECT 문에는 WITH AGGREGATION_THRESHOLD이 포함되어야 하고 데이터 세트가 지정되어야 합니다. 예를 들면 다음과 같습니다.

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

이 예에서는 FROM를 사용하여 미국의 places_insights___us___sample.places_sample 데이터 세트를 지정합니다.

위치 제한 지정

이전 쿼리에서는 위치 제한을 지정하지 않았으므로 데이터 집계가 전체 데이터 세트에 적용됩니다. 일반적으로 다음과 같이 위치 제한을 지정합니다.

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

이 예에서 쿼리는 뉴욕의 엠파이어 스테이트 빌딩을 중심으로 하고 반지름이 1,000미터인 타겟 제한을 지정합니다.

참고: 장소의 중심점이 위치 제한 내에 있는 경우에만 장소가 결과에 포함됩니다. 중심점이 위치 제한 밖에 있는 장소(예: 공원)는 결과에 포함되지 않습니다.

다각형을 사용하여 검색 영역을 지정할 수 있습니다. 다각형을 사용하는 경우 다각형의 점은 다각형의 첫 번째 점이 마지막 점과 동일한 닫힌 루프를 정의해야 합니다.

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `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)

다음 예에서는 연결된 점의 선을 사용하여 검색 영역을 정의합니다. 이 선은 Routes API에서 계산한 이동 경로와 유사합니다. 경로는 차량, 자전거 또는 보행자용일 수 있습니다.

DECLARE route GEOGRAPHY;

SET route = ST_GEOGFROMTEXT("""LINESTRING(-73.98903537033028 40.73655649223003,
                                          -73.93580216278471 40.80955538843361)""");

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM `places_insights___us___sample.places_sample`
WHERE
  ST_DWITHIN(route, point, 100)

이 예에서는 선을 중심으로 검색 반경을 100미터로 설정합니다.

장소 데이터 세트 필드로 필터링

데이터 세트 스키마에 정의된 필드를 기반으로 검색을 구체화합니다. 장소 regular_opening_hours, price_level, 고객 rating과 같은 데이터 세트 필드를 기반으로 결과를 필터링합니다.

관심 국가의 데이터 세트 스키마로 정의된 데이터 세트의 필드를 참조합니다. 각 국가의 데이터 세트 스키마는 다음 두 부분으로 구성됩니다.

• 모든 국가의 데이터 세트에 공통적인 핵심 스키마입니다.
• 해당 국가에 특정한 스키마 구성요소를 정의하는 국가별 스키마

예를 들어 쿼리에 쿼리의 필터링 기준을 정의하는 WHERE 절을 포함할 수 있습니다. 다음 예에서는 OPERATIONAL의 business_status이 있고 rating이 4.0 이상이며 allows_dogs이 true로 설정된 restaurant 유형의 장소에 대한 집계 데이터를 반환합니다.

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `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

다음 쿼리는 전기 자동차 충전소가 8개 이상 있는 장소의 결과를 반환합니다.

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

장소 기본 유형 및 장소 유형을 기준으로 필터링

데이터 세트의 각 장소에는 다음이 있을 수 있습니다.

• 표 A에 정의된 유형에서 연결된 단일 기본 유형 예를 들어 기본 유형은 mexican_restaurant 또는 steak_house일 수 있습니다. 쿼리에서 primary_type를 사용하여 장소의 기본 유형에 따라 결과를 필터링합니다.

• 표 A에 정의된 유형에서 연결된 여러 유형 값 예를 들어 레스토랑의 유형은 seafood_restaurant, restaurant, food, point_of_interest, establishment일 수 있습니다. 쿼리에서 types를 사용하여 장소와 연결된 유형 목록의 결과를 필터링합니다.

다음 쿼리는 기본 유형이 bar이지만 restaurant로도 기능하는 모든 장소의 결과를 반환합니다.

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

사전 정의된 데이터 값 필터링

많은 데이터 세트 필드에는 사전 정의된 값이 있습니다. 예를 들면 다음과 같습니다.

• price_level 필드는 다음 사전 정의된 값을 지원합니다.

  • PRICE_LEVEL_FREE
  • PRICE_LEVEL_INEXPENSIVE
  • PRICE_LEVEL_MODERATE
  • PRICE_LEVEL_EXPENSIVE
  • PRICE_LEVEL_VERY_EXPENSIVE

• business_status 필드는 다음 사전 정의된 값을 지원합니다.

  • OPERATIONAL
  • CLOSED_TEMPORARILY
  • CLOSED_PERMANENTLY

참고: 일반적으로 OPERATIONAL 비즈니스 수를 확인하기 위해 business_status 필터를 포함합니다. 그렇지 않으면 폐업한 비즈니스가 포함됩니다.

이 예에서 쿼리는 뉴욕시의 엠파이어 스테이트 빌딩에서 반경 1, 000미터 이내에 있고 business_status이 OPERATIONAL인 모든 레스토랑의 수를 반환합니다.

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `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)

영업시간별로 필터링

이 예에서는 금요일에 해피아워가 있는 지리적 영역의 모든 장소 수를 반환합니다.

SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count
FROM `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);

집계 함수 사용

아래 예에서는 지원되는 BigQuery 집계 함수를 보여줍니다. 이 쿼리는 뉴욕시의 엠파이어 스테이트 빌딩에서 반경 1,000m 이내에 있는 모든 장소의 평점을 집계하여 평점 통계를 생성합니다.

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

결과 그룹 반환

지금까지 표시된 쿼리는 쿼리의 집계 수를 포함하는 결과를 단일 행으로 반환합니다. GROUP BY 연산자를 사용하여 그룹화 기준에 따라 응답에서 여러 행을 반환할 수도 있습니다.

참고: Places Insights 데이터 세트의 주소 구성요소 필드 (예: administrative_area_level, locality, postal_code)에서는 GROUP BY 작업을 실행할 수 없습니다.

예를 들어 다음 쿼리는 검색 영역에 있는 각 장소의 기본 유형별로 그룹화된 결과를 반환합니다.

SELECT WITH AGGREGATION_THRESHOLD
  primary_type,
  COUNT(*) AS count
FROM
  `places_insights___us___sample.places_sample`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.99992071622756, 40.71818785986936), point, 1000)
GROUP BY primary_type

다음 이미지는 이 쿼리의 출력 예시를 보여줍니다.

이 예에서는 위치 테이블을 정의합니다. 그런 다음 각 위치에 대해 근처에 있는 음식점(1, 000미터 이내)의 수를 계산합니다.

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
  `places_insights___us___sample.places_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

다음 이미지는 이 쿼리의 출력 예시를 보여줍니다.