כתיבת שאילתה של מדדי עסקים

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

דרישות השאילתה

ההצהרה SELECT בשאילתה צריכה לכלול את WITH AGGREGATION_THRESHOLD ולציין את קבוצת הנתונים. לדוגמה:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `places_insights___us___sample.places_sample`

בדוגמה הזו, משתמשים ב-FROM כדי לציין את מערך הנתונים places_insights___us___sample.places_sample לארצות הברית.

ציון הגבלה על מיקום

שימו לב שבשאילתה הקודמת לא צוינה הגבלה על מיקום, ולכן צבירת הנתונים חלה על מערך הנתונים כולו. בדרך כלל מציינים הגבלת מיקום כמו בדוגמה הבאה:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `places_insights___us___sample.places_sample`
WHERE
ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)

בדוגמה הזו, השאילתה מציינת הגבלת טירגוט שממוקדת בבניין האמפייר סטייט בניו יורק, ברדיוס של 1,000 מטרים.

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

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `places_insights___us___sample.places_sample`
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)

בדוגמה הבאה, אזור החיפוש מוגדר באמצעות קו של נקודות מחוברות. הקו דומה למסלול נסיעה שמחושב על ידי 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 `places_insights___us___sample.places_sample`
WHERE
  ST_DWITHIN(route, point, 100)

בדוגמה הזו, הרדיוס של החיפוש מוגדר ל-100 מטרים מסביב לקו.

סינון לפי שדות של מערך נתונים של מקומות

מצמצמים את החיפוש על סמך השדות שמוגדרים בסכימת מערך הנתונים. סינון התוצאות לפי שדות של מערך נתונים, כמו מקום regular_opening_hours, price_level ולקוח rating.

אפשר להפנות לשדות כלשהם במערך הנתונים שמוגדר על ידי סכימת מערך הנתונים עבור המדינה שמעניינת אתכם. סכימת מערך הנתונים לכל מדינה מורכבת משני חלקים:

לדוגמה, השאילתה יכולה לכלול סעיף WHERE שמגדיר קריטריונים לסינון השאילתה. בדוגמה הבאה, מחזירים נתוני צבירה של מקומות מסוג restaurant עם business_status של OPERATIONAL, שערך rating שלהם גדול מ-4.0 או שווה לו, ועם allows_dogs שמוגדר ל-true:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `places_insights___us___sample.places_sample`
WHERE
ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
AND 'restaurant' IN UNNEST(types)
AND business_status = "OPERATIONAL"
AND rating >= 4.0
AND allows_dogs = true

השאילתה הבאה מחזירה תוצאות של מקומות שיש בהם לפחות שמונה תחנות טעינה לרכב חשמלי:

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `places_insights___us___sample.places_sample`
WHERE
  ev_charge_options.connector_count > 8;

סינון לפי סוג ראשי של מקום וסוג מקום

כל מקום במערך הנתונים יכול לכלול:

  • סוג ראשי יחיד שמשויך אליו מתוך הסוגים שמוגדרים בטבלה א'. לדוגמה, הסוג הראשי יכול להיות mexican_restaurant או steak_house. משתמשים ב-primary_type בשאילתה כדי לסנן את התוצאות לפי הסוג הראשי של המקום.

  • ערכים מרובים של type שמשויכים אליו מהסוגים שמוגדרים בTable A. לדוגמה, יכול להיות שלמסעדה יהיו הסוגים הבאים: seafood_restaurant, ‏restaurant, ‏food, ‏point_of_interest, ‏establishment. אפשר להשתמש ב-types בשאילתה כדי לסנן את התוצאות ברשימת הסוגים שמשויכים למקום.

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

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `places_insights___us___sample.places_sample`
WHERE
  'restaurant' IN UNNEST(types)
  AND 'bar' = primary_type

סינון לפי ערכי נתונים מוגדרים מראש

להרבה שדות של מערכי נתונים יש ערכים מוגדרים מראש. לדוגמה

  • בשדה 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 ברדיוס של 1,000 מטר מבניין האמפייר סטייט בניו יורק:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `places_insights___us___sample.places_sample`
WHERE
ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
AND business_status = "OPERATIONAL"
AND 'restaurant' IN UNNEST(types)

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

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

SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count
FROM `places_insights___us___sample.places_sample`,
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);

שימוש בפונקציית צבירה

בדוגמה שלמטה מוצגות פונקציות הצבירה הנתמכות ב-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
  `places_insights___us___sample.places_sample`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
  AND business_status = "OPERATIONAL"

החזרת קבוצות תוצאות

השאילתות שמוצגות עד עכשיו מחזירות שורה אחת בתוצאה שמכילה את ספירת הצבירה של השאילתה. אפשר גם להשתמש באופרטור GROUP BY כדי להחזיר כמה שורות בתשובה על סמך קריטריוני הקיבוץ.

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

SELECT WITH AGGREGATION_THRESHOLD
  primary_type,
  COUNT(*) AS count
FROM
  `places_insights___us___sample.places_sample`
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
  `places_insights___us___sample.places_sample` 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

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

תוצאות השאילתה לקיבוץ תוצאות לפי מיקום.