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

前言

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

說明

預期會有

1. 一個 Model 紀錄股票名稱、代號 (Stock)
2. 一個 Model 紀錄每日收盤行情相關資訊 (DailyQuote)
3. 一個 Model 紀錄除權除息計算結果表 (ExStock)
4. Stock 與 DailyQuote 為一對多關聯
5. 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

小結

設計建立好 DB 後，接下來的兩篇會示範把前兩篇抓下來的資料存到 DB~

鐵人賽文章連結：https://ithelp.ithome.com.tw/articles/10272854
medium 文章連結：https://link.medium.com/s8TWR4KuTjb
本文同步發布於 小菜的 Blog https://riverye.com/

備註：之後文章修改更新，以個人部落格為主