MySQL Forums
Forum List  »  Views

Re: Join
Posted by: Austin Kalb
Date: April 12, 2013 11:03AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
5623
April 11, 2013 04:14PM
2352
April 11, 2013 11:41PM
2431
April 12, 2013 12:10AM
2330
April 12, 2013 10:21AM
Re: Join
2492
April 12, 2013 11:03AM
2346
April 15, 2013 01:05PM
2442
April 15, 2013 01:09PM
2652
April 15, 2013 03:29PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.