Как составить SQL-запрос на получение данных по рекламной кампании?

Question

[Armanio]

Всем привет!
Имеется небольшой рекламный кабинет, для которого требуется вести статистику: когда кликнули, когда показали, агреггация данных и тд.

Схема очень простая:


Пытаюсь получить рекламу с подсчитанными просмотрами и кликами на сегодняшнюю дату вот так:

SELECT SQL_NO_CACHE ads.id, COUNT(clicks.id) as clicks, COUNT(shows.id) as shows FROM ads
LEFT JOIN shows ON ads.id = shows.ad_id
LEFT JOIN clicks ON ads.id = clicks.ad_id
WHERE
DATE(shows.created_at) = '2014-02-24' AND
DATE(clicks.created_at) = '2014-02-24'
AND ads.id = 35;

Но такой результат не радует:

id	clicks	shows
35	196		196

Для любителей бодрого интерактива: sqlfiddle.

Спасибо!

Answer 1

[Александр Хиренко]

Вот так:

SELECT ads.id , COUNT(DISTINCT clicks.id)  as clicks, COUNT(DISTINCT shows.id) as shows 
FROM ads
LEFT JOIN shows ON ads.id = shows.ad_id AND DATE(shows.created_at) = '2014-02-24'
LEFT JOIN clicks ON ads.id = clicks.ad_id AND DATE(clicks.created_at) = '2014-02-24'
GROUP BY ads.id

Comments

[Armanio]

Хех, как раз сам пришел к точно такому же варианту и увидел ваш способ.
Еще раз спасибо!

Answer 2

[Александр Хиренко]

Можно воспользоваться подзапросом:

SELECT ads.id,
(SELECT COUNT(*) FROM `clicks` WHERE ad_id = ads.id AND DATE(created_at) = '2014-02-24' AND
DATE(created_at) = '2014-02-24') AS clicks,
(SELECT COUNT(*) FROM `shows` WHERE ad_id = ads.id AND DATE(created_at) = '2014-02-24' AND
DATE(created_at) = '2014-02-24') AS shows
FROM ads
WHERE ads.id = 35;

Comments

[Armanio]

Я думал об этом, но мне кажется, что джойны лучше использовать, они же как раз для таких случаев. Разве нет?

[Александр Хиренко]

@Armanio Дошло, поставьте DISTINCT в COUNT() пример COUNT(DISTINCT clicks.id), ведь LEFT JOIN возвращает NULL, а COUNT() считает и его :)

[Александр Хиренко]

И да, подзапросы не лучший вариант

[Armanio]

@Satanpit точно, я и забыл об этом! Спасибо большое.

[Yuri Morozov]

На больших объемах данных можете пролететь с условиями вроде DATE(created_at) = ...
MySQL вроде не умел строить функциональные индексы?

[Armanio]

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

[Armanio]

@Satanpit, @metamorph, господа, я тут внезапно осознал что такая схема не сработает для вывода списка. Может есть мысли как организовать вывод списка с подсчитанными кликами и просмотрами?
Я встал в ступор, не смог заставить мускул возвращать рекламными компании, если у них 0 просмотров или кликов.

[Александр Хиренко]

@Armanio Объясните, что конкретно должно быть в итоге выборки из MySQL?

[Armanio]

@Satanpit что-то вот такое: cs606520.vk.me/v606520506/292d/2G_FQ6VegfY.jpg

[Александр Хиренко]

@Armanio Ответил ниже, у Вас было общее условие, а результатов из этих таблиц не было + отсутствие групировки