Запрос mysql, объединяющий 3 таблицы, дает неверные результаты

Я использую базу данных (структура прилагается ниже) с пользователями, которые запрашивают объект (таблица запросов) или отвечают на запрос (таблица действий).

Структура базы данных

(Примечание: request.user_request_id - это идентификатор запрашивающей стороны)

Я хочу найти запрос, который дает мне пользователей, которые либо выполнили запрос, либо ответили на запрос в течение месяца, а также количество запросов, количество ответов и общее количество (запросы + ответы).

У меня есть рабочий запрос по количеству запросов за март 2018 года:

SELECT user.email, COUNT(request.user_request_id) AS requests 
FROM request LEFT JOIN user ON request.user_request_id = user.id 
WHERE DATE_FORMAT(STR_TO_DATE(request.created_at, '%m-%d-%Y'), '%Y-%m') 
= '2018-03' 
GROUP BY user_request_id 
ORDER BY number desc;

И один по количеству ответов:

SELECT user.email, COUNT(action.lender_id) AS replies 
FROM action LEFT JOIN user ON action.lender_id = user.id 
WHERE DATE_FORMAT(STR_TO_DATE(action.created_at, '%m-%d-%Y'), '%Y-%m') = 
'2018-03' 
GROUP BY lender_id 
ORDER BY number desc;

Но если я попытаюсь использовать один запрос по трем таблицам, все будет не так. (Например, для пользователя, который сделал 3 запроса и 4 ответа, он дает мне 9 запросов и 9 ответов.) Вот запрос, который я использую:

SELECT user.email, 
COUNT(request.user_request_id) AS requests, 
COUNT(action.lender_id) AS replies, 
COUNT(request.user_request_id) + COUNT(action.lender_id) AS total 
FROM request LEFT JOIN user ON user.id=request.user_request_id LEFT JOIN 
action ON request.object_id=action.object_id 
WHERE DATE_FORMAT(STR_TO_DATE(request.created_at, '%m-%d-%Y'), '%Y-%m') 
= '2018-03' 
OR DATE_FORMAT(STR_TO_DATE(action.created_at, '%m-%d-%Y'), '%Y-%m') = 
'2018-03' 
GROUP BY user.email 
ORDER BY total desc;

Кто-нибудь видит, что я делаю не так?

пожалуйста, опубликуйте образцы данных и ожидаемые результаты - в идеале как SQLFiddle.

Neville Kuyt 10.08.2018 17:30

Вы должны хранить свои даты как объекты DATETIME. Они будут занимать намного больше места, помогут вам обрабатывать часовые пояса и т. д., И, что лучше всего, вы можете проиндексировать их и использовать WHERE datefield BETWEEN в качестве даты начала и окончания. По-вашему, вы должны преобразовывать каждую дату и сравнивать, что исключает возможность индексации того, что в противном случае значительно уменьшило бы сканирование таблицы. Никогда не помещайте дату в строку или int. Вы также можете автоматически создавать и заполнять поля даты при создании или обновлении: dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.htm‌ l

Daniel Farrell 10.08.2018 17:41
0
2
62
3

Ответы 3

Вы не делаете то же самое ЛЕВОЕ СОЕДИНЕНИЕ

Во втором запросе вы выполняете соединение между действием и пользователем с помощью "action.lender_id = user.id", но в третьем запросе вы выполняете соединение между запросом и действием с помощью "request.object_id=action.object_id", поэтому в основном вы не получите одинаковых результатов.

Вам не хватает условия JOIN. Вы присоединяетесь к request и action на object_id, но не на request_id. Ваш текущий запрос будет JOIN каждой строки запроса для всех строк действия, которые удовлетворяют условию на object_id.

SELECT user.email, 
    COUNT(request.user_request_id) AS requests, 
    COUNT(action.lender_id) AS replies, 
    COUNT(request.user_request_id) + COUNT(action.lender_id) AS total 
FROM request 
LEFT JOIN user ON user.id = request.user_request_id 
LEFT JOIN action ON request.object_id = action.object_id  AND request.user_request_id = action.request_id
WHERE DATE_FORMAT(STR_TO_DATE(request.created_at, '%m-%d-%Y'), '%Y-%m') = '2018-03' 
    OR DATE_FORMAT(STR_TO_DATE(action.created_at, '%m-%d-%Y'), '%Y-%m') = '2018-03' 
GROUP BY user.email 
ORDER BY total desc;

Для запросов на основе дат и в вашем сценарии, ища что-либо за март 2018 года (через строковое значение 2018-03), вы не получите никакой оптимизации в поле даты, когда вам нужно преобразовать каждую дату в строку и ЗАТЕМ увидеть если подходит.

