He creado una base de datos básica (imagen adjunta) Base de datos, estoy tratando de encontrar lo siguiente:
"Importe total medio gastado por usuario en cada mes natural"
Intenté lo siguiente, pero obtuve errores:
SELECT user_id, AVG(total_per_user) FROM (SELECT user_id, ROW_NUMBER() over (ORDER BY total_per_user DESC) AS desc_total, ROW_NUMBER() over (ORDER BY total_per_user ASC) AS asc_total FROM (SELECT EXTRACT(MONTH FROM created_at) AS calendar_month, user_id, SUM(amount) AS total_per_user FROM transactions GROUP BY calendar_month, user_id) AS total_amount ORDER BY user_id) AS a WHERE asc_total IN (desc_total, desc_total+1, desc_total-1) GROUP BY user_id ;En Postgres, podría usar la función agregada percentile_cont() :
select user_id, percentile_cont(0.5) within group(order by total_per_user) median_total_per_user from ( select user_id, sum(amount) total_per_user from transactions group by date_trunc('month', created_at), user_id ) t group by user_id Tenga en cuenta que date_trunc() probablemente esté más cerca de lo que desea que extract(month from ...) , a menos que desee sumar cantidades del mismo mes para diferentes años juntos, que no es como entendí su requisito.
Solo usa percentile_cont() . No entiendo completamente la pregunta. Si desea la mediana del gasto mensual, entonces:
SELECT user_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_per_user ROW_NUMBER() over (ORDER BY total_per_user DESC) AS desc_total, ROW_NUMBER() over (ORDER BY total_per_user ASC) AS asc_total FROM (SELECT DATE_TRUNC('month', created_at) AS calendar_month, user_id, SUM(amount) AS total_per_user FROM transactions t GROUP BY calendar_month, user_id ) um GROUP BY user_id;Hay una función incorporada para la mediana. No hay necesidad de un procesamiento más elegante.