10

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

 3 years ago
source link: https://blog.niclin.tw/2018/05/16/%E5%9C%A8-postgresql-%E4%B8%8B%E5%A6%82%E4%BD%95%E6%BC%82%E4%BA%AE%E7%9A%84%E6%8B%BF%E5%88%B0%E5%85%A9%E5%80%8B%E6%AC%84%E4%BD%8D%E6%99%82%E9%96%93%E5%B7%AE%E7%9A%84%E5%B9%B3%E5%9D%87/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Nic Lin's Blog

喜歡在地上滾的工程師

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

# == 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

參考來源：


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK