استخدام DATE_TRUNC لتجميع البيانات

مجتمع البيانات والتقارير
1

تُعدّ دالة date_trunc أداة قوية في SQL. تتيح لك هذه الدالة اقتطاع قيمة TIMESTAMP أو INTERVAL بناءً على جزء تاريخ محدد، مما يجعلها دالة لا غنى عنها عند رغبتك في تجميع أو تصنيف البيانات حسب فترة زمنية معينة.

الصيغة

تتمثل صيغة دالة date_trunc في الآتي:

date_trunc('date_part', field)
  • date_part: وهي سلسلة نصية تحدد جزء التاريخ أو الطابع الزمني المراد اقتطاعه. يمكن أن تكون واحدة من القيم التالية:
    • millennium (ألفية)
    • century (قرن)
    • decade (عقد)
    • year (سنة)
    • quarter (ربع سنة)
    • month (شهر)
    • week (أسبوع)
    • day (يوم)
    • hour (ساعة)
    • minute (دقيقة)
    • second (ثانية)
    • milliseconds (مللي ثانية)
    • microseconds (ميكرو ثانية)
  • field: وهو الطابع الزمني أو الفاصل الزمني المراد اقتطاعه.

أمثلة على الاستخدام في استعلامات Data Explorer

لنلقِ نظرة على بعض أمثلة الاستعلامات التي تستخدم date_trunc:

عدد المواضيع الجديدة حسب الشهر

مستوى التعقيد: مبتدئ

يُستخدم هذا الاستعلام في SQL لحساب عدد المواضيع التي تم إنشاؤها في كل شهر ضمن قاعدة بيانات Discourse.

SELECT 
    date_trunc('month', created_at)::DATE AS month,
    count(id)
FROM topics
GROUP BY month
ORDER BY month DESC

في هذا الاستعلام، تقوم عبارة date_trunc('month', created_at)::DATE باقتطاع الطابع الزمني created_at إلى الشهر ثم تحويله إلى تاريخ لعرضه، مما يجمع المواضيع فعليًا حسب الشهر الذي تم إنشاؤها فيه.

ثم تقوم دالة count(id) بحساب عدد المواضيع التي تم إنشاؤها في كل شهر. يتم ترتيب النتائج حسب الشهر تنازليًا، بحيث يظهر الشهر الأحدث أولاً.

أمثلة على النتائج:

month count
2023-09-01 1
2023-08-01 6
2023-07-01 10
شرح مفصل مع تعليقات داخلية 
-- تحديد الشهر الذي تم فيه إنشاء الموضوع وعدد المواضيع
SELECT 
    -- اقتطاع الطابع الزمني 'created_at' إلى الشهر وتحويله إلى تاريخ
    -- هذا يجمع المواضيع حسب الشهر الذي تم إنشاؤها فيه
    date_trunc('month', created_at)::DATE AS month,
    -- حساب عدد المواضيع التي تم إنشاؤها في كل شهر
    count(id)
-- من جدول 'topics'
FROM topics
-- تجميع النتائج حسب الشهر
GROUP BY month
-- ترتيب النتائج حسب الشهر تنازليًا
-- هذا يعني أن الشهر الأحدث سيظهر أولاً
ORDER BY month DESC

إجمالي المستخدمين التراكمي

مستوى التعقيد: متوسط

سيوفر هذا الاستعلام تقريرًا أسبوعيًا عن تسجيلات المستخدمين في منتدى Discourse، بالإضافة إلى إجمالي تراكمي للمستخدمين. يستخدم عبارة WITH لإنشاء مجموعة نتائج مؤقتة (daily_signups)، ثم يختار منها.

-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

WITH daily_signups AS(
SELECT
    to_char(date_trunc('week', u.created_at)::date,'YYYY-MM-DD') AS Date,
    Count (id) as Signups
FROM users u
WHERE
    u.created_at::date BETWEEN :start_date::date AND :end_date::date
GROUP BY Date
)

SELECT
    Date, Signups, SUM(Signups) OVER (ORDER BY Date) as total_users
FROM daily_signups
ORDER BY Date Asc

إليك تفصيلًا لكيفية عمل هذا الاستعلام:

  • تقوم عبارة WITH بإنشاء مجموعة نتائج مؤقتة باسم daily_signups. تحتوي هذه المجموعة على عدد تسجيلات المستخدمين لكل أسبوع بين تاريخي البداية والنهاية المحددين بالمعاملين :start_date و :end_date.
  • داخل مجموعة النتائج daily_signups، تقوم عبارة date_trunc('week', u.created_at)::date باقتطاع الطابع الزمني created_at إلى الأسبوع ثم تحويله إلى تاريخ. هذا يجمع المستخدمين فعليًا حسب الأسبوع الذي سجلوا فيه.
  • تقوم Count(id) بعد ذلك بحساب عدد المستخدمين الذين سجلوا في كل أسبوع.
  • في عبارة SELECT الرئيسية، تقوم SUM(Signups) OVER (ORDER BY Date) بحساب إجمالي تراكمي للمستخدمين. تحدد عبارة OVER (ORDER BY Date) أن المجموع يجب أن يُحسب على الصفوف المرتبة حسب التاريخ، مما يعطي مجموعًا تراكميًا للتسجيلات حتى كل تاريخ.
  • ثم يتم ترتيب النتائج حسب التاريخ تصاعديًا.

أمثلة على النتائج:

date signups total_users
2013-01-28 20 20.0
2013-02-04 2136 2156.0
2013-02-11 442 2598.0
شرح مفصل مع تعليقات داخلية 
-- تحديد المعاملات لتاريخ البداية والنهاية
-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- إنشاء تعبير جدول مشترك (CTE) لحساب عدد تسجيلات المستخدمين كل أسبوع
WITH daily_signups AS(
SELECT
    -- اقتطاع الطابع الزمني 'created_at' إلى الأسبوع وتنسيقه كسلسلة تاريخ
    to_char(date_trunc('week', u.created_at)::date,'YYYY-MM-DD') AS Date,
    -- حساب عدد المستخدمين الذين سجلوا
    Count (id) as Signups
FROM users u
WHERE
    -- تضمين المستخدمين فقط الذين سجلوا بين تاريخي البداية والنهاية
    u.created_at::date BETWEEN :start_date::date AND :end_date::date
GROUP BY Date
)

-- تحديد التاريخ وعدد التسجيلات والإجمالي التراكمي للتسجيلات
SELECT
    Date, 
    Signups, 
    -- حساب الإجمالي التراكمي للتسجيلات
    SUM(Signups) OVER (ORDER BY Date) as total_users
FROM daily_signups
-- ترتيب النتائج حسب التاريخ تصاعديًا
ORDER BY Date Asc

عدد الأسئلة المحلولة وغير المحلولة شهريًا

مستوى التعقيد: متوسط / يتطلب إضافة Discourse Solved

سيوفر هذا الاستعلام تقريرًا شهريًا عن عدد الأسئلة المحلولة وغير المحلولة في منتدى Discourse. يفترض هذا الاستعلام أن جميع المواضيع في الموقع قابلة للحل.

-- [params]
-- date :start_date
-- date :end_date

WITH monthly_questions AS (
    SELECT
        date_trunc('month', created_at)::DATE AS month,
        COUNT(id) as total_questions
    FROM topics
    WHERE
        created_at::date BETWEEN :start_date::date AND :end_date::date
    GROUP BY month
),
solved_questions AS (
    SELECT
        date_trunc('month', created_at)::DATE AS month,
        COUNT(id) as solved
    FROM user_actions
    WHERE
        created_at::date BETWEEN :start_date::date AND :end_date::date
    AND action_type = 15
    GROUP BY month
)

SELECT
    mq.month, 
    mq.total_questions, 
    COALESCE(sq.solved, 0) as solved,
    mq.total_questions - COALESCE(sq.solved, 0) as unsolved
FROM monthly_questions mq
LEFT JOIN solved_questions sq ON mq.month = sq.month
ORDER BY mq.month ASC

في هذا الاستعلام، تقوم عبارة الجدول المشترك (CTE) monthly_questions بحساب العدد الإجمالي للأسئلة (المواضيع) التي تم إنشاؤها كل شهر. بينما تقوم عبارة solved_questions بحساب عدد الأسئلة التي تم تحديدها كـ «محلولة» كل شهر عن طريق حساب عدد id من جدول user_actions حيث action type = 15.

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

أمثلة على النتائج:

month total_questions solved unsolved
2023-07-01 10 3 7
2023-08-01 6 0 6
2023-09-01 1 1 0
شرح مفصل مع تعليقات داخلية 
-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- إنشاء CTE لحساب العدد الإجمالي للأسئلة (المواضيع) التي تم إنشاؤها كل شهر
WITH monthly_questions AS (
    SELECT
        -- اقتطاع الطابع الزمني 'created_at' إلى الشهر
        date_trunc('month', created_at)::DATE AS month,
        -- حساب عدد المواضيع التي تم إنشاؤها في كل شهر
        COUNT(id) as total_questions
    FROM topics
    WHERE
        -- تضمين المواضيع التي تم إنشاؤها بين تاريخي البداية والنهاية فقط
        created_at::date BETWEEN :start_date::date AND :end_date::date
    GROUP BY month
),
-- إنشاء CTE لحساب عدد الأسئلة التي تم تحديدها كـ «محلولة» كل شهر
solved_questions AS (
    SELECT
        -- اقتطاع الطابع الزمني 'created_at' إلى الشهر
        date_trunc('month', created_at)::DATE AS month,
        -- حساب عدد الأسئلة المحلولة في كل شهر
        COUNT(id) as solved
    FROM user_actions
    WHERE
        -- تضمين الإجراءات التي تم اتخاذها بين تاريخي البداية والنهاية فقط
        created_at::date BETWEEN :start_date::date AND :end_date::date
        -- النظر فقط في الإجراءات حيث نوع الإجراء هو 15 (مما يشير إلى سؤال محلول)
        AND action_type = 15
    GROUP BY month
)

-- تحديد الشهر، العدد الإجمالي للأسئلة، عدد الأسئلة المحلولة، وعدد الأسئلة غير المحلولة
SELECT
    mq.month, 
    mq.total_questions, 
    -- إذا لم تكن هناك أسئلة محلولة في شهر ما، اعرض 0
    COALESCE(sq.solved, 0) as solved,
    -- طرح عدد الأسئلة المحلولة من العدد الإجمالي للأسئلة للحصول على عدد الأسئلة غير المحلولة
    mq.total_questions - COALESCE(sq.solved, 0) as unsolved
FROM monthly_questions mq
-- ربط CTEs 'monthly_questions' و 'solved_questions' حسب الشهر
LEFT JOIN solved_questions sq ON mq.month = sq.month
-- ترتيب النتائج حسب الشهر تصاعديًا
ORDER BY mq.month ASC

إحصائيات ردود المواضيع

مستوى التعقيد: متقدم

يوفر هذا الاستعلام المعقد في SQL تقريرًا أسبوعيًا عن نشاط المواضيع في منتدى Discourse. يقسم بيانات المواضيع إلى عدة مقاييس رئيسية: عدد المواضيع التي تحتوي على رد واحد على الأقل، عدد المواضيع التي لا تحتوي على رد، الحد الأقصى لعدد الأيام التي مرّت على موضوع دون رد، ومتوسط الوقت حتى أول رد.

