Tengo las siguientes tablas (simplificado a continuación):
Orders:
<id: 1, shipping: 6.0, price: 20.0> <id: 2, shipping: 10.0, price: 30.0> <id: 3, shipping: 7.0, price: 12.0> <id: 4, shipping: 5.0, price: 0.0> #0 dollars because it was updated after return Sales :
<id: 1, order_id: 1, price:10.0, qty:2, date: "2020-06-01T01:16:15-04:00"> <id: 2, order_id: 1, price:9.0, qty: 1, date: "2020-06-01T01:16:15-04:00"> <id: 3, order_id: 2, price:15.0, qty:2, date: "2020-06-01T01:23:53-04:00"> <id: 4, order_id: 3, price:4.0, qty: 1, date: "2020-06-01T20:28:18-04:00"> <id: 5, order_id: 3, price:4.0, qty: 2, date: "2020-06-01T20:31:15-04:00"> <id: 6, order_id: 4, price:29.0, qty:1, date: "2020-06-03T20:16:15-04:00"> Refunds :
<id: 1, order_id: 1, qty:1, amount: 9.0, date: "2020-06-01T01:23:15-04:00"> <id: 2, order_id: 4, qty:1, amount: 29.0, date: "2020-06-04T03:34:53-04:00"> Estoy escribiendo sql sin procesar para calcular el shipping (es decir, sum (orders.shipping)), total orders (ie COUNT (DISTINCT orders.id)) y net sales (ie sales.price * sales.qty - COALESCE (refunds.refund_amount) , 0)) agrupados por días. La búsqueda tomará min_date y max_date en formato: YYYY-MM-DDThh24:mi:ss para filtrar las ventas o reembolsos que no están dentro del rango de fechas. El problema que tengo es usar generate_series para agregar todos los días que no existen en las tablas con los valores establecidos en 0. Entonces, una respuesta de muestra si min_date = 2020-06-01T00: 00: 00 y max_date = 2020-06-05T23:59:59 sería algo como:
"2020-06-01": {shipping: 6, total_orders: 3, net_sales: 62.0}, "2020-06-02": {shipping: 0, total_orders: 0, net_sales: 0}, --> newly added "2020-06-03": {shipping: 5, total_orders: 1, net_sales: 29}, "2020-06-04": {shipping: 0, total_orders: 1, net_sales: -29.0}, "2020-06-05": {shipping: 0, total_orders: 0, net_sales: 0} --> newly added.¿Alguien puede ayudarme a recibir los resultados deseados arriba? He visto ejemplos, pero no puedo hacer que funcione con mi escenario. ¡Gracias!
Creo que esto haría lo que quieres:
select d.dt, o.shipping, s.total_orders, coalesce(s.sales_amount, 0) - coalesce(r.refound_amount, 0) net_sales from generate_series(?::timestamp, ?::timestamp, interval '1 day') d(dt) left join lateral ( select count(distinct order_id) total_orders, sum(price * quantity) sales_amount, array_agg(order_id) order_ids from sales s where s.date >= d.dt and s.date < d.dt + interval '1 day' ) s on true left join lateral ( select sum(o.shipping) shipping from orders o where o.id = any(s.order_ids) ) o on true left join lateral ( select sum(r.amount) refound_amount from refunds r where r.order_id = any(s.order_ids) ) r on true La consulta comienza generando todas las fechas dentro del intervalo dado (el ? representa los dos parámetros de fecha).
Luego, usamos una lateral join con una consulta agregada para traer la información sobre todas las ventas que ocurren dentro del período. Otro later join trae los envíos que corresponden a los order_id s seleccionados por el primer join lateral, y otro trae los reembolsos correspondientes.