Re: Join

[Austin Kalb]

I think this is better... :)

Thanks!
A

'CREATE TABLE `todaysstockoptions` (
  `optionSymbol` varchar(20) NOT NULL COMMENT ''The munged AF option symbol.'',
  `stockSymbol` varchar(20) DEFAULT NULL COMMENT ''The underlying stock symbol in AF format, i.e., all CAPS ending in a period (.e.g. IBM.)'',
  `localCode` varchar(45) DEFAULT NULL COMMENT ''OSI / OPRA Symbol'',
  `strikePrice` decimal(9,4) DEFAULT NULL COMMENT ''Strike price in USD.'',
  `expirationDate` date DEFAULT NULL,
  `PC` char(1) DEFAULT NULL COMMENT ''P = put, C = call'',
  `OI` int(11) DEFAULT NULL COMMENT ''Open interest going into today as reported by OCC.'',
  `dailyTotalBuyValue` decimal(14,2) DEFAULT ''0.00'',
  `averageDailyTotalBuyValue` decimal(14,2) DEFAULT ''0.00'',
  `dailyTotalSellValue` decimal(14,2) DEFAULT ''0.00'',
  `averageDailyTotalSellValue` decimal(14,2) DEFAULT ''0.00'',
  `dailyTotalValue` decimal(14,2) DEFAULT ''0.00'',
  `averageDailyTotalValue` decimal(14,2) DEFAULT ''0.00'',
  `dailyTotalBuyVolume` decimal(14,2) DEFAULT ''0.00'',
  `averageDailyTotalBuyVolume` decimal(14,2) DEFAULT ''0.00'',
  `dailyTotalSellVolume` decimal(14,2) DEFAULT ''0.00'',
  `averageDailyTotalSellVolume` decimal(14,2) DEFAULT ''0.00'',
  `dailyTotalVolume` int(11) DEFAULT ''0'',
  `averageDailyTotalVolume` decimal(14,2) DEFAULT ''0.00'',
  `previousVolume` int(11) DEFAULT ''0'',
  `previousOI` int(11) DEFAULT ''0'',
  `dailyNumBuyTrades` decimal(9,2) DEFAULT ''0.00'',
  `averageDailyNumBuyTrades` decimal(14,2) DEFAULT ''0.00'',
  `dailyNumSellTrades` decimal(9,2) DEFAULT ''0.00'',
  `averageDailyNumSellTrades` decimal(14,2) DEFAULT ''0.00'',
  `dailyNumTotalTrades` int(11) DEFAULT ''0'',
  `averageDailyNumTotalTrades` decimal(14,2) DEFAULT ''0.00'',
  `selected` tinyint(4) DEFAULT ''0'',
  `subscribed` tinyint(4) DEFAULT ''0'',
  `tradeDate` date DEFAULT NULL COMMENT ''The date associated with the data in this record.'',
  `averageBuyTradeValue` decimal(14,2) DEFAULT ''0.00'',
  `averageSellTradeValue` decimal(14,2) DEFAULT ''0.00'',
  `averageTotalTradeValue` decimal(14,2) DEFAULT ''0.00'',
  `averageBuyTradeSize` decimal(14,2) DEFAULT ''0.00'',
  `averageSellTradeSize` decimal(14,2) DEFAULT ''0.00'',
  `averageTotalTradeSize` decimal(14,2) DEFAULT ''0.00'',
  `sdBuyTradeValue` decimal(14,2) DEFAULT ''0.00'',
  `sdSellTradeValue` decimal(14,2) DEFAULT ''0.00'',
  `sdTotalTradeValue` decimal(14,2) DEFAULT ''0.00'',
  `sdBuyTradeSize` decimal(14,2) DEFAULT ''0.00'',
  `sdSellTradeSize` decimal(14,2) DEFAULT ''0.00'',
  `sdTotalTradeSize` decimal(14,2) DEFAULT ''0.00'',
  `sdDailyNumBuyTrades` decimal(14,2) DEFAULT ''0.00'',
  `sdDailyNumSellTrades` decimal(14,2) DEFAULT ''0.00'',
  `sdDailyNumTotalTrades` decimal(14,2) DEFAULT ''0.00'',
  `sdDailyTotalBuyVolume` decimal(14,2) DEFAULT ''0.00'',
  `sdDailyTotalSellVolume` decimal(14,2) DEFAULT ''0.00'',
  `sdDailyTotalVolume` decimal(14,2) DEFAULT ''0.00'',
  `sdDailyTotalBuyValue` decimal(14,2) DEFAULT ''0.00'',
  `sdDailyTotalSellValue` decimal(14,2) DEFAULT ''0.00'',
  `sdDailyTotalValue` decimal(14,2) DEFAULT ''0.00'',
  `optionStrength` decimal(9,2) DEFAULT NULL,
  PRIMARY KEY (`optionSymbol`),
  KEY `fk_todaysStockOptions_todaysStocks1_idx` (`stockSymbol`),
  KEY `PC_idx` (`PC`),
  KEY `optionSymbol_idx` (`optionSymbol`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=''All of the stock options available today as determined by the PreMarketProcessor.'''

'CREATE TABLE `todaysstockoptiontrades` (
  `optionSymbol` varchar(20) NOT NULL,
  `tradeID` int(11) NOT NULL COMMENT ''This comes from the exchange.'',
  `side` decimal(6,2) DEFAULT NULL COMMENT ''The % prob of a Buy. A Null value is Unknown - due to mismatched time-stamps.'',
  `tradePrice` decimal(9,4) DEFAULT NULL,
  `tradeSize` int(11) DEFAULT NULL,
  `tradeExchange` varchar(20) DEFAULT NULL,
  `tradeValue` decimal(14,2) DEFAULT NULL COMMENT ''size * price * 100'',
  `tradeDateTime` datetime(3) DEFAULT NULL,
  `tradeCondition` varchar(10) DEFAULT NULL COMMENT ''The condition code sent by the exchange'',
  `stockPrice` decimal(9,2) DEFAULT NULL COMMENT ''The stock price at the time of this trade'',
  `stockDayChangePct` decimal(6,2) DEFAULT NULL COMMENT ''The change in stock price at the time of this trade. Big changes in either direction usually trigger option buying. '',
  `amountOTM` decimal(9,2) DEFAULT NULL COMMENT ''More important than stock price, but can be used to infer the stock price at trade time.'',
  `pctOTM` decimal(6,2) DEFAULT NULL,
  `outsideTrade` tinyint(4) DEFAULT NULL COMMENT ''If trade is more than 0.01M outside the current market'',
  `stockCumVolume` int(11) DEFAULT NULL,
  `optionBid` decimal(9,4) DEFAULT NULL,
  `optionAsk` decimal(9,4) DEFAULT NULL,
  `tradeStrength` decimal(9,2) DEFAULT NULL,
  PRIMARY KEY (`optionSymbol`,`tradeID`),
  KEY `tradeDateTime_idx` (`tradeDateTime`),
  KEY `tradeValue_idx` (`tradeValue`),
  KEY `optionSymbol_idx` (`optionSymbol`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=''A record ov every individual trade for that tradeDate.'''

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: todaysStockOptionTrades
         type: index
possible_keys: PRIMARY,optionSymbol_idx
          key: tradeDateTime_idx
      key_len: 8
          ref: NULL
         rows: 55999
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: todaysStockOptions
         type: eq_ref
possible_keys: PRIMARY,optionSymbol_idx
          key: PRIMARY
      key_len: 22
          ref: itrader.todaysStockOptionTrades.optionSymbol
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

Edited 1 time(s). Last edit at 04/12/2013 12:46PM by Austin Kalb.