این صفحه به‌وسیله ‏Cloud Translation API‏ ترجمه شده است.

پرس و جو مستقیم از مجموعه داده

برای دسترسی مستقیم به داده‌های Places Insights، شما کوئری‌های SQL را در BigQuery می‌نویسید که بینش‌های تجمیعی در مورد مکان‌ها را برمی‌گردانند. نتایج از مجموعه داده‌ها برای معیارهای جستجوی مشخص شده در کوئری بازگردانده می‌شوند.

اگر نیاز به دریافت تعداد کمتر از ۵ دارید، به جای آن از توابع شمارش مکان استفاده کنید. این توابع می‌توانند هر تعداد، از جمله ۰، را برگردانند، اما حداقل مساحت جستجو را ۴۰.۰ متر در ۴۰.۰ متر (۱۶۰۰ متر ^مربع ) اعمال می‌کنند. درباره زمان پرس‌وجوی مستقیم و زمان استفاده از توابع بیشتر بدانید .

اصول اولیه پرس و جو

تصویر زیر قالب اولیه یک پرس و جو را نشان می‌دهد:

قالب اصلی یک پرس و جو.

هر بخش از پرس و جو با جزئیات بیشتر در زیر توضیح داده شده است.

الزامات پرس و جو

پرس‌وجوهای 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 را نشان می‌دهد. این کوئری امتیازهای تمام مکان‌های واقع در شعاع ۱۰۰۰ متری ساختمان امپایر استیت در شهر نیویورک را برای تولید آمار امتیازها تجمیع می‌کند:

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"

محدودیت مکانی را مشخص کنید

اگر محدودیت مکانی را مشخص نکنید، تجمیع داده‌ها برای کل مجموعه داده‌ها اعمال می‌شود. معمولاً برای جستجوی یک منطقه خاص، محدودیت مکانی را مشخص می‌کنید. این پرس‌وجوی نمونه، محدودیت هدف را با محوریت ساختمان امپایر استیت در شهر نیویورک، با شعاع ۱۰۰۰ متر، مشخص می‌کند.

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)

می‌توانید از یک چندضلعی برای مشخص کردن ناحیه جستجو استفاده کنید. هنگام استفاده از یک چندضلعی، نقاط چندضلعی باید یک حلقه بسته را تعریف کنند که در آن اولین نقطه در چندضلعی همان آخرین نقطه باشد:

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)

در مثال بعدی، شما منطقه جستجو را با استفاده از خطی از نقاط متصل تعریف می‌کنید و شعاع جستجو را روی ۱۰۰ متر در اطراف خط تنظیم می‌کنید. این خط مشابه یک مسیر سفر است که توسط API Routes محاسبه می‌شود. این مسیر ممکن است برای یک وسیله نقلیه، یک دوچرخه یا یک عابر پیاده باشد:

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)

فیلتر بر اساس فیلدهای مجموعه داده مکان

جستجوی خود را بر اساس فیلدهای تعریف شده توسط طرحواره مجموعه داده اصلاح کنید. نتایج را بر اساس فیلدهای مجموعه داده مانند place regular_opening_hours ، price_level و customer rating فیلتر کنید.

به هر فیلدی در مجموعه داده که توسط طرح مجموعه داده برای کشور مورد نظر شما تعریف شده است، ارجاع دهید. طرح مجموعه داده برای هر کشور از دو بخش تشکیل شده است:

طرحواره اصلی که در مجموعه داده‌های همه کشورها مشترک است.
یک طرحواره مختص هر کشور که اجزای طرحواره مختص آن کشور را تعریف می‌کند.

برای مثال، کوئری شما می‌تواند شامل یک عبارت WHERE باشد که معیارهای فیلترینگ برای کوئری را تعریف می‌کند.

در مثال زیر، داده‌های تجمیع را برای مکان‌هایی از نوع tourist_attraction با business_status برابر با OPERATIONAL ، که دارای rating بزرگتر یا مساوی ۴.۰ هستند و 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;

فیلتر بر اساس نوع اصلی مکان و نوع مکان

هر مکان در مجموعه داده‌ها می‌تواند شامل موارد زیر باشد:

یک نوع اصلی واحد که از انواع تعریف شده توسط انواع مکان (Place types) به آن مرتبط شده است. برای مثال، نوع اصلی ممکن است mexican_restaurant یا steak_house باشد. primary_type در یک پرس و جو برای فیلتر کردن نتایج بر اساس نوع اصلی یک مکان استفاده کنید.
چندین مقدار نوع از انواع تعریف شده توسط انواع مکان (Place types) به آن مرتبط می‌شوند. برای مثال، یک رستوران ممکن است انواع زیر را داشته باشد: 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

فیلتر بر اساس شناسه مکان

مثال زیر میانگین امتیاز را برای ۵ مکان محاسبه می‌کند. مکان‌ها با 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;

شناسه‌های مکان خاص را فیلتر کنید

همچنین می‌توانید آرایه‌ای از شناسه‌های مکان (Place IDs) را از یک پرس‌وجو (query) حذف کنید.

شما می‌توانید با استفاده از یابنده‌ی Place ID یا با استفاده از برنامه‌نویسی Places API برای انجام یک درخواست جستجوی متن (جدید) ، Place ID های مورد نظرتان را پیدا کنید.

در مثال زیر، کوئری تعداد کافه‌هایی را در کد پستی ۲۰۰۰ سیدنی، استرالیا، که در آرایه 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

در این مثال، کوئری تعداد تمام گل‌فروشی‌هایی را که business_status OPERATIONAL است، در شعاع ۱۰۰۰ متری ساختمان امپایر استیت در شهر نیویورک، برمی‌گرداند:

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

فیلتر بر اساس شارژ خودروهای برقی

این مثال تعداد مکان‌هایی را که حداقل ۸ شارژر خودروهای برقی دارند، نشان می‌دهد:

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

این مثال تعداد مکان‌هایی را که حداقل ۱۰ شارژر تسلا با پشتیبانی از شارژ سریع دارند، شمارش می‌کند:

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

گروه‌های نتیجه را برگردانید

پرس‌وجوهایی که تاکنون نشان داده شده‌اند، یک ردیف در نتیجه برمی‌گردانند که شامل تعداد تجمیع برای پرس‌وجو است. همچنین می‌توانید از عملگر GROUP BY برای بازگرداندن چندین ردیف در پاسخ بر اساس معیارهای گروه‌بندی استفاده کنید.

توجه: انجام عملیات GROUP BY روی postal_code_names در GB مجاز نیست.

برای مثال، کوئری زیر نتایجی را که بر اساس نوع اصلی هر مکان در ناحیه جستجو گروه‌بندی شده‌اند، برمی‌گرداند:

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

این تصویر نمونه‌ای از خروجی این پرس‌وجو را نشان می‌دهد:

نتایج پرس و جو برای گروه بندی نتایج بر اساس نوع اصلی.

در این مثال، شما جدولی از مکان‌ها تعریف می‌کنید. سپس برای هر مکان، تعداد رستوران‌های اطراف، یعنی رستوران‌هایی که در فاصله ۱۰۰۰ متری قرار دارند را محاسبه می‌کنید:

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`
JOIN
   my_locations l
ON
  ST_DWITHIN(l.location, p.point, 1000)
WHERE
  primary_type = "restaurant"
  AND business_status = "OPERATIONAL"
GROUP BY
  l.name