PostgresSQL/Ruby: Как получить результаты для вложенной таблицы SQL?

Содержание

Вопрос или проблема
Ответ или решение
Решение
Пояснение
Дополнительные рекомендации

Вопрос или проблема

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

events = Event.where(...).where(...).joins(...)

subquery = events.select("
  (SELECT MAX(t3.overlaps) FROM
    (SELECT t2.startAt, t2.expireAt, count(*) overlaps
      from
       (
         select lag(t1.dt) over (order by t1.dt) startAt, t1.dt endAt
         from
           (
             select events.start_at dt
             union
             select events.end_at dt
           ) t1
        ) t2
       where t2.startAt < events.end_at
       and t2.endAt > events.created_at
       group by t2.startAt, t2.endAt) t3)
").to_sql

results = ActiveRecord::Base.connection.exec_query("SELECT * FROM (#{subquery}) AS result")

Учитывая отношение событий, вышеуказанный запрос должен вернуть максимальное количество перекрывающихся событий (события, которые активно происходят одновременно).

Например:

Событие 1: 04.10.24 13:00 - 17:00 
Событие 2: 05.10.24 15:00 - 18:00 
Событие 3: 04.10.24 16:00 - 19:00 

Для событий 1-3 максимальное количество перекрывающихся событий равно 2, потому что Событие 1 и Событие 3 перекрываются немного.

Когда я просто выполняю внутренний запрос как “сырое” выражение, он дает мне ожидаемые результаты. Я вижу, что каждая вычисленная строка имеет startAt, endAt и количество перекрытий, происходящих в данном временном интервале.

Однако, выполняя запрос через ORM, я получаю “1” для каждой записи события, вместо того чтобы получить общее максимальное количество перекрытий из внутреннего запроса. SQL, сгенерированный с помощью ORM, выполняет SELECT(...все внутренние данные...) из событий WHERE events.blah....

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

Ответ или решение

Для решения вашей задачи с использованием PostgreSQL и Ruby on Rails, прежде всего, необходимо уточнить несколько моментов. Вы пытаетесь получить максимальное количество перекрывающихся событий на основе определенных фильтров, используя подзапросы. Когда вы выполняете подзапрос через ActiveRecord, вы получаете не тот результат, который ожидаете, а лишь количество максимальных перекрытий для каждой записи события.

Вот как можно переписать вашу задачу, чтобы правильно использовать ActiveRecord и одновременно удержать все предыдущие условия.

Решение

Создание базового запроса: Вам нужно начинать с вашего базового запроса, который будет фильтровать события.

events = Event.where(...) # Ваши условия фильтрации

Использование подзапроса: Чтобы получить максимальное количество перекрытий, мы можем создать подзапрос, который будет возвращать только одно агрегированное значение.
Объединение с исходным запросом: Мы можем добавить подзапрос как поле в ваш основной запрос.

subquery = <<-SQL
  (SELECT MAX(overlap_count) FROM (
     SELECT COUNT(*) AS overlap_count
     FROM (
       SELECT 
         LEAD(start_at) OVER (ORDER BY start_at) AS next_start_at, 
         start_at, 
         end_at 
       FROM events 
     ) t1
     WHERE 
       t1.start_at < events.end_at AND 
       t1.next_start_at > events.start_at
     ) subquery
   ) AS max_overlaps
SQL

# Объединяем подзапрос с вашим запросом событий
results = events.select("events.*, #{subquery}").to_sql

Экстракция данных: Теперь вы можете использовать ActiveRecord для выполнения вашего запроса и получения результата.

final_results = ActiveRecord::Base.connection.exec_query(results)

Пояснение

Мы создали подзапрос subquery, который считает количество перекрывающихся событий. В этом подзапросе используется окно LEAD, чтобы определить, идет ли событие после текущего.
Затем мы подсчитываем максимальное значение перекрытий с помощью MAX() и добавляем его как новое поле в основной запрос.
В итоге, когда вы выполняете этот запрос, вы должны получить основную информацию о событиях вместе с максимальным количеством перекрывающихся событий.

Дополнительные рекомендации

Убедитесь, что индексы на столбцах start_at и end_at созданы для оптимизации выполнения запросов, особенно если у вас большой объем данных.
Запустите анализ производительности вашего запроса, чтобы убедиться, что он соответствует ожиданиям.

Это решение должно решить вашу проблему, корректно используя Rails и PostgreSQL, и возвращая ожидаемые результаты. Если есть дополнительные детали или изменения в задаче, не стесняйтесь задавать уточнения!