5 months ago

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

狀況如下

# == Schema Information

#

# Table name: orders

# ...

#  notify_at       :datetime

#  released_at   :datetime

# ...

我們希望可以拿到訂單中,每一筆出貨時間與通知轉帳時間相減後的秒數,除以所有訂單

這樣一來,我就可以知道訂單平均在這個階段「付款 -> 出貨」所需花費時間的平均值

如果寫純 SQL query 當然不難

SELECT AVG(orders.released_at - orders.notify_at) FROM orders

但在 Rails 中,要如何對已有的大量數據做這樣的計算呢?

非正規化

  1. 對 orders 新增一個欄位 processing_time
  2. 在 order 的 after_commit callback 中計算並更新這個時間
  3. 寫 task 對以前的訂單進行 patching
  4. 最後你就可以用 Order.average(:processing_time)

會遇到幾個問題

  1. 每次訂單完成時,都會更新這個欄位,多一條 query
  2. 如果資料量龐大,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

參考來源:

← [Design pattern] 表現層的處理方式 [Rails] 邏輯抽象出 service object 需注意的點 →
 
comments powered by Disqus