首页ruby
2cb463fb78222ea7be096ee66e1674a0

gem 介绍之 groupdate 使用 (五)

hfpp2012发布于64 次阅读

1. 介绍

以前有实现过一个需求,用图表统计网站上每天登录和注册的人数,而这些信息统统是存放在一张表上,但是要根据时间进行分组统计,形成类似下图所示的图表:

存放这些信息的表叫做page_requests,它的结构是这样子的:

# rails console
> PageRequest
class PageRequest < ActiveRecord::Base {
                :id => :integer,
         :user_name => :string,
        :is_success => :boolean,
            :status => :string,
       :operated_at => :datetime,
    :reference_code => :string,
            :mobile => :string,
        :parameters => :text,
         :operation => :string,
              :type => :string,
       :operator_id => :integer,
       :instance_id => :integer
}

其实就是要根据表page_requests中的字段按照时间来统计次数,比如按照一天,一个月之类。使用的sql语句类似于group_by page_requests.operated_at

如果用一般的ruby代码,有可能是这样子实现的:

def chart_series(filter_date)
  page_requests = where(:operated_at => filter_date.to_date..Time.zone.now.end_of_day).
    group("date(operated_at)").
    reorder('operated_at ASC').
    select("date(operated_at) as operated_date, count(id) as total_count").
    group_by { |page_request| page_request.operated_date.to_date }

  (filter_date.to_date..Date.today).map do |date|
    page_requests[date].try(:first).try(:total_count) || 0
  end
end

生成的sql语句类似于这样:

# rails console
> PageRequest.chart_series Time.now
  PageRequest Load (43.0ms)  SELECT date(operated_at) as operated_date, count(id) as total_count FROM `page_requests` WHERE (`page_requests`.`operated_at` BETWEEN '2016-02-23' AND '2016-02-23 15:59:59') GROUP BY date(operated_at)  ORDER BY operated_at ASC
[
    [0] 6
]

可见,主要还是利用GROUP BY date(operated_at),只不过在operated_at前面加了一个函数date,再利用count来统计次数。

这样是能做到的,只不过,我们有时候不仅仅根据日期(date)来统计,有可能是一周(week),一个月(month),甚至是一年(year),我们只要改相关的函数即可,不过在这里会介绍一个专门的gem来使用。

它就是groupdate

它是一个gem,所以可能会适用各种数据库,它不仅适用于mysql,也适用于postgresql,会为特定的数据库生成特定的语句,这是使用它的其中一个原因。具体的可以查看其源码,在这里,我们先来使用它,再来发现其可贵之处。

2. 使用

要使用它很简单,比如下面这样:

> Article.group_by_day(:created_at).count
SELECT COUNT(*) AS count_all, (DATE_TRUNC('day', (created_at::timestamptz - INTERVAL '0 hour') AT TIME ZONE 'Asia/Shanghai') + INTERVAL '0 hour') AT TIME ZONE 'Asia/Shanghai' AS day FROM "articles" WHERE (created_at IS NOT NULL) GROUP BY (DATE_TRUNC('day', (created_at::timestamptz - INTERVAL '0 hour') AT TIME ZONE 'Asia/Shanghai') + INTERVAL '0 hour') AT TIME ZONE 'Asia/Shanghai'
{
    Tue, 01 Sep 2015 00:00:00 CST +08:00 => 1,
    Wed, 02 Sep 2015 00:00:00 CST +08:00 => 0,
    Thu, 03 Sep 2015 00:00:00 CST +08:00 => 0,
    Fri, 04 Sep 2015 00:00:00 CST +08:00 => 0,
    Sat, 05 Sep 2015 00:00:00 CST +08:00 => 0,
...

可见,很可贵的是,它会根据时区生成对应的sql语句,比如我现在的时区是Asia/Shanghai

还能自定义时间格式。

> Article.group_by_month(:created_at, format: "%b %Y").count
   (53.3ms)  SELECT COUNT(*) AS count_all, (DATE_TRUNC('month', (created_at::timestamptz - INTERVAL '0 hour') AT TIME ZONE 'Asia/Shanghai') + INTERVAL '0 hour') AT TIME ZONE 'Asia/Shanghai' AS month FROM "articles" WHERE (created_at IS NOT NULL) GROUP BY (DATE_TRUNC('month', (created_at::timestamptz - INTERVAL '0 hour') AT TIME ZONE 'Asia/Shanghai') + INTERVAL '0 hour') AT TIME ZONE 'Asia/Shanghai'
{
     "9月 2015" => 16,
    "10月 2015" => 29,
    "11月 2015" => 5,
    "12月 2015" => 19,
     "1月 2016" => 14,
     "2月 2016" => 4
}

更多的方法可见于官方的readme文档。

完结。

本站文章均为原创内容,如需转载请注明出处,谢谢。

0 条评论
暂无评论~~
喜欢