Выбор самой последней, самой низкой цены от нескольких поставщиков для элемента инвентаря

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  |
+---------+-----------+-------+------------+

(Обратите внимание: на самом деле созданная_метка - это отметка времени, слова "сегодня" и "вчера" были предоставлены просто для быстрой передачи концепции).

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

Тем не менее, я считаю, что камнем преткновения является огромное количество требований для оператора (или операторов) для обработки:

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

Это кажется простым, но я нашел эту проблему невероятно сложной.

Как примечание, я использую Postgres, поэтому все предоставляемые им возможности доступны для использования (т. Е. Оконные функции).

Answer 1

Намного проще с DISTINCT ON в Postgres:

Текущая цена за единицу для каждого продавца

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

Детальное объяснение:
Выберите первую строку в каждой группе GROUP BY?

Answer 2

Попробуй это

CREATE TABLE #Prices ( Iid INT, Vid INT, Price Money, Created DateTime)
INSERT INTO #Prices 
SELECT 1, 1, 5.99 ,GETDATE()    UNION
SELECT 1, 2, 4.99 ,GETDATE()    UNION
SELECT 2, 1, 6.99 ,GETDATE()    UNION
SELECT 2, 2, 6.99 ,GETDATE()    UNION
SELECT 1, 1, 3.99 ,GETDATE()-1  UNION
SELECT 1, 2, 4.99 ,GETDATE()-1  UNION
SELECT 2, 1, 6.99 ,GETDATE()-1  UNION
SELECT 2, 2, 6.99 ,GETDATE()-1 

WITH CTE AS
(
    SELECT 
        MyPriority = ROW_NUMBER() OVER ( partition by Iid, Vid ORDER BY Created DESC, Price ASC) 
    ,   Iid
    ,   Vid
    ,   price
    ,   Created
    FROM #Prices 
)

SELECT * FROM CTE WHERE MyPriority = 1

Answer 3

Это также возможно сделать с помощью оконных функций, это будет работать на версии SQL Server> 2005:

with cte1 as (
    select
        *,
        row_number() over(partition by vendor_id, item_id order by created_at desc) as row_num
    from prices
), cte2 as (
    select
        *,
        row_number() over(partition by item_id order by price asc) as row_num2
    from cte1
    where row_num = 1
)
select i.title, c.price, c.vendor_id
from cte2 as c
    inner join items as i on i.id = c.item_id
where c.row_num2 = 1;

sql fiddle demo(спасибо, Эрвин)