Ich suche Hilfe bei der Änderung dieser SQL-Abfrage, um auch die Anzahl der Lösungen für die Kategorien und die Anzahl der Antworten aufzunehmen:
-- [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
Derzeit ist die Ausgabe für diese Abfrage wie folgt, was sehr nützlich ist, aber ich möchte diese anderen Metriken einbeziehen.
Ich habe versucht, damit zu basteln, aber meine SQL-Kenntnisse sind sehr schlecht – Wenn mir jemand in die richtige Richtung weisen oder bei der Abfrage helfen könnte, wäre ich sehr dankbar
Ich konnte mit etwas KI-Hilfe etwas zum Laufen bringen, das meinem eigenen Kontext entspricht (Sie müssen möglicherweise die solved-Referenz anpassen).
Ich teile die endgültigen Lösungen, falls andere sie auch nützlich finden könnten.
Die erste Iteration:
-- [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
Die endgültige Lösung für meinen spezifischen Anwendungsfall:
-- [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
,
