select ...
create table <new one> select * from <orig>;
RENAME TABLE tbl_name TO new_tbl_name
select symbol,min(date) as mdate from td where date !='0000-00-00' group by symbol order by mdate;
tables
opentick_trades - all trades data from opentick
tickdata_minutes_gmt - all minutes data from tickdat GMT time
td - same as about but GMT-5;
td_open - same as above but between 9:30-10:00:
update pet set owner = 'yosi' where owner = 'ori';
select t1.date,t2.date from ews_3 t1,ews_13 t2 limit 6;
select avg(open) from ews_3;
set sabstruction:
select date from ews_13 where date != all(select date from ews_3);
when does the trade starts?
select date,min(time) from market_data.td where time between '09:30:00' and '10:00:00' and symbol = 'ews' group by date limit 6;
applying a function:
select time,basket * 10+pow(2,2) as yofi from td_open limit 6;
using variables:
select @delta:=5;
select @delta from td_open limit 6;
counting the symbols
select count(t1.symbol) from (select symbol from td_open group by symbol) t1;
show times after delta:
select date,time,open from td_open where symbol = 'ews' and time = addtime('09:30:00',@delta) limit 6;
only relevant delta ..
select t1.date,t1.time,t1.open from td_open t1, open_times_ews t2 where t1.symbol = 'ews' and t1.time = addtime('09:30:00',@delta) and t1.date = t2.date and t2.open_time < '09:32:00' limit 6;
how to construct open+delta information
1. select the line of open+delta for specific symbol:
create table ews_plus_5 select * from market_data.td_open where time = addtime('09:30:00','00:05:00');
2. create table of actual open times:
create table ews_open_times select date,min(time) as open_time from market_data.td_open where symbol = 'ews' group by date;
3. select the open+delta of those days that doesn't start late.
select t1.date,t1.time,t1.open from ews_plus_5 t1, ews_open_times t2 where t1.date = t2.date and t2.open_time < '09:32:00' limit 6;
using opentick data
multi grouping:
select date,basket,count(basket) from opentick_trades group by basket,date limit 6;
using case in order to count different things...
select date,sum(case when time <'10:01:00' then 1 else 0 end) as smaller,sum(case when time >'10:01:00' then 1 else 0 end) as bigger from tmp group by date;
select sum(case when basket=1 then 1 else 0 end) as basket1,sum(case when basket=2 then 1 else 0 end) as basket2 from opentick_trades group by date limit 6;
create table basket_cmp select date,sum(case when basket=1 then 1 else 0 end) as basket1,sum(case when basket=13 then 1 else 0 end) as basket13, sum(case when basket=226 then 1 else 0 end) as basket226, sum(case when basket=265 then 1 else 0 end) as basket265 from opentick_trades group by date;
sql 2 csv
select * into outfile '/tmp/tt1.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from open_2 order by date;
select * into outfile '/tmp/open_2.csv' fields terminated by ',' lines terminated by '\n' from open_2 order by date;
include titles (headers field names ...)
select 'title1', 'title2' union select * into outfile '/tmp/cc.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from tt;
connection to matlab
function example()
mysql( 'open','localhost','root','skaya7');
mysql('use','ori_db');
mysql('show tables');
mysql('select * from shop'); % [a,b,c] = mysql('select * from shop');
end
indexing
Indexing is aimed to improve efficiency of information retrieval in cost of maintaining an additional table
create index index_date_opentick_trades on opentick_trades (date);
building the index took a long time (<> 5 min) for a 6 million lines of the table.
now, the following is much quicker:
select * from opentick_trades where date between '2006-5-10' and '2006-6-15' limit 7;
boost of <> X15
function example()
mysql( 'open','localhost','root','skaya7');
mysql('use','ori_db');
mysql('show tables');
mysql('select * from shop'); % [a,b,c] = mysql('select * from shop');
end
indexing
Indexing is aimed to improve efficiency of information retrieval in cost of maintaining an additional table
create index index_date_opentick_trades on opentick_trades (date);
building the index took a long time (<> 5 min) for a 6 million lines of the table.
now, the following is much quicker:
select * from opentick_trades where date between '2006-5-10' and '2006-6-15' limit 7;
boost of <> X15
building from a backup.sql
mysql -u root -p****** -h localhost ori < backup/wikidb.sql
(the wikidb.sql can be downloaded from the backup utility of cpanel)
