Упрощение UNION с JOIN в каждом подзапросе

259

24 февраля 2017, 00:54

Есть таблицы:

пользователи: users (id, name)
посещения: visits (id, user_id, time)
платежи: pays (id, user_id, time)

Необходимо выбрать данные следующего вида:

месяц - кол-во посещений - кол-во оплат

Составил запрос следующего вида:

SELECT time, SUM(visits), SUM(pays) 
FROM (
    SELECT DATE_FORMAT(v.`time`, '%Y-%m') AS time, COUNT(l.`id`) AS visits, 0 AS pays
    FROM `visits` v
    WHERE v.`user_id` = '$id' AND v.`time` BETWEEN '$date1' AND '$date2'
    GROUP BY DATE_FORMAT(v.`time` , '%Y-%m') 
    UNION 
    SELECT DATE_FORMAT(p.`time`, '%Y-%m') AS time, 0 AS visits, COUNT(p.`id`) AS pays
    FROM `pays` p
    WHERE p.`user_id` = '$id' AND p.`time` BETWEEN '$date1' AND '$date2'
    GROUP BY DATE_FORMAT(p.`time`, '%Y-%m')
) t
GROUP BY time

Получаются два практически одинаковых подзапроса, которые уже хочется как-то оптимизировать, но все работает довольно быстро. Но теперь появилась необходимость в этом же запросе вместо user_id = '$id' написать выборку из таблицы users, например, по полю name. Получается, что к каждому из подзапросов добавится конструкция наподобие данной:

INNER JOIN `users` u ON u.`name` = '$name` AND l.`user_id` = u.`id`

Что автоматически усложняет запрос в разы.

Можно ли этого как-то избежать? Или проще будет уже выполнить два раздельных запроса к БД?

Answer 1

Вот такое нужно? Просто добавить name в конечный результат?

SELECT t.time,u.name, SUM(t.visits), SUM(t.pays) 
FROM (
    SELECT DATE_FORMAT(v.`time`, '%Y-%m') AS time, COUNT(l.`id`) AS visits, 0 AS pays,v.`user_id` AS user_id
    FROM `visits` v
    WHERE v.`user_id` = '$id' AND v.`time` BETWEEN '$date1' AND '$date2'
    GROUP BY DATE_FORMAT(v.`time` , '%Y-%m') ,v.`user_id`
    UNION 
    SELECT DATE_FORMAT(p.`time`, '%Y-%m') AS time, 0 AS visits, COUNT(p.`id`) AS pays,p.`user_id`
    FROM `pays` p
    WHERE p.`user_id` = '$id' AND p.`time` BETWEEN '$date1' AND '$date2'
    GROUP BY DATE_FORMAT(p.`time`, '%Y-%m'),p.`user_id`
) t
INNER JOIN `users` u ON u.`name` = '$name` AND t.user_id = u.`id`
GROUP BY t.time,u.`name`