В общем самый приемлемый вариант оказался таким:
SELECT
t2.id,
t2.offer_id,
SUM(t2.sum_hold) AS sum_hold,
SUM(t2.sum_unhold) AS sum_unhold,
SUM(t2.sum_chargeback) AS sum_chargeback,
SUM(t2.sum_bonus) AS sum_bonus,
SUM(t2.earning) AS sum_earning
FROM
(
SELECT
`t`.`id`,
`t`.`offer_id`,
`t`.`amount`,
SUM(IF(OtherTransactions.type = 'Hold', OtherTransactions.amount, 0)) AS sum_hold,
SUM(IF(OtherTransactions.type = 'UnHold', OtherTransactions.amount, 0)) AS sum_unhold,
SUM(IF(OtherTransactions.type = 'Chargeback', OtherTransactions.amount, 0)) AS sum_chargeback,
SUM(IF(OtherTransactions.type = 'Bonus', OtherTransactions.amount, 0)) AS sum_bonus,
t.amount AS earning
FROM `payments_transactions` `t`
LEFT OUTER JOIN `payments_transactions` `OtherTransactions`
ON (`OtherTransactions`.`id_daily_earning` = `t`.`id`)
AND (OtherTransactions.type = 'Hold'
OR OtherTransactions.type = 'UnHold'
OR OtherTransactions.type = 'Chargeback'
OR OtherTransactions.type = 'Bonus')
WHERE (t.type = "Daily Earning")
GROUP BY t.id
)
t2
GROUP BY t2.offer_id
ORDER BY t2.id DESC
LIMIT 30
Сначала джойним холды и пр, считаем их сумму в группировке по t.id. Как заработок берем не сумму amount, а просто amount. Затем оборачиваем это все в еще один запрос, в котором уже группируем как хотим, снова считаем суммы уже в этой группировке и делаем LIMIT.
На 350к записей отрабатывает за ~0.7 сек. Вполне терпимо.
Простой