Using postgres to search for some data

Question

I am using pg and need to get some datas from db to create a chart on my page.

Here is the table schema:

t.decimal  "balance",     precision: 16, scale: 2
t.datetime "created_at",                           null: false
t.datetime "updated_at",                           null: false

This is an example of the datas

[#<Account:0x007f8a58b8aee0
  id: 1,
  balance: 5000,
  created_at: Sun, 16 Nov 2015 02:27:43 UTC +00:00,
  updated_at: Sun, 16 Nov 2015 02:27:43 UTC +00:00,
  order_id: nil>,
 #<Account:0x007f8a58b8aaf8
  id: 2,
  balance: 4000,
  created_at: Sun, 16 Nov 2015 15:05:07 UTC +00:00,
  updated_at: Sun, 16 Nov 2015 15:05:07 UTC +00:00,
  order_id: nil>,
 #<Account:0x007f8a58b8a7d8
  id: 3,
  balance: 3000,
  created_at: Sun, 15 Nov 2015 15:07:04 UTC +00:00,
  updated_at: Sun, 15 Nov 2015 15:07:04 UTC +00:00,
  order_id: nil>,
 #<Account:0x007f8a58b8a508
  id: 4,
  balance: 2000,
  created_at: Sun, 15 Nov 2015 15:12:32 UTC +00:00,
  updated_at: Sun, 15 Nov 2015 15:12:32 UTC +00:00,
  order_id: nil>,
 #<Account:0x007f8a58b8a148
  id: 5,
  balance: 1800,
  created_at: Sun, 13 Nov 2015 15:14:14 UTC +00:00,
  updated_at: Sun, 13 Nov 2015 15:14:14 UTC +00:00,
  order_id: nil>]

Say for example below is the desire result I want to get:

[4000, 2000, 1800, 1800, 1800]

The conditions for the results are: 1) I want to get the result for last 5 days, so the array will always have 5 data. 2) I have to get the latest balance for the day. 3) If any of the day there is no transaction, the balance will be equal to the balance of the day before.

UPDATE:

I tried to get array of hashes from the datas like this:

Account.where("created_at > ?", Date.today - 5).map{|x| {x.created_at => x.balance}}

And get the following results:

[{Sun, 16 Nov 2015 02:27:43 UTC +00:00=>
   5000},
 {Sun, 16 Nov 2015 15:05:07 UTC +00:00=>
   4000},
 {Sun, 15 Nov 2015 15:07:04 UTC +00:00=>
   3000},
 {Sun, 15 Nov 2015 15:12:32 UTC +00:00=>
   2000},
 {Sun, 13 Nov 2015 15:14:14 UTC +00:00=>
   1800}]

I think I am on the right path, now I have to merge the keys which have the same date, and get the value of the latest time on that date. Any idea how I can do this? Thanks.

How this has to be done? Using ActiveRecord or pg gem?

Wand Maker
– Wand Maker

2016-01-18 17:01:47 +00:00
Commented Jan 18, 2016 at 17:01 — Wand Maker
– Wand Maker, Commented Jan 18, 2016 at 17:01

mabe02 · Accepted Answer · 2016-01-19 13:49:09Z

2

The conditions for the results are: 1) I want to get the result for last 5 days, so the array will always have 5 data. 2) I have to get the latest balance for the day. 3) If any of the day there is no transaction, the balance will be equal to the balance of the day before.

Get the result for the last 5 days:

five_days_ago = Date.today - 5 # you can make a date diff just subtracting the number of days
Account.where("created_at > ?", five_days_ago)

Get the latest balance for each and every day: you should get the balance by date and get the one where the created_at is the max. However this might be easier to do in Ruby processing your output:

require 'date'

five_days_ago = Date.today - 5

query = Account.where("created_at > ?", Date.today - 5).map{|x| {x.created_at => x.balance}}
query = query.reduce({}, :merge) # reduce to one hash

# Create an hash with the dates and the latest created_at
dates = query.group_by{|k, v| Date.parse(k.to_s)}
dates.map{|k, v| {Date.parse(v.max.to_s) => v.max }

# Create an output hash including all dates
result = {}
last_available_balance = 0
(five_days_ago...Date.today).each do |d|

  if dates.keys.includes? d
    # assign the value corresponding to the latest timestamp
    last_available_balance = query[dates[d]]
    result[d] = last_available_balance

  else
    # keep the value of the last available balance
    result[d] = last_available_balance

  end
end

edited Jan 19, 2016 at 13:49

answered Jan 18, 2016 at 21:18

mabe02

2,7642 gold badges22 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mabe02 Over a year ago

I am replying from the phone right now. I was planning to edit the answer and explain it tomorrow morning

pyfl88 Over a year ago

Thanks for the answer, but I think my condition to get the latest balance on the dates..

mabe02 Over a year ago

Now it should work! You can still make an optimization on the last_available_balance being as default equal to the latest balance outside your time range (last five days)

Collectives™ on Stack Overflow

Using postgres to search for some data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related