Google sử dụng công nghệ AI để dịch nội dung sang ngôn ngữ bạn ưu tiên. Bản dịch bằng AI có thể có lỗi.

Nâng cao: Truy vấn trực tiếp tập dữ liệu

Để truy cập trực tiếp vào dữ liệu Thông tin chi tiết về địa điểm, bạn viết các truy vấn SQL trong BigQuery để trả về thông tin chi tiết tổng hợp về địa điểm. Kết quả được trả về từ tập dữ liệu cho tiêu chí tìm kiếm được chỉ định trong truy vấn.

Tìm hiểu về ngưỡng tổng hợp

Các truy vấn SQL chuẩn chạy trực tiếp trên bảng thô sẽ thực thi một ngưỡng tổng hợp nghiêm ngặt. Hàng đầu ra chỉ được tạo nếu số lượng tổng hợp được tính là 5 trở lên.

Nếu bộ lọc cụm từ tìm kiếm của bạn cho ra số lượng là 0, 1, 2, 3 hoặc 4, thì toàn bộ hàng kết quả sẽ bị bỏ qua trong phản hồi. Nếu logic ứng dụng của bạn yêu cầu số lượng thấp chính xác (bao gồm cả 0) hoặc cần truy xuất từng Mã địa điểm, thì bạn phải truy vấn tập dữ liệu bằng Hàm đếm địa điểm.

Thông tin cơ bản về truy vấn

Hình ảnh sau đây cho thấy định dạng cơ bản của một truy vấn:

Định dạng cơ bản của một truy vấn.

Mỗi phần của truy vấn được mô tả chi tiết hơn ở bên dưới.

Yêu cầu về cụm từ tìm kiếm

Các truy vấn SQL được thực hiện trực tiếp trên tập dữ liệu phải chỉ định tập dữ liệu và bao gồm WITH AGGREGATION_THRESHOLD trong mệnh đề SELECT. Nếu không có, truy vấn sẽ không thành công.

Ví dụ này chỉ định places_insights___us.places để truy vấn tập dữ liệu cho Hoa Kỳ.

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

Chỉ định tên dự án (không bắt buộc)

Bạn có thể tuỳ ý thêm tên dự án vào truy vấn. Nếu bạn không chỉ định tên dự án, thì truy vấn của bạn sẽ mặc định là dự án đang hoạt động.

Bạn nên thêm tên dự án nếu đã liên kết các tập dữ liệu có cùng tên trong các dự án khác nhau hoặc nếu bạn đang truy vấn một bảng bên ngoài dự án đang hoạt động.

Ví dụ: [project name].[dataset name].places.

Chỉ định hàm tổng hợp

Ví dụ bên dưới cho thấy hàm tổng hợp được hỗ trợ trong BigQuery. Truy vấn này tổng hợp điểm xếp hạng của tất cả địa điểm nằm trong bán kính 1.000 mét của Toà nhà Empire State ở Thành phố New York để tạo số liệu thống kê về điểm xếp hạng:

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"

Lọc kết quả bằng đa giác và đường địa lý

Nếu bạn không chỉ định quy định hạn chế về vị trí, thì việc tổng hợp dữ liệu sẽ được áp dụng cho toàn bộ tập dữ liệu. Bạn thường chỉ định một quy định hạn chế về vị trí để tìm kiếm một khu vực cụ thể.

1. Lọc bằng vùng đệm tìm kiếm theo bán kính (ST_DWITHIN)

Truy vấn mẫu này chỉ định một quy định hạn chế về mục tiêu tập trung vào Toà nhà Empire State ở Thành phố New York, với bán kính 1.000 mét.

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. Lọc bằng ranh giới đa giác (ST_CONTAINS)

Bạn có thể sử dụng đa giác để chỉ định khu vực tìm kiếm. Khi sử dụng đa giác, các điểm của đa giác phải xác định một vòng lặp khép kín, trong đó điểm đầu tiên trong đa giác giống với điểm cuối cùng:

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. Lọc dọc theo vùng đệm di chuyển của tuyến đường (LINESTRING)

Trong ví dụ tiếp theo, bạn xác định khu vực tìm kiếm bằng một đường thẳng gồm các điểm được kết nối và đặt bán kính tìm kiếm là 100 mét xung quanh đường thẳng đó. Đường này tương tự như tuyến đường di chuyển được tính toán bằng Routes API. Tuyến đường có thể dành cho xe, xe đạp hoặc người đi bộ:

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)

Lọc theo các trường của tập dữ liệu địa điểm

Tinh chỉnh nội dung tìm kiếm dựa trên các trường do giản đồ tập dữ liệu xác định. Lọc kết quả dựa trên các trường của tập dữ liệu, chẳng hạn như địa điểm regular_opening_hours, price_level và khách hàng rating.

Tham chiếu mọi trường trong tập dữ liệu được xác định theo giản đồ tập dữ liệu cho quốc gia mà bạn quan tâm. Giản đồ tập dữ liệu cho mỗi quốc gia bao gồm hai phần:

Giản đồ cốt lõi chung cho các tập dữ liệu của tất cả quốc gia.
Giản đồ theo quốc gia cụ thể xác định các thành phần giản đồ dành riêng cho quốc gia đó.

Ví dụ: truy vấn của bạn có thể bao gồm mệnh đề WHERE xác định tiêu chí lọc cho truy vấn.

Trong ví dụ sau, bạn trả về dữ liệu tổng hợp cho các địa điểm thuộc loại tourist_attraction có business_status là OPERATIONAL, có rating lớn hơn hoặc bằng 4, 0 và có allows_dogs được đặt thành 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

