حساب إجمالي لحظات المشاركة حسب الشهر باستخدام Data Explorer

[Konrad_Sopala]

مرحبًا أيها السحرة في مستكشف البيانات!

هل جرب أحدكم من قبل إنشاء استعلام يعطي عدد لحظات المشاركة الإجمالية شهريًا باستخدام استعلامات مستكشف البيانات؟

أعني العدد الإجمالي لجميع المنشورات والحلول والإعجابات شهريًا.

شكرًا مقدّمًا!

[Rotblut (Bernhard)]

انظر ما إذا كان هذا يعجبك.

querys.md

[Konrad_Sopala]

لا أرى بالضبط ما أبحث عنه

[simon]

@Konrad_Sopala

مرحبًا أيها السحرة في استكشاف البيانات!

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

--[params]
-- date :start_date

WITH month_starts AS (
SELECT generate_series(date_trunc('month', :start_date::date), CURRENT_DATE, interval '1 month')::date AS month_start
),
monthly_posts AS (
SELECT
month_start,
COUNT(1) AS posts_count
FROM posts p
JOIN month_starts
ON p.created_at::date >= month_start AND p.created_at::date <= month_start + interval '1 month - 1 day'
WHERE p.deleted_at IS NULL
AND p.post_type = 1
AND p.created_at >= :start_date
GROUP BY month_start
),
monthly_total_users AS (
SELECT
month_start,
COUNT(1) AS total_users_count
FROM users u
JOIN month_starts
ON u.created_at::date <= month_start + interval '1 month - 1 day'
WHERE u.id > 0
GROUP BY month_start
),
monthly_active_users AS (
SELECT
month_start,
COUNT(DISTINCT user_id) AS active_users_count
fROM user_visits uv
JOIN month_starts
ON uv.visited_at >= month_start AND uv.visited_at <= month_start + interval '1 month - 1 day'
WHERE uv.visited_at >= :start_date
GROUP BY month_start
),
monthly_solutions AS (
SELECT
month_start,
COUNT(1) AS solutions_count
FROM user_actions ua
JOIN month_starts ms
ON ua.created_at::date >= month_start AND ua.created_at::date <= month_start + interval '1 month - 1 day'
WHERE ua.action_type = 15
AND ua.created_at >= :start_date
GROUP BY month_start
),
monthly_likes AS (
SELECT
month_start,
COUNT(1) AS likes_count
FROM user_actions ua
JOIN month_starts ms
ON ua.created_at::date >= month_start AND ua.created_at::date <= month_start + interval '1 month - 1 day'
WHERE ua.action_type = 2
AND ua.created_at >= :start_date
GROUP BY month_start
)

SELECT
ms.month_start,
COALESCE(posts_count, 0) AS posts_count,
COALESCE(total_users_count, 0) AS total_users_count,
COALESCE(active_users_count, 0) AS active_users_count,
COALESCE(solutions_count, 0) AS solutions_count,
COALESCE(likes_count, 0) AS likes_count
FROM month_starts ms
LEFT JOIN monthly_posts mp ON mp.month_start = ms.month_start
LEFT JOIN monthly_total_users mtu ON mtu.month_start = ms.month_start
LEFT JOIN monthly_active_users mau ON mau.month_start = ms.month_start
LEFT JOIN monthly_solutions mts ON mts.month_start = ms.month_start
LEFT JOIN monthly_likes ml ON ml.month_start = ms.month_start
ORDER BY month_start DESC

قبل تشغيل الاستعلام، يجب عليك توفير قيمة لمعامل start_date. يجب أن يكون بالصيغة yyyy-mm--dd. ومع ذلك، فإن الاستعلام يستخرج فقط جزء الشهر من هذا التاريخ. والسبب في الحاجة إلى معامل تاريخ البدء هو أن الاستعلام قد يتجاوز مهلة الوقت على موقع كبير إذا تم تشغيله على كامل الفترة الزمنية التي كان الموقع نشطًا فيها. باستخدام معامل تاريخ البدء، يمكنني تشغيله على Meta لفترة تمتد لسنتين تقريبًا دون تجاوز مهلة الوقت.

شيء واحد يجب ملاحظته في الاستعلام هو أن عمود active_users_count يعيد عدد المستخدمين الفريدين الذين سجلوا الدخول إلى الموقع خلال شهر - فهو لا يتحقق مما إذا كان هؤلاء المستخدمون قد نفذوا أي إجراءات على الموقع (مثل إعجاب منشور أو إنشاء منشور). ربما يمكن للاستعلام القيام بذلك، لكنني قلق بشأن مشاكل تجاوز مهلة الوقت.

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

[Konrad_Sopala]

شكرًا لك @simon على المشاركة، هذا مفيد حقًا وهو في الغالب ما أبحث عنه! هل يمكنني أن أسألك عما إذا كان من الممكن إجراء تغيير واحد على الاستعلام؟ لا أحب تحديد تاريخ البداية. أود جمع كل هذه القيم (المنشورات، المستخدمين، الحلول، الإعجابات) والحصول على نتيجة الاستعلام على هذا النحو، حسب الشهر:

[simon]

@Konrad_Sopala

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

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

[Konrad_Sopala]

بالتأكيد! سننتظرك بمجرد أن يتاح لك الوقت لإجراء هذا التعديل

[michebs (Michelle)]

مرحبًا كونراد،

