用Ruby on Rails实现Postgres通用表达式查询

Question

我正在尝试寻找在Rails应用程序中使用公共表达式进行Postgres查询的最佳方法，知道ActiveRecord显然不支持公共表达式。

我有一个名为“user_activity_transitions”的表，其中包含一系列记录，这些记录表示用户活动的开始和停止（每行都表示状态的更改：例如开始或停止）。

一个“user_activity_id”可能有很多对被分开记录的开始和停止动作，它们在2个不同的行中。

如果活动当前正在进行且未停止，则也可能只存在"started"。 "sort_key"从第一个状态开始以0开始，并每次状态更改增加10。

id      to_state     sort_key     user_activity_id    created_at
1       started      0            18                  2014-11-15 16:56:00
2       stopped      10           18                  2014-11-15 16:57:00
3       started      20           18                  2014-11-15 16:58:00
4       stopped      30           18                  2014-11-15 16:59:00
5       started      40           18                  2014-11-15 17:00:00

我想要的是以下输出，将开始-停止的夫妻组合在一起，以便能够计算持续时间等。

user_activity_id     started_created_at      stopped_created_at
18                   2014-11-15 16:56:00     2014-11-15 16:57:00
18                   2014-11-15 16:58:00     2014-11-15 16:59:00
18                   2014-11-15 17:00:00     null

这个表的实现方式使得查询变得更加困难，但为未来的改动（例如新的中间状态）提供了更多的灵活性，因此不会被修改。

我的Postgres查询（以及Rails中的相关代码）:

query = <<-SQL
    with started as (
    select 
        id,
        sort_key,
        user_activity_id,
        created_at as started_created_at
    from
        user_activity_transitions
    where  
        sort_key % 4 = 0
    ), stopped as (
    select 
        id,
        sort_key-10 as sort_key2,
        user_activity_id,
        created_at as stopped_created_at
    from
    user_activity_transitions
    where
        sort_key % 4 = 2
    )
    select
        started.user_activity_id AS user_activity_id,
        started.started_created_at AS started_created_at,
        stopped.stopped_created_at AS stopped_created_at
    FROM
        started
    left join stopped on stopped.sort_key2 = started.sort_key
    and stopped.user_activity_id = started.user_activity_id
SQL

results = ActiveRecord::Base.connection.execute(query)

它的作用是通过对排序键进行模运算，"欺骗" SQL 连接 2 个连续行。查询正常工作。但使用这个原始 AR 调用让我感到烦恼，特别是因为 connection.execute 返回的结果非常凌乱。基本上，我需要循环遍历结果并将其放入正确的哈希表中。有两个问题: 1.是否有一种方法可以摆脱 CTE 并使用 Rails 魔法运行相同的查询？2.如果没有，是否有更好的方法以漂亮的哈希方式获取所需的结果？请记住，我对 Rails 不是很了解，也不是查询专家，因此可能存在明显的改进...非常感谢！

- Davor

---

你可以使用postgres_ext与ActiveRecord一起编写CTE，请参见：https://github.com/dockyard/postgres_ext/blob/master/docs/querying.md#common-table-expressions-ctes - Dan McClain

如果没有的话，有没有更好的方法来获得我想要的漂亮哈希结果？正如Pospiszil所正确写的那样，您应该使用arel重写SQL。 - Малъ Скрылевъ

Answer 1

虽然Rails不直接支持CTE，但是您可以模拟单个CTE并仍然利用ActiveRecord。使用一个from子查询代替CTE即可。

Thing
  .from(
    # Using a subquery in place of a single CTE
    Thing
      .select(
        '*',
        %{row_number() over(
            partition by
              this, that
            order by
              created_at desc
          ) as rank
        }
      )
    :things
  )
  .where(rank: 1)

这并不完全相同，但等效于...

with ranked_things as (
  select
    *,
    row_number() over(
      partition by
        this, that
      order by
        created_at desc
    ) as rank
)
select *
from ranked_things
where rank = 1

- Schwern

Answer 2

我试图找到在Rails应用程序中使用公共表达式（CTE）进行Postgres查询的最佳方法，据我所知，ActiveRecord似乎支持CTE。但是，据我所知，ActiveRecord并不支持CTE。AR底层使用的Arel支持它们，但不向AR接口公开它们。

有没有办法摆脱CTE并使用Rails magic运行相同的查询？

实际上并没有。你可以使用AR的API编写它，但只会将相同的SQL拆分成几个方法调用。

如果不行，有没有更好的方式以漂亮的哈希形式获取我想要的结果？