Truy vấn tiếp theo sẽ trả về kết quả cho những địa điểm có ít nhất 8 trạm sạc xe điện:

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

Lọc theo loại địa điểm chính và loại địa điểm

Mỗi địa điểm trong tập dữ liệu có thể có:

Một loại chính liên kết với địa điểm đó trong số các loại được xác định theo Loại địa điểm. Ví dụ: loại chính có thể là mexican_restaurant hoặc steak_house. Sử dụng primary_type trong một truy vấn để lọc kết quả theo loại chính của địa điểm.
Nhiều giá trị loại liên kết với địa điểm đó trong số các loại được xác định theo Loại địa điểm. Ví dụ: một nhà hàng có thể có các loại sau: seafood_restaurant, restaurant, food, point_of_interest, establishment. Sử dụng types trong một truy vấn để lọc kết quả trong danh sách các loại liên kết với địa điểm.

Truy vấn sau đây trả về kết quả cho tất cả địa điểm có loại chính là skin_care_clinic và cũng hoạt động như một 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

Lọc theo mã địa điểm

Ví dụ dưới đây tính điểm xếp hạng trung bình cho 5 địa điểm. Các địa điểm được xác định bằng 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;

Loại trừ một số Mã địa điểm cụ thể khỏi kết quả truy vấn

Bạn cũng có thể loại trừ một mảng Mã địa điểm khỏi một truy vấn.

Bạn có thể tìm thấy Mã địa điểm mà mình đang tìm bằng cách sử dụng Trình tìm mã địa điểm hoặc sử dụng Places API để thực hiện yêu cầu Tìm kiếm bằng văn bản (Mới) theo phương thức lập trình.

Trong ví dụ sau, truy vấn tìm số lượng quán cà phê có mã bưu chính 2000 ở Sydney, Úc, không xuất hiện trong mảng excluded_cafes. Một truy vấn như vậy có thể hữu ích cho chủ doanh nghiệp muốn loại trừ doanh nghiệp của họ khỏi số lượng.

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;

Lọc theo giá trị dữ liệu được xác định trước

Nhiều trường trong tập dữ liệu có các giá trị được xác định trước. Ví dụ:

Trường price_level hỗ trợ các giá trị được xác định trước sau đây:
- PRICE_LEVEL_FREE
- PRICE_LEVEL_INEXPENSIVE
- PRICE_LEVEL_MODERATE
- PRICE_LEVEL_EXPENSIVE
- PRICE_LEVEL_VERY_EXPENSIVE
Trường business_status hỗ trợ các giá trị được xác định trước sau đây:
- OPERATIONAL
- CLOSED_TEMPORARILY
- CLOSED_PERMANENTLY
- FUTURE_OPENING

Trong ví dụ này, truy vấn trả về số lượng tất cả các cửa hàng hoa có business_status là OPERATIONAL trong bán kính 1000 mét của Toà nhà Empire State ở Thành phố New York:

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)

Lọc theo giờ hoạt động

Trong ví dụ này, hãy trả về số lượng tất cả địa điểm trong một khu vực địa lý có giờ vàng vào thứ Sáu:

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

Lọc theo Khu vực (thành phần địa chỉ)

Tập dữ liệu địa điểm của chúng tôi cũng chứa một tập hợp các thành phần địa chỉ hữu ích cho việc lọc kết quả dựa trên ranh giới chính trị. Mỗi thành phần địa chỉ được xác định bằng tên mã văn bản (10002 cho mã bưu chính ở NYC) hoặc mã địa điểm (ChIJm5NfgIBZwokR6jLqucW0ipg) cho mã bưu chính tương đương.

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

Lọc theo cấu hình sạc xe điện

Ví dụ này cung cấp số lượng địa điểm có ít nhất 8 bộ sạc xe điện:

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

Ví dụ này đếm số lượng địa điểm có ít nhất 10 bộ sạc Tesla hỗ trợ sạc nhanh:

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

Lọc theo ngày tổng quan nhanh về dữ liệu

Tập dữ liệu về địa điểm chứa dữ liệu về số lượng địa điểm của từng tháng kể từ tháng 1 năm 2024. Để truy vấn thông tin tổng quan nhanh về dữ liệu địa điểm của một tháng nhất định, hãy lọc bằng trường snapshot_date.

Ví dụ: truy vấn sau đây trả về kết quả được nhóm theo loại chính cho từng địa điểm trong khu vực tìm kiếm, trong tháng 1 năm 2024:

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;

Trả về các hàng kết quả được nhóm

Các truy vấn được hiển thị cho đến nay trả về một hàng duy nhất trong kết quả chứa số lượng tổng hợp cho truy vấn. Bạn cũng có thể sử dụng toán tử GROUP BY để trả về nhiều hàng trong phản hồi dựa trên tiêu chí nhóm.

Ví dụ: truy vấn sau đây trả về kết quả được nhóm theo loại chính của mỗi địa điểm trong khu vực tìm kiếm:

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

Hình ảnh này cho thấy kết quả mẫu của truy vấn này:

Kết quả truy vấn để nhóm kết quả theo loại chính.

Trong ví dụ này, bạn sẽ xác định một bảng vị trí. Đối với mỗi vị trí, bạn sẽ tính số lượng nhà hàng lân cận, tức là những nhà hàng trong phạm vi 1.000 mét:

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

Hình ảnh này cho thấy kết quả mẫu cho truy vấn này:

Kết quả truy vấn để nhóm kết quả theo vị trí.