Я пытаюсь составить отчет об эффективности одного объявления, показывающий количество просмотров и кликов за один день. Просмотры и клики хранятся в отдельных таблицах с разной структурой, поэтому я думаю, что мне нужно выполнить объединение.
Я прочитал и понял эту фантастическую статью . Это помогло мне, но я думаю, что это на один уровень сложнее, чем примеры, описанные там. Хотелось бы помощи от сообщества.
Это моя таблица views
, в которой хранится счетчик количества просмотров объявления за один день.
+-------------+--------------+
| COLUMN_NAME | COLUMN_TYPE |
+-------------+--------------+
| ad_day_id | bigint(13) |
| advert_id | bigint(20) |
| date | date |
| views | mediumint(6) |
+-------------+--------------+
Это моя таблица clicks
, в которой каждый клик хранится отдельно. (некоторые столбцы опущены, так как они не имеют отношения к вопросу)
+-------------+---------------------+
| COLUMN_NAME | COLUMN_TYPE |
+-------------+---------------------+
| id | bigint(20) unsigned |
| advert_id | bigint(20) |
| timestamp | timestamp |
+-------------+---------------------+
Результат должен выглядеть так (настоящие числа не используются, просто чтобы показать формат):
+------------+-------+--------+
| event_date | views | clicks |
+------------+-------+--------+
| 2016-05-09 | 25 | 4 |
| 2016-05-10 | 2 | |
| 2016-05-11 | 105 | 10 |
| 2016-05-13 | 96 | 7 |
| 2016-05-14 | | 1 |
+------------+-------+--------+
Что касается результатов:
- Не на каждой дате будут клики или просмотры
- На некоторые даты могут быть просмотры и нет кликов
- На некоторые даты могут быть клики и нет просмотров
Перейдем к коду... вот что у меня сейчас есть:
SELECT
$views_table.date AS event_date,
$views_table.views,
'' AS clicks
FROM
$views_table
WHERE
( $views_table.date BETWEEN '$from_date' AND '$to_date' )
AND $views_table.advert_id=$advert_id
UNION
SELECT
CAST($clicks_table.timestamp AS DATE) AS event_date,
'' AS views,
COUNT($clicks_table.advert_id) AS clicks
FROM
$clicks_table
WHERE
( CAST($clicks_table.timestamp AS DATE) BETWEEN '$from_date' AND '$to_date' )
AND $clicks_table.advert_id=$advert_id
GROUP BY
event_date
ORDER BY
event_date ASC;
Некоторые примечания к коду:
- Клики сохраняются индивидуально в отметке времени и, следовательно, должны быть приведены к датам, а затем сгруппированы по дате (по крайней мере, так я получил достоверные результаты для другого отчета).
- Отчет будет содержать диапазон дат и относится к одному конкретному объявлению. Это объясняет предложения where.
При составлении этого вопроса я немного лучше отформатировал код и для удобства чтения изменил порядок операторов выбора, что решило мою первоначальную проблему. По-видимому, оба выбора должны иметь одни и те же столбцы И быть в одном и том же порядке.
Я думаю, что почти у цели, потому что это мой текущий результат:
+------------+-------+--------+
| event_date | views | clicks |
+------------+-------+--------+
| 2016-05-09 | 1 | |
| 2016-05-09 | | 1 |
| 2016-05-10 | 2 | |
| 2016-05-11 | 105 | |
| 2016-05-11 | | 7 |
| 2016-05-13 | 96 | |
| 2016-05-13 | | 16 |
| 2016-05-14 | 2 | |
| 2016-05-14 | | 1 |
| 2016-05-15 | 2 | |
| 2016-05-15 | | 2 |
+------------+-------+--------+
Моя оставшаяся проблема связана с повторяющимися датами. Как я могу решить эту проблему?
Большое спасибо тем, кто так любезен ответить!
GROUP BY event_date
. Вам нужно будет настроить SELECT. - person Paul Spiegel   schedule 15.05.2016