Return hash from raw sql query

user_6849449 0
Viewed 1192

I'm building a ruby on rails application that uses raw SQL to query my database because I heard that it performs better than using ActiveRecord and I will be handling millions of records.

Lets say for simplicity’s sake, I have the following records in a Table1:

<id: 1, price: 20, quantity: 2, date: "2020-01-01T10:02:32"> 
<id: 2, price: 5, quantity: 1, date: "2020-01-01T10:32:12">
<id: 3, price: 10, quantity: 3, date: "2020-01-01T12:01:10">

What I want to do is get the total price * quantity per each hour as a hash or anything that makes sense. So in this case, the results would look like this:

{“2020-01-01 10:00:00”: 45, “2020-01-01 12:00:00”: 30}

As you can see, the value at 2020-01-01 10:00:00 is 45 and we got this form doing (20*2)+(5*1) since these records both have a date within the same hour.

Now originally, I had a simple loop in ruby that looped through this table and returned the desired results however I later learned that raw sql performs much better with larger data. I’m wondering how I can get this results using raw sql. I'm using postgresql. Any type of help is greatly appreciated. Sorry if it’s a noob question.

EDIT I changed the timestamps to be type string since that is how I'm getting the data.

postgresql ruby-on-rails
2 Answers

There are different ways you can execute a raw query in Rails (with ActiveRecord):

query = <<-SQL
  SELECT TO_CHAR(date::timestamptz, 'YYYY-MM-DD HH') AS formatted_date,
         SUM(price * quantity) AS total
  FROM table1s
  GROUP BY TO_CHAR(date::timestamptz, 'YYYY-MM-DD HH')
SQL

Table1.find_by_sql(query).to_h { |table| [table.formatted_date, table.total] }
# {"2020-01-01 12"=>30, "2020-01-01 10"=>45}

ActiveRecord::Base.connection.execute(query).values.to_h
# {"2020-01-01 12"=>30, "2020-01-01 10"=>45}

ActiveRecord::Base.connection.exec_query(query).rows.to_h
# {"2020-01-01 12"=>30, "2020-01-01 10"=>45}

You could give them a try and see how they perform. However, I must mention that the ActiveRecord version is much shorter, clear and easy to get:

Table1.group("TO_CHAR(date::timestamptz, 'YYYY-MM-DD HH')").sum('price*quantity')
# SELECT SUM(price*quantity) AS sum_priceallquantity, TO_CHAR(date, 'YYYY-MM-DD HH') AS to_char_date_yyyy_mm_dd_hh FROM "table1s" GROUP BY TO_CHAR(date, 'YYYY-MM-DD HH')
# {"2020-01-01 12"=>30, "2020-01-01 10"=>45}
user_50251160

if is a timestamp 

group by to_char(timestamp_field, 'YYYY-MM-DD HH')

and then in select

sum(price*quantity)
user_6949730
Related