SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 20.10.2014 16:51:38

kleon
Участник
Зарегистрирован: 20.10.2014
Сообщений: 1

Помогите оптимизировать однотипные запросы

Добрый день
подскажите как можно переписать запрос

Вот такой запрос

SELECT
t.name,
t.id,
t.trader_id,
t.create_date,
replace(CONVERT((t.control_capital/1000),DECIMAL(10,2)),".","") as control_capital,
replace(CONVERT((t.control_sum/1000),DECIMAL(10,2)),".","") as control_sum,
t.tp_week,
t.conditionally_periodic,
t.profit30d,
t.profit,
t.rel_drawdown,
(DATEDIFF(from_unixtime(p.offer_date,"%Y-%m-%d"),current_timestamp)/7) as days_to_end,
t.max_drawdown,
from_unixtime(t.last_update+10800,"%d-%m-%Y %H:%i") as last_update,
p.status,
p.start_capital,
p.current_capital,
p.sum,
p.relative_drawdown,
p.max_drawdown,
p.open_transactions,
p.exp_yield,
p.instalation_rollover,
p.prev_rollover,
from_unixtime(p.next_rollover,"%d-%m-%Y") as next_rollover,
from_unixtime(p.offer_date,"%d-%m-%Y") as offer_date,
of.reward,
of.min_sum,
of.responsibility,
IFNULL((select CONVERT(diff/1000,DECIMAL(10,1)) from trader_capital_change where trader_id = t.trader_id order by date desc limit 1),0) as trd_capdiff_chng,
IFNULL((select CONVERT(profit/1000,DECIMAL(10,1)) from trader_capital_change where trader_id = t.trader_id order by date desc limit 1),0) as trd_capprof_chng,
IFNULL((select date from trader_capital_change where trader_id = t.trader_id order by date desc limit 1),0) as trd_capdate_chng,
IFNULL((select CONVERT(diff/1000,DECIMAL(10,1)) from trader_investor_capital_change where trader_id = t.trader_id order by date desc limit 1),0) as invs_capdiff_chng,
IFNULL((select CONVERT(profit/1000,DECIMAL(10,2)) from trader_investor_capital_change where trader_id = t.trader_id order by date desc limit 1),0) as invs_capprof_chng,
IFNULL((select date from trader_investor_capital_change where trader_id = t.trader_id order by date desc limit 1),0) as invs_capdate_chng,
IFNULL((select CONVERT(diff,DECIMAL(10,1)) from investors_profit_changes where trader_id = t.trader_id order by date desc limit 1),0) as invs_diff_chng,
IFNULL((select CONVERT(profit,DECIMAL(10,2)) from investors_profit_changes where trader_id = t.trader_id order by date desc limit 1),0) as invs_prft_chng,
IFNULL((select date from investors_profit_changes where trader_id = t.trader_id order by date desc limit 1),0) as invs_prft_chng_date,
IFNULL((select diff from trader_open_transactions_change where trader_id = t.trader_id order by date desc limit 1),0) as open_trns,
IFNULL((select profit from trader_open_transactions_change where trader_id = t.trader_id order by date desc limit 1),0) as open_trns_curr,
IFNULL((select date from trader_open_transactions_change where trader_id = t.trader_id order by date desc limit 1),0) as open_trns_date,
IFNULL((select CONVERT(diff,DECIMAL(10,1)) from trader_current_max_drawdown_change where trader_id = t.trader_id order by date desc limit 1),0) as maxdd_change,
IFNULL((select CONVERT(profit,DECIMAL(10,1)) from trader_current_max_drawdown_change where trader_id = t.trader_id order by date desc limit 1),0) as maxdd_prft_change,
IFNULL((select date from trader_current_max_drawdown_change where trader_id = t.trader_id order by date desc limit 1),0) as maxdd_date,
(select CONVERT(max(percents),DECIMAL(10,2)) from traders_detailed_week where trader_id = t.trader_id and start<='1413791806' and start>='1382216400') as max,
(select CONVERT(min(percents),DECIMAL(10,2)) from traders_detailed_week where trader_id = t.trader_id and start<='1413791806' and start>='1382216400') as min,
(select CONVERT(max(percents),DECIMAL(10,2)) from traders_detailed_month where trader_id = t.trader_id) as month_max,
(select CONVERT(min(percents),DECIMAL(10,2)) from traders_detailed_month where trader_id = t.trader_id) as month_min,
(select CONVERT(percents*(1-(of.reward/100)),DECIMAL(10,2)) from traders_detailed_week where trader_id = t.trader_id order by start desc limit 1) as invesotr_percents_profit,
IFNULL((select replace(CONVERT((sum(profit)/1000),DECIMAL(10,2)),".","") from traders_detailed_week where trader_id = t.trader_id and start>=1387511806  and start<=1413791806 ),0) as sum,
IFNULL((select CONVERT(avg(percents),DECIMAL(10,2)) as avg from traders_detailed_week where trader_id = t.trader_id and start>=1387511806 and start<=1413791806 ),0)as avg,
(select CONVERT(sum(profit)/1000, DECIMAL(10,2)) from traders_detailed_week where trader_id = t.trader_id) as all_sum,
IFNULL(us_t.id,0) as favorite,
IFNULL(ui.sum,0) as investor_sum,
(SELECT CEIL(COUNT(percents)/2) FROM traders_detailed_month where trader_id = t.trader_id) as median
FROM `traders` `t`
LEFT JOIN `pamm_details` `p` ON p.trader_id = t.trader_id
LEFT JOIN `oferta` `of` ON of.trader_id = t.trader_id and of.trader_id = t.trader_id and p.trader_id = t.trader_id
LEFT JOIN `users_trader` `us_t` ON us_t.trader_id = t.trader_id and us_t.trader_id = t.trader_id and user_id=19
LEFT JOIN `user_investment` `ui` ON ui.trader_id=t.trader_id and ui.user_id=19
GROUP BY `t`.`trader_id`


-----------


Как переписать эти строки

IFNULL((select CONVERT(diff/1000,DECIMAL(10,1)) from trader_capital_change where trader_id = t.trader_id order by date desc limit 1),0) as trd_capdiff_chng,
IFNULL((select CONVERT(profit/1000,DECIMAL(10,1)) from trader_capital_change where trader_id = t.trader_id order by date desc limit 1),0) as trd_capprof_chng,
IFNULL((select date from trader_capital_change where trader_id = t.trader_id order by date desc limit 1),0) as trd_capdate_chng,


Чтоб они 3 раза не выполняли запрос



Спасибо

Неактивен

 

#2 21.10.2014 22:32:33

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Помогите оптимизировать однотипные запросы

Можно переписать через JOIN с подзапросом.

SELECT ...., IFNULL(J1.trd_capdiff_chng,0),IFNULL(J1.trd_capprof_chng,0), IFNULL(J1.trd_capdate_chng,0) FROM ....
LEFT JOIN (select CONVERT(diff/1000,DECIMAL(10,1)) as trd_capdiff_chng, CONVERT(profit/1000,DECIMAL(10,1)) as trd_capprof_chng, date as trd_capdate_chng from trader_capital_change where trader_id = t.trader_id order by date desc limit 1) AS J1 ON 1;
 


Условие JOIN обязательно для LEFT JOIN, поэтому указана 1.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson