أرغب في إنشاء تقرير مخصص: الوقت بين آخر استجابتين لموضوع معين

البيانات والتقارير
1

على غرار وقت الاستجابة الأول، أبحث عن طريقة لتحديد متوسط الوقت (على أي فترة زمنية) بين آخر استجابتين في المواضيع في قائمة محددة من الفئات.

حالة الاستخدام لهذا هي تحديد ما إذا كان إعداد الإغلاق التلقائي مناسبًا.

لقد قمت بتثبيت المكون الإضافي لمستكشف البيانات، لكنني لا أعرف ما يكفي عن مخطط قاعدة البيانات (أو، علاوة على ذلك، لا أتذكر الكثير من دورة قواعد البيانات/SQL في الكلية في أوائل التسعينيات) لإنشاء التقرير بنفسي.

سأكون ممتنًا للإرشادات والاقتراحات حول كيفية سحب هذه البيانات.

وقت الوصول المقدر: يبدو أن النهج هو إجراء ربط بين المواضيع والمشاركات (حتى أتمكن من التصفية حسب الفئة)، ثم العثور على المشاركات بأحدث طابعين زمني للتحديث وطرحهما.

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

بالتفكير أكثر في الأمر، يبدو أن هذا قد يكون مجرد اختلاف على أي استعلام يتم استخدامه في عملية rake topics:auto_close (الفرق هو أن هذا يستخدم الوقت الحالي بدلاً من طرح آخر مشاركتين في الموضوع).

2

لمن يهمه الأمر، إليك ما يبدو أنه يعمل:

-- [params]
-- null date :start_date
-- null date :end_date
-- null int_list :category_ids

WITH RankedPosts AS (
    SELECT 
        p.topic_id,
        p.created_at,
        ROW_NUMBER() OVER (PARTITION BY p.topic_id ORDER BY p.post_number DESC) AS rank
    FROM 
        posts p
    WHERE 
        p.created_at BETWEEN :start_date AND :end_date
        AND EXISTS (
            SELECT 1
            FROM topics t
            WHERE t.id = p.topic_id
            AND t.category_id IN (:category_ids)
        )
),
FilteredPosts AS (
    SELECT 
        topic_id,
        created_at,
        rank
    FROM 
        RankedPosts
    WHERE 
        rank <= 2
),
PostDifferences AS (
    SELECT 
        topic_id,
        EXTRACT(days FROM (MAX(created_at) FILTER (WHERE rank = 1) - MAX(created_at) FILTER (WHERE rank = 2)))::numeric(9,2) AS days_difference
    FROM 
        FilteredPosts
    GROUP BY 
        topic_id
)
SELECT 
    t.category_id,
    AVG(pd.days_difference) AS avg_days_difference,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pd.days_difference) AS median_days_difference
FROM 
    topics t
JOIN 
    PostDifferences pd ON t.id = pd.topic_id
GROUP BY 
    t.category_id
ORDER BY
    avg_days_difference DESC

يجب أن يوفر هذا المتوسط ​​والوسيط لأوقات إنشاء الطوابع الزمنية. بدت الطوابع الزمنية للتحديث مشكلة (حصلت على بعض القيم السالبة لسبب ما عند إجراء المتوسطات فقط).

لقد حصلت على بعض المساعدة من الذكاء الاصطناعي في إنشاء الاستعلام، لذلك من الممكن أن تكون هناك أخطاء - وأنا سعيد إذا كان لدى أي شخص اقتراحات حول كيفية تحسينه (أو تصحيح أي أخطاء). بدت النتائج التي حصلت عليها معقولة بناءً على الفحوصات التي تم إجراؤها على مجموعات أصغر، بالإضافة إلى التغييرات الناتجة عن زيادة النطاق الزمني مما أدى إلى اتجاهات تتماشى مع التوقعات التي كانت لدي من استخدام عينة بيانات أكبر.

3

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.