0.01-0.1赠金

/* 所有0.01bonus用户的运营数据 */
SELECT E.in_client_id,U.email, U.telephone, U.language, U.country_code, FROM_UNIXTIME(U.register_time/1000,('%Y-%m-%d %H:%i:%S')) AS register_time, T.total_deposit AS 'deposit',T.times_deposit AS 'times_d',T.total_withdraw AS 'withdraw', T.times_withdraw AS 'times_w', A.margin_balance/1e8 AS margin_balance, TRADE.volume, /*B.amount,*/  
SUM(E.amount)/1e8 AS 'bonus_in', E2.funding_in, CM.commission/1e8 AS 'commission',E3.all_out,
A.margin_balance/1e8 - T.total_deposit + ifnull(T.total_withdraw,0) - ifnull(SUM(E.amount/1e8),0) - ifnull(E2.funding_in,0) - ifnull(CM.commission/1e8,0) + ifnull(E3.all_out,0) AS 'PNL'
/*
用户总PNL = 账户余额 - 充值 + 提币 - 赠金 - 配资 - 得到佣金 + 赠金配资撤回
*/
FROM management.t_donation_transfer AS E
LEFT JOIN (SELECT transfer_in_client_id, SUM(transfer)/1e8 AS 'funding_in' FROM management.t_verify_transfer 
    WHERE transfer_status = 7 GROUP BY transfer_in_client_id) AS E2
    ON E.in_client_id = E2.transfer_in_client_id
LEFT JOIN (SELECT transfer_out_client_id, SUM(transfer)/1e8 AS 'all_out' FROM management.t_verify_transfer 
    WHERE transfer_status = 7 GROUP BY transfer_out_client_id) AS E3
    ON E.in_client_id = E3.transfer_out_client_id
LEFT JOIN (SELECT inviter, SUM(commission_amount) AS 'commission' FROM affiliate.t_affiliate_commission_transfer GROUP BY inviter) AS CM
    ON E.in_client_id = CM.inviter
LEFT JOIN management.t_trade_user U
    ON E.in_client_id = U.USER_ID
LEFT JOIN (
    SELECT USER_ID, transaction_type, transaction_status, 
    count(if(transaction_type='1',true,null)) as 'times_deposit',
    count(if(transaction_type='2' AND transaction_status='7',true,null)) as 'times_withdraw',
    SUM(IF(transaction_type='1',amount/1e8,0)) AS 'total_deposit',
    SUM(IF(transaction_type='2' AND transaction_status='7',amount/1e8,0)) AS 'total_withdraw'
    FROM management.t_wallet_transaction_history WHERE transaction_type IN ('1','2')
    GROUP BY USER_ID) AS T
    ON E.in_client_id = T.USER_ID 
LEFT JOIN  `t_account` as A
    ON E.in_client_id = A.client_id
/*LEFT JOIN (SELECT user_id, SUM(balance)/1e8 AS amount FROM management.t_donation_history GROUP BY USER_ID )AS B 
    ON E.in_client_id = B.USER_ID */ 
LEFT JOIN 
    (SELECT client_id, SUM(trade_volume)AS volume FROM affiliate.t_daily_trade GROUP BY client_id ) AS TRADE
    ON TRADE.client_id = E.in_client_id
WHERE E.activity_name IN ('BONUS0_01F0_01','100BONUS_0_1','100BONUS_0_03','50BONUS_0_005','50BONUS_0_01', '50BONUS_0_0075', '50BONUS_0_05') AND E.transfer_status = 7
GROUP BY E.in_client_id
ORDER BY E.last_changed_time DESC