SQL -如果在连接-更改日期期间日期不匹配，则连接

Question

我正在使用POSTGRESQL并创建一个表，该表连接adwords数据和salesforce数据，以获得我的指标的完整视图，从成本/印象到salesforce中的表单填充(营销活动成员记录)。我们可以将这两件事(salesforce和adwords)用一个UTM参数连接起来，这个UTM参数是adwords中的"adgroup“值，并成为salesforce中的"marketing_content_stamp__c”。问题是我们已经创建了一个cookie，上面写着“如果这个人是有机的，在这个人的cookie中查找他们最后一次来到这里的UTM值”。这就产生了一个问题，因为有时我们在salesforce中使用adgroup没有记录的日期来填写表单。

我想做的是:连接这些表，并说明“如果我的表中存在表单填充"temp.adwords_sf”，并且adgroup表中没有该日期的记录，则将该记录连接到最后一个adgroup记录日期。“

例如，我在11/13日期为我的ADGROUPX设置了一个表单填充，但我最后单击的记录是11/11。我希望使表单填充记录与11/11相关联，否则它将不会显示。

SQL查询：

drop table temp.adwords_ui;
Create table temp.adwords_ui as(
Select 
cast(date as date),
Case 
WHEN adgroup like '%-uk-%' then 'UK'
WHEN adgroup like '%-usa-%' then 'USA'
WHEN adgroup like '%-us-%' then 'USA'
END AS Target_Country,
Case 
WHEN adgroup like '%-product-social%' then 'Product-SocialNetworks'
WHEN adgroup like '%-product-solu%' then 'Product-Solutions'
WHEN adgroup like '%-brand-%' then 'Brand'
WHEN adgroup like '%-competitors-%' then 'Competitors'
END AS ad_grouping,
adgroup,
sum(adcost) as cost,
sum(impressions) as impressions,
sum(adclicks) as clicks
from rjm_current.adwords45309635_v2
where adcost <> 0 
and date > '11/3/2015'
group by 1,2,3,4);


drop table temp.adwords_sf;
Create table temp.adwords_sf as (
Select
id,
campaign_name__c as campaign,
marketing_content_stamp__c as marketing_content,
cast(firstrespondeddate as date) as form_fill_date,
count(*) as form_fills
from rjm_current.sf_campaignmember
where marketing_source_stamp__c = 'google-adwords'
and firstrespondeddate > '11/3/2015'
group by 1,2,3,4); 


drop table custom.adwords;
Create table custom.adwords as (
select
a.date,
a.target_country,
a.ad_grouping,
a.adgroup,
b.campaign,
a.cost,
a.impressions,
a.clicks,
b.form_fills
--((a.cost) / nullif(a.clicks,0)) as CPC,
--(a.clicks / a.impressions) as CTR,
--((a.cost) / nullif(b.form_fills,0)) as Cost_per_FormFill
from temp.adwords_ui a
Left Join temp.adwords_sf b on a.adgroup = b.marketing_content
and cast(a.date as date) = cast(b.form_fill_date as date)
);

Answer 1

我想这将会很混乱。

您可以通过将其更改为右连接，然后添加“where a.date is null”来标识“未命中”的行

在此基础上，为每个'a‘列添加一列，并使用如下所示的子查询(例如date列)

(select top 1 date 
from a where date < b.form_filled_date 
and a.adgroup = b.marketing_content
order by date desc) as a_date,

(select top 1 target_country
from a where date < b.form_filled_date 
and a.adgroup = b.marketing_content
order by date desc) as a_target_country,

将上述代码写入临时表，然后将它们涂抹到自定义表的末尾！

希望能帮你入门……