MySQL对两个表进行联合，其中一个带有时间戳，另一个带有日期

debugcn 发表于 Dev

鲜雪

我正在尝试针对一个广告的效果汇总一份报告，以显示该广告在一天中的观看次数和点击次数。视图和单击存储在具有不同结构的单独表中，因此我认为我必须执行合并。

我已经阅读并理解了这篇奇妙的文章。它帮助了我，但我认为这比那里解释的示例复杂了一个层次。希望得到社区的帮助。

这是我的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 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

演示版

代替CAST(clicks.timestamp AS DATE)您还可以使用DATE(clicks.timestamp)并希望MySQL将来会使用索引。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。