groupby在teradata的个人笔记

---

在teradata中Selected non-aggregate values must be part of the associated group.这个错误一直出现，虽然知道是groupby的问题，但是根本原因还是不太清楚，这次用这来祭奠我的groupby。
根据网上的教程说明的是groupby需要配合聚合函数来使用分组，起初我以为就是groupby的话就必须select出这个groupby的函数，后来的例子让我发现我错的还是比较离谱的。

select sum(process.Target),sum(conversion.cnvrsn) from (select sum(Targeted_User_Cnt) as Target,offer_id from EADW_ADM_APP.CAMPAIGN_PROCESS  where Campaign_Id='1000dq9pd69b' and lead_type_id='999' group by offer_id) process left join (select sum(email_campaign_cnvrsn_cnt) as cnvrsn,offer_id from EADW_ADM_APP.CAMPAIGN_CONVERSION  where Campaign_Id='1000dq9pd69b' and lead_type_id='999' group by offer_id) conversion on conversion.offer_id=process.offer_id;

上面这个sql语句是可以得出想要的结果，从中看出select中不需要添加任何offerid就能获得我想要的结果。

350047,368

以上的数据就是最终产生的结果，与我单个sql语句生产的结果是一样的。

Select sum(Targeted_User_Cnt) from EADW_ADM_APP.CAMPAIGN_PROCESS where Campaign_Id='1000dq9pd69b' and lead_type_id='999';

通过跟上司的讨论，因为两个表细致程度不是一样的，所以很容易就产生一对多的问题，这样重复数据对结果影响还是比较大的，所以我就提高维度，把我不需要关心的数据直接去除掉，不降低维度是因为比较麻烦，而去意义并不大。

groupby根据2个字段中数据范围比较大的来对此进行分组。可以将两个细致程度不一样的表进行数据的统计，可以防止一对多，然后对总的数据进行groupby，这样就可以获得自己想要的数据。

select Pr.Lead_Type_Id, sum(Email_Delivered_Cnt), sum(Targeted_User_Cnt),sum(case when Co.email_conv is null then 0 else Co.email_conv end) as total_convfrom EADW_ADM_APP.CAMPAIGN_Process Pr left join (select CC.Campaign_Collection_Id as Col_Id, CC.Campaign_Id as Cam_Id, CC.Generation_Date as G_date, CC.Segment_Id as Seg_Id, CC.Lead_Type_Id as Lead_Id, CC.Country_Code as Cou_Code,CC.Offer_Id as O_Id, CC.Step_Id as S_Id, CC.Step_Order_Nbr as S_Nbr, sum(email_campaign_cnvrsn_cnt) as email_conv from EADW_ADM_APP.CAMPAIGN_Conversion CC group by 1,2,3,4,5,6,7,8,9) Coon (Pr.Campaign_Collection_Id=Co.Col_Id and	Pr.Campaign_Id=Co.Cam_Id andPr.Generation_Date=Co.G_Date and Pr.Segment_Id=Co.Seg_Id andPr.Lead_Type_Id=Co.Lead_Id and	Pr.Country_Code=Co.Cou_Code and Pr.Offer_Id=Co.O_Id and	Pr.Step_Id=Co.S_Id and Pr.Step_Order_Nbr=Co.S_Nbr)where Pr.Campaign_Id='1000dq9pd69b' group by 1;
sum(case when Co.email_conv is null then 0 else Co.email_conv end) as total_conv
from EADW_ADM_APP.CAMPAIGN_Process Pr left join 
(select CC.Campaign_Collection_Id as Col_Id, CC.Campaign_Id as Cam_Id, CC.Generation_Date as G_date, CC.Segment_Id as Seg_Id, 
CC.Lead_Type_Id as Lead_Id, CC.Country_Code as Cou_Code,
CC.Offer_Id as O_Id, CC.Step_Id as S_Id, CC.Step_Order_Nbr as S_Nbr, sum(email_campaign_cnvrsn_cnt) as email_conv from EADW_ADM_APP.CAMPAIGN_Conversion CC group by 1,2,3,4,5,6,7,8,9
) Co
on (Pr.Campaign_Collection_Id=Co.Col_Id and	Pr.Campaign_Id=Co.Cam_Id and
Pr.Generation_Date=Co.G_Date and Pr.Segment_Id=Co.Seg_Id and
Pr.Lead_Type_Id=Co.Lead_Id and	Pr.Country_Code=Co.Cou_Code and 
Pr.Offer_Id=Co.O_Id and	Pr.Step_Id=Co.S_Id and Pr.Step_Order_Nbr=Co.S_Nbr)
where Pr.Campaign_Id='1000dq9pd69b' group by 1;

以上是为上司的sql语句，对于我的sql语句rightjoin和leftjoin没啥区别，过去笼统，需要更多数据的话则需要改动。groupby后跟数字的话代表的是select后面的字段顺序。

在groupby的having语句中，是闲where出来之后对group后的结果在进行having过滤。在groupby中可以直接这样使用

select a,b from table group by 1,2

但是不能直接在having后面这样用数字来替换ab，会报string类型和number类型的错误