SQL 按日期加入和排序单个主题

Question

所以我有两张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
 ...

我想我对使用哪些函数(如排序方式、限制和连接)有一个大致的了解，但我无法将它们组合在一起。

更糟糕的是，我需要找出处理联系的方法，这意味着如果客户在同一天进行多次购买。默认情况下，我认为它只会列出第一个价格，但我如何才能让它列出当天的最高价格？还是平均价？

Answer 1

您可以使用 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;

通过在 order by 中包含 price desc 如果一天有两个价格，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;