clickhouse多表join和子查询的出现错误修复

1，因业务需要，需要对多张表进行join，但是进行子查询是出现问题，通过问题排查，发现在子查询需要把两张表的join进行别名处理。

1.1 错误代码如下：

select a.dd,a.sourcetype ,count(distinct a.imei) pv ,countDistinct(a.imei) uv ,countDistinct(a.user_id) uv2 ,countDistinct(case when a.click='是' then a.imei end) pv_click ,countDistinct(case when a.click='是' then a.imei end) uv_click ,countDistinct(case when a.click='是' then a.user_id end) uv2_click ,countDistinct(b.user_no) deal_uv ,countDistinct(case when b.pay_time is not null then b.user_no end) done_uv ,countDistinct(case when b.is_pay='是' then b.user_no end) done_uv2 from ( select toDate(date_time) dd,sourcetype,click,imei,user_id from cdm_dwd.dwd_feed_detail_di_cls where biztype='订餐' and toDateOrZero(date_time) =toDate('2020-06-28') ) a global left join ( select t1.is_pay,t2.shop_name,t2.title,t2.create_time,t2.user_no,t2.pay_time from ads.ads_order_event_track_and_order_info_di_cls t1 global inner join( select p_order_id,user_no,title,shop_name,create_time,pay_time from cdm_dws.dws_ord_bkml_df_cls --where toDate(create_time) ='2020-06-28' )t2 on t1.order_id=t2.p_order_id group by t1.is_pay,t2.shop_name,t2.title,t2.create_time,t2.user_no,t2.pay_time ) b on a.user_id = b.user_no group by a.dd,a.sourcetype

1.2把相应代码修复成功

select a.dd,a.sourcetype ,count(a.imei) pv ,countDistinct(a.imei) uv ,countDistinct(a.user_id) uv2 ,count(case when a.click='是' then a.imei end) pv_click ,countDistinct(case when a.click='是' then a.imei end) uv_click ,countDistinct(case when a.click='是' then a.user_id end) uv2_click ,countDistinct(b.user_no) deal_uv ,countDistinct(case when b.pay_time is not null then b.user_no end) done_uv ,countDistinct(case when b.is_pay='是' then b.user_no end) done_uv2 from ( select toDate(date_time) dd,sourcetype,click,imei,user_id from cdm_dwd.dwd_feed_detail_di_cls where biztype='订餐' and toDateOrZero(date_time) =toDate('2020-06-28') ) a left join ( select t1.is_pay,t2.shop_name,t2.title,t2.create_time create_time,t2.user_no user_no,t2.pay_time pay_time from ads.ads_order_event_track_and_order_info_di_cls t1 global inner join( select p_order_id,user_no,title,shop_name,create_time,pay_time from cdm_dws.dws_ord_bkml_df_cls --where toDate(create_time) ='2020-06-28' )t2 on t1.order_id=t2.p_order_id group by t1.is_pay,t2.shop_name,t2.title,t2.create_time,t2.user_no,t2.pay_time ) b on a.user_id = b.user_no and a.dd=toDate(b.create_time) group by a.dd,a.sourcetype

1.3 主要是在子查询里修改一下别名即可