如何在Rails 3中获取汇总数据时间片(合计,平均,最小值,最大值等) [英] How to Get Aggregate Data by Time Slice (sum, avg, min, max, etc.) in Rails 3
本文介绍了如何在Rails 3中获取汇总数据时间片(合计,平均,最小值,最大值等)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我怎样才能创建由时间片聚集在Rails 3的积极关系的查询?
我想建立查询,对于每N分钟间隔可以返回最小值,最大值,平均值,求和,计数,对于具有特定名称的计数器的每一个样本。
CREATE_TABLE样本
t.integercounter_id
t.string名
t.float值
t.datetimecreated_at
结束
解决方案
不幸的是我从来没有用过Postgres的,所以这个解决方案在MySQL的。但我认为你可以找到Postgres的类似物。
类反<的ActiveRecord :: Base的
的has_many:样品做
#默认30分钟
高清per_time_slice(片= 30)
开始=2000-01-01 00:00:00
self.select(*,
CONCAT(FLOOR(TIMESTAMPDIFF(MINUTE,#{}开始,created_at)/#{}片)*#{}片,
(FLOOR(TIMESTAMPDIFF(MINUTE,#{}开始,created_at)/#{}片)+ 1)*#{}片)的切片,
AVG(值)为avg_value,
分钟(值)为MIN_VALUE,
最大值(值)为MAX_VALUE,
和(数值)作为sum_value,
计(值)为count_value)。
组(片)。为了(切片)
结束
结束
结束
用法
计数器= find_some_counter
样品= counter.samples.per_time_slice(60)。凡(:名称=>中巴比)
samples.map(安培;:avg_value)
samples.map(安培;:MIN_VALUE)
samples.map(安培;:MAX_VALUE)
等
How can I create active relation queries in Rails 3 that are aggregated by time slice?
I'd like to build queries that for every n minute interval can return min, max, avg, sum, count, for every sample of a counter with a particular name.
create_table "samples"
t.integer "counter_id"
t.string "name"
t.float "value"
t.datetime "created_at"
end
解决方案
Unfortunately I've never used Postgres, so this solution works in MySQL. But I think you can find out Postgres analogs.
class Counter < ActiveRecord::Base
has_many :samples do
# default 30 minutes
def per_time_slice(slice = 30)
start = "2000-01-01 00:00:00"
self.select("*,
CONCAT( FLOOR(TIMESTAMPDIFF(MINUTE,'#{start}',created_at)/#{slice})*#{slice},
(FLOOR(TIMESTAMPDIFF(MINUTE,'#{start}',created_at)/#{slice})+1)*#{slice} ) as slice,
avg(value) as avg_value,
min(value) as min_value,
max(value) as max_value,
sum(value) as sum_value,
count(value) as count_value").
group("slice").order("slice")
end
end
end
Usage
counter = find_some_counter
samples = counter.samples.per_time_slice(60).where(:name => "Bobby")
samples.map(&:avg_value)
samples.map(&:min_value)
samples.map(&:max_value)
etc
这篇关于如何在Rails 3中获取汇总数据时间片(合计,平均,最小值,最大值等)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文