如何获得这个组的Postgres按日期计包括天0 [英] How to get this postgres group by date count include days with 0
问题描述
我有这样的Postgres的查询,从过去的30天中选择的所有用户和组通过计数。
I have this Postgres query to select all users from the last 30 days and group by count.
@users = User.where('created_at >= ?', last_30_day).count(:order => 'DATE(created_at) ASC', :group => ["DATE(created_at)"])
的问题是,在返回这样
The problem is that is returning like this
2014-01-15 6
2014-01-13 2
2014-01-09 1
和我想获得与CERO结果的日子还有这样
And I would like to get the days with cero results as well like this
2014-01-15 6
2014-01-14 0
2014-01-13 2
2014-01-12 0
2014-01-11 0
2014-01-10 0
2014-01-09 1
不知道如何做到这一点?
Any idea how to do this?
推荐答案
要阐述的@mu_is_too_short回答,您可以用做 generate_series
像这样:
To expound on @mu_is_too_short answer you can do it using generate_series
like so:
SELECT dt.series, COUNT(u.created_at)
FROM users u
RIGHT OUTER JOIN (
SELECT
GENERATE_SERIES( (NOW() - INTERVAL '30 day')::date, NOW()::date, '1 day')::date
AS series
) AS dt
on DATE_TRUNC('month', u.created_at) = dt.series
GROUP BY dt.series
ORDER BY dt.series DESC
这会产生这样的:
Which yields like:
series | count
------------+-------
2014-01-08 | 0
2014-01-07 | 0
2014-01-06 | 0
2014-01-05 | 0
2014-01-04 | 0
2014-01-03 | 0
2014-01-02 | 0
2014-01-01 | 1
2013-12-31 | 0
Futhermore ActiveRecord的语法是相当难看,最好的办法是把它写在纯SQL上面,只是检索您的原始结果。是这样的:
Futhermore the ActiveRecord syntax for this is quite ugly, your best bet is to write it in the pure SQL above and just retrieve your raw results. Something like:
sql = <<-SQL
SELECT dt.series, COUNT(u.created_at)
FROM users u
RIGHT OUTER JOIN (
SELECT
GENERATE_SERIES( (NOW() - INTERVAL '30 day')::date, NOW()::date, '1 day')::date
AS series
) AS dt
on DATE_TRUNC('month', u.created_at) = dt.series
GROUP BY dt.series
ORDER BY dt.series DESC
SQL
records = User.connection.select_all(sql)
records.each do |record|
puts record['series']
end
由于&LT;&LT; - SQL
插值可以使用里面的变量来调整您的查询
Because <<-SQL
is interpolated you can use variables inside to adjust your query.
这篇关于如何获得这个组的Postgres按日期计包括天0的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!