JOIN 和 SUM 不同的语句结果（Wordpress-Mailster 数据库）

Question

在上次更新 Mailster（wordpress 电子邮件营销插件）后，他们更改了存储打开信息的方式、点击、取消订阅...

到目前为止，所有内容都存储在两个数据库中：

1. bao_posts：与任何其他 WordPress 帖子一样，帖子的信息 发送的电子邮件就在那里。 （当post_type = 'newsletter'时）
2. bao_mailster_actions：这是用户的操作与 电子邮件已存储。 1 当它被发送给一个人时，2 当他们 打开它，点击它时为 3，取消订阅时为 4。

通过这个查询，我可以获得一个表格，其中包含所有电子邮件及其打开、点击、取消订阅的信息...

SELECT  bao_posts.post_modified, 
        bao_posts.ID, 
        bao_posts.post_title, 
        COUNT(CASE WHEN bao_mailster_actions.type = 1 then 1 ELSE NULL END) AS Number_People_Reached, 
        COUNT(CASE WHEN bao_mailster_actions.type = 2 then 1 ELSE NULL END) AS Opens,
        COUNT(CASE WHEN bao_mailster_actions.type = 3 then 1 ELSE NULL END) AS Clicks, 
        COUNT(CASE WHEN bao_mailster_actions.type = 4 then 1 ELSE NULL END) AS Unsubs
FROM bao_posts
LEFT JOIN bao_mailster_actions ON bao_mailster_actions.campaign_id = bao_posts.ID
WHERE bao_posts.post_type = 'newsletter'
GROUP BY bao_posts.ID ;

*此查询的预期结果位于帖子末尾。

现在的问题是，此设置在更新之前保留用于电子邮件，但它已更改为新的电子邮件，现在 bao_mailster_actions 分为：

• bao_mailster_action_sent
• bao_mailster_action_opens
• bao_mailster_action_clicks
• bao_mailster_action_unsubscribes

我知道如何获取每个表的计数，如下所示

SELECT bao_mailster_action_sent.campaign_id, 
       COUNT(bao_mailster_action_sent.count) AS Number_People_Reached
FROM bao_mailster_action_sent
GROUP BY bao_mailster_action_sent.campaign_id;

： ：

campaign_id	Number_People_Reached
9785	300
9786	305

（这 4 个新表依次类推）。

所以我想做的是将这 4 个新查询加入到原来的查询中。 我一直在尝试组合不同的 JOIN，但我不太明白该怎么做

*请记住，如果电子邮件 ID 两者都匹配，我需要它来添加它们的点击次数、打开次数（或其他内容）。

预期结果将是这样的（与第一个查询相同，但具有聚合数据）：

post_modified	ID	post_title	Number_People_Reached	Opens	Clicks	Unsubs
2021-04-29 13:13:03	9785	Prueba email	300	102	30	1
2021-04- 30 15:12:01	9786	第二电子邮件	305	97	56	0

提前致谢！

Answer 1

我建议您使用 UNION ALL 来连接 CTE 中的所有表。然后您可以在查询中使用它。我修改了名称，因为我们不能有相同名称的记录。

> create table if not exists bao_mailster_action_sent
( campaign_id int,count int);
create table if not exists bao_mailster_action_opens
( campaign_id int,count int);
create table if not exists bao_mailster_action_clicks
( campaign_id int,count int);
create table if not exists bao_mailster_action_unsubscribes
( campaign_id int,count int);
CREATE TABLE if not exists bao_posts(
        post_modified date,
        ID int,
        post_title varchar(50) );


insert into bao_mailster_action_sent values
(1,88),(2,4),(4,6);
insert into bao_mailster_action_opens values
(2,4),(3,5),(4,10);
insert into bao_mailster_action_clicks values
(1,3),(2,3),(4,6);
insert into bao_mailster_action_unsubscribes values
(1,4),(3,5),(4,5);

INSERT INTO bao_posts values
( '2021-03-01',1,'first post'),
( '2021-06-01',2,'second opion'),
( '2021-09-01',3,'third way'),
( '2021-12-01',4,'last post');

