mysql - 从多个供应商中为库存项目选择最新的最低价格

Question

我对 SQL 相当精通，但是这个问题让我自己困惑了很长一段时间。从最基本的意义上讲，只有两个表:

Items
+----+--------+
| id | title  |
+----+--------+
|  1 | socks  |
|  2 | banana |
|  3 | watch  |
|  4 | box    |
|  5 | shoe   |
+----+--------+

...和价格表:

Prices
+---------+-----------+-------+------------+
| item_id | vendor_id | price | created_at |
+---------+-----------+-------+------------+
|       1 |         1 | 5.99  | Today      |
|       1 |         2 | 4.99  | Today      |
|       2 |         1 | 6.99  | Today      |
|       2 |         2 | 6.99  | Today      |
|       1 |         1 | 3.99  | Yesterday  |
|       1 |         1 | 4.99  | Yesterday  |
|       2 |         1 | 6.99  | Yesterday  |
|       2 |         2 | 6.99  | Yesterday  |
+---------+-----------+-------+------------+

(请注意:created_at 实际上是一个时间戳，“今天”和“昨天”只是为了快速传达概念)。

我的目标是返回一个简单的结果，其中包含与最新最低价格关联的库存项目，包括对提供上述价格的 vendor_id 的引用。

但是，我发现绊脚石似乎是要处理的语句(或语句)的绝对数量要求:

• 每件商品都有多个供应商，因此我们需要确定每个商品的所有供应商之间的价格最低
• 项目的新价格会定期附加，因此我们只想考虑每个供应商的每个项目的最新价格
• 我们希望将所有这些汇总到一个结果中，每行一个项目，其中包括项目、价格和供应商

看似简单，但我发现这个问题异常困难。

请注意，我使用的是 Postgres，因此它提供的所有功能都可供使用(即:窗口函数)。

Answer 1

在 Postgres 中使用 DISTINCT ON 更简单:

每个供应商每件商品的当前价格

SELECT DISTINCT ON (p.item_id, p.vendor_id)
       i.title, p.price, p.vendor_id
FROM   prices p
JOIN   items  i ON i.id = p.item_id
ORDER  BY p.item_id, p.vendor_id, p.created_at DESC;

每个项目的最佳供应商

SELECT DISTINCT ON (item_id) 
       i.title, p.price, p.vendor_id -- add more columns as you need
FROM (
   SELECT DISTINCT ON (item_id, vendor_id)
          item_id, price, vendor_id -- add more columns as you need
   FROM   prices p
   ORDER  BY item_id, vendor_id, created_at DESC
   ) p
JOIN   items i ON i.id = p.item_id
ORDER  BY item_id, price;

->SQLfiddle demo

详细解释:
Select first row in each GROUP BY group?