Union MySQL sur deux tables, une avec timestamp et une avec date

Question

J'essaie d'établir un rapport sur les performances d'une publicité en montrant les vues et les clics qu'elle a eus pendant une journée. Les vues et les clics sont stockés dans des tables distinctes avec des structures différentes. Je pense donc devoir effectuer une union.

J'ai lu et compris cette pièce fantastique . Il m'a aidé mais je pense que c'est un niveau plus complexe que les exemples expliqués ici. J'aimerais avoir l'aide de la communauté.

C'est mon views qui stocke un compteur du nombre de vues d'une publicité sur une journée.

+-------------+--------------+
| COLUMN_NAME | COLUMN_TYPE  |
+-------------+--------------+
| ad_day_id   | bigint(13)   |
| advert_id   | bigint(20)   |
| date        | date         |
| views       | mediumint(6) |
+-------------+--------------+

C'est mon clicks qui enregistre chaque clic individuellement. (certaines colonnes ont été laissées de côté car elles ne sont pas pertinentes pour la question)

+-------------+---------------------+
| COLUMN_NAME | COLUMN_TYPE         |
+-------------+---------------------+
| id          | bigint(20) unsigned |
| advert_id   | bigint(20)          |
| timestamp   | timestamp           |
+-------------+---------------------+

Le résultat devrait ressembler à ceci (aucun chiffre réel n'est utilisé, juste pour montrer le format) :

+------------+-------+--------+
| 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 |
+------------+-------+--------+

En ce qui concerne les résultats :

• Chaque date n'aura pas de clics ou de vues
• Certaines dates peuvent avoir des vues et pas de clics
• Certaines dates peuvent avoir des clics et pas de vues

Je me suis mis au code... voici ce que j'ai actuellement :

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;

Quelques notes sur le code :

• Les clics sont stockés individuellement sur l'horodatage et doivent donc être en dates et ensuite regroupés par date (ou du moins c'est ainsi que j'ai obtenu des résultats valides pour un autre rapport).
• Le rapport comportera une plage de dates et concernera une annonce spécifique. Ceci explique les clauses where.

En rédigeant cette question, j'ai formaté le code un peu mieux et, pour faciliter la lecture, j'ai changé l'ordre des instructions de sélection, ce qui a résolu mon problème initial. Apparemment, les deux sélections doivent comporter les mêmes colonnes ET être dans le même ordre.

Je pense que j'y suis presque, car voici mon résultat actuel :

+------------+-------+--------+
| 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      |
+------------+-------+--------+

Le problème qui me reste est celui des dates en double. . Comment puis-je résoudre ce problème ?
Un grand merci à ceux qui ont la gentillesse de répondre !

Answer 1

Ce n'est pas d'une simple union dont vous avez besoin, mais d'une union et de jointures. Ainsi, vous avez besoin d'une union pour obtenir une liste combinée de dates à partir des deux vues et de la table des clics. Ensuite, vous devez effectuer une jointure gauche des tables vues et clics sur la liste des dates :

select ds.event_date, max(v.views) views, count(c.clicks) clicks
from
    (select distinct date as event_date from views
     union distinct
     select distinct date(timestamp) from clicks) ds
left join views v on ds.event_date=v.date
left join clicks c on ds.event_date=date(c.timestamp)
where ...
group by ds.event_date

Answer 2

J'ai modifié un peu votre requête (voir les commentaires en ligne) et l'ai enveloppée dans une sous-requête pour utiliser GROUP BY event_date dans la requête externe.

SELECT event_date, MAX(views) AS views, MAX(clicks) AS clicks
FROM (
    SELECT
        views.date AS event_date,
        views.views,
        0 AS clicks -- '' causes strange results on sqlfiddle
    FROM
        views
    WHERE
        ( views.date BETWEEN '2016-05-09' AND '2016-05-15' )
        AND views.advert_id=1
    UNION
    SELECT
        CAST(clicks.timestamp AS DATE) AS event_date,
        0 AS views, -- '' causes strange results on sqlfiddle
        COUNT(clicks.advert_id) AS clicks
    FROM
        clicks
    WHERE
        ( CAST(clicks.timestamp AS DATE) BETWEEN '2016-05-09' AND '2016-05-15' )
        AND clicks.advert_id=1
    GROUP BY
        event_date
    -- ORDER BY is useless here
) sub
GROUP BY event_date
ORDER BY event_date

Démo

Au lieu de CAST(clicks.timestamp AS DATE) vous pouvez également utiliser DATE(clicks.timestamp) et espérer que MySQL utilisera les index à l'avenir.