Je ne pense pas que les entrées que vous avez listées correspondent à la sortie que vous espérez obtenir. Si vous souhaitez obtenir des données pour un seul utilisateur, une requête comme celle ci-dessous pourrait servir de point de départ. Elle utilise des expressions de table commune (CTE) pour rendre la requête plus facile à lire et à écrire. Le même modèle pourrait être suivi pour ajouter plus de données aux résultats.
Une chose dont je n’étais pas certain est de savoir si vous voulez la somme des données pour une journée donnée, ou le nombre d’actions qui ont eu lieu chaque jour. Par exemple, voulez-vous savoir que le 2019-10-31, un utilisateur a créé un total de 20 messages sur tous les jours où il a été sur le site, ou voulez-vous savoir que le 2019-10-31, l’utilisateur a créé exactement 3 messages ? La façon dont la requête est actuellement structurée, elle renvoie ce dernier résultat.
--[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