我试图找到在 Rails 应用程序中使用通用表表达式进行 Postgres 查询的最佳方法,因为我知道 ActiveRecord 显然不支持 CTE。
我有一张桌子叫user_activity_transitions
其中包含一系列正在启动和停止的用户活动记录(每行表示状态更改:例如启动或停止)。
One user_activity_id
可能有很多情侣开始停下来,他们分布在不同的两排。
如果活动当前正在进行并且尚未停止,则也可能只有“已开始”。这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
回报相当混乱。我基本上需要循环遍历结果并将其放入正确的哈希中。
2个问题:
- 有没有办法摆脱 CTE 并使用运行相同的查询
轨道魔法?
- 如果没有,是否有更好的方法来获得我想要的漂亮哈希结果?
请记住,我对 Rails 很陌生,而且不是查询专家,因此可能会有明显的改进......
多谢!