مقاييس صفحة المستخدم

[srinivas.chilukuri]

@SaraDev
هل يمكنك تقديم استعلام SQL للمقاييس المتاحة على https://meta.discourse.org/u?cards=no&order=post_count
راجع الصورة أدناه

المقاييس

• الإعجابات المستلمة
• الإعجابات المقدمة
• المواضيع التي تم عرضها
• المشاركات التي تم قراءتها
• الأيام التي تم زيارتها
• الحلول
• تحياتي

---

نحن نستخدم مشرفي الفئات، لذلك نقوم بتعديل استعلام المشرف لأي مجموعة معينة

[SaraDev (Sara Devlaeminck)]

مرحباً @srinivas.chilukuri،

يمكن استرداد إحصائيات صفحة المستخدم /u عبر مستكشف البيانات باستخدام الجدول directory_items.

مقاييس صفحة دليل المستخدم

-- [params]
-- int :period
-- خيارات الفترة:
-- 1. all
-- 2. yearly
-- 3. monthly
-- 4. weekly
-- 5. daily
-- 6. quarterly

SELECT 
    di.user_id,
    COALESCE(di.likes_received, 0) AS likes_received,
    COALESCE(di.likes_given, 0) AS likes_given,
    COALESCE(di.topics_entered, 0) AS topics_viewed,
    COALESCE(di.topic_count, 0) AS topic_count,
    COALESCE(di.post_count, 0) AS post_count,
    COALESCE(di.days_visited, 0) AS days_visited,
    COALESCE(di.posts_read, 0) AS posts_read,
    COALESCE(di.solutions, 0) AS solutions,
    COALESCE(di.gamification_score, 0) AS cheers
FROM 
    directory_items di
WHERE 
    di.period_type = :period
ORDER BY 
    di.user_id

بدلاً من معلمات start_date و end_date المعتادة، يمكن تصفية البيانات من هذا الجدول باستخدام الحقل period_type، حيث تتوافق القيم التالية مع الفترات الزمنية المختلفة المتاحة في صفحة الدليل:

• 1: كل الوقت
• 2: سنوي
• 3: شهري
• 4: أسبوعي
• 5: يومي
• 6: ربع سنوي

مثال للنتائج لهذا التقرير ستبدو كالتالي:

user	likes_received	likes_given	topics_viewed	topic_count	post_count	days_visited	posts_read	solutions	cheers
Username1	4	17	250	69	116	480	217	10	844100
Username2	2	5	47	0	2	43	59	1	112305
Username3	0	4	2	0	0	2	7	0	3100
…	…	..	…	…	…	…	…	…	…

[srinivas.chilukuri]

@SaraDev
أنا بحاجة إلى تاريخ البدء وتاريخ الانتهاء. هل هناك حل بديل للحصول على المقاييس المحددة مع تاريخ البدء وتاريخ الانتهاء

• مستخدم
• الإعجابات المستلمة
• الإعجابات المقدمة
• المواضيع التي تم عرضها
• عدد المواضيع
• عدد المشاركات
• الأيام التي تمت زيارتها
• المشاركات المقروءة
• الحلول
• التحيات

ملاحظة: أحصل على المقاييس لمجموعة فرعية صغيرة من إجمالي المستخدمين

[SaraDev (Sara Devlaeminck)]

@srinivas.chilukuri

أحتاج إلى تاريخ البدء والانتهاء. هل هناك حل بديل للحصول على المقاييس المحددة بتاريخ البدء والانتهاء

• user
• likes_received
• likes_given
• topics_viewed
• topic_count
• post_count
• days_visited
• posts_read
• solutions
• cheers

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

إليك كيف سيبدو ذلك:

مقاييس المستخدم

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2025-01-01

WITH likes_received AS (
    SELECT
        ua.user_id AS user_id,
        COUNT(*) AS likes_received
    FROM
        user_actions ua
    WHERE
        ua.action_type = 2
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.user_id
),
likes_given AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS likes_given
    FROM
        user_actions ua
    WHERE
        ua.action_type = 1
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
user_metrics AS (
    SELECT
        us.user_id,
        SUM(us.topics_entered) AS topics_viewed,
        SUM(us.posts_read_count) AS posts_read,
        SUM(us.days_visited) AS days_visited
    FROM
        user_stats us
    WHERE
        us.first_post_created_at BETWEEN :start_date AND :end_date
    GROUP BY
        us.user_id
),
solutions AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS solutions
    FROM
        user_actions ua
    WHERE
        ua.action_type = 15
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
cheers AS (
    SELECT
        gs.user_id,
        SUM(gs.score) AS cheers
    FROM
        gamification_scores gs
    WHERE
        gs.date BETWEEN :start_date AND :end_date
    GROUP BY
        gs.user_id
)

