متطلب تقرير نشاط المستخدم المعقد

[Paul_King]

أبحث عن طريقة لجعل Discourse يولد تقارير نشاط على شكل ملخص لجميع نشاطات المستخدمين بين أي تاريخين مخصصين في كل عام (حيث تبدو الوظيفة المدمجة الحالية أنها تدعم فقط نطاقات محددة مسبقًا، وعلاقتها بتاريخ اليوم فقط)، وحفظ النتائج في ملف متوافق مع Excel (يبدو حاليًا أن هذا ممكن فقط لتقارير نشاط المستخدم الفردية).

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

• أود تصفية هذه النتائج وفقًا لحقل مخصص في ملف تعريف المستخدم (رقم عضوية فريد).

• أود استبعاد نتائج المستخدمين الذين لا تقع أرقام عضويتهم ضمن نطاق رقمي محدد.

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

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

• سيكون من الجميل الحصول على تفصيل حسب فئة الموضوع/الوسم لكل مستخدم.

• مثاليًا، سيكون من الرائع أن يتم توليد هذا التقرير تلقائيًا وإرساله إلي عبر البريد الإلكتروني في أوقات محددة في كل عام (سيكون هذا هو التتويج المثالي).

ما مدى جدوى كل هذا؟

هل يتطلب ذلك إضافة مخصصة جديدة لتنفيذه، أم أنه ممكن عبر استعلام SQL ذكي داخل إضافة Data Explorer الحالية؟

أم أن الخطوة الذكية هي البحث عن خيار تصدير شامل “all” بسيط نسبيًا، ومحاولة تنفيذ الباقي باستخدام Excel؟

[david (David Taylor)]

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

الشيء الوحيد الذي لا يستطيع مستكشف البيانات القيام به هو نقطة “توليد تلقائي وإرسالها إلي عبر البريد الإلكتروني”. إذا كانت هذه نقطة إلزامية، فيمكنك تحقيق ذلك نظريًا عن طريق استدعاء واجهة برمجة تطبيقات مستكشف البيانات من نظام آخر.

[Paul_King]

@david

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

شكرًا لك يا ديفيد.
يبدو أنني سأحتاج إلى فهم استعلامات SQL.

هل يمكن لاستعلام واحد أن يأخذ كمُدخلات مخرجات مُفلترة من استعلام آخر؟ كما أنه، هل يمكن تضمين التفاعلات القائمة على قوائم البريد الإلكتروني بالتأكيد؟ هل يتم تخزين هذه البيانات بطريقة مختلفة ما؟ كنت قلقًا من أنها قد تم استبعادها من تقارير النشاط القياسية لسبب غامض لكنه غير قابل للتغلب عليه.

[david (David Taylor)]

@Paul_King

هل يمكن لاستعلام واحد أن يستقبل كمُدخل مخرجات مُرشَّحة من استعلام آخر؟

لا، سيتعين عليك نسخ/لصق أجزاء من الاستعلام.

@Paul_King

أيضًا، هل يمكن بالتأكيد تضمين التفاعل القائم على قائمة البريد الإلكتروني؟

تتضمن جدول posts في قاعدة البيانات حقلًا من نوع via_email (قيمة منطقية)، لذا نعم، يمكنك معرفة المنشورات التي أُُنشِئَت عبر البريد الإلكتروني

ومع ذلك، لا يتضمن نظام Discourse أي أدوات تتبع في رسائل البريد الإلكتروني التي يرسلها، لذا لن تكون هناك أي طريقة لتحديد ما إذا كانت إشعارات البريد الإلكتروني قد “قُرِئَت” أم لا.

[Paul_King]

هل وظيفة تقارير نشاط المستخدم المدمجة تُدار عبر استعلامات SQL يمكنني نسخها من أي مكان والتعديل عليها؟ حتى لا أضيع أسابيع في إعادة اختراع العجلة.

[david (David Taylor)]

هل تنظر إلى تقرير “المستخدمين النشطين يوميًا”؟ يتم إنشاؤه باستخدام هذه المنطقية، والتي تستخدم ActiveRecord (لذا لا توجد أوامر SQL مباشرة). ومع ذلك، قد تكون هذه المنطقية نقطة انطلاق مفيدة.

[Paul_King]

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

[david (David Taylor)]

يمكن العثور على منطق دليل المستخدمين هنا: discourse/app/models/directory_item.rb at 1e66e4602f408d7b2d1105b6c228542dce7f4b38 · discourse/discourse · GitHub

[Paul_King]

حسناً، لا يبدو الأمر مهمة تافهة يمكن تكرارها عبر SQL (على الأقل بالنسبة لي)، إذ سأحتاج إلى تعلم ما يكفي من SQL لتنفيذها من الصفر في الوقت نفسه، ما لم يكن هناك شيء مشابه بما يكفي يمكنني نسخه مباشرة. آخر مرة قمت فيها بأي برمجة كانت منذ سنوات عديدة في المدرسة الثانوية، عندما لم يكن BASIC محرجاً للإدلاء به إلى هذه الدرجة.

[david (David Taylor)]

نعم، أعتقد أن تنفيذ هذا سيتطلب معرفة معقولة بـ SQL. إذا كان لديك ميزانية لهذا العمل، فقد تتمكن من العثور على شخص يمكنه المساعدة في Marketplace

[Paul_King]

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

[Grayden_Shand]

@Paul_King

قد يساعدك هذا الاستعلام.

https://meta.discourse.org/t/daily-weekly-or-total-stats-by-user-over-a-specified-time-range/275167u=grayden_shand

[Paul_King]

شكرًا جزيلاً! جربتها، لكنني أحصل على خطأ في الصيغة

