Можно ли это все сделать одним запросом, вместо нескольких?

Question

[sasmoney]

Код выдает сумму успешных платежей за последние 7 дней, по дням за последние 7 дней и сумму всех выбранных записей.

Можно ли это все сделать одним запросом, вместо нескольких? чтобы снизить нагрузку, так как записей в бд очень много

$bjx1 = date('1.m.Y 00:00:01');
$bjx2 = date('31.m.Y 23:59:59');
$sm1 = strtotime($bjx1);
$sm2 = strtotime($bjx2);

$summes = $db->query('select sum(amount) from payments where status = 3 and shop_id = '.$shop->id.' and time > '.$sm1.' and time < '.$sm2.' ')->fetchColumn();

$b1 = date('d.m.Y 00:00:01');
$b2 = date('d.m.Y 23:59:59');
$bb1 = strtotime($b1);
$bb2 = strtotime($b2);
$dt1 = date('d.m.Y', $bb1);

$u1 = $db->query('select sum(amount) from payments where status = 3 and shop_id = '.$shop->id.' and time > '.$bb1.' and time < '.$bb2.' ')->fetchColumn();

$rr1 = $bb1 - 86400;
$rr2 = $bb2 - 86400;
$dt2 = date('d.m.Y', $rr1);

$u2 = $db->query('select sum(amount) from payments where status = 3 and shop_id = '.$shop->id.' and time > '.$rr1.' and time < '.$rr2.' ')->fetchColumn();

$rrk1 = $bb1 - 172000;
$rrk2 = $bb2 - 172000;
$dt3 = date('d.m.Y', $rrk1);

$u3 = $db->query('select sum(amount) from payments where status = 3 and shop_id = '.$shop->id.' and time > '.$rrk1.' and time < '.$rrk2.' ')->fetchColumn();

$rrb1 = $bb1 - 259200;
$rrb2 = $bb2 - 259200;
$dt4 = date('d.m.Y', $rrb1);

$u4 = $db->query('select sum(amount) from payments where status = 3 and shop_id = '.$shop->id.' and time > '.$rrb1.' and time < '.$rrb2.' ')->fetchColumn();

$rrn1 = $bb1 - 345600;
$rrn2 = $bb2 - 345600;
$dt5 = date('d.m.Y', $rrn1);

$u5 = $db->query('select sum(amount) from payments where status = 3 and shop_id = '.$shop->id.' and time > '.$rrn1.' and time < '.$rrn2.' ')->fetchColumn();

$rrm1 = $bb1 - 432000;
$rrm2 = $bb2 - 432000;
$dt6 = date('d.m.Y', $rrm1);

$u6 = $db->query('select sum(amount) from payments where status = 3 and shop_id = '.$shop->id.' and time > '.$rrm1.' and time < '.$rrm2.' ')->fetchColumn();

$rrp1 = $bb1 - 518400;
$rrp2 = $bb2 - 518400;
$dt7 = date('d.m.Y', $rrp1);

$u7 = $db->query('select sum(amount) from payments where status = 3 and shop_id = '.$shop->id.' and time > '.$rrp1.' and time < '.$rrp2.' ')->fetchColumn();
$uall = $db->query('select sum(amount) from payments where status = 3 and shop_id = '.$shop->id.' ')->fetchColumn();

$jfdv = $u1 + $u2 + $u3 + $u4 + $u5 + $u6 + $u7;
if ($summes == '') {
    $summes = 0;
}
if ($uall == '') {
    $uall = 0;
}
if ($u1 == '') {
    $u1 = 0;
}
if ($u2 == '') {
    $u2 = 0;
}
if ($u3 == '') {
    $u3 = 0;
}
if ($u4 == '') {
    $u4 = 0;
}
if ($u5 == '') {
    $u5 = 0;
}
if ($u6 == '') {
    $u6 = 0;
}
if ($u7 == '') {
    $u7 = 0;
}

Answer 1

[Taulan Khatuaev]

Да можно сделать все одним запросом с помощью sql.

SELECT 
    SUM(amount) as total_sum,
    DATE_FORMAT(FROM_UNIXTIME(time), '%d.%m.%Y') as date,
    SUM(CASE WHEN time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY)) THEN amount ELSE 0 END) as day_1,
    SUM(CASE WHEN time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 DAY)) AND time <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY)) THEN amount ELSE 0 END) as day_2,
    SUM(CASE WHEN time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 3 DAY)) AND time <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 DAY)) THEN amount ELSE 0 END) as day_3,
    SUM(CASE WHEN time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 4 DAY)) AND time <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 3 DAY)) THEN amount ELSE 0 END) as day_4,
    SUM(CASE WHEN time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 5 DAY)) AND time <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 4 DAY)) THEN amount ELSE 0 END) as day_5,
    SUM(CASE WHEN time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 6 DAY)) AND time <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 5 DAY)) THEN amount ELSE 0 END) as day_6,
    SUM(CASE WHEN time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY)) AND time <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 6 DAY)) THEN amount ELSE 0 END) as day_7,
    SUM(amount) as total_amount
FROM payments
WHERE status = 3 AND shop_id = :shop_id AND time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))

Comments

[sasmoney]

HTTP ERROR 500 получается

[Константин Б.]

sasmoney, сделайте обработчик ошибки и сморите что пишет. Может быть поле какое-то неверно написано. Вы же наверное решение к себе копипастом перенесли

[pantsarny]

ROLLUP можно попробовать

Answer 2

[nokimaro]

select \'u1\' as key, sum(amount) as sum from payments where status = 3 and shop_id = '.$shop->id.' and time > '.$sm1.' and time < '.$sm2.'
UNION
select \'u2\' as key, sum(amount) as sum from payments where status = 3 and shop_id = '.$shop->id.' and time > '.$rr1.' and time < '.$rr2.'
UNION
select \'u3\' as key, sum(amount) as sum from payments where status = 3 and shop_id = '.$shop->id.' and time > '.$rrk1.' and time < '.$rrk2.'

и тд все запросы через union вставляем в один select
вместо fetchColumn используем цикл и fetchAssoc

в итогу получится список вида

key | sum
-----------
u1  | ...
u2  | ...
u3  | ...

по нагрузке на базу будет то же самое что делать отдельные запросы, просто мы склеиваем результаты не на php, а на уровне бд через union