SELECT
    u.id AS user_id,
    COALESCE(lr.likes_received, 0) AS likes_received,
    COALESCE(lg.likes_given, 0) AS likes_given,
    COALESCE(um.topics_viewed, 0) AS topics_viewed,
    COALESCE(um.posts_read, 0) AS posts_read,
    COALESCE(um.days_visited, 0) AS days_visited,
    COALESCE(sol.solutions, 0) AS solutions,
    COALESCE(ch.cheers, 0) AS cheers
FROM
    users u
LEFT JOIN
    likes_received lr ON u.id = lr.user_id
LEFT JOIN
    likes_given lg ON u.id = lg.user_id
LEFT JOIN
    user_metrics um ON u.id = um.user_id
LEFT JOIN
    solutions sol ON u.id = sol.user_id
LEFT JOIN
    cheers ch ON u.id = ch.user_id
ORDER BY
    u.id

شرح الاستعلام:

1. المعلمات:
   • :start_date و :end_date هي معلمات تحدد النطاق الزمني للبيانات التي يتم الاستعلام عنها.
2. تعبيرات الجدول المشتركة (CTEs):
   • likes_received: يحسب عدد الإعجابات التي تلقاها كل مستخدم (action_type = 2) ضمن النطاق الزمني المحدد.
   • likes_given: يحسب عدد الإعجابات التي قدمها كل مستخدم (action_type = 1) ضمن النطاق الزمني المحدد.
   • user_metrics: يجمع إحصائيات المستخدم مثل الموضوعات التي تم عرضها، والمشاركات التي تمت قراءتها، والأيام التي تمت زيارتها للمستخدمين الذين نشروا أول مشاركة لهم ضمن النطاق الزمني المحدد.
   • solutions: يحسب عدد الحلول التي قدمها كل مستخدم (action_type = 15) ضمن النطاق الزمني المحدد.
   • cheers: يجمع نقاط الألعاب لكل مستخدم ضمن النطاق الزمني المحدد.
3. الاختيار النهائي:
   • يحدد الاستعلام الرئيسي مقاييس تفاعل المستخدم لكل مستخدم، بما في ذلك الإعجابات المستلمة، والإعجابات المقدمة، والموضوعات التي تم عرضها، والمشاركات التي تمت قراءتها، والأيام التي تمت زيارتها، والحلول المقدمة، والتشجيعات المستلمة.
   • يستخدم LEFT JOIN لضمان تضمين جميع المستخدمين، حتى لو لم يكن لديهم نشاط في بعض الفئات، مع ملء القيم الفارغة بالأصفار باستخدام COALESCE.

نتائج مثال

user_id	likes_received	likes_given	topics_viewed	posts_read	days_visited	solutions	cheers
1	10	5	20	100	15	2	30
2	0	3	5	20	5	0	10
—	—	—	—	—	—	—	—

[srinivas.chilukuri]

@SaraDev

@SaraDev

مقاييس المستخدم

لقد استخدمنا الاستعلام المقدم في المنشور أعلاه ولدينا الأسئلة التالية:

1. هل user_metrics من جدول user_stats هو المصدر الصحيح لهذه المعلومات؟ بالنظر إلى أن user_stats هو جدول ثابت يلخص مقاييس المستخدم منذ انضمامه إلى Discourse، فقد لا يكون مثاليًا لتصفية المقاييس ضمن نطاق زمني محدد (على سبيل المثال، من تاريخ البدء إلى تاريخ الانتهاء).
2. مقارنة السلاسل الزمنية (T/S C/O)
   بالنسبة لمجموعة معينة من المستخدمين، قارنا البيانات المتاحة للفترة الزمنية على صفحة المستخدم ولاحظنا اختلافات كبيرة.

الاختلافات الرئيسية:

• topics_entered
• posts_read_count
• days_visited

هل يمكنك توضيح ما إذا كانت هناك طريقة أفضل لاسترداد مقاييس المستخدم المقيدة بالوقت؟

[Jagster (Jakke Lehtonen)]

