Выберите уникальный второй столбец для каждого первого столбца с минимальным значением третьего столбца
Используя postgres, возможно ли или как выбрать из таблицы ниже разные значения id и id2? :
Мне нужно выбрать разные значения идентификатора, например, id2 также различен, а различия между a и b, а затем a1 и b2 минимальны.
В приведенном выше примере это должно быть только две строки, поскольку в id2 есть только два различных значения.
Правильный
Я пытался заставить это работать через
distinct on(rank() over (order by b - a, a1 - b1, id)
Даже если я попытаюсь выбрать дважды, например, выбрать вместо выбора, это не сильно поможет, поэтому я немного застрял.
На данный момент я получил только это, что не верно:
Неправильный
with data as (
select 1 id, 1 as id2, 150 a, 200 b, 200 a1, 150 b1
union
select 1 id, 2 as id2, 150 a, 150 b, 200 a1, 200 b1
union
select 1 id, 3 as id2, 150 a, 100 b, 200 a1, 100 b1
union
select 1 id, 4 as id2, 150 a, 150 b, 200 a1, 200 b1
union
select 2 id, 1 as id2, 150 a, 200 b, 200 a1, 150 b1
union
select 2 id, 2 as id2, 150 a, 150 b, 200 a1, 200 b1
union
select 2 id, 3 as id2, 150 a, 100 b, 200 a1, 100 b1
union
select 2 id, 4 as id2, 150 a, 150 b, 200 a1, 200 b1
union
select 3 id, 1 as id2, 150 a, 200 b, 200 a1, 150 b1
union
select 3 id, 4 as id2, 150 a, 150 b, 200 a1, 200 b1
union
select 3 id, 3 as id2, 150 a, 100 b, 200 a1, 100 b1
union
select 3 id, 2 as id2, 150 a, 150 b, 200 a1, 200 b1
)
select *
from data
where b >= a and b1 >= a1
order by id2, b - a, a1 - b1, rank() over (order by id2, id);
И только следующие пять пар для (id, id2, a, b) = (1|4|1|1), (1|5|1|3), (2|4|1|3), (2 |6|1|1), (3|4|1|10), какую комбинацию вы бы хотели сейчас? Если мы выберем (1|4|1|1) для идентификатора 1 и (2|6|1|1) для идентификатора 2, то мы не сможем взять (3|4|1|10) и в итоге получить сумму (b-a) равно 0. Или, если это возможно, мы должны использовать все идентификаторы? Тогда мы получим (1|5|1|3), (2|6|1|1) и (3|4|1|10) с суммой (b-a) = 11. Итак, каковы точные правила , какие строки выбрать?
Ваш пример не очень хорош, потому что, поскольку все значения одинаковы, мы не видим, чтобы вы принимали решение относительно определенных строк в зависимости от различий. В любом случае вы получите рекурсивный запрос для решения этой проблемы в SQL.
@ThorstenKettner, вопрос, будет ли это нормально, на самом деле я сортировал по разнице, чтобы достичь наименьшей разницы между b и a, поэтому поиск неконфликтного минимума является частью цели, верно. Согласно вашему второму вопросу, да, в результате нам нужно получить максимально возможные пары идентификаторов. Может быть, это помогает, но обычно набор id2 повторяется для каждого идентификатора, поскольку я в основном выбираю лучший вариант между двумя таблицами на основе некоторых критериев выбора (например, раздела), как вы можете видеть в моем примере.
кроме того, id связан с a, а id2 с b, они из одних и тех же строк, поэтому для id2=4 невозможно иметь разные значения, такие как 1 и 3, 10 для b. если мы проигнорируем это, то (1|5|1|3), (2|6|1|1) и (3|4|1|10) вполне подходят как результат
Ты потерял меня. Это не один стол, а два стола? Один с уникальным столбцом id и другим столбцом a, а другой с уникальным столбцом id2 и еще одним столбцом b? И таблица, которую вы показываете, на самом деле является результатом запроса двух таблиц? Да? Идентификаторы уникальны в соответствующей таблице, поэтому мы всегда будем видеть одно и то же значение a для id и одно и то же значение b для id2? Как a1 и a2 вступают в игру? Они из тех же таблиц или из других таблиц? Является ли таблица, которую вы показываете, просто декартовым произведением таблиц table1 и table2, то есть всех возможных комбинаций строк?
Например. если вы добавите строку (4, 4 и т. д.), ожидаете ли вы, что она будет возвращена или нет? Почему?
@jarlh: Они отклонили (3, 4, ...), поэтому они также отклонят (4, 4, ...), потому что для id2 = 4 у них уже есть (3, 4, ...) в Результаты. В любом случае, комментарий «id связан с a, а id2 с b, они из одних и тех же строк», по-видимому, указывает на некоторое декартово (?) соединение двух таблиц здесь, создавая показанные строки, что делает определенные комбинации невозможными («это невозможно, чтобы id2=4 имел разные значения, например 1 и 3, 10 для b"). Я подожду ответа, прежде чем думать о решении :-)
@ThorstenKettner, извините за отсутствие подробностей, я пытался создать что-то простое в качестве примера. Да, вы совершенно правы, id, a, a1 из «первой таблицы», а id1, b, b1 из «второй». На самом деле это всего лишь примеры. Кстати, что вы подразумеваете под «рекурсивным запросом», не могли бы вы показать какую-нибудь ссылку?
@Павел: Спасибо за разъяснение. Я подумаю над ответом. Если вы хотите узнать о рекурсивных запросах: postgresqltutorial.com/postgresql-tutorial/…
Проблема в том, что «первая» и «вторая» таблицы не полностью равны (т.е. данные не соответствуют напрямую или не равны друг другу), поэтому их нельзя просто соединить напрямую.
@ThorstenKettner Я придумал использовать lag() для каждого из двух идентификаторов, чтобы выбрать уникальные, но, вероятно, все равно не могу обработать все случаи
Дайте мне время подумать:-)
Ответы 1
К сожалению, вы допустили довольно распространенную ошибку: вы попытались написать запрос, прежде чем разобрались в алгоритме. Сначала продумайте, что именно должен делать запрос. Только после этого пишите запрос.
Ниже я попытаюсь сначала получить алгоритм, а затем запрос.
В комментариях к запросу вы пояснили, что показанная таблица на самом деле представляет собой две таблицы. Чтобы избежать двусмысленности, я буду называть идентификатор столбца первой таблицы id1. Это дает нам две таблицы:
- t1 (id1, а, а1)
- t2 (id2, b, b1).
Кандидаты
Конечным результатом является комбинация строк. В вашем случае одна строка для (id1, id2) = (1, 2), а другая — для (id1, id2) = (2, 4). ID1 3 исключен из набора результатов. Это означает, что набор результатов не обязательно включает весь идентификатор id1.
Если бы таблица t1 содержала только id1 = 1, а таблица t2 только id2 = 1 и 2, мы бы получили объединенный результат:
Ваше требование: «Мне нужно выбрать разные значения id1», тогда очевидно, что мы включим только один из двух id2 и отклоним другой. Это означает, что набор результатов не обязательно включает в себя все идентификаторы id2.
Когда мы соединяем строки из t1 и t2, мы можем получить любые объединенные a и b, а также любые объединенные a1 и b1. Ваш запрос показывает, что вы хотите применить здесь два правила: b должно быть больше или равно a, а b1 должно быть больше или равно a1.
Из следующего результата объединения
мы бы исключили следующие идентификаторы из строк результатов-кандидатов, поскольку все их объединенные строки имеют отрицательный результат (b-a):
- идентификатор1 = 2
- идентификатор1 = 3
- идентификатор2 = 4
Как объяснялось ранее и показано сейчас, оба идентификатора могут иметь значения, которые полностью исключаются из нашего списка кандидатов.
Желаемый результат
После выбора наших кандидатов у нас осталось несколько комбинаций. Для некоторых id1 это может быть один id2. Для некоторых id2 это может быть один id1. А для других идентификаторов может быть много-много строк. Например:
Основное правило, которое вы указали в запросе, заключается в том, что в конечном результате вам нужны только отдельные идентификаторы id1 и отдельные идентификаторы 2.
Если бы мы выбрали третью строку (1, 6) для id1 = 1, это исключило бы последнюю строку (3, 6) из результатов, и мы полностью потеряли бы id1 = 3. Этого можно избежать, выбрав первую строку для id1 = 1: (1,4). Учитывая это, вы указали второстепенное правило в комментариях к запросу: вам нужно как можно больше строк результатов. Таким образом, для только что показанной объединенной таблицы результатов вам нужен окончательный результат из трех строк и отклоните любой результат, содержащий только две строки или меньше.
Наконец, в своем запросе вы указали такой набор правил: «различия между a и b, а затем a1 и b2 минимальны». Я полагаю, что это третичное правило должно означать: вы хотите получить как можно меньшую сумму (b-a). И тогда четвертичное правило таково: вы хотите получить как можно меньшую сумму (b1-a1).
Только сейчас мы указали все правила и их приоритеты и только теперь можем приступить к написанию запроса.
Рейтинг
Мы узнали, что мы должны объединить строки в окончательный набор результатов, и в зависимости от того, какие строки мы выберем, мы можем получить больше или меньше строк результатов и более низкие или более высокие различия в значениях. Это означает, что мы должны сначала получить все комбинации, чтобы сделать из них выбор. Для последней упомянутой объединенной таблицы мы получим следующие параметры:
Теперь мы можем ранжировать их, поставив первый вариант сверху, потому что в нем больше строк, чем в остальных. Если бы сверху было более одной строки с одинаковым количеством строк, нам пришлось бы проверять дальше: мы искали бы суммы (b - a) и, наконец, суммы (b1 - a1). Таким образом, мы получим либо одну лучшую строку, либо несколько лучших строк. В последнем случае мы выберем один из этих наборов верхних строк произвольно.
Запрос
Чтобы сначала создать все параметры, мы используем рекурсивный запрос, в котором мы соединяем одну строку со второй, затем с третьей и т. д. до тех пор, пока мы не получим дубликаты. Результатом являются строки с массивом пар идентификаторов и разностной суммой для каждой, например. результат № 1 = [(1,4), (2,5), (3,6)], diff_sum = 9. Мы ранжируем строки, как показано, выбираем лучшую строку, а затем используем ее для выбора исходных строк. для конечного результата.
with recursive
candidates as
(
select
t1.id1, t1.a, t1.a1,
t2.id2, t2.b, t2.b1
from t1
join t2 on t2.b - t1.a >= 0
and t2.b1 - t1.a1 >= 0
),
combinations (arr_pairs, last_id1, arr_id2, diff1, diff2) as
(
select
array[(id1, id2)],
id1,
array[id2],
b - a,
b1 - a1
from candidates
union all
select
com.arr_pairs || (can.id1, can.id2),
can.id1,
com.arr_id2 || can.id2,
com.diff1 + can.b - can.a,
com.diff2 + can.b1 - can.a1
from combinations com
join candidates can on can.id1 > com.last_id1
and can.id2 != all (com.arr_id2)
),
best_combination as
(
select *
from combinations
order by cardinality(arr_pairs) desc, diff1, diff2
fetch first row only
),
best_combination_rows as
(
select bcr.id1, bcr.id2
from unnest((select arr_pairs from best_combination)) as bcr (id1 int, id2 int)
)
select *
from candidates
where (id1, id2) in (select id1, id2 from best_combination_rows)
order by id1;
Демо: https://dbfiddle.uk/LqggdaM_
Отказ от ответственности: я не являюсь ни математиком, свободно разбирающимся в комбинациях, ни разработчиком PostgreSQL, поэтому здесь и там можно сделать это немного более эффективным.
отлично спасибо. так что вы действительно получили рекурсивный запрос. Я вчера рассмотрел концепцию, но не понял, как ее можно применить к моему случаю, потому что не понимаю, как реализовать своего рода ящик с кандидатами, из которого я буду читать "деструктивно", т.е. удалять, читая . Теперь я понимаю, что это можно сделать путем итерации с помощью «курсора», который удерживает позицию, и «выбирать только первую строку». Очень интересный и понятный ответ, спасибо. воспользуюсь и добавлю в закладки
Кстати, я на самом деле придумал другое решение. Просто интересно, я что-то упускаю? На самом деле он дает тот же ответ, что и ваш, а также решает мой первоначальный образец. Вот оно: с данными как (выбрать * из перекрестного соединения t1 t2) выбрать отдельные для (id2, b, b1) * из данных, где b >= a и b1 >= a1, упорядочить по id2, b, b1, b-a, b1 -а1; Я не играю, правда интересно, нормально ли это. Кажется, работает.
ага, неважно, обнаружил, что на самом деле ваша решает мою исходную таблицу/проблему, а моя в результате дает два дубликата id/id1. Спасибо :)
Не могли бы вы дать какие-нибудь советы о том, как применить этот метод для «больших» таблиц? Я думал создать представление с перекрестным соединением, например, 4000 по 15000 строк (кажется вполне нормальным), но, пытаясь поиграть с рекурсивным запросом, я получил «на устройстве не осталось места».
Поиск оптимального набора данных из многих комбинаций — сложная задача, и SQL может оказаться для этого неподходящим инструментом. SQL работает с наборами данных. Именно поэтому мы сначала получаем кандидатов, затем все комбинации этих кандидатов, затем лучшую из комбинаций. Мы не можем сказать: «дайте нам только одну комбинацию, затем дайте нам другую, сравните две, отбросьте худшую из них, получите третью комбинацию...», как мы могли бы сделать в языке программирования.
Мы хотим рассмотреть все возможные комбинации. При идентификаторах 1, 2, 3 в одной таблице и 6, 7, 8 в другой мы получили бы уже 33 комбинации (если не ошибаюсь): (16) (17) (18) (26) (27 ) (28) (36) (37) (38) (16 27) (16 28) (17 26) (17 28) (18 26) (18 27) (16 37) (16 38) (17 36) ( 17 38) (18 36) (18 37) (26 37) (26 38) (27 36) (27 38) (28 36) (28 37) (16 27 38) (16 28 37) (17 26 38) (17 28 36) (18 26 37) (18 27 36). Из них мы должны найти лучший вариант (в данном случае одну из троек). Чем больше идентификаторов, тем набор комбинаций становится огромным.
Теперь представим, что мы очень рано находим одну тройку. В этом случае нам больше не придется рассматривать одиночек и пар. Мы бы их сразу уволили. Вероятно, это невозможно в SQL. PostgreSQL имеет встроенный язык программирования PL/pgSQL. Возможно, лучше использовать это вместо SQL, основанного на наборах.
С шестью парами для (id, id2, a, b) = (1|4|1|1), (1|5|1|2), (1|6|1|10), (2|4|1 |1), (2|5|1|2), (2|6|1|10), (3|4|1|3), (3|5|1|3), (3|6|1 |100), какую комбинацию вы хотите? Было бы нормально получить (1|4|1|1), который является самым низким для идентификатора 1, (2|5|1|2), который является вторым самым низким для идентификатора 2, поскольку самый низкий будет конфликтовать с идентификатором 1, и (3|6|1|100) для идентификатора 3, что является лишь третьим по величине для идентификатора 2, поскольку два самых низких значения будут конфликтовать с идентификаторами 1 и 2? В этом случае общая сумма (b-a) будет очень высокой из-за выбранной строки для идентификатора 3. Имеет ли это значение? Должны ли мы найти минимальную сумму (b-a)?