JOIN和SUM不同的陳述句結果（Wordpress-Mailster資料庫）

在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 ;

*此查詢的預期結果在帖子末尾。

Now the problem is that this setting is kept for emails before the update, but it has changed for new ones and now bao_mailster_actions is separated into:

• bao_mailster_action_sent
• bao_mailster_action_opens
• bao_mailster_action_clicks
• bao_mailster_action_unsubscribes

I know how to get the count of each of these tables like this:

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;

To get:

(And so on with each of these 4 new tables).

So what I would like to do would be to join these 4 new queries to the original one. I've been trying to combine different JOINs, but I don't quite understand how to do it.

*Bearing in mind that if an email ID matches in both, I would need it to add up their clicks, opens (or whatever).

The expected outcome would be something like this (the same as the first query but with the aggregate data):

提前致謝！

uj5u.com熱心網友回復：

我建議您使用 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

活動 ID | 總數 | 總數 | 型別
----------: | ---------: | ----------: | ---:
          1 | 1 | 88 | 1
          2 | 1 | 4 | 1
          4 | 1 | 6 | 1
          2 | 1 | 4 | 2
          3 | 1 | 5 | 2
          4 | 1 | 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

修改后 | 身份證 | post_title | Number_People_Reached | 打開 | 點擊次數 | 取消訂閱
:------------ | -: | :----------- | --------------------: | ----: | -----: | -----：
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<>在這里擺弄

uj5u.com熱心網友回復：

我終于讓它只使用 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 的支持根本沒有幫助:'(

標籤：mysql sql WordPress 加入 和

上一篇：R使用條件/連接資料操作串列串列

下一篇：MySQL：通過子查詢找到最有效的INNERJOIN用法