场景

有两张表，一张活动清单表actv_evt：记录了所有的活动，包括活动id，活动名称及活动相关配置信息；一张客户活动参与表cust_actv，记录了客户参与活动信息。

cust_actv中参与了活动的客户定义为活跃客户，现在公司想对活跃客户做推广，将没参与过的活动推送给他们

即需要这样的目标数据：

cust1：actv1,actv3

cust2：actv2,actv3,actv7

......

原脚本

-- 1.按uid,通过collect_set将[待推广的未参加活动清单]组合
select 
     a.cust_uid
    ,concat_ws(',',collect_set(concat_ws(':',actv_id,actv_nm))) as actv
from (
    -- 2.通过b表关联不上的数据找到cust_uid对应未参加的活动
    select
         a.actv_id,a.actv_nm,a.cust_uid
    from (
        -- 3.通过笛卡尔积把每个客户加到每个活动
        select
             a.actv_id,a.actv_nm,b.cust_uid
        from (
            -- 4.筛出所有活动清单
            select distinct a.actv_id,a.actv_nm from tb_actv_evt
        ) as a
        left join (
                -- 5.筛选出活跃客户
                select distinct cust_uid from tb_cust_actv
                ) as b
        on 1=1  --笛卡尔积
    ) as a
    left join tb_cust_actv as b
    on a.actv_id=b=actv_id
    and a.cust_uid=b.cust_uid
    where b.actv_id is null
) as a
group by cust_uid

优化

可以看出原脚本4、5两个distinct，3笛卡尔积是消耗资源多的点。本题的笛卡尔积不可避免，但可以减少笛卡尔积的数据量。

-- 1.拿所有活动与已参与活动做差集运算
select 
     b.cust_uid
    ,a.actv_all-b.actv_usd   -- 这里指的是差集运算
from (
-- 2.筛选出活跃客户参与过的活动清单
    select 
         cust_uid 
        ,collect_set(concat_ws(':',b.actv_id,b.actv_nm) as actv_usd
    from tb_cust_actv as b
    group by cust_uid 
    ) as b
left join (
-- 3.筛出所有活动清单,通过笛卡尔积加上
    select 
         collect_set(concat_ws(':',a.actv_id,a.actv_nm) as actv_all
    from tb_actv_evt
    -- group by collect_set(concat_ws(':',a.actv_id,a.actv_nm) collect_set是类似sum,avg的UDAF聚合函数，这里不用group by
    ) as a
on 1=1  --笛卡尔积

附：笛卡尔积常用场景

常用于给表的每条记录加上一些通用性质(都是单一取值)，如本题中整体活动清单(对所有cust活动清单都是一样的，只有单一取值)就是通用的，也可以用于加一些汇总值，通用时间属性等之类。

eg:查看每个项目的营业收入，并且每条数据要附上总营收

select  
     a.proj_name
    ,a.income
    ,b.all_income
    ,b.avg_income
from tb_proj_income
inner join (        -- 在这里添加通用属性。一个优化思路：如果上面的主select语句中有很多通用重复计算，也可以采用将其固定下来在inner join的子查询中
    select 
        sum(income) as all_income
        avg(income) as avg_income  --注意这里sum、avg的取值都是单一的
    from tb_proj_income
    ) as b

--因为除聚合函数,其余字段要加group by, select proj_name,sum(income)实现不了