Google ใช้เทคโนโลยี AI เพื่อแปลเนื้อหาเป็นภาษาที่คุณต้องการ การแปลโดย AI อาจมีข้อผิดพลาด

ขั้นสูง: ค้นหาชุดข้อมูลโดยตรง

หากต้องการเข้าถึงข้อมูล Places Insights โดยตรง ให้ เขียนการค้นหาด้วย SQLใน BigQuery ซึ่งจะแสดงข้อมูลเชิงลึกแบบรวมเกี่ยวกับสถานที่ ระบบจะแสดงผลลัพธ์จากชุดข้อมูลสำหรับเกณฑ์การค้นหาที่ระบุในการค้นหา

ทำความเข้าใจเกณฑ์ขั้นต่ำของการรวมข้อมูล

การค้นหาด้วย SQL มาตรฐาน ที่เรียกใช้ในตารางข้อมูลดิบโดยตรงจะบังคับใช้เกณฑ์ขั้นต่ำของการรวมข้อมูลที่เข้มงวด ระบบจะสร้างแถวเอาต์พุตก็ต่อเมื่อจำนวนการรวมข้อมูลที่คำนวณได้มีค่าตั้งแต่ 5 ขึ้นไป

หากตัวกรองการค้นหาแสดงผลลัพธ์เป็น 0, 1, 2, 3 หรือ 4 ระบบจะละเว้นทั้งแถวผลลัพธ์ จากการตอบกลับ หากตรรกะของแอปพลิเคชันกำหนดให้ต้องมีจำนวนต่ำที่แม่นยำ (รวมถึง 0) หรือต้องดึงข้อมูลรหัสสถานที่ แต่ละรายการ คุณต้องค้นหาชุดข้อมูลโดยใช้ฟังก์ชันการนับสถานที่แทน

ข้อมูลเบื้องต้นเกี่ยวกับการค้นหา

รูปภาพต่อไปนี้แสดงรูปแบบพื้นฐานของการค้นหา

รูปแบบพื้นฐานของคำค้นหา

เราจะอธิบายแต่ละส่วนของการค้นหาโดยละเอียดเพิ่มเติมด้านล่าง

ข้อกำหนดในการค้นหา

การค้นหาด้วย SQL ที่ดำเนินการในชุดข้อมูลโดยตรงต้องระบุชุดข้อมูลและใส่ WITH AGGREGATION_THRESHOLD ในอนุประโยค SELECT หากไม่มีการระบุ การค้นหาจะล้มเหลว

ตัวอย่างนี้ระบุ places_insights___us.places เพื่อค้นหาชุดข้อมูลสำหรับสหรัฐอเมริกา

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

ระบุชื่อโปรเจ็กต์ (ไม่บังคับ)

คุณจะใส่ชื่อโปรเจ็กต์ในการค้นหาหรือไม่ก็ได้ หากไม่ระบุชื่อโปรเจ็กต์ การค้นหาจะใช้โปรเจ็กต์ที่ใช้งานอยู่เป็นค่าเริ่มต้น

คุณอาจต้องการใส่ชื่อโปรเจ็กต์หากลิงก์ชุดข้อมูลที่มีชื่อเดียวกันในโปรเจ็กต์ต่างๆ หรือหากกำลังค้นหาตารางนอกโปรเจ็กต์ที่ใช้งานอยู่

เช่น [project name].[dataset name].places

ระบุฟังก์ชันการรวมข้อมูล

ตัวอย่างด้านล่างแสดงฟังก์ชัน การรวมข้อมูลของ BigQuery ที่รองรับ การค้นหานี้จะรวมข้อมูลการให้คะแนนของสถานที่ทั้งหมดที่อยู่ในรัศมี 1,000 เมตรจากอาคารเอ็มไพร์สเตตในนครนิวยอร์กเพื่อสร้างสถิติการให้คะแนน

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"

กรองผลลัพธ์โดยใช้รูปหลายเหลี่ยมและเส้นทางภูมิศาสตร์

หากไม่ระบุข้อจำกัดด้านสถานที่ ระบบจะใช้การรวมข้อมูลกับชุดข้อมูลทั้งหมด โดยปกติแล้วคุณจะระบุข้อจำกัดด้านสถานที่เพื่อค้นหาพื้นที่ที่เฉพาะเจาะจง

1. กรองโดยใช้บัฟเฟอร์การค้นหาแบบรัศมี (ST_DWITHIN)

การค้นหาตัวอย่างนี้ระบุข้อจำกัดเป้าหมายที่อยู่ตรงกลางอาคารเอ็มไพร์สเตตในนครนิวยอร์ก โดยมีรัศมี 1, 000 เมตร

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. กรองโดยใช้ขอบเขตของรูปหลายเหลี่ยม (ST_CONTAINS)

