PostgreSQL - GROUP BY子句或用于聚合函数

I found some topics here on SO, but I still can't find the right setup for my query.

我在SO上找到了一些主题，但我仍然无法找到正确的查询设置。

This is query, that works me well on localhost:

这是查询，在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')

But on Heroku gives me the error above - GROUP BY clause or be used in an aggregate function.

但是在Heroku上给出了上面的错误 - GROUP BY子句或者在聚合函数中使用。

Then I have read somewhere, that I should specify all columns in the table, so I tried this:

然后我在某个地方读过，我应该指定表中的所有列，所以我尝试了这个：

@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')

But this doesn't work on localhost and also not on Heroku...

但这不适用于localhost，也不适用于Heroku ......

What should be the right config of the query?

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

3 个解决方案

#1

I think you are trying to aggregate and group by on the same column. It depends on what data you want. Ether do this:

我想你正试图在同一列上汇总和分组。这取决于您想要的数据。以太这样做：

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

Or you want to count all maybe? Then like this:

或者你想要计算所有可能的？然后像这样：

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

#2

A query such as this (retrieving all or most rows) is faster if you GROUP before you JOIN. Like this:

如果在JOIN之前进行GROUP，则此类查询（检索全部或大多数行）会更快。喜欢这个：

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;

This way you need far fewer join operations. And the rows of the table cars do not have to be first multiplied by joining to many users each and then grouped back to be unique again.
Only the right table has to be grouped, which makes the logic simpler, too.

这样，您需要更少的连接操作。并且表车的行不必首先通过连接到每个用户而倍增，然后再次分组回来是唯一的。只有正确的表必须进行分组，这也使逻辑更简单。

#3

You can use MAX() trick on cars column.

您可以在汽车列上使用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')

#1