Day22 - 用 Ruby on Rails 處理臺灣證券交易所資料-DB 設計

13th鐵人賽

 2021/10/05

字數統計: 729閱讀時間: 3 min

前言

有了前 2 篇從「臺灣證券交易所」取得 CSV 檔後，接著要把資料存入 DB，在存入前，需要先有 DB，本篇以 DB 設計為主

說明

預期會有

一個 Model 紀錄股票名稱、代號 (Stock)
一個 Model 紀錄每日收盤行情相關資訊 (DailyQuote)
一個 Model 紀錄除權除息計算結果表 (ExStock)
Stock 與 DailyQuote 為一對多關聯
Stock 與 ExStock 為一對多關聯

實作

class CreateStocks < ActiveRecord::Migration[6.1]
  def change
    create_table :stocks do |t|
      t.string   :name, null: false
      t.string   :code, null: false
      t.datetime :deleted_at

      t.timestamps
    end

    add_index :stocks, :code, unique: true
  end
end

class CreateDailyQuotes < ActiveRecord::Migration[6.1]
  def change
    create_table :daily_quotes do |t|
      t.string  :code, null: false
      t.date    :transaction_date, null: false # 收盤日期
      t.bigint  :trade_volume                  # 成交股數
      t.bigint  :number_of_transactions        # 成交筆數
      t.bigint  :trade_price                   # 成交金額
      t.float   :opening_price                 # 開盤價
      t.float   :highest_price                 # 最高價
      t.float   :lowest_price                  # 最低價
      t.float   :closing_price                 # 收盤價
      t.string  :ups_and_downs                 # 漲跌
      t.float   :price_difference              # 價差
      t.float   :last_best_bid_price           # 最後揭示買價
      t.bigint  :last_best_bid_volume          # 最後揭示買量
      t.float   :last_best_ask_price           # 最後揭示賣價
      t.bigint  :last_best_ask_volume          # 最後揭示賣量
      t.float   :price_earning_ratio           # 本益比

      t.timestamps
    end

    add_index :daily_quotes, %i[code transaction_date], unique: true
  end
end

class CreateExStocks < ActiveRecord::Migration[6.1]
  def change
    create_table :ex_stocks do |t|
      t.string  :code,                        null: false
      t.date    :data_date,                   null: false # 資料日期
      t.float   :closing_price_before,        null: false # 除權息前收盤價
      t.float   :reference_price,             null: false # 除權息參考價
      t.float   :dr_value,                    null: false # 權值+息值
      t.integer :dividend_right,              null: false # 權/息
      t.float   :limit_up,                    null: false # 漲停價格
      t.float   :limit_down,                  null: false # 跌停價格
      t.float   :opening_reference_price,     null: false # 開盤競價基準
      t.float   :ex_dividend_reference_price, null: false # 減除股利參考價
      t.string  :reporting_day                            # 最近一次申報資料 季別/日期
      t.float   :price_book                               # 最近一次申報每股 (單位)淨值
      t.float   :eps                                      # 最近一次申報每股 (單位)盈餘

      t.timestamps
    end

    add_index :ex_stocks, %i[code data_date], unique: true
  end
end

# app/models/stock.rb

class Stock < ApplicationRecord
  validates :name, :code, presence: true
  validates :code, uniqueness: true

  has_many :daily_quotes,               foreign_key: :code, primary_key: :code, dependent: :destroy
  has_many :exs, class_name: "ExStock", foreign_key: :code, primary_key: :code, dependent: :destroy
  
  scope :latest_transaction_date, -> (date = nil) do
    date = date ? date.to_date : DailyQuote.latest_transaction_date
    self.joins(:daily_quotes).where(daily_quotes: { transaction_date: date })
  end
end

# app/models/daily_quote.rb

class DailyQuote < ApplicationRecord
  acts_as_paranoid

  validates :code, :transaction_date, presence: true
  validates :code, uniqueness: { scope: :transaction_date,
    message: "該收盤日期已有紀錄" }

  belongs_to :stock, foreign_key: :code, primary_key: :code

  scope :latest_transaction_date, -> { maximum(:transaction_date) }
end

# app/models/ex_stock.rb

class ExStock < ApplicationRecord
  validates :code, :data_date, :closing_price_before, :reference_price, :dr_value,
            :dividend_right, :limit_up, :limit_down, :opening_reference_price, :ex_dividend_reference_price,
            presence: true
  validates :code, uniqueness: { scope: :data_date, message: "該資料日期已有紀錄" }

  belongs_to :stock, foreign_key: :code, primary_key: :code

  DIVIDEND_RIGHT = {
    "息"   => "xd",
    "權"   => "xr",
    "權息" => "dr",
  }

  enum dividend_right: {
    xd: 0, # 除息 (Exclude Dividend)
    xr: 1, # 除權 (Exclude Right)
    dr: 2, # 除權除息(同時) (DR (Dividend + Right))
  }

  scope :latest_data_date, -> { maximum(:data_date) }
end