Day22 - 用 Ruby on Rails 处理台湾证券交易所资料-DB 设计

前言

有了前 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