PostgreSQL/Ruby：如何在ORM中获取嵌套SQL查询的正确结果？

问题背景 #

需要基于Event模型已有的筛选作用域（包含where、joins等条件），计算该作用域内同时进行的事件最大重叠数。原有写法执行后，每个Event记录仅返回1，无法得到正确的全局最大值。

原有代码示例：

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")

示例场景：

Event 1: 10/4/24 1pm - 5pm 
Event 2: 10/5/24 3pm - 6pm 
Event 3: 10/4/24 4pm - 7pm 

预期返回最大重叠数2（Event1和Event3存在重叠），但原有代码返回多个1。

问题根源 #

原有查询的子查询中，直接引用了主表events的end_at、created_at字段，导致子查询会针对主表的每一行（单个Event）单独执行统计，而非基于作用域内所有事件的时间点计算全局最大值。过滤条件where t2.startAt < events.end_at and t2.endAt > events.created_at进一步限制了统计范围仅当前Event的区间，最终得到错误结果。

修复方案 #

核心思路：将作用域内所有事件的时间点（开始、结束）提取为独立数据集，基于全局时间点计算重叠数，再取最大值，避免与主表的逐行关联。

1. 基础实现代码 #

# 第一步：获取作用域内所有事件的开始/结束时间点的联合集合
time_points_subquery = events.select("start_at AS dt").union(events.select("end_at AS dt")).to_sql

# 第二步：计算每个时间段的事件重叠数
overlap_counts_subquery = <<~SQL
  SELECT 
    lag(t1.dt) OVER (ORDER BY t1.dt) AS start_at,
    t1.dt AS end_at,
    COUNT(*) AS overlaps
  FROM (#{time_points_subquery}) t1
  GROUP BY start_at, end_at
  HAVING start_at IS NOT NULL -- 排除第一个时间点（lag返回null的无效区间）
SQL

# 第三步：查询全局最大重叠数
max_overlap_result = ActiveRecord::Base.connection.exec_query(<<~SQL).first
  SELECT MAX(overlaps) AS max_overlaps
  FROM (#{overlap_counts_subquery}) t2
SQL

# 提取结果，无重叠时返回0
max_overlap = max_overlap_result["max_overlaps"] || 0

2. 封装为模型方法（复用性更高） #

将逻辑封装到Event模型中，方便后续调用：

class Event < ApplicationRecord
  def self.max_overlapping(scope = all)
    # 生成时间点子查询
    time_points_subquery = scope.select("start_at AS dt").union(scope.select("end_at AS dt")).to_sql
    
    # 生成重叠数统计子查询
    overlap_counts_subquery = <<~SQL
      SELECT 
        lag(t1.dt) OVER (ORDER BY t1.dt) AS start_at,
        t1.dt AS end_at,
        COUNT(*) AS overlaps
      FROM (#{time_points_subquery}) t1
      GROUP BY start_at, end_at
      HAVING start_at IS NOT NULL
    SQL
    
    # 执行最终查询并返回结果
    result = ActiveRecord::Base.connection.exec_query(<<~SQL)
      SELECT MAX(overlaps) AS max_overlaps
      FROM (#{overlap_counts_subquery}) t2
    SQL
    
    result.first["max_overlaps"] || 0
  end
end

# 使用方式：传入自定义作用域即可
filtered_events = Event.where(status: :active).joins(:venue).where(venues: { city: "Beijing" })
max_overlap = Event.max_overlapping(filtered_events)

方案说明 #

• 先通过union合并作用域内所有事件的开始、结束时间，得到全局时间点集合，避免与单个Event关联。
• 用lag()窗口函数将相邻时间点组成时间段，每个时间段的重叠数代表该区间内同时进行的事件数量。
• 最后取所有时间段重叠数的最大值，即为作用域内的最大同时事件数。

内容的提问来源于stack exchange，提问作者Megan Nelson