Rails的数记录，而无需再次查询 [英] Rails count records without querying again

问题描述

说我查询的表

rawStats = Stats::TrackProcessed.select("token")

和接收表中的所有令牌的数量。

and received number of all tokens in a table.

是这样的：

3e79a387c29bda1069271e06ad03d82b8296242e
059681f46ab1c1fa8cf8443a82f0898172e0b646
eacd846ea4e91b49f92f416f61e0f2d075b9dae7
eacd846ea4e91b49f92f416f61e0f2d075b9dae7
811705019a970929801adbf3db0ede31ed01816c

我需要返回一个哈希表，将看起来像这样

I need to return a hash list that will look like this

{
   '3e79a387c29bda1069271e06ad03d82b8296242e' => 1,
   '059681f46ab1c1fa8cf8443a82f0898172e0b646' => 1
   'eacd846ea4e91b49f92f416f61e0f2d075b9dae7' => 2
   '811705019a970929801adbf3db0ede31ed01816c' => 1
}

其中第一值是一个令牌和第二，该标记在一个表中的occurances的数目。 然后，我需要得到这个从所有记录的平均值。

Where first value is a token and a second, number of occurances of this token in a table. I then need to get an average value of this from all records.

到目前为止，我已经得到了与查询表

So far i have gotten with querying table with

rawStats = Stats::TrackProcessed.select("distinct token")

通过检索独特的标记

By retrieving unique tokens

再出现，虽然对于每个标记每个循环计数

And then though each loop count number of occurrences for each token

rawArr = []
rawStats.each do |r|
  token = {
    :token => r.token,
    :count => rawStats.where("token = ?",r.token).count('token')
  }

  rawArr << token
end

但这种处理为

But this processes as

(0.7ms)  SELECT COUNT(`stats_track_processed`.`token`) FROM `stats_track_processed` WHERE `stats_track_processed`.`token` = '196e595b573f71fc2af04693c73809303bebd62d'
(0.7ms)  SELECT COUNT(`stats_track_processed`.`token`) FROM `stats_track_processed` WHERE `stats_track_processed`.`token` = 'db67ab44e94ca338d90e902a36c37b4998a47ff0'
(0.7ms)  SELECT COUNT(`stats_track_processed`.`token`) FROM `stats_track_processed` WHERE `stats_track_processed`.`token` = 'a8a78cffc0935b07f90b5f3008dcad27e8ac71c7'
(0.8ms)  SELECT COUNT(`stats_track_processed`.`token`) FROM `stats_track_processed` WHERE `stats_track_processed`.`token` = '91b8ae5ffa12ece30d8548b488fab8aff7614f2b'
(0.7ms)  SELECT COUNT(`stats_track_processed`.`token`) FROM `stats_track_processed` WHERE `stats_track_processed`.`token` = '649e7aa59cc8af6c59cec1cf637ffed1ce6b3be7'
(0.7ms)  SELECT COUNT(`stats_track_processed`.`token`) FROM `stats_track_processed` WHERE `stats_track_processed`.`token` = '441dae6f22776687b57daaaeef2c63c31902b987'
(0.7ms)  SELECT COUNT(`stats_track_processed`.`token`) FROM `stats_track_processed` WHERE `stats_track_processed`.`token` = '6b8937a13012f22c8a4cdd4ed4caad0ad8761d3b'
(0.6ms)  SELECT COUNT(`stats_track_processed`.`token`) FROM `stats_track_processed` WHERE `stats_track_processed`.`token` = 'beaba154f38d5c9b64e4fb2d851ec785bd6bc4ec'
(0.6ms)  SELECT COUNT(`stats_track_processed`.`token`) FROM `stats_track_processed` WHERE `stats_track_processed`.`token` = 'efa9f3a71c4ff33abcae4af788ef6d0be599f76a'
(0.7ms)  SELECT COUNT(`stats_track_processed`.`token`) FROM `stats_track_processed` WHERE `stats_track_processed`.`token` = '053a7727885f3c4099a9b31a2eccc77b99df50ee'
(0.7ms)  SELECT COUNT(`stats_track_processed`.`token`) FROM `stats_track_processed` WHERE `stats_track_processed`.`token` = '32dc984c9f85ff91242533144cd179a9b4529bed'
(0.7ms)  SELECT COUNT(`stats_track_processed`.`token`) FROM `stats_track_processed` WHERE `stats_track_processed`.`token` = 'a58804ae38490f08a1645c8271e88df3757fe771f'
(0.7ms)  SELECT COUNT(`stats_track_processed`.`token`) FROM `stats_track_processed` WHERE `stats_track_processed`.`token` = 'a58804a2e38490f08a1645c8271e88df3757fe771f'

这真的看起来像一个非常糟糕的主意通过处理大量数据时，每个标记分别查询。

Which really looks like a very bad idea by querying each token separately when processing a large amount of data.

我有这个通过SQL查询已完成，看起来像这样

I had this done via SQL query already, looks like this

SELECT AVG(viewsAverage) as total 
FROM 
 (SELECT COUNT(token) AS viewsAverage 
  FROM stats_track_processed 
  WHERE admedia_id = #{params[:admedia_id]} 
    AND #{params[:banner_id]} 
    AND (access_time BETWEEN '#{params[:begin]}' 
    AND DATE_ADD('#{params[:end]}', INTERVAL 1 DAY)) 
  GROUP BY token) 
stats_track_processed

但是，这真的看起来像一个蹩脚的解决方案，我重构code的时刻，将所有的逻辑控制器。

But this really looks like a crappy solution and i am refactoring code at the moment and moving all the logics to the controller.

任何帮助将大大AP preciated。

Any help will be much appreciated.

感谢你在前进。

推荐答案

遍历的完整列表，并保持计数的哈希值。

Iterate through the full list and maintain a hash of the counts.

result = Stats::TrackProcessed.select("token")
counts = Hash.new{|h,k| h[k] = 0 }
result.each{|el| counts[el] +=1}

然后得到一个平均，你可以不喜欢

Then to get an average you could do something like

 average = counts.values.inject(0){|sum, el| sum + el} / counts.length

这篇关于Rails的数记录，而无需再次查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！