说我查询的表
rawStats = Stats::TrackProcessed.select("token")
和接收到的表中的所有令牌的数量。
是这样的:
3e79a387c29bda1069271e06ad03d82b8296242e
059681f46ab1c1fa8cf8443a82f0898172e0b646
eacd846ea4e91b49f92f416f61e0f2d075b9dae7
eacd846ea4e91b49f92f416f61e0f2d075b9dae7
811705019a970929801adbf3db0ede31ed01816c
我需要返回一个散列表,将这个样子
{
'3e79a387c29bda1069271e06ad03d82b8296242e' => 1,
'059681f46ab1c1fa8cf8443a82f0898172e0b646' => 1
'eacd846ea4e91b49f92f416f61e0f2d075b9dae7' => 2
'811705019a970929801adbf3db0ede31ed01816c' => 1
}
其中第一值是一个令牌和第二,该令牌在表中的occurances的数目。 然后我需要从所有记录得到的这个平均值。
到目前为止,我已经与查询表得到
rawStats = Stats::TrackProcessed.select("distinct token")
通过检索独特的令牌
然后,尽管每个令牌出现的每个循环计数
rawArr = []
rawStats.each do |r|
token = {
:token => r.token,
:count => rawStats.where("token = ?",r.token).count('token')
}
rawArr << token
end
但这种处理的
(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'
这真的看起来像通过处理大量数据的时候分别查询每个令牌一个非常糟糕的主意。
我已经通过这个SQL查询已经完成,看起来像这样
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
但是,这真的看起来像一个蹩脚的解决方案,我此刻的重构代码,所有的逻辑移动到控制器。
任何帮助都感激不尽。
先感谢您。