PostgreSQL - GROUP BY子句或在聚合函数中使用

问题描述：

我在这里找到了一些关于SO的话题，但我仍然找不到适合我的查询的正确设置。PostgreSQL - GROUP BY子句或在聚合函数中使用

这是查询的作品我很好在localhost：

@cars = Car.find_by_sql('SELECT cars.*, COUNT(cars.id) AS counter 
         FROM cars 
         LEFT JOIN users ON cars.id=users.car_id 
         GROUP BY cars.id ORDER BY counter DESC')

但在Heroku给我上面的错误 - GROUP BY子句或聚合函数使用。

然后我读的地方，我应该指定表中的所有列，所以我尝试这样做：

@cars = Car.find_by_sql('SELECT cars.id, cars.name, cars.created_at, 
           cars.updated_at, COUNT(cars.id) AS counter 
         FROM cars 
         LEFT JOIN users ON cars.id=users.car_id 
         GROUP BY (cars.id, cars.name, cars.created_at, cars.updated_at) 
         ORDER BY counter DESC')

但这并不在localhost在Heroku上工作，也没有...

什么应该是查询的正确配置？

答

我认为你正在尝试在同一列进行聚合和分组。这取决于你想要的数据。以太做这个：

SELECT 
cars.name, 
cars.created_at, 
cars.updated_at, 
COUNT(cars.id) AS counter 
FROM cars 
LEFT JOIN users 
    ON cars.id=users.car_id 
GROUP BY cars.name, cars.created_at, cars.updated_at 
ORDER BY counter DESC

或者你想数一切都可能？然后像这样：

SELECT 
cars.id, 
cars.name, 
cars.created_at, 
cars.updated_at, 
COUNT(*) AS counter 
FROM cars 
LEFT JOIN users 
    ON cars.id=users.car_id 
GROUP BY cars.id, cars.name, cars.created_at, cars.updated_at 
ORDER BY counter DESC

谢谢，真的有助于明确此操作。 – edencorbin 2017-03-04 23:54:53

答

您可以使用MAX()汽车专栏的技巧。

@cars = Car.find_by_sql(' 
SELECT cars.id, MAX(cars.name) as name, MAX(cars.created_at) AS 
created_at, MAX(cars.updated_at) as updated_at, COUNT(cars.id) AS counter 
FROM cars LEFT JOIN users ON cars.id=users.car_id 
GROUP BY cars.id ORDER BY counter DESC')

任何想法，如果使用像这样的max可能会导致性能问题？ – achabacha322 2017-09-15 19:24:24

答

的查询，如下所示（检索所有或大部分行）是更快如果你GROUP之前JOIN。像这样：

SELECT id, name, created_at, updated_at, u.ct 
FROM cars c 
LEFT JOIN (
    SELECT car_id, count(*) AS ct 
    FROM users 
    GROUP BY 1 
    ) u ON u.car_id = c.id 
ORDER BY u.ct DESC;

这样你就不需要连接操作了。并且表cars的行不必先乘以每个加入许多用户然后再分组以再次唯一。
只有正确的表格必须分组，这也使得逻辑更简单。

PostgreSQL - GROUP BY子句或在聚合函数中使用

相关推荐