مساء الخير،
أود استقصاء أسماء مستخدمين محددة لمعرفة عدد الإعجابات التي منحوها، والإعجابات التي تلقوها، والمنشورات، والحلول، خلال نطاق زمني محدد مقسّم حسب الأيام. هل لديكم أي اقتراحات حول هذا النوع من التقارير؟ أعتقد أنني سأحتاج إلى استخدام إضافة مستكشف البيانات، فهل لدى أحدكم استعلام جاهز لهذا النوع من العمل؟ أي ملاحظات منكم موضع تقدير!
كان لدي استعلام مشابه جدًا لما تبحث عنه على موقع التطوير المحلي الخاص بي. قمت بتعديله قليلاً ليتوافق مع متطلباتك. لقد حاولت في هذا التعديل تمكين تشغيل الاستعلام ضد إما نشاط حدث في المواضيع العادية، أو نشاط حدث في الرسائل الخاصة. كان جزء من دافعي لذلك هو رغبي في تتبع نشاطي الخاص خلال فترة زمنية معينة. إن القدرة على الاستعلام بناءً على المواضيع العادية أو الرسائل الخاصة تبدو منطقية لحالتي. الطريقة التي يتعامل بها الاستعلام مع الحلول عند تحديد خانة اختيار personal_messages ليست مثالية — حيث سيعود عمود solutions_provided بقيمة NULL في هذه الحالة. ربما يجب إزالة خيار التبديل بين المواضيع العادية والرسائل الخاصة من الاستعلام.
هذا الاستعلام ليس معقدًا بشكل مفرط، لكنه طويل نسبيًا. إذا لاحظت أي مشاكل في نتائجه، يرجى إخباري بذلك.
يتطلب الاستعلام تزويده بثلاثة معاملات قبل تشغيله: username، وstart_date، وend_date. يجب أن تكون معاملات التاريخ بالصيغة ‘yyyy-mm-dd’. على سبيل المثال 2020-03-13. المعامل personal_messages افتراضيًا على قيمة false. قم بتحديد خانة اختيار personal_messages في الاستعلام إذا كنت ترغب في رؤية النتائج المتعلقة بالنشاط الذي حدث في الرسائل الخاصة.
--[params]
-- string :username
-- date :start_date
-- date :end_date
-- boolean :personal_messages = false
WITH target_user AS (SELECT id FROM users WHERE username = :username),
days AS (
SELECT day::date
FROM GENERATE_SERIES(:start_date, :end_date, INTERVAL '1 day') AS day
),
topics_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS topics
FROM topics t
JOIN target_user tu
ON tu.id = t.user_id
JOIN days
ON t.created_at::date = day
WHERE t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END
AND t.deleted_at IS NULL
GROUP BY day, tu.id
),
posts_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS posts
FROM posts p
JOIN target_user tu
ON tu.id = p.user_id
JOIN topics t
ON t.id = p.topic_id
JOIN days
ON p.created_at::date = day
WHERE p.post_type = 1
AND t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END
AND p.deleted_at IS NULL
AND p.post_number > 1
GROUP BY day, tu.id
),
likes_received AS (
SELECT tu.id,
day,
COUNT(tu.id) AS likes_received_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN topics t
ON t.id = ua.target_topic_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 2
AND t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END
GROUP BY day, tu.id
),
likes_given AS (
SELECT tu.id,
day,
COUNT(tu.id) AS likes_given_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN topics t
ON t.id = ua.target_topic_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 1
AND t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END
GROUP BY day, tu.id
),
solutions_provided AS (
SELECT tu.id,
day,
COUNT(tu.id) AS solutions_provided_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 15
GROUP BY day, tu.id
)
SELECT
d.day,
COALESCE(topics, 0) AS topics,
COALESCE(posts, 0) AS posts,
COALESCE(likes_received_count, 0) AS likes_received,
COALESCE(likes_given_count, 0) AS likes_given,
(SELECT CASE WHEN NOT :personal_messages THEN COALESCE(solutions_provided_count, 0) END) AS solutions_provided
FROM days d
LEFT JOIN topics_created tc
ON tc.day = d.day
LEFT JOIN posts_created pc
ON pc.day = d.day
LEFT JOIN likes_received lc
ON lc.day = d.day
LEFT JOIN likes_given lg
ON lg.day = d.day
LEFT JOIN solutions_provided sp
ON sp.day = d.day
ORDER BY d.day DESC
هذا مثالي! شكرًا لك @simon!