WITH posts_list AS (
    SELECT 
        t.id topic_id,
        p.post_number,
        p.created_at,
        ROW_NUMBER () OVER (
                    PARTITION BY p.topic_id 
                    ORDER BY p.post_number)  AS post_order
    FROM posts p
    INNER JOIN topics t ON t.id = p.topic_id AND archetype = 'regular' AND t.deleted_at ISNULL
    WHERE p.post_type = 1
        AND p.deleted_at ISNULL
        AND t.deleted_at ISNULL
        AND t.archetype = 'regular'
    ORDER BY p.topic_id, p.post_number
),
atleast_1_response AS (
    SELECT 
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics with atleast one response"
    FROM topics t
    WHERE t.archetype = 'regular'
        AND t.deleted_at ISNULL
        AND t.posts_count >= 2
    GROUP BY "week"
),
no_response AS(
    SELECT 
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics without response"
    FROM topics t
    WHERE t.archetype = 'regular'
        AND t.deleted_at ISNULL
        AND t.posts_count = 1
    GROUP BY "week"
),
max_days_without_response AS(
    SELECT 
        date_trunc('week', t.created_at::date)::date AS "week",
        CURRENT_DATE::date-MIN(created_at)::date AS "max days without response"
    FROM topics t
    WHERE t.archetype = 'regular'
        AND t.deleted_at ISNULL
        AND t.posts_count = 1
    GROUP BY "week"
),
avg_time_first_response AS (
    SELECT 
        date_trunc('week', pl.created_at::date)::date AS "week",
        EXTRACT(epoch FROM AVG(pl2.created_at-pl.created_at))/3600 AS "avg time first response (h)"
    FROM posts_list pl
    INNER JOIN posts_list pl2 ON pl.topic_id = pl2.topic_id AND pl2.post_order = 2
    WHERE  pl.post_order = 1
    GROUP BY "week" 
)

SELECT ar.week, 
    "topics without response", 
    "max days without response", 
    "topics with atleast one response",
    "avg time first response (h)"
FROM atleast_1_response ar
INNER JOIN no_response nr ON nr.week = ar.week
INNER JOIN max_days_without_response mdwr ON mdwr.week = ar.week
INNER JOIN avg_time_first_response atfr ON atfr.week = ar.week
ORDER BY "week" DESC

إليك ملخصًا لكيفية عمل هذا الاستعلام:

  • تقوم عبارة الجدول المشترك (CTE) posts_list باختيار قائمة بجميع المنشورات العادية من جداول posts و topics، مرتبة حسب topic_id و post_number. كما تقوم بتعيين رقم صف (post_order) لكل منشور داخل موضوعه.
  • تقوم CTE atleast_1_response بحساب عدد المواضيع العادية التي تحتوي على رد واحد على الأقل (أي أن posts_count أكبر من أو يساوي 2) لكل أسبوع.
  • تقوم CTE no_response بحساب عدد المواضيع العادية التي لا تحتوي على رد (أي أن posts_count يساوي 1) لكل أسبوع.
  • تقوم CTE max_days_without_response بحساب الحد الأقصى لعدد الأيام التي مرّت على موضوع دون رد لكل أسبوع.
  • تقوم CTE avg_time_first_response بحساب متوسط الوقت حتى أول رد لكل موضوع بالساعات، لكل أسبوع.
  • ثم تقوم عبارة SELECT الرئيسية بربط هذه CTEs معًا حسب الأسبوع واختيار الأعمدة ذات الصلة. يتم ترتيب النتائج حسب الأسبوع تنازليًا.
