I have very large table around 15 GB.
schema is create table cash_order(Record_Indicator char(2) not null, Segment char(4) not null,Order_number bigint unsigned not null, Transaction_Times bigint not null, Buy_Sell_Indicator char(1) not null, Activity_Type Int(1) not null,Symbol varchar(10) not null, Series char(2) not null, Volume_Disclosed int unsigned not null,Volume_Original int unsigned, Limit_Price int unsigned, Trigger_Price int unsigned, Market_Order_Flag char(1) not null,Stop_Loss_Flag char(1) not null, IO_Flag char(1), Algo_Indicator int(1), primary key(Order_number,Buy_Sell_Indicator,Symbol,Transaction_Times))max_rows=5000000000;
from that table I have to show the like
first column (distinct Symbol), second column (Number of orders which belong to Symbol), third column( number of orders which belong to Symbol and also Algo_Indicator=0), fourth column(number order which belong to Symbol and also Algo_Indicator=0, Market_Order_Flag='Y') like wise it goes on.
for example it should look like this
bharthi,1020,890,400,......
bharathi is symbol name, bharathi has 1020 number of orders. out of 1020 orders the 890 orders are algo_indicator=0, and out of 890 orders the 400 orders are market_order_flag='y'.
select Symbol,count(Order_number)from cash_order01 where Algo_Indicator=0 group by Symbol order by count(Order_number) desc into outfile '/tmp/exe4.csv';
I have used this command but i generated the number of order_number which satisfy the condition that Algo_Indicator=0 not the total order number;
Can any one help me.
You can compute the sums separately e.g. via views and then assemble them in the result:
create view col1(Symbol,sum1)
as select Symbol, count(distinct Order_number)
from cash_order
group by Symbol;
create view col2(Symbol,sum2)
as select Symbol, count(distinct Order_number)
from cash_order
where Algo_Indicator=0
group by Symbol;
create view col3(Symbol,sum3)
as select Symbol, count(distinct Order_number)
from cash_order
where Algo_Indicator=0 and Market_Order_Flag='Y'
group by Symbol;
select Symbol,sum1,sum2,sum3
from (col1 natural join col2) natural join col3
order by sum1 desc into outfile '/tmp/exe4.csv';
Cf. count(distinct Order_number) assumes the one you declared in the schema is really the primary key of the relation.
Try this example query: http://www.codeshare.io/5a1Pv
Maybe it will help you. I don't know how fast it's run, because I haven't tested it on real data, only on made up samples.
Third and fourth column values are related to algo_indicator=0, so you are getting like that. Either you need to modify the structure or subject your query to first and second columns itself so as to get total orders.
You can compute the sums separately e.g. via views and then assemble them in the result:
create view col1(Symbol,sum1)
as select Symbol, count(distinct Order_number)
from cash_order
group by Symbol;
create view col2(Symbol,sum2)
as select Symbol, count(distinct Order_number)
from cash_order
where Algo_Indicator=0
group by Symbol;
create view col3(Symbol,sum3)
as select Symbol, count(distinct Order_number)
from cash_order
where Algo_Indicator=0 and Market_Order_Flag='Y'
group by Symbol;
select Symbol,sum1,sum2,sum3
from (col1 natural join col2) natural join col3
order by sum1 desc into outfile '/tmp/exe4.csv';
Cf. count(distinct Order_number) assumes the one you declared in the schema is really the primary key of the relation.
Please notice that the above solution works with any standard SQL (even with SQL92) but may also be very inefficient depending on the smartness of the query optimizer. On the other hand, in some systems, conditional aggregates can be used (e.g., by means of the IF() function in MySQL or CASE WHEN construct in SQL Server). In MySQL the solution with conditional aggregates would be:
select Symbol,
count(distinct Order_number) as sum1,
count(distinct if(Algo_Indicator=0,Order_number,NULL)) as sum2,
count(distinct if(Algo_Indicator=0 and Market_Order_Flag='Y',Order_number,NULL)) as sum3
from cash_order group by Symbol
order by sum1 desc into outfile '/tmp/exe4.csv';
- Badges
- Science topic
- Similar topics
- Computer Science
- Data Mining