תצוגה ממולאת לאגרגציה של מדדים

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

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

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

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

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

למטה סכמת טבלת המשתמשים וטבלה לאחסון מדדים יומיים.

CREATE TABLE users (

    id INT AUTO_INCREMENT PRIMARY KEY,

    first_name VARCHAR(100) NOT NULL,       

    last_name VARCHAR(100) NOT NULL,        

    email VARCHAR(255) UNIQUE NOT NULL,     

    password_hash VARCHAR(255) NOT NULL, 

    age INT ,               

    city VARCHAR(100),                      

    country VARCHAR(100),                  

    currency VARCHAR(10),   

    last_login VARCHAR,

    hotel_preference VARCHAR(100)

);

​

CREATE TABLE daily_user_metrics (

    id BIGINT IDENTITY PRIMARY KEY,       -- Unique identifier for the record

    user_id BIGINT NOT NULL,              -- Foreign key to the users table

    clicks INT DEFAULT 0,                 -- Number of total site clicks

    visits INT DEFAULT 0,                 -- Number of visits to the hotel page

    bookings INT DEFAULT 0,               -- Number of bookings

    reviews INT DEFAULT 0,            -- Number of reviews

    cancellations INT DEFAULT 0,          -- Number of cancellations

​

    date_created DATE,            -- Daily metrics for each user

);

​

ניתן לקבל הרבה תובנות מהטבלאות למעלה.

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

SELECT

    date_created,

    SUM(clicks) AS total_clicks,

    SUM(visits) AS total_visits,

    SUM(bookings) AS total_bookings,

    SUM(reviews) AS total_reviews,

    SUM(cancellations) AS total_cancellations,

FROM

    daily_user_metrics m

INNER JOIN users u on m.user_id = u.id

WHERE

   u.age BETWEEN 30 and 40

   and u.city ='New York'   

   and m.date_created BETWEEN :startDate and :endDate

GROUP BY

    date_created 

ORDER BY

    date_created DESC

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

• לשאול את הטבלה ולקבץ את ה־date_created באופן שבועי או חודשי, בהתאם לדרישה.
• ליצור מעטפות שמגדירות את הנתונים על בסיס שבועי או חודשי לפי משתמש. ראו את השאילתה למטה:

CREATE VIEW weekly_user_metrics AS

SELECT

    DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) AS week_start, -- Start of the week (Sunday)

    SUM(clicks) AS total_clicks,

    SUM(visits) AS total_visits,

    SUM(bookings) AS total_bookings,

    SUM(reviews) AS total_reviews,

    SUM(cancellations) AS total_cancellations,

FROM

    daily_user_metrics m

INNER JOIN users u on m.user_id = u.id

WHERE

   u.age BETWEEN 30 and 40

   and u.city ='New York'   

   and m.date_created BETWEEN :startDate and :endDate    

GROUP BY

    DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) -- Group by week start

ORDER BY

    DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) DESC -- Sort by latest week

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

כדי לטפל בבעיות אלו, שרת Azure SQL מציע תכונה נהדרת הידועה כ- Indexed View.  Indexed View הוא תיאור פיזי של תצוגה המאוחסנת במסד הנתונים עם אינדקס מקוטע ייחודי. שינויים בטבלאות הבסיס מעדכנים באופן אוטומטי את ה- indexed view כדי לשמור על סנכרון. הוא משתמש ב- clustered index המארגן את הנתונים בתצוגה על פי סדר המפתחות של האינדקס.

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

ישנם כמה דברים לשקול לפני שמחליטים אם להשתמש ב- indexed views. תצוגות מאוחסנות לא יכולות לכלול פונקציות לא-דטרמיניסטיות. non–deterministic function היא פונקציה שלא תמיד מחזירה את אותו התוצאה עבור אותו קלט, גם כאשר מבוצעת עם ארגומנטים זהים ותחת תנאי בסיס נתונים זהה. כמו כן, indexed view היא מבנה בפועל שדורש אחסון, דומה ל- Materialized View.

התחביר ליצירת indexed view דומה לשאילתת יצירת התצוגה לעיל. אך, לא ניתן לכלול פונקציות לא-דטרמיניסטיות במהלך יצירת indexed view. השורה DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) AS week_start בשאילתת התצוגה לעיל תלויה בהגדרת ה- SET DATEFIRST הספציפית להפעלה, שמקבעת את היום הראשון בשבוע. זה נחשב ללא דטרמיניסטי כי זה יצור תוצאות שונות לתנאים שונים.

שמורים על הדברים שנאמרו לעיל, נוכל להמשיך ולהסיר את החישובים האינדטרמיניסטיים על ידי הפיכת העמודה לדטרמיניסטית. נוסיף עמודת week_start לטבלה התחתונה ונחשב ונמלא את ערך week_start בטבלה עבור משיכות נתונים יומיים. לכן, השורות עם התאריכים D1 עד D7 שייכות ל-W1, D8 עד D14 שייכות ל-W2, וכן הלאה.

עכשיו, נוכל להמשיך וליצור תצוגה מאושרת עם ה-SQL שלמטה.

ALTER TABLE daily_user_metrics ADD week_start DATE;-- Populate this column with first day of the week going forward

​

CREATE VIEW dbo.weekly_user_metric_aggregations_view

WITH SCHEMABINDING

AS

SELECT

    user_id,

    week_start,

    SUM(clicks) AS total_clicks,

    SUM(visits) AS total_visits,

    SUM(bookings) AS total_bookings,

    SUM(reviews) AS total_reviews,

    SUM(cancellations) AS total_cancellations,

    COUNT_BIG(*) AS row_count --SQL Server requires COUNT_BIG(*) in indexed views to handle scenarios where the count exceeds the range of an INT data type.

FROM

    dbo.daily_user_metrics

GROUP BY

    user_id,

    week_start;

CREATE UNIQUE CLUSTERED INDEX IX_weekly_user_metric_aggregations_view

ON dbo.weekly_user_metric_aggregations_view (user_id, week_start);    

לאחר שהתצוגה המאושרת נוצרה, נוכל לשאול אותה כך:

SELECT

    week_start,

    SUM(total_clicks) AS total_clicks,

    SUM(total_visits) AS total_visits,

    SUM(total_bookings) AS total_bookings,

    SUM(total_reviews) AS total_reviews,

    SUM(total_cancellations) AS total_cancellations,

FROM

    weekly_user_metric_aggregations_view mv

INNER JOIN users u on mv.user_id = u.id

WHERE

   u.age BETWEEN 30 and 40

   and u.city ='New York'   

   and m.date_created BETWEEN :startDate and :endDate

GROUP BY

    week_created 

ORDER BY

    week_created DESC

מסקנה

תצוגה מאושרת תציג זמן שאילתה מהיר יותר מתצוגה בסיסית או משאילתה לטבלה ישירות עבור סטי נתונים גדולים שבהם הנתונים גדלו עד למיליון שורות או יותר. המשתמש הסופי יהיה חוויית ניתור נמוכה, והשאילתות לבסיס הנתונים תהיינה מאותורכות. מאחר שסיכלנו שבוע של מידע בשורה אחת, חתכנו בזמן הסיכול ל-1/7.