PG::UndefinedColumn: ERROR:  column uv.topic_id does not exist
LINE 38:         COUNT(DISTINCT uv.topic_id) AS topics_viewed, -- Cou...

[SaraDev (Sara Devlaeminck)]

@srinivas.chilukuri

هل user_metrics من جدول user_stats هو المصدر الصحيح لهذه المعلومات؟ بالنظر إلى أن user_stats هو جدول ثابت يلخص مقاييس المستخدم منذ انضمامه إلى Discourse، فقد لا يكون مثاليًا لتصفية المقاييس ضمن نطاق زمني محدد (على سبيل المثال، من تاريخ البدء إلى تاريخ الانتهاء).

أنت على حق في أن جدول user_stats هو جدول ثابت يلخص مقاييس المستخدم مدى الحياة منذ انضمامه إلى Discourse.

بدلاً من ذلك، لتصفية المقاييس حسب التاريخ مثل posts_read_count و days_visited، سنحتاج إلى استخدام جدول قاعدة البيانات user_visits لـ posts. سنحتاج أيضًا إلى استخدام جدول topic_views لتصفية مقاييس topics_entered حسب التاريخ.

@srinivas.chilukuri

بالنسبة لمجموعة معينة من المستخدمين، قارنا البيانات المتاحة للفترة الزمنية في صفحة المستخدم ولاحظنا اختلافات كبيرة.

تنبع الاختلافات التي لاحظتها من استخدام جدول user_stats بدلاً من جداول أخرى مثل user_visits و topic_views لتصفية هذه الإحصائيات حسب التاريخ.

لمعالجة هذا، يمكننا تحديث الاستعلام لاستخدام تلك الجداول قاعدة البيانات بدلاً من ذلك:

إليك نسخة محدثة من الاستعلام:

مقاييس صفحة المستخدم

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH likes_received AS (
    SELECT
        ua.user_id AS user_id,
        COUNT(*) AS likes_received
    FROM
        user_actions ua
    WHERE
        ua.action_type = 2
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.user_id
),
likes_given AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS likes_given
    FROM
        user_actions ua
    WHERE
        ua.action_type = 1
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
user_metrics AS (
    SELECT
        tv.user_id,
        COUNT(DISTINCT tv.topic_id) AS topics_viewed
    FROM
        topic_views tv
    WHERE
        tv.viewed_at BETWEEN :start_date AND :end_date
    GROUP BY
        tv.user_id
),
days_and_posts AS (
    SELECT
        uv.user_id,
        COUNT(DISTINCT uv.visited_at) AS days_visited,
        SUM(uv.posts_read) AS posts_read
    FROM
        user_visits uv
    WHERE
        uv.visited_at BETWEEN :start_date AND :end_date
    GROUP BY
        uv.user_id
),
solutions AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS solutions
    FROM
        user_actions ua
    WHERE
        ua.action_type = 15
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
cheers AS (
    SELECT
        gs.user_id,
        SUM(gs.score) AS cheers
    FROM
        gamification_scores gs
    WHERE
        gs.date BETWEEN :start_date AND :end_date
    GROUP BY
        gs.user_id
)

SELECT
    u.id AS user_id,
    COALESCE(lr.likes_received, 0) AS likes_received,
    COALESCE(lg.likes_given, 0) AS likes_given,
    COALESCE(um.topics_viewed, 0) AS topics_viewed,
    COALESCE(dp.days_visited, 0) AS days_visited,
    COALESCE(dp.posts_read, 0) AS posts_read,
    COALESCE(sol.solutions, 0) AS solutions,
    COALESCE(ch.cheers, 0) AS cheers
FROM
    users u
LEFT JOIN
    likes_received lr ON u.id = lr.user_id
LEFT JOIN
    likes_given lg ON u.id = lg.user_id
LEFT JOIN
    user_metrics um ON u.id = um.user_id
LEFT JOIN
    days_and_posts dp ON u.id = dp.user_id
LEFT JOIN
    solutions sol ON u.id = sol.user_id
LEFT JOIN
    cheers ch ON u.id = ch.user_id
ORDER BY
    u.id

لاحظ أنه بهذه الطريقة، فإن بيانات posts_read في جدول user_visits لديها فرق مهم واحد - فهي لا تحسب منشورات المستخدم الخاصة، بينما تتضمن البيانات من جدول user_stats المنشورات التي كتبها المستخدم بنفسه، لذلك قد تجد فرقًا بين هذين الإحصائيين في هذا الاستعلام وفي صفحة المستخدم.