เขียนการค้นหาของ Places Insights

หากต้องการเข้าถึงข้อมูลข้อมูลเชิงลึกเกี่ยวกับสถานที่ คุณต้องเขียนคำค้นหา SQL ใน 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

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

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

เช่น การค้นหาอาจมีวลี WHERE ที่กำหนดเกณฑ์การกรอง สำหรับการค้นหา ในตัวอย่างต่อไปนี้ คุณจะแสดงข้อมูลการรวบรวม สำหรับสถานที่ประเภท restaurant ที่มี business_status เป็น OPERATIONAL ซึ่งมี rating มากกว่าหรือเท่ากับ 4.0 และตั้งค่า allows_dogs เป็น true

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

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

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

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

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

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

• ค่าประเภทหลายค่าที่เชื่อมโยงกับค่าดังกล่าวจากประเภทที่กำหนดโดย Table 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

ในตัวอย่างนี้ การค้นหาจะแสดงผลจำนวนร้านอาหารทั้งหมดที่มีbusiness_statusOPERATIONAL ภายในรัศมี 1, 000 เมตรจากเอ็มไพร์สเตตในนครนิวยอร์ก

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

แสดงกลุ่มผลลัพธ์

คำค้นหาที่แสดงจนถึงตอนนี้จะแสดงผลแถวเดียวในผลลัพธ์ที่มี จำนวนการรวมสำหรับคำค้นหา นอกจากนี้ คุณยังใช้ตัวดำเนินการ 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

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