当前位置: 动力学知识库 > 问答 > 编程问答 >

ruby on rails - What is the difference between count and select('DISTINCT COUNT(xxx)') in ActiveRecord?

问题描述:

I have two queries that are similar:

StoreQuery.group(:location).count(:name)

vs

StoreQuery.group(:location).select('DISTINCT COUNT(name)')

I was expecting the results to be exactly the same but they're not. What is the difference between the two?

网友答案:

The difference is that the first query counts all names, and the second query counts unique names, ignoring duplicates. They will return different numbers if you have some names listed more than once.

网友答案:

With this sample data

id | name | location |
---+------+----------+ 
1  | NULL | US
2  | A    | UK
3  | A    | UK
4  | B    | AUS

Let check the generated queries the results

1st query

StoreQuery.group(:location).count(:name)

Generated query:

SELECT location, COUNT(name) AS count FROM store_queries GROUP BY location

Result:

{US => 0, UK => 2, AUS => 1}

2nd query

StoreQuery.group(:location).select('DISTINCT COUNT(name)')

Generated query:

SELECT DISTINCT COUNT(name) FROM store_queries GROUP BY location

Result:

ActiveRecord::Relation [StoreQuery count: 0, StoreQuery count: 1, StoreQuery count: 1]
# Mean {US => 0, UK => 1, AUS => 1}

So the differences will be:

                 |1st query | 2nd query |
                 |----------+-----------+
# returned fields|    2     |    1      |
     distinction |    no    |    yes    |

Btw, rails supports this:

StoreQuery.group(:location).count(:name, distinct: true)
分享给朋友:
您可能感兴趣的文章:
随机阅读: