必须出现在Group By子句中或在agg中使用。功能 [英] Must appear in Group By clause or used in agg. function

问题描述

我有一个很大的用户表，我正在尝试按街道对城镇内的所有用户进行分组，并通过postgres&铁轨。以下给出了正确的数据，但是我想知道是否可以得到更好的输出：

I have a large User table and I'm trying to group all users within a town by street and get a count via postgres & rails. The following gives me the right data however I'm wondering if I could get a better output:

User.group(:town,:street).count ###Gives a large hash of key/value pairs with town_name repeated each time for every grouped street like {['TOWN_NAME', 'STREET_NAME']=>'#ofUsers'}

因此，代替此： {['BETHPAGE'，'JACKSON AVE'] => 372，['BETHPAGE'，'WILLIAM ST'] => 28}

我要 { 'BETHPAGE'=> {['JACKSON'，372]，['WILLIAM ST'，28]}}

更好的查询将以TOWN_NAME一次用作键的格式输出此信息，并且每个街道及其计数均以数组形式给出：

Is there a better query that would output this information in a format that the TOWN_NAME would be used once as a key and each street and its count are given in an array like:

{ 'TOWN_NAME' => {['STREET_NAME', '#ofUsers'], ['STREET_NAME', '#ofUsers']}

推荐答案

您可以使用ruby将其分组

You can group it using ruby

original = User.group(:town, :street).count
grouped = original.group_by { |vals, count| vals[0] }
grouped.each do |town, matches| 
  grouped[town] = matches.map { |vals, count| [vals.second, count] }
end

grouped # => { 'TOWN_NAME' => [['STREET_NAME', '#ofUsers'], ['STREET_NAME', '#ofUsers']] }

我一直在寻找一种在普通查询中执行此操作的方法，但是您需要多次调用数据库。

I was looking for a way of doing it in a plain query but you would need multiple calls to the DB.

这篇关于必须出现在Group By子句中或在agg中使用。功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！