Querying a cumulative sum of a boolean column per day

Question

I've been attempting to get a cumulative sum of a boolean column per day. I've tried different ways using either #sum and looked up #distinct too (by looking at other posts on Stack Overflow) but haven't been able to successfully implement either of those methods in a way I'd like my hash to look.

Currently, this is the query I have:

Device.where(boxed: true).group('date(updated_at)').count

Which gives me this:

{Fri, 17 Apr 2015=>48, Sat, 18 Apr 2015=>44, Sun, 19 Apr 2015=>5, Mon, 20 Apr 2015=>48}

But I would like it to look like this:

{Fri, 17 Apr 2015=>48, Sat, 18 Apr 2015=>92, Sun, 19 Apr 2015=>97, Mon, 20 Apr 2015=>145}

I am using Rails 4 and Postgres. Any help would be appreciated.

Edit:

I ended up doing this to get the cumulative sum, even though this probably is not the best way or elegant at all:

data_all_time = Device.where(boxed: true).group('date(updated_at)').count 
data_all_time_keys = data_all_time.keys
data_all_time_values = data_all_time.values
sum = 0
data_all_time_values.map!{|x| sum += x}

all_time_sum_tuple = data_all_time_keys.zip(data_all_time_values)

I end up getting something like this:

[["April 17, 2015", 48], ["April 18, 2015", 92], ["April 19, 2015", 97], ["April 20, 2015", 145]]

Which works for now until I can figure out a better way.

Answer 1

I would keep your sql as is

data = Device.where(boxed: true).group('date(updated_at)').count

and then do the custom aggregation in ruby

result = {}
keys = []
data.each_with_index do |(key, value), index| 
  keys << key
  result[key] = data.values_at(*keys).sum
end    

the above ruby could be cleaned up a bit, but ...

One other thing to watch out for - subtle time zone issues

-- your data is probably stored in UTC and this will group by the UTC dates
'date(updated_at)'

depending on where your users are viewing the reports from this may or may not return the counts they expect

• postgres AT TIME ZONE might help you in this case