تعديل تقرير لتضمين مقاييس الردود والمحلولة

[Danny_Dainton]

مرحباً بالفريق ،

أبحث عن بعض المساعدة لتعديل استعلام SQL هذا ليشمل أيضًا عدد الحلول للفئات وعدد الردود:

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-19

SELECT c.id category_id, COUNT(DISTINCT(t.id)) topics, COUNT(p.id) posts, sum(p.like_count) likes, sum(p.reads) reads
FROM categories c
INNER JOIN topics t ON (t.category_id = c.id)
INNER JOIN posts p ON (p.topic_id = t.id AND p.post_type = 1)
WHERE p.created_at BETWEEN :start_date AND :end_date
GROUP BY c.id
ORDER BY COUNT(p.id) DESC

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

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

[Danny_Dainton]

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

أشارك الحلول النهائية في حال تمكن الآخرين من إيجاد فائدة لهذا أيضًا.

التكرار الأول:

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-19

SELECT
  c.id AS category_id,
  COUNT(DISTINCT t.id) AS topics,
  COUNT(p.id) AS posts,
  SUM(p.like_count) AS likes,
  SUM(p.reads) AS reads,
  COUNT(DISTINCT CASE WHEN tf.name = 'accepted_solution_id' THEN t.id END) AS solutions,
  COUNT(CASE WHEN p.reply_to_post_number IS NOT NULL THEN p.id END) AS replies
FROM categories c
INNER JOIN topics t ON t.category_id = c.id
INNER JOIN posts p ON p.topic_id = t.id AND p.post_type = 1
LEFT JOIN topic_custom_fields tf ON t.id = tf.topic_id AND tf.name = 'accepted_solution_id'
WHERE p.created_at BETWEEN :start_date AND :end_date
GROUP BY c.id
ORDER BY COUNT(p.id) DESC

الحل النهائي لحالتي الاستخدام المحددة:

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-19

SELECT
  c.id AS category_id,
  COUNT(DISTINCT t.id) AS topics,
  COUNT(p.id) AS posts,
  SUM(p.like_count) AS likes,
  SUM(p.reads) AS reads,
  COUNT(DISTINCT CASE WHEN ua.action_type = 15 THEN t.id END) AS solutions,
  COUNT(CASE WHEN p.reply_to_post_number IS NOT NULL THEN p.id END) AS replies
FROM categories c
INNER JOIN topics t ON t.category_id = c.id
INNER JOIN posts p ON p.topic_id = t.id AND p.post_type = 1
LEFT JOIN user_actions ua ON ua.target_topic_id = t.id AND ua.action_type = 15
WHERE p.created_at BETWEEN :start_date AND :end_date
GROUP BY c.id
ORDER BY COUNT(p.id) DESC