У меня есть таблица Postgres с данными о продуктах разных производителей, здесь упрощенная структура таблицы:
CREATE TABLE test_table (
sku text,
manufacturer_name text,
price double precision,
stock int
);
INSERT INTO test_table
VALUES ('sku1', 'Manufacturer1', 110.00, 22),
('sku1', 'Manufacturer2', 120.00, 15),
('sku1', 'Manufacturer3', 130.00, 1),
('sku1', 'Manufacturer3', 30.00, 11),
('sku2', 'Manufacturer1', 10.00, 2),
('sku2', 'Manufacturer2', 9.00, 3),
('sku3', 'Manufacturer2', 21.00, 3),
('sku3', 'Manufacturer2', 1.00, 7),
('sku3', 'Manufacturer3', 19.00, 5);
Мне нужно вывести каждого производителя для каждого артикула, но если есть несколько идентичных производителей для одного и того же артикула, мне нужно выбрать производителя с самой низкой ценой (обратите внимание, что мне также нужно включить столбец «запас»), здесь желаемые результаты:
| sku | man1_price | man1_stock | man2_price | man2_stock | man3_price | man3_stock |
|------|------------|------------|------------|------------|------------|------------|
| sku1 | 110.0 | 22 | 120.0 | 15 | 30.0 | 11 |
| sku2 | 10.0 | 2 | 9.0 | 3 | | |
| sku3 | | | 1.0 | 7 | 19.0 | 5 |
Я пытался использовать Postgres crosstab()
:
SELECT *
FROM crosstab('SELECT sku, manufacturer_name, price
FROM test_table
ORDER BY 1,2',
$$ SELECT DISTINCT manufacturer_name FROM test_table ORDER BY 1 $$
)
AS ct (sku text, "man1_price" double precision,
"man2_price" double precision,
"man3_price" double precision
);
Но это создает таблицу только с одним столбцом price
. И я не нашел способа включить столбец stock
.
Я также пытался использовать условную агрегацию:
SELECT sku,
MIN(CASE WHEN manufacturer_name = 'Manufacturer1' THEN price END) as man1_price,
MIN(CASE WHEN manufacturer_name = 'Manufacturer1' THEN stock END) as man1_stock,
MIN(CASE WHEN manufacturer_name = 'Manufacturer2' THEN price END) as man2_price,
MIN(CASE WHEN manufacturer_name = 'Manufacturer2' THEN stock END) as man2_stock,
MIN(CASE WHEN manufacturer_name = 'Manufacturer3' THEN price END) as man3_price,
MIN(CASE WHEN manufacturer_name = 'Manufacturer3' THEN stock END) as man3_stock
FROM test_table
GROUP BY sku
ORDER BY sku
И этот запрос тоже не работает в моем случае - он просто выбирает минимальный уровень запаса - но если есть несколько одинаковых производителей для одного и того же артикула, но с разными ценами/наличиями - этот запрос выбирает минимальную цену от одного производителя и минимальный запас от Другой.
Как я могу вывести price
каждого производителя и соответствующий stock
из этой таблицы?
P.S. Спасибо всем за такие полезные ответы. Моя таблица Postgres довольно мала - там не более 15 тысяч продуктов (я не знаю, могут ли такие числа быть полезными для правильного сравнения), но поскольку Эрвин Брандштеттер попросил сравнить производительность разных запросов, я выполнил 3 запроса с EXPLAIN ANALYZE
, вот время их выполнения:
Erwin Brandstetter query: 400 - 450 ms
Kjetil S query: 250 - 300 ms
Gordon Linoff query: 200 - 250 ms
a_horse_with_no_name query: 250 - 300 ms
Опять же - я не уверен, что эти цифры могут быть полезны в качестве справки. Для моего случая я выбрал комбинированный вариант запросов Kjetil S
и Gordon Linoff
, но варианты Erwin Brandstetter
и a_horse_with_no_name
также очень полезны и интересны. Стоит отметить, что если в моей таблице в будущем окажется больше, чем несколько производителей - корректировка запроса и ввод их имен каждый раз будет утомительным - и, следовательно, запрос из a_horse_with_no_name
ответа будет наиболее удобным для использования.