Блог

Главная - Вопросы по программированию - Как мне сузить группу строк Postgres, значения которых наиболее близки к определенным значениям?

Как мне сузить группу строк Postgres, значения которых наиболее близки к определенным значениям?

#postgresql #aggregates

#postgresql #агрегирует

Вопрос:

У меня есть некоторые данные, которые выглядят примерно так после того, как я запустил над ними несколько фильтров:

 Business Type | Business City | Min Rating | Max Rating
-------------- --------------- ------------ -----------
Restaurant    | Barcelona     |          2 |          8
Restaurant    | Barcelona     |          1 |         10
Restaurant    | Madrid        |          4 |          8
Diner         | Madrid        |          5 |          8
Diner         | Madrid        |          1 |          8
Diner         | Barcelona     |          3 |          8

Мне нужно вернуть только одну строку для каждого типа бизнеса и комбинации городов. Возвращаемая мной строка должна быть той, в которой минимальные и максимальные значения наиболее близки к определенным числам. Например, я хочу вернуть строку, ближайшую к минимальному рейтингу 3 и максимальному рейтингу 7. Это привело бы к:

 Business Type | Business City | Min Rating | Max Rating
-------------- --------------- ------------ -----------
Restaurant    | Barcelona     |          2 |          8
Restaurant    | Madrid        |          4 |          8
Diner         | Madrid        |          5 |          8
Diner         | Barcelona     |          3 |          8

Это в приложении Rails 5, использующем ActiveRecord. Я открыт для использования Arel, ActiveRecord DSL или PostgreSQL.

Комментарии:

1. Упорядочите их по разнице в конкретных значениях и примените ограничение.

2. Похоже, все, что вам нужно, это GROUP BY для столбцов, которые должны быть уникальными, и min или max для агрегированных столбцов. Что меня смущает, так это то, что вы суммируете «Минимальный рейтинг», беря максимальный…

3. @Marek Мне нужно по одному для каждой сгруппированной пары. Ваше предложение ограничило бы количество строк в целом.

4. @Laurenz Я не ищу максимальное или минимальное значение. Мне нужен тот, в котором оба столбца находятся ближе всего к своим целевым номерам, которые не совпадают. Целевое число также всегда будет находиться в середине всех чисел, вот почему я привел пример таким образом. В моем примере я ищу 3 в столбце min и 7 в столбце max. Это не реальные данные, с которыми я работаю, но это простой пример, который показывает, что мне нужно сделать.

Ответ №1:

Для этого вы можете определить пользовательскую агрегатную функцию:

 CREATE FUNCTION closer_to(integer, integer, integer) RETURNS integer
   LANGUAGE sql AS
'SELECT CASE WHEN abs($1 - $3) < abs($2 - $3) THEN $1 ELSE $2 END';

CREATE AGGREGATE closest_to(integer, integer) (
   STYPE = integer,
   SFUNC = closer_to
);

Затем вы можете записать свой запрос в виде:

 SELECT "Business Type", "Business City",
       closest_to("Min Rating", 3) AS "Min Rating",
       closest_to("Max Rating", 7) AS "Max Rating",
FROM mytable
GROUP BY "Business Type", "Business City";