J’essaie d’ajouter des paramètres à une requête d’explorateur de données qui permet à un utilisateur d’entrer le nom de la catégorie et de filtrer les résultats.
Voici le code que j’ai utilisé :
-- [paramètres]
-- chaîne :category_name
WITH selected_category AS (
SELECT id
FROM categories
WHERE name = :category_name
LIMIT 1
),
monthly_users AS (
SELECT
date_part('year', created_at) AS year,
date_part('month', created_at) AS month,
COUNT(*) AS nouveaux_utilisateurs_mois
FROM users
GROUP BY 1, 2
),
monthly_posts AS (
SELECT
date_part('year', created_at) AS year,
date_part('month', created_at) AS month,
COUNT(*) AS nombre_posts
FROM posts p
WHERE p.deleted_at IS NULL
AND p.post_type = 1
GROUP BY 1, 2
),
monthly_active_users AS (
SELECT
date_part('year', visited_at) AS year,
date_part('month', visited_at) AS month,
COUNT(DISTINCT user_id) AS nombre_utilisateurs_actifs
FROM user_visits uv
GROUP BY 1, 2
),
monthly_solutions AS (
SELECT
date_part('year', created_at) AS year,
date_part('month', created_at) AS month,
COUNT(*) AS nombre_solutions
FROM user_actions ua
WHERE ua.action_type = 15
GROUP BY 1, 2
),
monthly_likes AS (
SELECT
date_part('year', created_at) AS year,
date_part('month', created_at) AS month,
COUNT(*) AS nombre_likes
FROM user_actions ua
WHERE ua.action_type = 2
GROUP BY 1, 2
),
monthly_topics AS (
SELECT
date_part('year', t.created_at) AS year,
date_part('month', t.created_at) AS month,
COUNT(*) AS nombre_topics
FROM topics t
JOIN users us ON us.id = t.user_id
JOIN selected_category sc ON t.category_id = sc.id
WHERE t.deleted_at IS NULL
AND t.archetype = 'regular'
AND t.closed = false
AND t.archived = false
AND t.visible = true
AND us.username_lower != 'system'
GROUP BY 1, 2
)
SELECT
mu.year,
mu.month,
SUM(mu.nouveaux_utilisateurs_mois) OVER (ORDER BY mu.year, mu.month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_utilisateurs,
mp.nombre_posts,
COALESCE(mau.nombre_utilisateurs_actifs, 0) AS nombre_utilisateurs_actifs,
COALESCE(ms.nombre_solutions, 0) AS nombre_solutions,
COALESCE(ml.nombre_likes, 0) AS nombre_likes,
COALESCE(mt.nombre_topics, 0) AS nombre_topics
FROM monthly_users mu
LEFT JOIN monthly_posts mp ON mp.year = mu.year AND mp.month = mu.month
LEFT JOIN monthly_active_users mau ON mau.year = mu.year AND mau.month = mu.month
LEFT JOIN monthly_solutions ms ON ms.year = mu.year AND ms.month = mu.month
LEFT JOIN monthly_likes ml ON ml.year = mu.year AND ml.month = mu.month
LEFT JOIN monthly_topics mt ON mt.year = mu.year AND mt.month = mu.month
ORDER BY mu.year, mu.month
Cela ne semble pas fonctionner et j’ai du mal à comprendre pourquoi - toute aide serait appréciée (notez que je veux le nom, les utilisateurs ne connaîtront pas nécessairement leur ID de catégorie)
