Ich glaube nicht, dass die von Ihnen aufgeführten Eingaben mit dem gewünschten Ergebnis übereinstimmen. Wenn Sie Daten für einen einzelnen Benutzer erhalten möchten, könnte eine Abfrage wie die folgende als Ausgangspunkt dienen. Sie verwendet Common Table Expressions, um die Abfrage lesbarer und einfacher zu schreiben. Dasselbe Muster kann beibehalten werden, um weitere Daten in die Ergebnisse aufzunehmen.
Eine Sache, bei der ich mir nicht sicher war, ist, ob Sie die Summe der Daten für einen bestimmten Tag oder die Anzahl der Aktionen, die an jedem Tag stattgefunden haben, benötigen. Möchten Sie beispielsweise wissen, dass ein Benutzer am 2019-10-31 insgesamt 20 Beiträge erstellt hat, seit er auf der Website ist, oder möchten Sie wissen, dass der Benutzer am 2019-10-31 genau 3 Beiträge erstellt hat? Die aktuelle Struktur der Abfrage liefert das letztere Ergebnis.
--[params]
-- string :username
-- date :start_date
WITH target_user AS (SELECT id FROM users WHERE username = :username),
days AS (
SELECT day::date
FROM GENERATE_SERIES(:start_date, NOW()::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 = 'regular'
AND t.deleted_at IS NULL
GROUP BY day, tu.id
),
posts_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS posts,
SUM(array_length(regexp_split_to_array(raw, '\s'), 1)) AS word_count
FROM posts p
JOIN target_user tu
ON tu.id = p.user_id
JOIN days
ON p.created_at::date = day
WHERE p.post_type = 1
AND p.deleted_at IS NULL
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 days
ON ua.created_at::date = day
WHERE ua.action_type = 2
GROUP BY day, tu.id
)
SELECT
d.day,
COALESCE(topics, 0) AS topics,
COALESCE(posts, 0) AS posts,
COALESCE(word_count, 0) AS word_count,
COALESCE(likes_received_count, 0) AS likes_received
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
ORDER BY d.day