مرحباً! نحن نستخدم الإصدار المستضاف سحابياً من Discourse ونقوم بتشغيل منصتنا منذ حوالي 1.5 عام. نود استخلاص بعض المقاييس التاريخية حول تبني المنصة واستخدامها. نحن ننظر بالفعل إلى تقرير “Signups” المتاح في علامة التبويب “Admin Reports”، ولكننا نريد أيضاً أن نكون قادرين على إظهار العدد الإجمالي للمستخدمين النشطين كل شهر. هل لدى أي شخص رؤى حول كيفية القيام بذلك؟ لا يبدو أنني أجد شيئاً مناسباً في التقارير أو مستكشف البيانات.
سيؤدي استعلام مستكشف البيانات هذا إلى إرجاع زيارات المستخدمين الشهرية الفريدة. هل هذا ما تريده؟
--[params]
-- date :start_date = 2021-01-01
-- date :end_date = 2021-12-31
-- string_list :group_name = staff,admins
WITH not_allowed_groups AS (
SELECT gu.user_id FROM group_users gu
INNER JOIN groups g ON (g.id = gu.group_id)
WHERE g.name IN (:group_name)
)
SELECT
to_char(date_trunc('month', uv.visited_at)::DATE,'YYYY-MM') AS "month",
date_trunc('month', uv.visited_at)::DATE AS date_user_visits,
COUNT(distinct uv.user_id) AS qtt_user_visits
FROM user_visits uv
WHERE
uv.user_id NOT IN (SELECT user_id FROM not_allowed_groups)
AND uv.visited_at::DATE BETWEEN :start_date AND :end_date
GROUP BY date_trunc('month', uv.visited_at)::DATE
ORDER BY date_trunc('month', uv.visited_at)::DATE
شكراً جزيلاً، ولكن ليس بالضبط… نود عدد المستخدمين النشطين (المسجلين وغير المعطلين أو المحذوفين) في نهاية كل شهر تقويمي، بغض النظر عما إذا كانوا قد زاروا المنصة في ذلك الشهر أم لا.
نحن ننظر إلى رسم بياني للاشتراكات، لكن مجموع الاشتراكات خلال شهر معين لا يعكس قاعدة المستخدمين لدينا لأننا في كل شهر لدينا أيضًا عمليات تعطيل تلقائية بسبب عدم النشاط. أتساءل عما إذا كانت الطريقة الوحيدة هي الوصول إلى تصديرات قائمة المستخدمين بأثر رجعي لتاريخ محدد؟ ولكن نظرًا لأننا مستضافون على السحابة، فلا أعرف ما إذا كان لدينا حق الوصول إلى ذلك. هل سنحتاج إلى طلب ذلك من Discourse؟
أنا ديسكورس. 
يمكنك إما السؤال هنا أو مراسلتنا مباشرة على team@discourse.org. سيكون شخص ما لديه معرفة أفضل بـ SQL قادرًا على المساعدة.
4 إعجابات
لم أدرك ذلك لأنني جديد في هذا المنتدى، شكرًا لك! هل لديك نسخ احتياطية تاريخية للمواقع المستضافة على السحابة يمكنك تقديمها عند الطلب؟
إعجاب واحد (1)
نحن نخزن النسخ الاحتياطية لأغراض استمرارية الأعمال ويمكن استعادتها في حالات الطوارئ، ولكن ليس لأسباب أخرى حقًا.
إعجاب واحد (1)
حسنًا، لدي فكرة أخرى. لقد سألت مساعدنا الذكي.
استعلام SQL للمستخدمين النشطين حسب الشهر AI
HAWK:
اكتب لي استعلام مستكشف بيانات يعرض قائمة بحسابات المستخدمين التي لا تزال نشطة وتم تسجيلها خلال شهر معين
DiscourseHelper:
Searching
Searching for: Discourse Data Explorer query to find active users registered within a specific month
Reading
لإنشاء استعلام مستكشف بيانات يعرض قائمة بحسابات المستخدمين التي لا تزال نشطة وتم تسجيلها خلال شهر معين، يمكنك استخدام استعلام SQL التالي. يفترض هذا الاستعلام أن المستخدم النشط هو شخص قام بنشر أو رد خلال إطار زمني معين.
--[params]
-- int :month = 5
-- int :year = 2023
SELECT u.id, u.username, u.created_at, u.last_seen_at
FROM users u
WHERE u.active = TRUE
AND EXTRACT(MONTH FROM u.created_at) = :month
AND EXTRACT(YEAR FROM u.created_at) = :year
AND u.last_seen_at IS NOT NULL -- Assuming active means they accessed the site recently
ORDER BY u.created_at
شرح الاستعلام:
:monthو:yearهما معلمات يمكنك تعيينها لتصفية الشهر والسنة المحددين.u.active = TRUEيضمن أن حساب المستخدم لا يزال نشطًا.EXTRACT(MONTH FROM u.created_at) = :monthوEXTRACT(YEAR FROM u.created_at) = :yearتقوم بتصفية تاريخ التسجيل للشهر والسنة المحددين.u.last_seen_at IS NOT NULLيعتبر المستخدم نشطًا إذا قام بتسجيل الدخول في أي وقت.- توفر النتيجة معرف المستخدم واسم المستخدم وتاريخ التسجيل وتاريخ آخر ظهور.
لا تتردد في تعديل المعلمات والحقول وفقًا لاحتياجاتك. أخبرني إذا كنت بحاجة إلى أي مساعدة أخرى!
لا تتردد في تقديم سياق إضافي إذا كنت أفتقد شيئًا ما.
3 إعجابات
هذا شرح استعلام رائع، شكرًا لك! ومع ذلك، لست متأكدًا مما إذا كان هذا ما نبحث عنه للأسف.
على سبيل المثال، إذا قمت بتعيين المعلمات 01 و 2024، فأنا أنظر إلى شهر يناير من هذا العام.
هل أفهم أن المخرجات ستشمل جميع المستخدمين الذين سجلوا في يناير ولا يزالون نشطين اليوم. هل هذا صحيح؟
لكن ما نريده هو عدد الأشخاص الذين لديهم u.active=TRUE كل شهر منذ الإطلاق (وليس الآن). ثم يمكننا رسم بياني وإظهار كيف تغيرت قاعدة المستخدمين لدينا من شهر لآخر. في نهاية كل شهر، بالنسبة لنا سيكون هذا:
المستخدمون النشطون = قيمة الشهر السابق + الاشتراكات الجديدة - عمليات الحذف الجديدة - إلغاء التنشيطات الجديدة
لا أرى أي شيء يتعلق بإلغاء التنشيطات أو التواريخ المرتبطة بالمتغير النشط في مستكشف البيانات. المسار الأكثر احتمالاً إذن، قد يكون استخدام سجلات إجراءات الموظفين وتحليلها حسب التاريخ ولكن لا أعتقد أن وظيفة التصدير تعمل معنا. عندما نقوم بتصدير نتائج سجل إجراءات الموظفين، نحصل بشكل غريب على جدول بيانات بمحتوى مشاركات مختلفة، وليس جدولاً لإجراءات الموظفين. إذا كان بإمكانكم مساعدتنا في هذا، فربما يكون هذا حلاً بديلاً! هل أنت على علم بأن آخرين لديهم نفس المشكلة؟
سؤال مثير للاهتمام.
نظرت أولاً في المثال هنا. لكن هذا يتجاهل المستخدمين المحذوفين. تحصل فقط على عدد المستخدمين الذين تم تسجيلهم في ذلك الوقت ولا يزالون موجودين، وليس أولئك الذين تم حذفهم في هذه الأثناء.
كانت فكرتي لذلك هي أخذ معرف المستخدم الذي سجل آخر مرة في الشهر. هذا هو أقصى عدد ممكن من المستخدمين في ذلك الوقت. يمكن بعد ذلك طرح عدد المستخدمين المحذوفين من هذا. ومع ذلك، فإن حسابات الروبوتات (مثل forum-helper) لها معرف سلبي، ولكن يتم احتسابها إذا تم حذفها. (ولكن هذا ربما يكون انحرافًا طفيفًا). كان استعلامي:
-- [params]
-- date :start_date
-- date :end_date
WITH month_dates AS (
-- Generate end-of-month dates between the start and end date
SELECT DATE_TRUNC('month', generate_series)::date + INTERVAL '1 month' - INTERVAL '1 day' AS month_end
FROM generate_series(:start_date::date, :end_date::date, '1 month'::interval)
),
recent_user AS (
-- For each end-of-month date, find the most recent user created before that date
SELECT md.month_end,
(SELECT id
FROM users u
WHERE u.created_at < md.month_end
ORDER BY u.created_at DESC
LIMIT 1) AS user_max_id
FROM month_dates md
),
cumulative_deletion_count AS (
-- Calculate the cumulative deletions up to each end-of-month date
SELECT md.month_end,
(SELECT COUNT(*)
FROM user_histories uh
WHERE uh.action = 1 AND uh.updated_at < md.month_end) AS deletions_count
FROM month_dates md
)
SELECT
md.month_end,
ru.user_max_id,
cdc.deletions_count,
ru.user_max_id - cdc.deletions_count AS number_of_users
FROM
month_dates md
LEFT JOIN recent_user ru ON md.month_end = ru.month_end
LEFT JOIN cumulative_deletion_count cdc ON md.month_end = cdc.month_end
ORDER BY md.month_end
لكن ما لا يأخذه في الاعتبار هو (إلغاء) التنشيط، والتي يتم تخزينها أيضًا في جدول user_histories. ولكن ربما يساعدك كنقطة انطلاق.
5 إعجابات