คุณสามารถใช้รูปหลายเหลี่ยมเพื่อระบุพื้นที่ค้นหาได้ เมื่อใช้รูปหลายเหลี่ยม จุดของรูปหลายเหลี่ยมต้องกำหนดลูปปิด โดยจุดแรกในรูปหลายเหลี่ยมต้องเป็นจุดเดียวกับจุดสุดท้าย

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. กรองตามบัฟเฟอร์การเดินทางตามเส้นทาง (LINESTRING)

ในตัวอย่างถัดไป คุณจะกำหนดพื้นที่ค้นหาโดยใช้เส้นที่เชื่อมต่อกันเป็นจุดๆ และตั้งรัศมีค้นหาเป็น 100 เมตรรอบเส้น เส้นนี้คล้ายกับ เส้นทางการเดินทางที่คำนวณโดย 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 `PROJECT_NAME.places_insights___us.places`
WHERE
  ST_DWITHIN(route, point, 100)

กรองตามช่องชุดข้อมูลสถานที่

ปรับแต่งการค้นหาตามช่องที่กำหนดโดยสคีมาของ ชุดข้อมูล กรองผลลัพธ์ตามช่องชุดข้อมูล เช่น regular_opening_hours, price_level และ rating ของลูกค้า

อ้างอิงช่องใดก็ได้ในชุดข้อมูลที่กำหนดโดยสคีมาของชุดข้อมูลสำหรับประเทศที่คุณสนใจ สคีมาของชุดข้อมูลสำหรับแต่ละประเทศประกอบด้วย 2 ส่วน ได้แก่

สคีมาหลักที่ชุดข้อมูลของทุกประเทศมีเหมือนกัน
สคีมาเฉพาะประเทศ ซึ่งกำหนดคอมโพเนนต์สคีมาที่เฉพาะเจาะจงสำหรับประเทศนั้น

ตัวอย่างเช่น การค้นหาอาจมีอนุประโยค WHERE ที่กำหนดเกณฑ์การกรองสำหรับการค้นหา

ในตัวอย่างต่อไปนี้ คุณจะแสดงข้อมูลการรวมข้อมูลสำหรับสถานที่ประเภท tourist_attraction ที่มี business_status เป็น OPERATIONAL ซึ่งมี rating มากกว่าหรือเท่ากับ 4.0 และตั้งค่า allows_dogs เป็น 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

การค้นหาถัดไปจะแสดงผลลัพธ์สำหรับสถานที่ที่มีสถานีชาร์จ EV อย่างน้อย 8 แห่ง

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

กรองตามประเภทหลักและประเภทของสถานที่

สถานที่แต่ละแห่งในชุดข้อมูลอาจมีข้อมูลต่อไปนี้

ประเภทหลักประเภทเดียว ที่เชื่อมโยงกับสถานที่นั้นจากประเภทที่กำหนดโดย ประเภทสถานที่ เช่น ประเภทหลักอาจเป็น mexican_restaurant หรือ steak_house ใช้ primary_type ในการค้นหาเพื่อกรองผลลัพธ์ตามประเภทหลักของสถานที่
ค่าประเภทหลายค่า ที่เชื่อมโยงกับสถานที่นั้นจากประเภทที่กำหนดโดย ประเภท สถานที่ เช่น ร้านอาหารอาจมีประเภทต่อไปนี้ seafood_restaurant, restaurant, food, point_of_interest, establishment ใช้ types ในการค้นหาเพื่อกรองผลลัพธ์ตามรายการประเภทที่เชื่อมโยงกับสถานที่

การค้นหาต่อไปนี้จะแสดงผลลัพธ์สำหรับสถานที่ทั้งหมดที่มีประเภทหลักเป็น skin_care_clinic ซึ่งทำหน้าที่เป็น 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

กรองตามรหัสสถานที่

ตัวอย่างด้านล่างจะคำนวณคะแนนเฉลี่ยของสถานที่ 5 แห่ง โดยระบุสถานที่ด้วย 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;

ยกเว้นรหัสสถานที่ที่เฉพาะเจาะจงจากผลการค้นหา

นอกจากนี้ คุณยังยกเว้นอาร์เรย์ของ รหัส สถานที่จากการค้นหาได้ด้วย

คุณสามารถค้นหารหัสสถานที่ที่ต้องการได้โดยใช้เครื่องมือค้นหา รหัสสถานที่ หรือ โดยใช้ Places API เพื่อส่งคำขอการค้นหาข้อความ (ใหม่) แบบเป็นโปรแกรม

