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.
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}
if is a timestamp
group by to_char(timestamp_field, 'YYYY-MM-DD HH')
and then in select
sum(price*quantity)