تستخدم 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. يجمع هذا الطلب تقييمات جميع الأماكن الواقعة ضمن نطاق 1000 متر من مبنى إمباير ستيت في مدينة نيويورك لإنشاء إحصاءات التقييم:

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)

يحدّد نموذج طلب البحث هذا قيدًا مستهدفًا يتمحور حول مبنى إمباير ستيت في مدينة نيويورك، بنطاق جغرافي يبلغ 1000 متر.

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 ضمن نطاق 1000 متر من مبنى إمباير ستيت في مدينة نيويورك:

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