将含CTE与窗口函数的SQL转换为Rails ActiveRecord写法咨询

方案1：Rails 7+ 原生CTE写法（推荐） #

Rails 7开始内置支持CTE（公共表表达式）的with方法，写法最直观且符合ActiveRecord规范：

# 定义CTE子查询，生成带行号的临时结果集
cte_subquery = Annotation.select(
  "*",
  Arel.sql("row_number() OVER (PARTITION BY column_a ORDER BY column_b) AS rn")
)

# 基于CTE做条件过滤
@annotations = Annotation
  .with(some_table: cte_subquery)
  .from("some_table")
  .where("(column_a = ? AND rn <= ?) OR column_b <> ?", "ABC", 10, "AAA")

注意：用Arel.sql包裹原生SQL片段是Rails高版本要求的安全写法，可避免SQL注入警告

方案2：低版本Rails兼容写法（Rails 6及以下） #

如果你的Rails版本没有原生with方法，可以用子查询嵌套实现相同逻辑：

subquery = Annotation
  .select("*", "row_number() OVER (PARTITION BY column_a ORDER BY column_b) AS rn")
  .to_sql

@annotations = Annotation
  .from("(#{subquery}) AS some_table")
  .where("(column_a = ? AND rn <= ?) OR column_b <> ?", "ABC", 10, "AAA")

方案3：小数据集场景的Ruby层过滤 #

如果查询返回的总数据量很小，也可以直接在Ruby层过滤结果，写法更简单：

# 先查出带行号的全量结果
@annotations = Annotation.select(
  "*",
  Arel.sql("row_number() OVER (PARTITION BY column_a ORDER BY column_b) AS rn")
)

# 内存中过滤
@filtered_annotations = @annotations.select do |anno|
  (anno.column_a == "ABC" && anno.rn <= 10) || anno.column_b != "AAA"
end

注意：该方案会加载全量结果到内存，数据量大时性能极低，仅适合百级以内的小数据集使用

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