Выберите записи с самыми высокими значениями для каждого подмножества

Question

У меня есть набор записей, некоторые из которых, но не все, имеют поле "путь", а все имеют поле "значение". Я хочу выбрать только те, которые либо не имеют пути, либо имеют наибольшее значение из всех записей с конкретным путем.

То есть, учитывая эти записи:

Name:  Path:   Value:
A      foo     5
B      foo     6
C      NULL    2
D      bar     2
E      NULL    4

Я хочу вернуть B, C, D и E, но не A (потому что A имеет путь, и его путь такой же, как B, но A имеет более низкое значение).

Как я могу сделать это, используя ActiveRecord, ARel и Postgres? В идеале я хотел бы решение, которое функционирует как область.

Answer 1

Вы можете использовать что-то вроде этого, используя 2 подзапроса (будет выполнять только один SQL-запрос, который имеет подзапросы). Не проверял, но должен привести вас в правильном направлении. Это для Postgres.

scope :null_ids, -> { where(path: nil).select('id') }
scope :non_null_ids, -> { where('path IS NOT NULL').select('DISTINCT ON (path) id').order('path, value desc, id') }
scope :stuff, -> {
  subquery = [null_ids, non_null_ids].map{|q| "(#{q.to_sql})"}.join(' UNION ')
  where("#{table_name}.id IN (#{subquery})")
}

Если вы используете другую БД, вам может потребоваться использовать группу / порядок вместо отдельных для области действия non_nulls. Если запрос выполняется медленно, поместите указатель на путь и значение.

Вы получаете только 1 запрос, и это цепная область.

Answer 2

Прямая транслитерация вашего описания в SQL будет выглядеть так:

select name, path, value
from (
    select name, path, value,
           row_number() over (partition by path order by value desc) as r
    from your_table
    where path is not null
) as dt
where r = 1
union all
select name, path, value
from your_table
where path is null

Вы могли бы обернуть это в find_by_sql и вытащить свои объекты с другой стороны.

Этот запрос работает так:

1. row_number оконная функция позволяет нам группировать строки по path, заказать каждую группу по value, а затем нумеровать строки в каждой группе. Поиграйте с SQL немного внутри psql и вы увидите, как это работает, есть другие оконные функции, которые позволят вам делать все виды замечательных вещей.
2. Вы лечите NULL path значения отдельно от ненулевых path с, следовательно, path is not null во внутреннем запросе.
3. Мы можем снять первый ряд в каждом из path групп, выбирая те строки из производной таблицы, которые имеют номер строки один (т.е. where r = 1).
4. Лечение path is null строки легко обрабатываются запросом раздела.
5. UNION используется для объединения результирующих наборов запросов.

Я не могу придумать какой-либо способ создания такого запроса с использованием ActiveRecord, и при этом я не могу придумать способ интеграции такого запроса с механизмом области действия ActiveRecord. Если бы вы могли легко получить доступ только к компоненту ГДЕ ActiveRecord::Relation тогда вы могли бы увеличить where path is not null а также where path is null компоненты этого запроса с компонентами WHERE в области видимости. Я не знаю, как это сделать, хотя.

По правде говоря, я склонен отказываться от ActiveRecord по первому требованию. Я считаю ActiveRecord довольно громоздким для большинства сложных вещей, которые я делаю, и не так выразительно, как SQL. Это относится ко всем ORM, которые я когда-либо использовал, поэтому проблема не относится к ActiveRecord.

Answer 3

У меня нет опыта работы с ActiveRecord, но вот пример с SQLAlchemy, чтобы заставить замолчать толпу только для использования SQL;)

q1 = Session.query(Record).filter(Record.path != None)
q1 = q1.distinct(Record.path).order_by(Record.path, Record.value.desc())

q2 = Session.query(Record).filter(Record.path == None)

query = q1.from_self().union(q2)
# Further chaining, e.g. query = query.filter(Record.value > 3) to return B, E

for record in query:
    print record.name