Вместо этого вы знаете месяц / год, поэтому установите предложение where на первое и МЕНЬШЕ, чем на следующий день после окончания. Пример: вам нужен март 2018 года. Так что> = '2018-03-01' без проблем. В конце концов, если у ваших дат есть время, и вы хотите включить до 31 марта в 23:59:59 вечера, вы бы сделали МЕНЬШЕ, чем '2018-04-01'. Итак, теперь у вас есть целый месяц, о котором идет речь, и МОЖЕТ использовать индекс, основанный на вашем поле даты / времени created_at.

Следующая часть - вы ПРИСОЕДИНЯЕТЕСЬ к действиям. Действия, которые у вас были как левое соединение, означают все запросы независимо от найденного действия. ОДНАКО, вы затем добавили дату ДЕЙСТВИЯ как часть предложения where, изменив его на INNER JOIN (даже если это часть OR между критериями). Это приведет к любым запросам в течение периода времени, для которых НЕ было действие как ИСКЛЮЧЕНО из набора результатов. Вероятно, вам нужно переместить это в предложение JOIN. Из чего это действительно нужно? Действие никогда не будет предшествовать запросу, но как насчет запроса от 29 марта, в котором действия были выполнены 30 марта, 1 апреля, 2 апреля. Вы хотите, чтобы в результате счетчик был 1 (29 марта) или 3 (включая 1 апреля и 2 апреля).

Другая простая настройка. Все запросы ДОЛЖНЫ иметь идентификатор пользователя, но у вас это будет левое соединение. Это соотношение 1: 1, поэтому, вероятно, СЛЕДУЕТ установить как обычное ВНУТРЕННЕЕ СОЕДИНЕНИЕ.

SELECT 
      u.email, 
      COUNT(r.user_request_id) AS requests, 
      COUNT(a.lender_id) AS replies, 
      COUNT(r.user_request_id) + COUNT(a.lender_id) AS total 
   FROM 
      request r
         INNER JOIN user u
            ON r.user_request_id = u.id
         LEFT JOIN action a
            ON r.object_id = a.object_id 
            -- if you explicitly want to RESTRICT Actions within same date range
            AND a.created_at >= '2018-03-01'
            AND a.created_at < '2018-04-01'
   WHERE 
          r.created_at >= '2018-03-01'
      AND r.created_at < '2018-04-01'
   GROUP BY 
      u.email 
   ORDER BY 
      total desc;

Теперь вы упоминаете неправильные ответы, когда пытались выполнить аналогичный запрос выше, пытаясь выполнить за один раз. Вероятно, это связано с декартовым результатом. Для каждой записи в запросе, на который есть ответ, этот счетчик применяется несколько раз. Вероятно, вам нужно сохранить каждый отдельный соответствующий счетчик в качестве собственного запроса, поэтому для данного пользователя он будет правильно подсчитан только ОДИН РАЗ для соответствующего пользователя / кредитора.

SELECT 
      u.email,
      coalesce( R1.RequestCnt, 0 ) Requests,
      coalesce( A1.ActionCnt, 0 ) Replies,
      coalesce( R1.RequestCnt, 0 ) 
         + coalesce( A1.ActionCnt, 0 ) AS total 
   FROM 
      user u
         LEFT JOIN 
         ( select 
                 r.user_request_id as userID, 
                 count(*) requestCnt
              from 
                 request r
              WHERE 
                     r.created_at >= '2018-03-01'
                 AND r.created_at < '2018-04-01'
              group by 
                 r.user_request_id ) R1
            ON u.id = R1.userID

         LEFT JOIN
         ( select 
                 a.lender_id as userID, 
                 count(*) actionCnt
              from 
                 action a
              WHERE 
                     a.created_at >= '2018-03-01'
                 AND a.created_at < '2018-04-01'
              group by 
                 a.lender_id ) A1
            ON u.id = A1.userID
   where 
      coalesce( R1.RequestCnt, 0 ) 
         + coalesce( A1.ActionCnt, 0 ) > 0
   ORDER BY 
      coalesce( R1.RequestCnt, 0 ) 
         + coalesce( A1.ActionCnt, 0 ) desc;

Теперь, если у вас большой набор применений (например, миллионы), мы можем сократить запрос еще на один шаг, предварительно получив список отдельных пользователей в течение рассматриваемого периода ...

   from
      ( select distinct
              r.user_request_id as userID
           from 
              request r
           WHERE 
                  r.created_at >= '2018-03-01'
              AND r.created_at < '2018-04-01'
        UNION
        select a.lender_id as userID
           from 
              action a
           WHERE 
                  a.created_at >= '2018-03-01'
              AND a.created_at < '2018-04-01' ) as UniqUsers
          JOIN user u
             on UniqUsers.UserID = u.id
             -- rest of the other pre-queries above...

Другие вопросы по теме