PG::SyntaxError: ERROR: syntax error at or near “WITH”
LINE 13: WITH date_range AS (

(تقبلوا اعتذاري، لقد أرسلت رسالة خاصة إليك عبر ذلك الموضوع قبل أن أدرك أنك والمؤلف الأصلي هناك شخص واحد!)

[Grayden_Shand]

هل تمانع إذا سألتك عن القيم التي تستخدمها للمتغيرات؟

لقد استخرجت الاستعلام للتو وشغّلته على موقع اختباري بالقيم التالية:

• start_date: 2021-07-01
• end_date: 2021-07-30
• coverage: all

[Paul_King]

مرحباً، جربتُ عدداً منها، لكن جميعها أعطتني نفس النتيجة - على سبيل المثال

[Grayden_Shand]

عذراً، لم أتمكن من إعادة إنتاج الخطأ.

هل يمكنك لصق الاستعلام هنا كما هو لديك؟

[Paul_King]

SELECT 1-- التغطية: 'week' (أسبوع)، 'all' (الكل)، أو 'date' (تاريخ)
-- [المعاملات]
-- date :start_date = 2019-08-27
-- date :end_date = 2019-09-30
-- text :coverage = week

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "date", EXTRACT(week from date_trunc('day', dd):: date) AS "week"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.date, dr.week, count(pa.*) AS "likes"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.date and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.date, dr.week, u.id
ORDER BY u.id, dr.date
), posts_summary AS (SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date), 
visits AS (SELECT u.id, dr.*, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, COUNT(uv.*) AS visits
FROM date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN user_visits uv ON (uv.user_id = u.id AND visited_at = dr.date)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)

SELECT ps.id, ps.username, ps.created_at, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.date = ps.date)
GROUP BY ps.id, ps.username, ps.created_at, CASE 
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  
ORDER BY ps.id, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END

[Grayden_Shand]

أه، فهمت الآن.

عبارة SELECT 1 في البداية ليست جزءًا من الاستعلام وهي مصدر مشكلتك. هذه هي القيمة المحجوزة عند إنشاء استعلام جديد في مستكشف البيانات. احذفها وستعمل بشكل صحيح.

-- coverage: 'week', 'all', or 'date'
-- [params]
-- date :start_date = 2019-08-27
-- date :end_date = 2019-09-30
-- text :coverage = week

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "date", EXTRACT(week from date_trunc('day', dd):: date) AS "week"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.date, dr.week, count(pa.*) AS "likes"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.date and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.date, dr.week, u.id
ORDER BY u.id, dr.date
), posts_summary AS (SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date), 
visits AS (SELECT u.id, dr.*, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, COUNT(uv.*) AS visits
FROM date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN user_visits uv ON (uv.user_id = u.id AND visited_at = dr.date)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)

SELECT ps.id, ps.username, ps.created_at, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.date = ps.date)
GROUP BY ps.id, ps.username, ps.created_at, CASE 
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  
ORDER BY ps.id, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END

[Paul_King]

شكرًا لك @Grayden_Shand

اختفى الخطأ.

إذا أمكنني استفسارك قليلًا، هل تتضمن الإحصائيات التي يولدها هذا الاستعلام المنشورات عبر البريد الإلكتروني، والردود عبر البريد الإلكتروني للمستخدمين في وضع قائمة البريد؟ وإذا لم يكن الأمر كذلك، كيف يمكنني تضمينها؟

أيضًا، كيف يمكنني تضمين قيمة حقل ملف تعريف مخصص بجانب اسم المستخدم؟

هل لديك أي نصائح يمكنك تقديمها حول كيفية تحديد اسم الحقل المعني وتنفيذ ذلك؟

[Grayden_Shand]

@Paul_King

إذا أمكنني استشارتك قليلاً، هل تشمل الإحصائيات التي يولدها هذا الاستعلام المنشورات عبر البريد الإلكتروني، والردود عبر البريد الإلكتروني للمستخدمين في وضع قائمة البريد؟ وإذا لم تكن كذلك، فكيف يمكنني تضمينها؟

نعم، يجب أن تشملها. كما ذكر ديفيد، تحتوي جدول posts على حقل منطقي (boolean) يُدعى via_email. الاستعلام الحالي يتجاهل هذا الحقل ويحسب جميع المنشورات سواء تم نشرها عبر البريد الإلكتروني أم لا.

@Paul_King

أيضاً، كيف يمكنني تضمين قيمة حقل مخصص في ملف تعريف المستخدم بجانب اسم المستخدم؟

هناك جدول يُدعى user_custom_fields. ستحتاج إلى إجراء عملية ربط (JOIN) مع هذا الجدول لتضمين حقل مخصص معين.

ربما سأقوم بذلك في الاستعلام الفرعي post_summary.

على سبيل المثال:

...
), posts_summary AS (
SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received, cf.value as LABEL_FOR_CUSTOM_FIELD
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
LEFT JOIN user_custom_fields cf ON (u.id == cf.user_id and cf.name == "YOUR CUSTOM FIELD NAME") 
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)
...

لقد أضفت عموداً إلى جملة SELECT، وأضفت جملة JOIN جديدة لجدول user_custom_fields.

لاحظ أنك ستحتاج إلى استبدال "YOUR CUSTOM FIELD NAME" و LABEL_FOR_CUSTOM_FIELD بالقيم المناسبة.

بعد ذلك، ستحتاج أيضاً إلى تحديث الأعمدة التي تختارها في الاستعلام النهائي.

...
SELECT ps.id, ps.username, ps.created_at, ps.LABEL_FOR_CUSTOM_FIELD, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
..

هذا هو النهج الذي سأتبعه على الأرجح.

بالتوفيق!