تجميع البيانات الزمنية: فترات زمنية مدتها 5 أشهر في SQL

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

حلول لأنظمة قاعدة بيانات مختلفة

يختلف النهج الأمثل اعتمادًا على نظام قاعدة البيانات الخاص بك. فيما يلي حلول لـ PostgreSQL و MySQL:

postgresql

postgresql نهجًا مرنًا باستخدام استخراج ('epoch') للحصول على timestamp unix (ثواني منذ فترة) و الفاصل الزمني :

SELECT
    date_trunc('minute', timestamp)   INTERVAL '5 minutes' * (extract(minute from timestamp)::int / 5) AS five_minute_interval,
    name,
    COUNT(b.name)
FROM time a, id b
WHERE ... -- Your WHERE clause here
GROUP BY five_minute_interval, name
ORDER BY five_minute_interval;

هذا الاستعلام يقطع الطابع الزمني أولاً إلى دقيقة باستخدام Date_trunc . بعد ذلك ، يحسب الفاصل الزمني لمدة 5 دقائق عن طريق إضافة مضاعفات 5 دقائق بناءً على دقيقة الطابع الزمني الأصلي.

mysql

MySQL حلاً أبسط باستخدام UNIX_TIMESTAMP () وقسم Integer:

SELECT
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(timestamp) / 300) * 300) AS five_minute_interval,
    name,
    COUNT(b.name)
FROM time a, id b
WHERE ... -- Your WHERE clause here
GROUP BY five_minute_interval, name
ORDER BY five_minute_interval;

يحول هذا الاستعلام الطابع الزمني إلى طابع زمني لـ UNIX ، ويؤدي تقسيم عدد صحيح بمقدار 300 (ثانية في 5 دقائق) ، ثم يحول النتيجة مرة أخرى إلى الطابع الزمني باستخدام من _unixtime () .

كلا الاستعلامات تجمع النتائج بواسطة الفاصل الزمني المحسوب لمدة 5 دقائق والاسم ، مما يوفر الإخراج المجمعة المطلوب. تذكر أن تحل محل ... بالفقرة الخاصة بك حيث . يضمن الترتيب بواسطة عرض التقديم الزمني للنتائج.