数据库 请教一个累计统计的 sql

as181920 · 2013年08月17日 · 最后由 mvj3 回复于 2013年08月18日 · 5111 次阅读

比如每日汇总:

Mxx.select("sum( col_a * col_b ) as s, date(created_at) as created_date").group("created_date")

这样可以查询每日汇总的数据,比如 2013.3.3 日当天的汇总数据

如果要每日累计汇总的数据,怎么查询,比如从 2012.3.3 到 2013.3.3 日累计的汇总 sum 数。

匿名 #1 2013年08月17日

sql 有个 extract 函数,试试: Mxx.select("sum( col_a * col_b ) as s, date(extract(year from created_at)) as created_date").group("created_date")

#1 楼 @KgTong 这是汇总一年的数据,偶要的是比如 1 月份,1+2 月份,1+2+3 月份,,,:)

现在是跑多个 sql 来做,不过数据量大了就不好玩了。

按月统计,取出来再求 accumulation sum。

而且累记和一般是用在前端的图表,在前端自己去累加下也可以。

建议使用 https://github.com/eoecn/statlysis 这个面向 ORM 统计的 Ruby DSL,具体见 README,比这样复杂地拼 SQL 好理解多了。

我看到你这里需要对两个字段做额外计算,建议也是预先在事务里写入到该表其他字段为好。

补: 抱歉没看到 SQL 里有 SUM,过段时间给出解决方案:)

#4 楼 @doitian 哈,多谢。基础数据抽出来,剩下程序处理。以前也这么干的,居然又忘了,真快老年痴呆了。

另,数据大了肯定不会是实时的,那时候线下 batch 处理,暂时还不用。

#6 楼 @as181920 数据大了也是可以近实时的,只要把比如之前月份的统计数据缓存了,只处理最近数据即可。千万以下级别的数据实在不算什么。

很好奇如果有几十条类似 SQL 统计需求,不作些模式封装的话,程序在性能和维护上会有问题。

你这是要做报表啊,我们也在做,哈哈。 我们采用了线下 batch 处理。

#7 楼 @mvj3 虽然 sql 多,但是代码足够简单,一眼即明,所以维护不是问题。用上缓存反而多了维护和开发成本(因为基础功能还在开发,一早用上缓存,后面开发时考虑的东西就多了,先放放)。

如果数据多了,可以 batch 可以缓存,偶更倾向于后台 batch 哈,开发简单。 如果代码多了,封装也好,提到 lib 也好,肯定要抽出来才更好维护。

才想起来,原来问这个是想会不会某个基础的 sql 被我遗忘掉了。

事实上上面的优化我一个都不做,等到了需要的时候再去优化。不过目前这方式实在有点离谱,所以先问个储备下。

#8 楼 @ery 你们离线怎么个方式,就是单独后台程序慢慢跑?还是数据库主备同步,在备份数据上随意跑?还是导入其它数据存储然后处理?用数据库统计功能再汇总,还是提出所有基础数据全部程序来计算汇总?

#10 楼 @as181920 单独后台一个程序,分析现有数据,然后生成报表基础数据。 Rails 程序再根据报表基础数据,做报表计算。

#7 楼 @mvj3 Statlysis 看起来不错。报表如果多的话,维护的代码量还是灰长大的。所以用第三方 Gem 应该是优先的选择。谢谢你的推荐。

#9 楼 @as181920 #12 楼 @ery

下午我给 statlysis.gem 增加了对 sum 的支持 。主题的解决方案如下:

第一步,数据准备:

class Mxx; include Mongoid::Document; field :col_sum, :type => Float; end

class Mxx
  before_save do
    self.col_sum = self.col_a * self.col_b
  end
end

第二步,配置统计:

Statlysis.setup do
  daily Mxx, :sum_columns => [:col_sum]
end

第三步,统计和察看结果:

cron = Statlysis.daily['mxx'].first => #<Statlysis::Timely:0x7f891cc9db30 @source_type=:mongoid, @time_column=:created_at, @time_unit=:day, @time_zone=#<ActiveSupport::TimeZone:0x7f891b4783e8>, @multiple_dataset=#<Statlysis::MongoidDataset:0x7f891cca2338>, @sum_columns=#<Array:0x7f891cc9fd18>, @stat_table_name="timely_mxxes_d", @stat_model=#<Class:0x7f8920966918>>

cron.stat_model # => TimelyMxxesD

cron.run

# 统计结果类似于
cron.output[1] # => {:t=>Fri, 14 Dec 2012 00:00:00 +0800, :timely_c=>11, :totally_c=>16, :timely_favcount_s=>24.0, :totally_favcount_s=>38.0}

然后显示的时候从统计表 timely_mxxes_d 抽取就可以了,平均数,中位数等都可以从里面算出来。

欢迎报告 BUG 或功能改进:)

需要 登录 后方可回复, 如果你还没有账号请 注册新账号