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

不念不忘少年蓝@ 2023-02-22 08:27 16阅读 0赞

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 主要是在子查询里修改一下别名即可

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dwcHdwcDE_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dwcHdwcDE_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200702180851254.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dwcHdwcDE=,size_16,color_FFFFFF,t_70