ในตัวอย่างต่อไปนี้ การค้นหาจะนับจำนวนคาเฟ่ในรหัสไปรษณีย์ 2000 ของซิดนีย์ ออสเตรเลีย ซึ่ง ไม่ ปรากฏในอาร์เรย์ excluded_cafes การค้นหาดังกล่าวอาจมีประโยชน์สำหรับเจ้าของธุรกิจที่ต้องการยกเว้นธุรกิจของตนเองจากการนับ

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;

กรองตามค่าข้อมูลที่กำหนดไว้ล่วงหน้า

ช่องชุดข้อมูลหลายช่องมีค่าที่กำหนดไว้ล่วงหน้า เช่น

ช่อง 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
- FUTURE_OPENING

ในตัวอย่างนี้ การค้นหาจะแสดงจำนวนร้านดอกไม้ทั้งหมดที่มี business_status เป็น OPERATIONAL ซึ่งอยู่ในรัศมี 1, 000 เมตรจากอาคารเอ็มไพร์สเตตในนครนิวยอร์ก

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)

กรองตามเวลาทำการ

ในตัวอย่างนี้ ให้แสดงจำนวนสถานที่ทั้งหมดในพื้นที่ทางภูมิศาสตร์ที่มีช่วงเวลาพิเศษในวันศุกร์

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

กรองตามภูมิภาค (คอมโพเนนต์ที่อยู่)

ชุดข้อมูลสถานที่ของเรายังมีชุดคอมโพเนนต์ที่อยู่ซึ่งมีประโยชน์สำหรับการกรองผลลัพธ์ตามขอบเขตทางการเมือง คอมโพเนนต์ที่อยู่แต่ละรายการจะระบุด้วยชื่อรหัสข้อความ (10002 สำหรับรหัสไปรษณีย์ในนิวยอร์ก) หรือรหัสสถานที่ (ChIJm5NfgIBZwokR6jLqucW0ipg) สำหรับรหัสไปรษณีย์ที่เทียบเท่า

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

กรองตามการกำหนดค่าการชาร์จ EV

ตัวอย่างนี้แสดงจำนวนสถานที่ที่มีเครื่องชาร์จ EV อย่างน้อย 8 เครื่อง

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

ตัวอย่างนี้จะนับจำนวนสถานที่ที่มีเครื่องชาร์จ Tesla อย่างน้อย 10 เครื่องที่รองรับการชาร์จเร็ว

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

กรองตามวันที่สแนปชอตข้อมูล

ชุดข้อมูลสถานที่ประกอบด้วยข้อมูลจำนวนสถานที่สำหรับแต่ละเดือนย้อนกลับไปจนถึงเดือนมกราคม 2024 หากต้องการค้นหาสแนปชอตข้อมูลสถานที่สำหรับเดือนที่ต้องการ ให้กรองโดยใช้ช่อง snapshot_date

ตัวอย่างเช่น การค้นหาต่อไปนี้จะแสดงผลลัพธ์ที่จัดกลุ่มตามประเภทหลักของสถานที่แต่ละแห่งในพื้นที่ค้นหาในเดือนมกราคม 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;

แสดงแถวผลลัพธ์ที่จัดกลุ่ม

การค้นหาที่แสดงไปแล้วจะแสดงแถวเดียวในผลลัพธ์ซึ่งมีจำนวนการรวมข้อมูลสำหรับการค้นหา นอกจากนี้ คุณยังใช้โอเปอเรเตอร์ GROUP BY เพื่อแสดงผลลัพธ์หลายแถวในการตอบกลับตามเกณฑ์การจัดกลุ่มได้ด้วย

ตัวอย่างเช่น การค้นหาต่อไปนี้จะแสดงผลลัพธ์ที่จัดกลุ่มตามประเภทหลักของสถานที่แต่ละแห่งในพื้นที่ค้นหา

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

รูปภาพนี้แสดงตัวอย่างเอาต์พุตของการค้นหานี้

ผลการค้นหาของคำค้นหาสำหรับการจัดกลุ่มผลลัพธ์ตามประเภทหลัก

ในตัวอย่างนี้ คุณจะกำหนดตารางสถานที่ จากนั้นคำนวณจำนวนร้านอาหารที่อยู่ใกล้เคียงสำหรับแต่ละสถานที่ ซึ่งหมายถึงร้านอาหารที่อยู่ในรัศมี 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
  `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

รูปภาพนี้แสดงตัวอย่างเอาต์พุตของการค้นหานี้

ผลการค้นหาสำหรับการจัดกลุ่มผลลัพธ์ตามสถานที่ตั้ง