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

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

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

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

סינון לפי מזהה מקום

בדוגמה שלמטה מחושב הדירוג הממוצע של 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)

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

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

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 עמדות טעינה של טסלה שתומכות בטעינה מהירה:

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 בבריטניה.

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

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

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