在 PostgreSQL 下如何漂亮的拿到兩個欄位時間差的平均

2018-05-16

有個需求是，對一個集合算出所有的數據中，兩個欄位的時間相減，取全部平均花費時間。

狀況如下

# == 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_commit callback 中計算並更新這個時間
寫 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

參考來源：

Nic Lin's Blog

非正規化

寫純 SQL Query 在 model 內

更好的作法