множественный подсчет sql из разных таблиц

#mysql #sql

Вопрос:

КАК получить все лиды и обращения реферера?

Таблица: hits_log

  ----------- ---------- 
| topic     | referer  |
 ----------- ---------- 
| topic0614 | xxxxxxxx |
| topic0614 | xxxxxxxx |
| topic0615 | zzzzz    |
| topic0615 | yyyyyy   |
| topic0614 | xxxxxxxx |
| topic0614 | xxxxxxxx |
| topic0615 | zzzzz    |
| topic0615 | yyyyyy   |
| topic0614 | yyyyyy   |
| topic0614 | yyyyyy   |
| topic0615 | zzzzz    |
| topic0615 | yyyyyy   |
 ----------- ----------

Таблица: leads_log

  ----------- ---------- 
| topic     | referer  |
 ----------- ---------- 
| topic0614 | xxxxxxxx |
| topic0614 | xxxxxxxx |
| topic0614 | xxxxxxxx |
| topic0615 | zzzzz    |
| topic0615 | yyyyyy   |
| topic0614 | xxxxxxxx |
| topic0615 | zzzzz    |
| topic0614 | yyyyyy   |
 ----------- ----------

Я хочу, чтобы результат был таким
Если поиск по теме topic0614

  ----------- ---------- ------------ 
| referer   | hits     | leads      |
 ----------- ---------- ------------ 
| xxxxxxxx  | 4        | 4          |
| yyyyyy    | 2        | 1          |
 ----------- ---------- ------------

я пытался

 SELECT h.referer, COUNT(h.referer)  as hits, COUNT(l.referer)  as leads FROM `hits_log` h ,`leads_log` l
WHERE h.topic='topic0614' and h.referer=l.referer
GROUP BY h.referer

но это не сработало

может ли кто-нибудь мне помочь? Спасибо.

1. Почему xxxxxxxx в вашем примере вывода есть 5 лидов? Я вижу только 4 в leads_log таблице. И почему yyyyyy только 1, в leads_log таблице их 2.

2. у меня нет обращений из hit_log и нет обращений из журнала обращений, хранящихся независимо, так что это так

3. Но xxxxxxxx это только в 4 строках hits_log , почему у вас есть 5 в результатах?

4. При подсчете в leads_log вы не говорите, что нужно только считать topic0614 . Итак, в моем ответе учитываются все совпадающие ссылки.

5. ой, sry, это была моя ошибка, см. Отредактировано, это 4, а не 5

Ответ №1:

Вам нужно сгруппировать каждую таблицу отдельно в подзапросе. Если вы выполняете подсчет в основном запросе, вы подсчитываете результаты перекрестного произведения, что вызывает умножение.

 SELECT h.referer, hits, leads
FROM (SELECT referer, COUNT(*) AS hits
      FROM hits_log
      WHERE topic = 'topic0614'
      GROUP BY referer) AS h
JOIN (SELECT referer, COUNT(*) AS leads
      FROM leads_log
      GROUP BY referer) AS l
ON h.referer = l.referer

ДЕМОНСТРАЦИЯ

Возможно, это действительно то, что вы хотите. Это ограничивает оба обращения и ведет к определенной теме и будет включать ссылки с нулевым количеством в любой таблице.

 SELECT referer, MAX(hits) AS hits, MAX(leads) AS leads
FROM (SELECT referer, COUNT(*) AS hits, 0 as leads
      FROM hits_log
      WHERE topic = 'topic0614'
      GROUP BY referer
      UNION
      SELECT referer, 0 AS hits, COUNT(*) as leads
      FROM leads_log
      WHERE topic = 'topic0614'
      GROUP BY referer) AS x
GROUP BY referer

ДЕМОНСТРАЦИЯ

1. это дает только одну строку, если два реферера относятся к одной теме, это не дает 2-й строки

2. Я не уверен, что вы имеете в виду. Взгляните на мою демонстрационную версию sqlfiddle. Я не совсем уверен, чего вы действительно хотите, потому что ваш пример вывода, похоже, не соответствует образцу ввода — смотрите Мой вопрос в комментариях.

3. Все обращения для topic0614 имеют referer xxxxxxxx в вашей скрипке, так что другого нет h.referer .

4. Смотрите запрос, который я только что добавил.