SQL 准备数据以显示图表

Question

我想建立注册用户图表(每天)

我将使用谷歌图表，所以我应该有数据可以使用

我写了一个 SQL 查询，它按天计算注册用户

SELECT DATE(created_at), COUNT(DISTINCT id) amount 
FROM users 
GROUP BY DATE(created_at)

这会返回一个包含数据和用户数的哈希值

{"date"=>"2013-09-24", "amount"=>"100"} 
{"date"=>"2013-09-26", "amount"=>"1"} 
{"date"=>"2013-10-01", "amount"=>"2"} 

问题:

我想在没有任何新用户的日子里显示 0 个注册，比如

{"date"=>"2013-09-25", "amount"=>"0"}

(我正在使用 pg 数据库，我也在 rails 应用程序中使用此查询，因此您可以使用 ActiveRecord 给我建议)

                           **UPDATE**

对于我的 rails 应用程序，我以这种方式解决了这个问题

#controller
  def charts
    users = User.select([:id, :created_at])
    @data = users.where("created_at >= ?", Date.today - 1.month)
    @inf = [] 
    @date = [] 
    month = Date.today - 1.month
    month.upto(Date.today).each do |date| 
      @date << date.strftime('%d/%m')   
      @inf << @data.select{|u| u.created_at.to_date == date }.size
    end 
  end

在 @inf和 @date数组我有注册用户和日期的计数。

Answer 1

您将需要您希望考虑的所有日期的来源。

例如从第一次进入到现在

WITH cte_days 
AS 
( 
    SELECT MIN(DATE(created_at)) AS [Day] FROM USERS -- First registered User
    UNION ALL 
    SELECT DATEADD(dd, 1, [Day]) 
    FROM cte_days
    WHERE DATEADD(dd, 1, [Day]) < GETDATE() -- Stop at today
)
SELECT [Day], COUNT(DISTINCT id) amount 
FROM cte_days LEFT JOIN users ON [Day] = DATE(created_at)
GROUP BY [Day]