Php Sql 按日期从多个表分组

Question

我有三个表，我试图通过连接所有表来生成报告。表打开:

id   |  offer_id   |  datetime
1    |  1          | 2017-08-19 00:00:00
2    |  1          | 2017-08-20 00:00:00
3    |  1          | 2017-08-20 00:00:00

表格点击次数:

id   |  offer_id   |  datetime
1    |  1          | 2017-08-20 00:00:00
2    |  1          | 2017-08-20 00:00:00
3    |  1          | 2017-08-20 00:00:00

表格转换:

id   |  offer_id   |  datetime
1    |  1          | 2017-08-20 00:00:00
2    |  1          | 2017-08-21 00:00:00
3    |  1          | 2017-08-21 00:00:00

我需要构建一个查询，以便获得如下所示的输出

需要的输出:

datetime                 |  opens      |  clicks  | conversions
2017-08-19 00:00:00      |  1          | 0        | 0
2017-08-20 00:00:00      |  2          | 3        | 1
2017-08-21 00:00:00      |  0          | 0        | 2

到目前为止我已经尝试过了

SELECT count(DISTINCT opens.id) as opens,
count(DISTINCT clicks.id) as clicks,
count(DISTINCT conversions.id) as conversions
FROM opens

LEFT JOIN clicks
ON clicks.offer_id = 1

LEFT JOIN conversions
ON conversions.offer_id = 1

WHERE opens.offer_id = 1 GROUP BY DATE_FORMAT(opens.datetime, '%Y %M %D'), DATE_FORMAT(clicks.datetime, '%Y %M %D'), DATE_FORMAT(conversions.datetime, '%Y %M %D')

但是这个查询没有给我想要的输出。请帮忙。

Answer 1

为了得到你想要的结果，它可能是一个复杂的 SQL，如下所示，首先使用 union 从三个表中获取日期列表，然后在 offer_id 和 date 上对你的 3 个表进行左连接

SELECT groupdate,
COUNT(DISTINCT o.id) AS opens,
COUNT(DISTINCT c.id) AS clicks,
COUNT(DISTINCT con.id) AS conversions
FROM (
  SELECT offer_id,DATE_FORMAT(`datetime`, '%Y %M %D') groupdate FROM opens 
  UNION ALL
  SELECT offer_id,DATE_FORMAT(`datetime`, '%Y %M %D') groupdate FROM clicks
  UNION ALL
  SELECT offer_id,DATE_FORMAT(`datetime`, '%Y %M %D') groupdate FROM conversions
) t
LEFT JOIN opens o ON (t.offer_id = o.offer_id AND t.groupdate = DATE_FORMAT(o.`datetime`, '%Y %M %D'))
LEFT JOIN clicks c ON (t.offer_id = c.offer_id AND t.groupdate = DATE_FORMAT(c.`datetime`, '%Y %M %D'))
LEFT JOIN conversions con ON (t.offer_id = con.offer_id AND t.groupdate = DATE_FORMAT(con.`datetime`, '%Y %M %D'))
WHERE t.offer_id = 1 
GROUP BY groupdate

DEMO