مشاهدة مُفَهَّرَسَة لتجميع المقاييس

يعتبر Microsoft Azure SQL منصة قاعدة بيانات مُدارة بالكامل وقوية مصممة للاستعلام عن الأداء العالي وتخزين البيانات العلاقوية والتحليل. بالنسبة لتطبيق الويب النموذجي مع جزء خلفي، فإنه خيار جيد عندما نرغب في النظر في قاعدة بيانات مُدارة يمكن أن توسع بشكل رأسي وأفقي.

يولّد برنامج التطبيق مقاييس المستخدمين يوميًا، والتي يمكن استخدامها للتقارير أو التحليلات. Azure SQL هو خيار رائع للنظر في تخزين هذه البيانات واستعلامها تحت ظروف معينة:

1. الاستعلامات التحليلية تتطلب عمليات انضمام مع جداول أخرى (تطبيق عوامل تصفية على واجهة المستخدم)
2. ترغب في دمج البيانات التاريخية والتحويلية
3. حجم البيانات ليس كبيرًا للغاية، ويمكن إدارة أداء الاستعلام من خلال ضبطه

لنأخذ مثالًا على موقع حجز الفنادق الذي يعمل بنظام Azure SQL في الخلفية. نحن نرغب في رؤية لوحة تحكم واجهة المستخدم لتتبع نشاط المستخدمين، مثل النقرات على الموقع، الزيارات إلى صفحة وصف الفندق، الحجوزات المُنجزة، إلخ.

لنفترض أن جميع هذه البيانات الدلالية يتم تخزينها لكل مستخدم يوميًا في تخزين غير منظم، ونقوم بسحب هذه البيانات إلى قاعدة البيانات الخاصة بنا باستخدام وظائف الخلفية، مثل Apache Airflow.

أدناه هي مخطط الجدول لجدول المستخدمين وجدول لتخزين المقاييس اليومية.

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 Server ميزة رائعة تعرف باسم  عرض مفهرس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

الاستنتاج

سيكون لعرض المُفهرس وقت استعلام أسرع بشكل كبير من عرض أساسي أو استعلام الجدول مباشرة لمجموعات بيانات كبيرة حيث تكون البيانات قد وصلت إلى مليون صف أو أكثر. سيتمتع المستخدم النهائي بتجربة منخفضة التأخير، وسيتم تحسين استعلامات قاعدة البيانات. نظرًا لأننا قمنا بتجميع بيانات تغطي أسبوع في صف واحد، فقد قصرنا وقت التجميع إلى السابع.