如何使他们创造了Postgres的查询，以组记录按月份？ （：created_at日期时间列） [英] How to make a query in Postgres to group records by month they were created? (:created_at datetime column)

问题描述

我有这样的红宝石code：

I have this Ruby code:

def visits_chart_data(domain)
  last_12_months.collect do |month|
    { created_at: month, count: domain.visits.created_on_month(month).count }
  end
end

def last_12_months
  (0..11).to_a.reverse.collect{ |month_offset| month_offset.months.ago.beginning_of_month }
end

CreatedOnMonth只是一个范围：

CreatedOnMonth is just a scope:

scope :created_on_month, -> (month = Time.now) { where{ (created_at >= month.beginning_of_month) & (created_at <= month.end_of_month) } } 

created_at 是一个标准的日期时间时间戳。

created_at is just a standard datetime timestamp.

我如何优化它做一个查询，而不是12？

How can I optimize it to make one query instead of 12?

我看到一些人用GROUP_BY，但我不是在PostgreSQL好，能够建立这样的查询自己。查询应组记录按月份年份和收益计数。也许有人可以帮助我。谢谢你。

I saw some people use GROUP_BY, but I'm not that good with PostgreSQL to be able to build such query myself. The query should group records by month of the year and return count. Maybe someone could help me out. Thanks.

推荐答案

使用的<一个href="http://www.postgresql.org/docs/current/static/functions-datetime.html"><$c$c>date_trunc()功能在 GROUP BY 子句中，如果你想按每月，每年的：

Use the date_trunc() function in your GROUP BY clause, if you want to group by each month of each year:

GROUP BY date_trunc('month', created_at)

使用<一个href="http://www.postgresql.org/docs/current/static/functions-datetime.html"><$c$c>EXTRACT()功能在 GROUP BY 子句中，如果你想按每月每年：

Use EXTRACT() function in your GROUP BY clause, if you want to group by each month in every year:

GROUP BY EXTRACT(MONTH FROM created_at)

这篇关于如何使他们创造了Postgres的查询，以组记录按月份？ （：created_at日期时间列）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！