Consulta por rango de fechas con muchas consultas anidadas

publicado por: Anonymous

Tengo que obtener una lista con los clientes de la empresa a los que hemos facturado algo durante el presente año, indicando cuánto hemos facturado cada mes.

Tengo dos tablas: la tabla de clientes (customer) y la tabla de facturas (invoice). La relación es la evidente: un cliente puede tener muchas facturas, y cada factura está relacionada con un único cliente.

Lo único que se me ha ocurrido es lo siguiente:

select id, 
name as "Nombre",
identity_number as "CIF", 
(select sum(total) 
    from invoice 
    where payment_date between '2017-01-01' and '2017-02-01' and customer_id=c.id group by customer_id) as "Enero",
(select sum(total) 
    from invoice 
    where payment_date between '2017-02-01' and '2017-03-01' and customer_id=c.id group by customer_id) as "Febrero",
(select sum(total) 
    from invoice 
    where payment_date between '2017-03-01' and '2017-04-01' and customer_id=c.id group by customer_id) as "Marzo",
(select sum(total) 
    from invoice 
    where payment_date between '2017-04-01' and '2017-05-01' and customer_id=c.id group by customer_id) as "Abril",
(select sum(total) 
    from invoice 
    where payment_date between '2017-05-01' and '2017-06-01' and customer_id=c.id group by customer_id) as "Mayo",
(select sum(total) 
    from invoice 
    where payment_date between '2017-06-01' and '2017-07-01' and customer_id=c.id group by customer_id) as "Junio",
(select sum(total) 
    from invoice 
    where payment_date between '2017-07-01' and '2017-08-01' and customer_id=c.id group by customer_id) as "Julio",
(select sum(total) 
    from invoice 
    where payment_date between '2017-08-01' and '2017-09-01' and customer_id=c.id group by customer_id) as "Agosto",
(select sum(total) 
    from invoice 
    where payment_date between '2017-09-01' and '2017-10-01' and customer_id=c.id group by customer_id) as "Septiembre",
(select sum(total) 
    from invoice 
    where payment_date between '2017-10-01' and '2017-11-01' and customer_id=c.id group by customer_id) as "Octubre",
(select sum(total) 
    from invoice 
    where payment_date between '2017-11-01' and '2017-12-01' and customer_id=c.id group by customer_id) as "Noviembre",
(select sum(total) 
    from invoice 
    where payment_date between '2017-12-01' and '2017-12-31' and customer_id=c.id group by customer_id) as "Diciembre"
from customer c
where c.branding_id=1 and (select sum(total) 
    from invoice 
    where payment_date between '2017-01-01' and '2017-12-31' and customer_id=c.id group by customer_id) > 0

Esto funciona, pero lo encuentro extremadamente ineficiente (hay 13 subconsultas, si incluimos la del WHERE). ¿Hay algún modo de simplificar esta consulta o es la mejor forma de obtener los datos?

solución

Basado en la respuesta de @amenadiel (que es correcta para MySQL), he creado la siguiente consulta:

select c.id, 
c.name as "Nombre",
c.identity_number as "CIF", 
  SUM(CASE WHEN date_part('month',i.payment_date)=12 then total else 0 end ) as diciembre,
  SUM(CASE WHEN date_part('month',i.payment_date)=11 then total else 0 end) as noviembre,
  ...
  SUM(CASE WHEN date_part('year',i.payment_date)=2017 then total else 0 end) as TOTAL
from customer c join invoice i on c.id=i.customer_id
where date_part('year',i.payment_date)=2017 and c.branding_id=2
group by c.id
order by c.id

Como curiosidad, la consulta de la pregunta necesitaba casi dos minutos en ejecutarse mientras que esta solución tarda alrededor de 300ms en mostrar los mismos resultados

Respondido por: Anonymous

Leave a Reply

Your email address will not be published. Required fields are marked *