week topics without response max days without response topics with atleast one response avg time first response (h)
2023-09-04 15 2 47 2.6778684519444444
2023-08-28 30 9 138 8.7899938238888889
2023-08-21 22 16 130 9.3280889688888889
شرح مفصل مع تعليقات داخلية 
-- إنشاء جدول مؤقت (CTE) لجميع المنشورات العادية، مرتبة حسب topic_id و post_number
WITH posts_list AS (
    SELECT 
        t.id topic_id,  -- معرف الموضوع
        p.post_number,  -- رقم المنشور
        p.created_at,   -- تاريخ إنشاء المنشور
        -- تعيين رقم صف لكل منشور داخل موضوعه
        ROW_NUMBER () OVER (
                    PARTITION BY p.topic_id 
                    ORDER BY p.post_number)  AS post_order
    FROM posts p
    -- الربط مع جدول topics، مع النظر فقط في المواضيع العادية غير المحذوفة
    INNER JOIN topics t ON t.id = p.topic_id AND archetype = 'regular' AND t.deleted_at ISNULL
    WHERE p.post_type = 1
        AND p.deleted_at ISNULL  -- استبعاد المنشورات المحذوفة
        AND t.deleted_at ISNULL  -- استبعاد المواضيع المحذوفة
        AND t.archetype = 'regular'  -- النظر فقط في المواضيع العادية
    ORDER BY p.topic_id, p.post_number
),
-- إنشاء CTE لحساب عدد المواضيع العادية التي تحتوي على رد واحد على الأقل لكل أسبوع
atleast_1_response AS (
    SELECT 
        -- اقتطاع الطابع الزمني created_at إلى الأسبوع
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics with atleast one response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- النظر فقط في المواضيع العادية
        AND t.deleted_at ISNULL  -- استبعاد المواضيع المحذوفة
        AND t.posts_count >= 2  -- النظر فقط في المواضيع التي تحتوي على رد واحد على الأقل
    GROUP BY "week"
),
-- إنشاء CTE لحساب عدد المواضيع العادية التي لا تحتوي على رد لكل أسبوع
no_response AS(
    SELECT 
        -- اقتطاع الطابع الزمني created_at إلى الأسبوع
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics without response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- النظر فقط في المواضيع العادية
        AND t.deleted_at ISNULL  -- استبعاد المواضيع المحذوفة
        AND t.posts_count = 1  -- النظر فقط في المواضيع التي لا تحتوي على رد
    GROUP BY "week"
),
-- إنشاء CTE لحساب الحد الأقصى لعدد الأيام التي مرّت على موضوع دون رد لكل أسبوع
max_days_without_response AS(
    SELECT 
        -- اقتطاع الطابع الزمني created_at إلى الأسبوع
        date_trunc('week', t.created_at::date)::date AS "week",
        -- حساب عدد الأيام من تاريخ إنشاء الموضوع حتى التاريخ الحالي
        CURRENT_DATE::date-MIN(created_at)::date AS "max days without response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- النظر فقط في المواضيع العادية
        AND t.deleted_at ISNULL  -- استبعاد المواضيع المحذوفة
        AND t.posts_count = 1  -- النظر فقط في المواضيع التي لا تحتوي على رد
    GROUP BY "week"
),
-- إنشاء CTE لحساب متوسط الوقت حتى أول رد لكل موضوع بالساعات، لكل أسبوع
avg_time_first_response AS (
    SELECT 
        -- اقتطاع الطابع الزمني created_at إلى الأسبوع
        date_trunc('week', pl.created_at::date)::date AS "week",
        -- حساب متوسط الوقت حتى أول رد بالساعات
        EXTRACT(epoch FROM AVG(pl2.created_at-pl.created_at))/3600 AS "avg time first response (h)"
    FROM posts_list pl
    -- الربط مع CTE posts_list، مع النظر فقط في المنشور الثاني في كل موضوع
    INNER JOIN posts_list pl2 ON pl.topic_id = pl2.topic_id AND pl2.post_order = 2
    WHERE  pl.post_order = 1  -- النظر فقط في المنشور الأول في كل موضوع
    GROUP BY "week" 
)

-- تحديد الأسبوع، عدد المواضيع التي لا تحتوي على رد، الحد الأقصى لعدد الأيام دون رد، عدد المواضيع التي تحتوي على رد واحد على الأقل، ومتوسط الوقت حتى أول رد
SELECT ar.week, 
    "topics without response", 
    "max days without response", 
    "topics with atleast one response",
    "avg time first response (h)"
FROM atleast_1_response ar
-- ربط CTEs معًا حسب الأسبوع
INNER JOIN no_response nr ON nr.week = ar.week
INNER JOIN max_days_without_response mdwr ON mdwr.week = ar.week
INNER JOIN avg_time_first_response atfr ON atfr.week = ar.week
-- الترتيب حسب الأسبوع تنازليًا
ORDER BY "week" DESC

هذه مجرد أمثلة قليلة على الطرق التي يمكنك بها استخدام date_trunc في استعلامات Data Explorer. لا تتردد في استخدام أي من هذه الاستعلامات في موقعك، وإذا كان لديك أي أسئلة، فلا تتردد في طرحها في الأسفل. :slight_smile:

5 إعجابات
Retrospectively pulling number of users each calendar month