SQL объединение и упорядочение индивидуальных субъектов по дате

Таким образом, у меня есть две таблицы SQL, одна из которых является адресной книгой клиента, а другая-журналом покупок.



Таблица Клиентов 



Cust ID      Cust Name

1            Adam

2            Brian

3            Charles

4            Dave

...


История Покупок 



Customer ID         Price          Date

1                   $100           1996-01-20

1                   $200           1995-01-01

2                   $70            1999-05-22

...


То, что я хочу видеть, - это имя клиента и цена самой последней покупки.
Таким образом, таблица должна выглядеть следующим образом: 

 Customer Name      Price      

 Adam               $100

 Brian              $70

 ...


Я думаю, что у меня есть общее представление о том, какие функции использовать (например, order by, limit и join), но мне трудно собрать все это вместе.

Чтобы усугубить ситуацию, мне нужно выясните, как справиться со связями, то есть, если в один и тот же день клиент совершает несколько покупок. По умолчанию, я думаю, что он будет просто перечислять первую цену, но как я могу сделать так, чтобы он указывал самую высокую цену в этот день? Или средняя цена?

639   3  

3 ответов:

Можно использовать оператор Postgres' distinct on (): 

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;

Включив price desc в order by Postgres выберет самую высокую цену, если есть две цены за один день.

Другой вариант-присоединиться к производной таблице (что может быть быстрее) 

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  
2017-06-19 09:18:05

Стандартный способ ранжирования записей SQL-это RANK или DENSE_RANK (оба учитывают связи) или ROW_NUMBER (что не так).

Следующий запрос принимает последний день покупки, и если есть несколько покупок, он выбирает запись с более высокой ценой. (И хорошо, если есть две покупки с одинаковой самой высокой ценой, одна из записей выбирается произвольно, но это не имеет значения.) 
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;

Следующий запрос принимает покупки последнего дня покупки и вычисляет среднюю цену покупки. эти.

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  
2017-06-19 09:15:35

Давайте отложим на минуту вопрос о связях, который вы написали, начнем с основ, чтобы получить имя в таблице покупок. Это простое соединение: 

SELECT c.name, p.price, p.date from purchase as p inner join customer as c
ON c.cust_id = c.customer_id;
Это даст вам таблицу, содержащую все покупки, с именем.
Теперь вы можете добавить среднее, сумму, максимум или любую другую агрегацию, например: 
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  
2017-06-18 22:15:55

Comments

    Ничего не найдено.
Click here to cancel reply.