连接表在选择单行时返回正确的值，但在整个数据集时不正确

Question

我有3个表：广告系列级别，广告级别和关键字级别，我需要其中的每一个。所有3个都包含以下相同的列：竞选_name，竞选_id，day。其中两个还包含“ ad_group_name”。

查询正在运行并返回来自关键字和广告系列的数据的所有正确值，但是我从广告级别（conversion_name及其值）所需的值不是。但是，对我来说令人困惑的部分是，当我使用“ Where”子句并仅选择一行时，值正确并匹配到源表。此外，a.conversion_name的值总计为“转换”总计（+/- 1/2）。

从单行（其中句子）

当我删除WHERE WHERE子句并选择整个表格时，我的数字明显大于应该的。 a.conversion_name值不再添加到“转换”总计中 - 实际上有时转换= 0，并且a.conversion_name值返回值。

选择整个表格时结果

kdnxe.png“ rel =“ nofollow noreferrer”>选择整个表2

我想我理解为什么会发生这种情况，这是一个分组问题（？），我已经通过许多现有线程进行了搜索，找出子查询和独特的疑问，但是目前我的技能水平意味着我真的很难弄清楚这一点。

我应该改变事物的分组方式吗？我还尝试将a.conversion_name添加为维度，然后选择它，但这也无济于事。

WITH raw AS (SELECT 
k.day, 
k.campaign_name,
k.ad_group_name,
k.ad_group_type, 
k.ad_group_id, 
k.campaign_id, 
k.keyword, 
k.keyword_match_type,
AVG(CASE WHEN a.conversion_name = 'Verification Submitted' THEN a.conversions END) AS conv_verification_submitted,
AVG(CASE WHEN a.conversion_name = 'Email Confirmed' THEN a.conversions END) AS conv_email_confirmed,
AVG(CASE WHEN a.conversion_name = 'Account created' THEN a.conversions END) AS conv_account_created,
AVG(CASE WHEN a.conversion_name = 'Verification Started' THEN a.conversions END) AS conv_verification_started,
AVG(CASE WHEN a.conversion_name = 'Deposit Succeeded' THEN a.conversions END) AS conv_deposit_succeeded,
AVG(CASE WHEN a.conversion_name = 'Trade Completed' THEN a.conversions END) AS conv_trade_completed,
AVG(K.clicks) as clicks, 
AVG(K.conversions) as conversions, 
AVG(K.costs) as spend, 
AVG(K.impressions) as impressions, 
AVG(k.quality_score) as quality_score, 
AVG(c.search_impression_share) as search_impression_share, 
AVG(k.search_exact_match_impression_share) as search_exact_match_impression_share, 
AVG(c.search_lost_impression_share_rank) as search_lost_impression_share_rank, 
AVG(c.search_top_impression_share) as search_top_impression_share, 
AVG(c.search_lost_impression_share_budget) as search_lost_impression_share_budget, 
FROM `bigqpr.keyword-level-data` as k
LEFT JOIN `bigqpr.campaign-level-data` as c
ON c.campaign_name = k.campaign_name and c.day = k.day
LEFT JOIN `bigqpr.ad-level-data` as a
ON a.campaign_name = k.campaign_name and a.day = k.day and a.ad_group_name = k.ad_group_name
group by 1,2,3,4,5,6,7,8,a.conversion_name)
SELECT
day, 
campaign_name,
ad_group_name,
ad_group_type, 
ad_group_id, 
campaign_id, 
keyword, 
keyword_match_type,
AVG(conv_verification_submitted) as conv_verification_submitted,
AVG(conv_email_confirmed) as conv_email_confirmed,
AVG(conv_account_created) as conv_account_created,
AVG(conv_verification_started) as conv_verification_started,
AVG(conv_deposit_succeeded) as conv_deposit_succeeded,
AVG(conv_trade_completed) as conv_trade_completed,
AVG(clicks) as clicks, 
AVG(conversions) as conversions, 
AVG(spend) as spend, 
AVG(impressions) as impressions, 
AVG(quality_score) as quality_score, 
AVG(search_impression_share) as search_impression_share, 
AVG(search_exact_match_impression_share) as search_exact_match_impression_share, 
AVG(search_lost_impression_share_rank) as search_lost_impression_share_rank, 
AVG(search_top_impression_share) as search_top_impression_share, 
AVG(search_lost_impression_share_budget) as search_lost_impression_share_budget, 
FROM raw
WHERE keyword = "specifickeyword" and day = "2022-05-22" and ad_group_name = "specificadgroup"
GROUP BY 1,2,3,4,5,6,7,8

Answer 1

我也是一个初学者，但我的第一个想法是尝试内联机而不是左JOIN。有时，这有助于我的结果符合我想要的数字，而不是那么大。