מתקדם: הרצת שאילתה ישירות בקבוצת הנתונים

כדי לגשת ישירות לנתוני Places Insights, אתם יכולים לכתוב שאילתות SQL ב-BigQuery שמחזירות תובנות מצטברות על מקומות. התוצאות מוחזרות מקבוצת הנתונים לפי הקריטריונים לחיפוש שצוינו בשאילתה.

הסבר על הסף לצבירת נתונים

שאילתות SQL סטנדרטי שמופעלות ישירות על הטבלאות הגולמיות אוכפות סף צבירה מחמיר. שורת פלט נוצרת רק אם מספר הצבירה המחושב הוא 5 ומעלה.

אם מסנני השאילתה מניבים ספירה של 0, 1, 2, 3 או 4, כל שורת התוצאות מושמטת מהתגובה. אם הלוגיקה של האפליקציה דורשת ספירות נמוכות מדויקות (כולל 0) או צריכה לאחזר מזהי מקומות ספציפיים, צריך לשלוח שאילתה למערך הנתונים באמצעות הפונקציות לספירת מקומות.

מידע בסיסי על שאילתות

בתמונה הבאה מוצג הפורמט הבסיסי של שאילתה:

כל חלק בשאילתה מתואר בפירוט בהמשך.

טיפ: ב-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. השאילתה הזו צוברת את הדירוגים של כל המקומות שנמצאים ברדיוס של 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 שמגדיר קריטריונים לסינון השאילתה.

טיפ: כדאי לכלול את המסנן business_status כדי לוודא שאתם מקבלים את מספר העסקים ב-OPERATIONAL. אחרת, הספירה תכלול עסקים סגורים.

בדוגמה הבאה, אתם מחזירים נתונים נצברים של מקומות מסוג 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. אחרת, הספירה תכלול עסקים סגורים.

בדוגמה הזו, השאילתה מחזירה את מספר כל חנויות הפרחים עם 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)

סינון לפי שעות הפעילות

בדוגמה הזו, מחזירים את מספר כל המקומות באזור גיאוגרפי עם שעות Happy Hour בימי שישי:

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.

הערה: השדה snapshot_date מופיע בפורמט YYYY-MM-01 (לדוגמה, "2024-01-01"). למרות שחובה לציין ערך של יום (01) במחרוזת התאריך, כל תמונת מצב מייצגת נתונים שנצברו במשך כל החודש ולא ביום בודד.

לדוגמה, השאילתה הבאה מחזירה תוצאות שמקובצות לפי הסוג הראשי של כל מקום באזור החיפוש, בחודש ינואר 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 כדי להחזיר כמה שורות בתגובה על סמך הקריטריונים לקיבוץ.

הערה: אי אפשר לבצע פעולת 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

בתמונה הזו מוצג פלט לדוגמה של השאילתה הזו:

בדוגמה הזו אתם מגדירים טבלה של מיקומים. לכל מיקום, מחשבים את מספר המסעדות בקרבת מקום, כלומר בטווח של 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
  `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

בתמונה הזו מוצג פלט לדוגמה של השאילתה הזו: