有個需求是，對一個集合算出所有的數據中，兩個欄位的時間相減，取全部平均花費時間。
狀況如下
# == Schema Information
#
# Table name: orders
# ...
# notify_at :datetime
# released_at :datetime
# ...
我們希望可以拿到訂單中，每一筆出貨時間與通知轉帳時間相減後的秒數，除以所有訂單
這樣一來，我就可以知道訂單平均在這個階段「付款 -> 出貨」所需花費時間的平均值
如果寫純 SQL query 當然不難
SELECT AVG(orders.released_at - orders.notify_at) FROM orders
但在 Rails 中，要如何對已有的大量數據做這樣的計算呢？
非正規化
- 對 orders 新增一個欄位
processing_time - 在 order 的
after_commitcallback 中計算並更新這個時間 - 寫 task 對以前的訂單進行 patching
- 最後你就可以用
Order.average(:processing_time)
會遇到幾個問題
- 每次訂單完成時，都會更新這個欄位，多一條 query
- 如果資料量龐大，task 會跑很久，也會在這個時間吃滿 db memory
寫純 SQL Query 在 model 內
也不是不行，但如果團隊都是 ORM 派的就很受不了了 XD
class Order < ApplicationRecord
def self.avg_released_time
sql = <<-SQL
SELECT AVG(orders.released_at - orders.notify_at) FROM orders
SQL
find_by_sql(sql)
end
end
# 拿到這個...也不能用
# [
# [0] #<Order:0x00007fe0c7d16370> {
# :id => nil
# }
# ]
更好的作法
既然有 average (ActiveRecord::Calculations) 可以用，那只要組合一下就行了
一開始會想 Order.average(:xxx), 這樣到底要怎麼寫？
xxx 裡面到底要放什麼？
能夠跟 where 一樣帶參數之類的嗎？
看了一下 source code 後發現，他其實是去呼叫 calculate
# File activerecord/lib/active_record/relation/calculations.rb, line 55
def average(column_name, options = {})
# TODO: Remove options argument as soon we remove support to
# activerecord-deprecated_finders.
calculate(:average, column_name, options)
end
於是我嘗試出了這樣的組合
Order.calculate(:average, "orders.notify_at - orders.created_at")
# (0.7ms) SELECT AVG(orders.notify_at - orders.created_at) FROM "orders"
# 0.0
發現好像成功了，但不知道為什麼數據總是 0.0
後來發現這樣的 timestamp 相減出來的數值是時間格式，但 average 這個 API 是預計回傳 Numeric, 所以就會無論如何都回傳 0.0
那麼就要用 PostgreSQL 的方法，把兩筆時間相減後變成數字，這樣 Rails 應該就接的到了
找了一下方法
用 epoch 可以將時間轉換成為秒數
SELECT EXTRACT(EPOCH from TIMESTAMP '2001-02-16 20:38:40');
Result: 982352320
SELECT EXTRACT(EPOCH from INTERVAL '5 days 3 hours');
Result: 442800
那麼把這個方法用 Rails 呼叫看看
Order.calculate(:average, "extract(epoch from orders.notify_at - orders.created_at)")
# (1.3ms) SELECT AVG(extract(epoch from orders.notify_at - orders.created_at)) FROM "orders"
# 51.146269
完美，這樣一來不用新增欄位，也可以快速的拉出數據，兼顧效能及美觀
稍微整理一下就可以變成一個好用的方法
class Order < ApplicationRecord
def self.avg_released_time
calculate(:average, "extract(epoch from orders.released_at - orders.notify_at)") || 0
end
end
# Usage
# Order.avg_released_time
參考來源：