如何使用 ActiveRecord 在 mysql 中使用 DISTINCT ON
想要所有不同用户的所有最新访问.
Want all the latest visit of all the distinct user.
为此，我使用以下查询
Event.order(time: :desc).select('DISTINCT ON(user_id) user_id, time')
出现 SQL 语法错误.
Getting SQL syntax error.
ActiveRecord::StatementInvalid (Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON(user_id) user_id, time FROM `events` ORDER BY `events`.`time` DESC ' at line 1: SELECT DISTINCT ON(user_id) user_id, time FROM `events` ORDER BY `events`.`time` DESC LIMIT 11)
事件表列名
["id", "visit_id", "user_id", "name", "properties", "time"]
谁能帮我解决这个问题
预期的输出是这样的
{ 21 => "2018-12-18 09:44:59", 42 => "2018-12-19 12:08:59"}
21 是 user_id，value 是上次访问时间
21 is user_id and value is last visit time
使用mysql作为数据库
推荐答案
所以你希望所有用户访问的最后一次访问时间.
So you want all user visits with last visited time.
您可以使用 GROUP 和 MAX 函数代替 DISTINCT 函数.
Instead of use DISTINCT function, you can use GROUP with MAX function.
查询看起来像
Events.group(:user_id).maximum(:time)
这会输出您想要的结果
{21=>Tue, 18 Dec 2018 11:15:24 UTC +00:00, 23=>Thu, 20 Dec 2018 06:42:10 UTC +00:00}
希望这对你有用.
仅供参考DISTINCT ON(列).是 PostgreSQL 语法.
FYI DISTINCT ON(columns). is PostgreSQL Syntax.
相关文章