Постгрес SQL. Выберите столбец, который коррелирует со значением в агрегатной функции
Вот таблица «items», содержащая более 10 строк:
+-----+-----------+-----------+----------+
| id | item_name | category | quantity |
+=====+===========+===========+==========+
| 3 | item33 | category1 | 5 |
+-----+-----------+-----------+----------+
| 2 | item52 | category5 | 1 |
+-----+-----------+-----------+----------+
| 1 | item46 | category1 | 3 |
+-----+-----------+-----------+----------+
| 4 | item11 | category3 | 2 |
+-----+-----------+-----------+----------+
| ... | ... | ... | ... |
+-----+-----------+-----------+----------+
Значения в столбце «элементы» уникальны, значения в столбце «категория» — не уникальны.
Задача:
- Удалить дубликаты категорий: если категория содержит более 1 элемента, брать строку с минимальным «id».
- Упорядочить результаты по «количеству» (ASC).
- Возьмите 10 строк: первые 5 и случайные 5 из остальных результатов вывода данных.
Итак, таблица заказов (после подзадачи №2) должна выглядеть так:
+-----+-----------+-----------+----------+
| id | item_name | category | quantity |
+=====+===========+===========+==========+
| 2 | item52 | category5 | 1 |
+-----+-----------+-----------+----------+
| 4 | item11 | category3 | 2 |
+-----+-----------+-----------+----------+
| 1 | item46 | category1 | 3 |
+-----+-----------+-----------+----------+
| ... | ... | ... | ... |
+-----+-----------+-----------+----------+
Я знаю, как исключить дубликаты для категорий:
SELECT min(id) as id, category
FROM items
GROUP BY category
Но я не знаю, как заказать его по количеству. Если я попытаюсь добавить «количество» в строку «выбрать», а затем сделать «ЗАКАЗАТЬ ПО количеству», я получаю сообщение об ошибке: «столбец «количество» должен отображаться в предложении GROUP BY или использоваться в агрегатной функции».
Если есть способ добавить этот столбец «количество» в вывод данных (значение в этом столбце должно коррелировать с результирующим значением «id» (т.е. «min (id)»))? А затем сделать заказ и выбор строк...
Ответы 3
Вам нужно использовать аналитическую функцию следующим образом:
Select * from
(Select t.*,
Row_number() over (order by quantity) as rn_q
from
(Select t.*,
Row_number() over (partition by category order by id) as rn
From your_table) t
Where rn = 1) t
Order by case when rn_q <= 5 then quantity else 6 end;
Рассмотрите возможность присоединения вашего агрегированного запроса к данным на уровне единиц для всех столбцов, включая quantity:
SELECT i.id, i.item_name, i.category, i.quantity
FROM items i
INNER JOIN
(SELECT category, min(id) AS min_id
FROM items
GROUP BY category) agg
ON i.id = agg.min_id
AND i.category = agg.category
ORDER BY i.quantity
Для разделения 5 лучших и случайных 5 интегрируйте объединение с CTE, чтобы сохранить набор результатов:
WITH sub AS (
SELECT i.id, i.item_name, i.category, i.quantity
FROM items i
INNER JOIN
(SELECT category, min(id) AS min_id
FROM items
GROUP BY category) agg
ON i.id = agg.min_id
AND i.category = agg.category
)
-- TOP 5 ROWS
SELECT id, item_name, category, quantity
FROM sub
ORDER BY i.quantity
LIMIT 5
UNION
-- RANDOM ROWS OF NON-TOP 5
SELECT id, item_name, category, quantity
FROM
(SELECT id, item_name, category, quantity
FROM sub
ORDER BY i.quantity
OFFSET 5) below5
ORDER BY random()
LIMIT 5
Приятно слышать и рад помочь! Это решение также работает в других СУБД и не ограничивается методами диалекта Postgres. Удачного SQL!
По сути, DISTINCT ON хорошо работает в Postgres. Видеть:
Простое (правильное!) решение:
WITH dist_cat AS (
SELECT t, row_number() OVER (ORDER BY quantity, id) AS rn -- added id as tiebreaker
FROM (
SELECT DISTINCT ON (category) *
FROM tbl
ORDER BY category, id
) t -- distinct categories
ORDER BY ORDER BY quantity, id -- match sort for row_number()
)
SELECT (t).*
FROM dist_cat
WHERE rn <= 5
UNION ALL -- not just UNION
( -- parentheses required
SELECT (t).*
FROM dist_cat
WHERE rn > 5
ORDER BY random()
LIMIT 5
);
Добавлено id в качестве разрешения на сортировку, так как сортировка по quantity вряд ли является детерминированной. Поместите туда любое уникальное выражение, соответствующее вашим требованиям. Или пропустите его, если вы согласны с произвольными результатами, которые могут меняться при каждом вызове.
Тип строки t предназначен для удобства, поэтому нам не нужно указывать все имена столбцов по буквам, и при этом избавиться от добавленного rn в результате, который не был запрошен.
Я решил упорядочить строки в CTE и добавить номер строки rn, чтобы избежать дополнительной операции сортировки.
Дополнительные 5 случайных строк выбираются действительно случайным образом, а не просто произвольно.
Используйте UNION ALL, а не только UNION. Потому что это правильно для того, чем мы занимаемся, и к тому же дешевле. Но также для сохранения порядка сортировки из CTE; UNION может напортачить, пытаясь удалить дубликаты - тщетно.
Для больших таблиц, в зависимости от распределения данных, могут быть (намного) более быстрые методы...
... для получения уникальных категорий:
.. для получения случайных строк:
Это решение работает лучше всего для меня, потому что с моим небольшим знанием Postgres я могу, по крайней мере, понять этот код: D Спасибо за помощь всем, несмотря ни на что. Я очень ценю это.