فيما يلي الاستعلام المعدّل.

WITH monthly_users AS (
    SELECT 
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
        COUNT(*) AS "new_users_month"
    FROM users
    WHERE id > 0
    GROUP BY date_part('year', created_at), date_part('month', created_at)
    ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),

monthly_posts AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS "posts_count"
	FROM posts p
	WHERE p.deleted_at IS NULL
		AND p.post_type = 1
	GROUP BY date_part('year', created_at), date_part('month', created_at)
	ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),

monthly_active_users AS (
	SELECT
        date_part('year', visited_at) AS year, 
        date_part('month', visited_at) AS month,
		COUNT(DISTINCT user_id) AS "active_users_count"
	FROM user_visits uv
	GROUP BY date_part('year', visited_at), date_part('month', visited_at)
	ORDER BY date_part('year', visited_at) ASC, date_part('month', visited_at)
),

monthly_solutions AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS "solutions_count"
	FROM user_actions ua
	WHERE ua.action_type = 15
	GROUP BY date_part('year', created_at), date_part('month', created_at)
	ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),

monthly_likes AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS "likes_count"
	FROM user_actions ua
	WHERE ua.action_type = 2
	GROUP BY date_part('year', created_at), date_part('month', created_at)
	ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
)

SELECT
    mu.year,
    mu.month,
    SUM(new_users_month) over (ORDER BY mu.year, mu.month rows between unbounded preceding AND current row) AS total_users,
    posts_count,
    COALESCE(active_users_count, 0) AS active_users_count,
    COALESCE(solutions_count, 0) AS solutions_count,
    COALESCE(likes_count, 0) AS solutions_count
FROM monthly_users mu
LEFT JOIN monthly_posts mp ON mp.year = mu.year AND mp.month = mu.month
LEFT JOIN monthly_active_users mau ON mau.year = mu.year AND mau.month = mu.month
LEFT JOIN monthly_solutions ms ON ms.year = mu.year AND ms.month = mu.month
LEFT JOIN monthly_likes ml ON ml.year = mu.year AND ml.month = mu.month
ORDER BY mu.year, mu.month 

إذا كنت بحاجة إلى إضافة جميع الأعمدة لتكون مطابقة تمامًا للصورة، فإليك الاستعلام:

تفاصيل SQL

WITH monthly_users AS (
    SELECT 
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
        COUNT(*) AS new_users_month
    FROM users
    WHERE id > 0
    GROUP BY date_part('year', created_at), date_part('month', created_at)
    ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),

monthly_posts AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS posts_count
	FROM posts p
	WHERE p.deleted_at IS NULL
		AND p.post_type = 1
	GROUP BY date_part('year', created_at), date_part('month', created_at)
	ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),

monthly_active_users AS (
	SELECT
        date_part('year', visited_at) AS year, 
        date_part('month', visited_at) AS month,
		COUNT(DISTINCT user_id) AS active_users_count
	FROM user_visits uv
	GROUP BY date_part('year', visited_at), date_part('month', visited_at)
	ORDER BY date_part('year', visited_at) ASC, date_part('month', visited_at)
),

monthly_solutions AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS solutions_count
	FROM user_actions ua
	WHERE ua.action_type = 15
	GROUP BY date_part('year', created_at), date_part('month', created_at)
	ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),

monthly_likes AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS likes_count
	FROM user_actions ua
	WHERE ua.action_type = 2
	GROUP BY date_part('year', created_at), date_part('month', created_at)
	ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
)

SELECT
    mu.year,
    mu.month,   
    SUM(new_users_month + COALESCE(posts_count,0) + 
        COALESCE(active_users_count, 0) + 
        COALESCE(solutions_count, 0) + 
        COALESCE(likes_count, 0)) 
        over (ORDER BY mu.year, mu.month rows between unbounded preceding AND current row) AS sum_total
FROM monthly_users mu
LEFT JOIN monthly_posts mp ON mp.year = mu.year AND mp.month = mu.month
LEFT JOIN monthly_active_users mau ON mau.year = mu.year AND mau.month = mu.month
LEFT JOIN monthly_solutions ms ON ms.year = mu.year AND ms.month = mu.month
LEFT JOIN monthly_likes ml ON ml.year = mu.year AND ml.month = mu.month
ORDER BY mu.year, mu.month 

[Konrad_Sopala]

هذا بالضبط ما كنت أبحث عنه! شكرًا مجددًا @michebs! هل فكّرتم في إنشاء مجموعة مفتوحة المصدر لاستعلامات مستكشف البيانات ومشاركتها مع مستخدمين Discourse؟

[codinghorror (Jeff Atwood)]

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

[Konrad_Sopala]

نعم، أعرف، لكن هل هذا شيء يمكنك توسيعه؟ على سبيل المثال، بناءً على الأسئلة هنا من المنتدى.

[rishabh]

يمكننا قبول طلبات السحب (PRs) لإضافة استعلامات، إليك مثال:

FEATURE: Add a poll results query (#43)

إلى جانب ذلك، يحتوي موضوع (Superseded) What cool data explorer queries have you come up with? وقائمة @SidV في discourse-data-explorer/querys.md at queries · SidVal/discourse-data-explorer · GitHub على عدد كبير من الاستعلامات المفيدة أيضًا!

[Konrad_Sopala]

عظيم! شكرًا لمشاركتك ذلك!