Entonces tengo dos tablas SQL, una es una libreta de direcciones del cliente y la otra es un registro de compras.

Tabla de clientes

Cust ID      Cust Name
1            Adam
2            Brian
3            Charles
4            Dave
...

Historial de compras

Customer ID         Price          Date
1                   $100           1996-01-20
1                   $200           1995-01-01
2                   $70            1999-05-22
...

Lo que quiero ver es el nombre del Cliente y el precio de la compra más reciente. Entonces la tabla debería verse así:

 Customer Name      Price      
 Adam               $100
 Brian              $70
 ...

Creo que tengo una idea general de qué funciones usar (como ordenar por, limitar y unir), pero tengo problemas para armarlo todo.

Para empeorar las cosas, necesito encontrar formas de lidiar con los lazos, es decir, si un cliente realiza múltiples compras el mismo día. Por defecto, creo que solo enumeraría el primer precio, pero ¿cómo lo hago para que enumere el precio más alto ese día? O el precio promedio?

2
Vic 18 jun. 2017 a las 22:09

3 respuestas

La mejor respuesta

Puede usar el operador distinct on () de Postgres:

SELECT distinct on (c.cust_id) c.cust_name, p.price, p.purchase_date 
from customer c 
  join purchase p ON c.cust_id = c.customer_id
order by c.cust_id, p.date desc, p.price desc;

Al incluir price desc en order by Postgres elegirá el precio más alto si hay dos precios para un día.

Otra opción es unirse a una tabla derivada (que podría ser más rápida)

select c.cust_id, c.cust_name, p.price, p.purchase_date
from customer c
  join (
     select distinct on (customer_id) customer_id, price, purchase_date
     from purchase
     order by customer_id, purchase_date desc, p.price desc
  ) p on c.cust_id = p.customer_id;
2
a_horse_with_no_name 19 jun. 2017 a las 06:18

La forma estándar de SQL para clasificar registros es RANK o DENSE_RANK (ambos consideran empates) o ROW_NUMBER (que no lo hace).

La siguiente consulta toma el último día de compra y, si hay varias compras, selecciona el registro con el precio más alto. (Y bueno, si hay dos compras con el mismo precio más alto, uno de los registros se selecciona arbitrariamente, pero eso no importa).

select
  customer.cust_name,
  ranked.price 
from customer
join
(
  select
    customer_id,
    price,
    row_number() over (partition by customer_id order by date desc, price desc) as rnk
  from purchase_history
) ranked on ranked.customer_id = customer.cust_id and ranked.rnk = 1;

La siguiente consulta toma las compras del último día de compra y calcula el precio promedio de estas.

select
  customer.cust_name,
  avg(ranked.price) 
from customer
join
(
  select
    customer_id,
    price,
    rank() over (partition by customer_id order by date desc) as rnk
  from purchase_history
) ranked on ranked.customer_id = customer.cust_id and ranked.rnk = 1
group by customer.cust_id, customer.cust_name;
1
Thorsten Kettner 19 jun. 2017 a las 06:15

Dejemos de lado por un minuto el problema de lazos que escribió, comience con lo básico, para poner el nombre en la tabla de compras. Esta es una unión simple:

SELECT c.name, p.price, p.date from purchase as p inner join customer as c
ON c.cust_id = c.customer_id;

Esto le dará una tabla que contiene todas las compras, con el nombre.
Ahora, en esto, puede agregar promedio, suma, máximo o cualquier agregación que desee, por ejemplo:

SELECT name, date, MAX(price) from (
    SELECT c.name, p.price, p.date from purchase as p inner join customer as c
    ON c.cust_id = c.customer_id
) group by name, date;
0
Nir Levy 18 jun. 2017 a las 19:15