تستخدم Google تكنولوجيا الذكاء الاصطناعي لترجمة المحتوى إلى لغتك المفضّلة، وقد تتضمّن بعض الأخطاء.

مستوى متقدّم: الاستعلام عن مجموعة البيانات مباشرةً

للوصول إلى بيانات إحصاءات الأماكن مباشرةً، عليك كتابة طلبات بحث 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

يمكنك الرجوع إلى أي حقول في مجموعة البيانات المحدّدة بواسطة مخطط مجموعة البيانات الخاصة بالبلد الذي يهمّك. يتألف مخطط مجموعة البيانات لكل بلد من جزأين:

المخطط الأساسي المشترك بين مجموعات البيانات في جميع البلدان
مخطط خاص ببلد معيّن يحدّد عناصر المخطط الخاصة بهذا البلد.

على سبيل المثال، يمكن أن يتضمّن طلب البحث بندًا برمجيًا 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

يعرض طلب البحث التالي نتائج عن الأماكن التي تتضمّن ثماني محطات شحن على الأقل للمركبات الكهربائية:

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

الفلترة حسب إعدادات شحن المركبات الكهربائية

يقدّم هذا المثال عدد الأماكن التي تتضمّن 8 شواحن على الأقل للمركبات الكهربائية:

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

يحتسب هذا المثال عدد الأماكن التي تتضمّن 10 شواحن Tesla على الأقل متوافقة مع الشحن السريع:

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

تعرض هذه الصورة مثالاً على الناتج الذي يظهر عند إدخال طلب البحث هذا:

نتائج طلب البحث لتجميع النتائج حسب النوع الأساسي

في هذا المثال، يمكنك تحديد جدول للمواقع الجغرافية. لكل موقع جغرافي، عليك بعد ذلك احتساب عدد المطاعم القريبة، أي تلك التي تقع ضمن مسافة 1000 متر:

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