我尝试运行查询，我得到了以下结果，我认为这个结果足够好看。你得到了不同的结果吗？

[
  {"user_activity_id"=>"18", "started_created_at"=>"2014-11-15 16:56:00", "stopped_created_at"=>"2014-11-15 16:57:00"},
  {"user_activity_id"=>"18", "started_created_at"=>"2014-11-15 16:58:00", "stopped_created_at"=>"2014-11-15 16:59:00"},
  {"user_activity_id"=>"18", "started_created_at"=>"2014-11-15 17:00:00", "stopped_created_at"=>nil}
]

我假设您有一个名为 UserActivityTransition 的模型用于操作数据。您也可以使用该模型获取结果。

results = UserActivityTransition.find_by_sql(query)
results.size # => 3
results.first.started_created_at # => 2014-11-15 16:56:00 UTC

请注意，这些“虚拟”属性在检查结果时不可见，但它们确实存在。

- Jiří Pospíšil

---

谢谢 - 我拼错了“支持CTE”，我是指不支持。你的意思是我可以用Arel以任何方式重写查询吗？我的数组与你的略有不同：我得到了重复的结果，例如{"user_activity_id"=>18, "started_created_at"=>"2014-11-15 16:56:50.136922", "stopped_created_at"=>"2014-11-15 16:57:02.552191", 0=>18, 1=>"2014-11-15 16:56:50.136922", 2=>"2014-11-15 16:57:02.552191"}。我可以接受它，但看起来两次结果很奇怪。 - Davor

@Davor 你可以使用Arel，但是我认为它不会有太大的改进，因为Arel也可能非常冗长，而且我通常发现对于更长的查询，原始SQL更易读。当然，你可以尝试一下，这里提供了帮助：http://jpospisil.com/2014/06/16/the-definitive-guide-to-arel-the-sql-manager-for-ruby.html。你尝试直接使用模型了吗？在我看来，这是目前最好的方法。 - Jiří Pospíšil

非常感谢！我会查看您的链接，但可能会按照您的建议坚持使用原始SQL。 - Davor

Answer 3

现在可以使用with方法了。AR查询的样子是这样的：

results =
  UserActivityTransition
    .with(started: UserActivityTransition.select('id, sort_key, user_activity_id, created_at AS started_created_at').where('sort_key % 4 = 0'))
    .with(stopped: UserActivityTransition.select('id, sort_key-10 as sort_key2, user_activity_id, created_at AS stopped_created_at').where('sort_key % 4 = 2'))
    .select('started.user_activity_id AS user_activity_id, started.started_created_at AS started_created_at, stopped.stopped_created_at AS stopped_created_at')
    .from('started')
    .joins('LEFT JOIN stopped ON stopped.sort_key2 = started.sort_key AND stopped.user_activity_id = started.user_activity_id')

让我们验证一下SQL是否正确

puts results.to_sql

输出：

WITH "started" AS (SELECT id, sort_key, user_activity_id, created_at AS started_created_at FROM "user_activity_transitions" WHERE (sort_key % 4 = 0)), "stopped" AS (SELECT id, sort_key-10 as sort_key2, user_activity_id, created_at AS stopped_created_at FROM "user_activity_transitions" WHERE (sort_key % 4 = 2)) SELECT started.user_activity_id AS user_activity_id, started.started_created_at AS started_created_at, stopped.stopped_created_at AS stopped_created_at FROM started LEFT JOIN stopped ON stopped.sort_key2 = started.sort_key AND stopped.user_activity_id = started.user_activity_id

现在有一种方法可以处理数据了。

results.each do |r|
  puts "#{r.user_activity_id} | #{r.started_created_at} | #{r.stopped_created_at}"
end

输出

18 | 2014-11-15 16:56:00 | 2014-11-15 16:57:00
18 | 2014-11-15 16:58:00 | 2014-11-15 16:59:00
18 | 2014-11-15 17:00:00 | 

---

这个问题的查询足够大。

更易读的使用示例：

Book
  .with(books_with_reviews: Book.where("reviews_count > ?", 0))
  .with(books_with_ratings: Book.where("ratings_count > ?", 0))
  .joins("JOIN books_with_reviews ON books_with_reviews.id = books.id")

# WITH books_with_reviews AS (
#   SELECT * FROM books WHERE (reviews_count > 0)
# ), books_with_ratings AS (
#   SELECT * FROM books WHERE (ratings_count > 0)
# )
# SELECT * FROM books JOIN books_with_reviews ON books_with_reviews.id = books.id

- mechnicov