پیشرفته: پُرسمان کردن مستقیم مجموعه داده

برای دسترسی مستقیم به داده‌های «اطلاعات آماری جاها»، پُرسمان‌های SQL را در BigQuery می‌نویسید که اطلاعات آماری انبوهشی درباره جاها را برمی‌گرداند. نتایج از مجموعه داده برای معیارهای جستجوی مشخص‌شده در پُرسمان برگردانده می‌شود.

آشنایی با آستانه تجمیع

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

اگر فیلترهای پُرسمان شما تعداد ۰، ۱، ۲، ۳، یا ۴ را نشان دهد، کل ردیف نتیجه از پاسخ حذف می‌شود. اگر منطق برنامه شما به تعداد کم دقیق (ازجمله ۰) نیاز دارد یا باید شناسه‌های مکان را به‌صورت جداگانه بازیابی کند، باید به‌جای آن بااستفاده از توابع شمارش مکان از مجموعه داده‌ها پرسمان کنید.

اصول اولیه پُرسمان

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

هر بخش از پُرسمان در زیر با جزئیات بیشتر شرح داده شده است.

الزامات پُرسمان

پُرسمان‌های 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"

فیلتر کردن نتایج بااستفاده از خطوط و چندضلعی‌های جغرافیایی

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

۱. فیلتر کردن بااستفاده از میانگیرهای جستجوی شعاعی (ST_DWITHIN)

این پُرسمان نمونه، محدودیت هدفی را مشخص می‌کند که مرکز آن ساختمان امپایر استیت در شهر نیویورک است و شعاع آن ۱۰۰۰ متر است.

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)

۲. فیلتر کردن بااستفاده از مرزهای چندضلعی (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)

۳. فیلتر کردن براساس حائل‌های سفر درطول مسیر (LINESTRING)

در مثال بعدی، ناحیه جستجو را بااستفاده از خطی از نقطه‌های متصل تعریف می‌کنید و شعاع جستجو را روی ۱۰۰ متر در اطراف خط تنظیم می‌کنید. این خط شبیه به مسیر سفری است که توسط 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 بزرگ‌تر یا مساوی ۴٫۰ دارند و 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

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

مثال زیر رده‌بندی میانگین را برای ۵ مکان محاسبه می‌کند. مکان‌ها با 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 برای انجام درخواست «جستجوی نوشتار (جدید)» پیدا کنید.

در مثال زیر، پُرسمان تعداد کافه‌های موجود در کد پستی ۲۰۰۰ سیدنی، استرالیا را که در آرایه 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 را در شعاع ۱۰۰۰ متری ساختمان امپایر استیت در شهر نیویورک برمی‌گرداند:

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 برای کد پستی در NYC) یا شناسه مکان (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;

این مثال تعداد مکان‌هایی را که حداقل ۱۰ شارژر 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

فیلتر کردن براساس تاریخ عکس آنی داده‌ها

مجموعه داده‌های مکان‌ها حاوی داده‌های تعداد مکان برای هر ماه از ژانویه ۲۰۲۴ است. برای پُرسمان کردن عکس آنی داده‌های مکان‌ها برای یک ماه مشخص، بااستفاده از فیلد snapshot_date فیلتر کنید.

برای مثال، پُرسمان زیر نتایج را براساس نوع اصلی برای هر مکان در منطقه جستجو در ماه ژانویه ۲۰۲۴ گروه‌بندی می‌کند:

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

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

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

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

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