mysql - 左连接 SQL 表

Question

使用以下表格：

• 用户（user_id）
• 购买 (user_id, event_datetime = 购买日期)
• email_sent (user_id, message_id
• email_open (user_id, message_id)
• email_click (user_id, message_id)

我正在尝试获得这样的表格：

但正如你所看到的数字是错误的（连接似乎很糟糕）。

我应该：

email sent> email opened > email clicked 

（因为它是在发送的电子邮件中打开的电子邮件，点击相同）

我不知道我的 JOIN/LEFT JOIN 做错了什么。

这是我的查询（查询结果如上表）：

WITH user AS (
  SELECT 
  DISTINCT user_id
  ,CASE WHEN DATE_DIFF('day',DATE(MAX(event_datetime)), NOW()) < 30
  THEN 'buyer_30d' 
  ELSE 'not_buyer_30d' END AS user_type

  FROM [orion_sandbox]."[Domain]_purchase" 

  GROUP BY user_id

),email_sent AS
(
  SELECT 
  u.user_id
  ,es.message_id 

  FROM [orion_sandbox]."[Domain]_user" AS u
  JOIN [orion_sandbox]."[Domain]_purchase" AS p USING(user_id)
  JOIN [orion_sandbox]."[Domain]_email_sent" AS es ON u.user_id =es.user_id

  WHERE DATE_DIFF('day',DATE(es.event_datetime), NOW())<60 AND DATE_DIFF('day',DATE(es.event_datetime), NOW()) >30
)
SELECT user_type,
COUNT(es.message_id) AS email_received,
COUNT(eo.message_id) AS email_opened,
COUNT(ec.message_id) AS email_clicked

FROM user 
JOIN email_sent AS es ON user.user_id=es.user_id
LEFT JOIN [orion_sandbox]."[Domain]_email_open" AS eo ON eo.message_id=es.message_id AND eo.user_id=es.user_id
LEFT JOIN [orion_sandbox]."[Domain]_email_click" AS ec ON ec.message_id=eo.message_id AND ec.user_id=eo.user_id 

GROUP BY user_type

标签： mysqljoinleft-join