يعد تصميم الجدول التراكمي أداة قوية للغاية لهندسة البيانات يجب أن يعرفها جميع مهندسي البيانات.
ينتج هذا التصميم جداول يمكنها تقديم تحليلات فعالة على أطر زمنية كبيرة (تصل إلى آلاف الأيام).
فيما يلي رسم تخطيطي لتصميم خط الأنابيب عالي المستوى لهذا النمط:
نقوم في البداية ببناء جدول المقاييس اليومي الخاص بنا والذي يتناسب مع جوهر كياننا. هذه البيانات مستمدة من أي مصادر أحداث لدينا في المنبع.
بعد أن نحصل على مقاييسنا اليومية، FULL OUTER JOIN الجدول التراكمي بالأمس مع البيانات اليومية لليوم وننشئ مصفوفات القياس الخاصة بنا لكل مستخدم. يتيح لنا ذلك إحضار السجل الجديد دون الحاجة إلى مسحه بالكامل. (زيادة كبيرة في الأداء)
تسمح لنا مصفوفات المقاييس هذه بالإجابة بسهولة على الاستفسارات المتعلقة بسجل جميع المستخدمين باستخدام أشياء مثل ARRAY_SUM لحساب أي مقياس نريده في أي إطار زمني يسمح به المصفوفة.
كلما زاد الإطار الزمني لتحليلك، أصبح هذا النمط أكثر أهمية!!
تستخدم جميع صيغ الاستعلامات تركيب ووظائف Presto/Trino. سيحتاج هذا المثال إلى تعديل لمتغيرات SQL الأخرى!
سنستخدم التواريخ:
- 01-01-2022 كما هو الحال اليوم من حيث تدفق الهواء هو
{{ ds }}- 31-12-2021 كما حدث بالأمس في مصطلحات قالب Airflow، هذا هو
{{ yesterday_ds}}
في هذا المثال، سنبحث في كيفية إنشاء هذا التصميم لحساب المستخدمين النشطين يوميًا وأسبوعيًا وشهريًا بالإضافة إلى إعجابات المستخدمين وتعليقاتهم ومشاركاتهم.
جدولنا المصدر في هذه الحالة هو events .
event_type وهو like comment أو share أو viewمن المغري الاعتقاد بأن الحل لهذا هو تشغيل خط أنابيب مثل هذا
SELECT
COUNT(DISTINCT user_id) as num_monthly_active_users,
COUNT(CASE WHEN event_type = 'like' THEN 1 END) as num_likes_30d,
COUNT(CASE WHEN event_type = 'comment' THEN 1 END) as num_comments_30d,
COUNT(CASE WHEN event_type = 'share' THEN 1 END) as num_shares_30d,
...
FROM events
WHERE event_date BETWEEN DATE_SUB('2022-01-01', 30), AND '2022-01-01'
المشكلة في ذلك هي أننا نقوم بمسح بيانات الأحداث لمدة 30 يومًا كل يوم لإنتاج هذه الأرقام. خط أنابيب مهدر للغاية ولكنه بسيط. يجب أن تكون هناك طريقة حيث يتعين علينا فقط مسح بيانات الحدث مرة واحدة ودمجها مع نتائج الـ 29 يومًا السابقة، أليس كذلك؟ هل يمكننا إنشاء بنية بيانات حيث يمكن لعالم البيانات الاستعلام عن بياناتنا ومعرفة عدد الإجراءات التي اتخذها المستخدم بسهولة في آخر عدد من الأيام؟
هذا التصميم بسيط جدًا ويتكون من 3 خطوات فقط:
GROUP BY user_id ثم قم بحسابهم على أنهم نشطون يوميًا إذا كان لديهم أي أحداثCOUNT(CASE WHEN event_type = 'like' THEN 1 END) لمعرفة عدد الإعجابات والتعليقات والمشاركات اليومية أيضًاFULL OUTER JOIN مجموعتي البيانات هاتين في today.user_id = yesterday.user_idCOALESCE(today.user_id, yesterday.user_id) as user_id لتتبع جميع المستخدمينactivity_array . نريد فقط أن يقوم activity_array بتخزين بيانات آخر 30 يومًاCARDINALITY(activity_array) < 30 لفهم ما إذا كان بإمكاننا فقط إضافة قيمة اليوم إلى مقدمة المصفوفة أم أننا بحاجة إلى تقطيع عنصر من نهاية المصفوفة قبل إضافة قيمة اليوم إلى المقدمةCOALESCE(t.is_active_today, 0) لوضع قيم صفرية في المصفوفة عندما لا يكون المستخدم نشطًاactivity_array ، لكن فيما يتعلق بالإعجابات والتعليقات والمشاركات أيضًا!CASE WHEN ARRAY_SUM(activity_array) > 0 THEN 1 ELSE 0 END يمنحنا أنشطة شهرية نظرًا لأننا حددنا حجم المصفوفة بـ 30CASE WHEN ARRAY_SUM(SLICE(activity_array, 1, 7)) > 0 THEN 1 ELSE 0 END يمنحنا نشاطًا أسبوعيًا لأننا نتحقق فقط من العناصر السبعة الأولى من المصفوفة (أي آخر 7 أيام)ARRAY_SUM(SLICE(like_array, 1, 7)) as num_likes_7d يعطينا عدد الإعجابات التي قام بها هذا المستخدم في الأيام السبعة الماضيةARRAY_SUM(like_array) as num_likes_30d يعطينا عدد الإعجابات التي قام بها هذا المستخدم خلال الثلاثين يومًا الماضية منذ أن تم تثبيت المصفوفة على هذا الحجمdepends_on_past: True