WITH bao_mailster_actionsent AS
( SELECT campaign_id,count, 1 type FROM
bao_mailster_action_sent
UNION ALL
SELECT  campaign_id,count,2 FROM
bao_mailster_action_opens
UNION ALL
SELECT  campaign_id,count,3 FROM
bao_mailster_action_clicks
UNION ALL
SELECT  campaign_id,count,4 FROM
bao_mailster_action_unsubscribes)
SELECT bao_mailster_actionsent.campaign_id,
       COUNT(bao_mailster_actionsent.count) AS TotalCount,
       SUM(bao_mailster_actionsent.count) AS TotalNumber,
       'type'
FROM bao_mailster_actionsent
GROUP BY bao_mailster_actionsent.campaign_id,'type' ;

 WITH baoMailsterAction  AS
( SELECT campaign_id,count, 1 type FROM
bao_mailster_action_sent
UNION ALL
SELECT  campaign_id,count,2 FROM
bao_mailster_action_opens
UNION ALL
SELECT  campaign_id,count,3 FROM
bao_mailster_action_clicks
UNION ALL
SELECT  campaign_id,count,4 FROM
bao_mailster_action_unsubscribes)

SELECT  bao_posts.post_modified,
        bao_posts.ID,
        bao_posts.post_title,
        COUNT(CASE WHEN bao_mailster_actions.type = 1 then 1 ELSE NULL END) AS Number_People_Reached,
        COUNT(CASE WHEN bao_mailster_actions.type = 2 then 1 ELSE NULL END) AS Opens,
        COUNT(CASE WHEN bao_mailster_actions.type = 3 then 1 ELSE NULL END) AS Clicks,
        COUNT(CASE WHEN bao_mailster_actions.type = 4 then 1 ELSE NULL END) AS Unsubs
FROM bao_posts

campaign_id | 广告活动 ID总数 |总数 |类型
----------: | ---------: | ----------: | ---：
          1 | 1 | 88 | 88 1
          2 | 1 | 4 | 1
          4 | 1 | 6 | 1
          2 | 1 | 4 | 2
          3 | 1 | 5 | 2
          4 | 1 | 10 | 10 2
          1 | 1 | 3 | 3
          2 | 1 | 3 | 3
          4 | 1 | 6 | 3
          1 | 1 | 4 | 4
          3 | 1 | 5 | 4
          4 | 1 | 5 | 4

修改后 |身份证 |帖子标题 |已达到人数 |打开 |点击次数 |取消订阅者
:------------ | -: | :------------ | --------------------: | ----： | -----: | -----：
2021-03-01 | 1 |第一篇文章 | 1 | 0 | 1 | 1
2021-06-01 | 2 |第二种选择| 1 | 1 | 1 | 0
2021-09-01 | 3 |第三条路| 0 | 1 | 0 | 1
2021-12-01 | 4 |最后发表 | 1 | 1 | 1 | 1

db<>fiddle 此处

Answer 2

我终于只使用 Mailster 创建的新表（看起来他们最终确实通过更新将所有信息移动到新表中）和 4 个 LEFT JOINS 使其正常工作。

我留下代码以防其他人发现它有用：

SELECT P.post_modified, 
        P.ID, 
        P.post_title, 
        IFNULL(S.count,0) as 'Total',
        IFNULL(O.count,0) as 'Aperturas',
        IFNULL(C.count,0) as 'Clicks', 
        IFNULL(U.count,0) as 'Bajas' from bao_posts as P
LEFT JOIN (select campaign_id, count(DISTINCT subscriber_id) as count from bao_mailster_action_clicks group by campaign_id) as C ON C.campaign_id = P.ID
LEFT JOIN (select campaign_id, count(DISTINCT subscriber_id) as count from bao_mailster_action_opens group by campaign_id) as O ON O.campaign_id = P.ID
LEFT JOIN (select campaign_id, count(DISTINCT subscriber_id) as count from bao_mailster_action_sent group by campaign_id) as S ON S.campaign_id = P.ID
LEFT JOIN (select campaign_id, count(DISTINCT subscriber_id) as count from bao_mailster_action_unsubs group by campaign_id) as U ON U.campaign_id = P.ID

WHERE P.post_type = 'newsletter'
ORDER BY P.post_modified ASC ;

PS：正如我所料，Mailster 的支持根本没有帮助:'(