题解 | #10月的新户客单价和获客成本 预处理#

使用CTE语法来进行预处理

with t2 as (
    select
        order_id,
        sum(price) sumprice
    from tb_order_detail tod
    group by order_id
	#获取每单商品单价之和
),
t3 as (

    select
        uid,
        order_id,
        rank() over (partition by uid order by event_time) n,
        total_amount
    from tb_order_overall
    where date_format(event_time,'%Y-%m')
	#过滤掉日期不符的，并且先给上排号
)
select
    round(avg(total_amount),1) avg_amount,#这就是核心逻辑
    round(avg(sumprice-t3.total_amount),1) avg_cost#这就是核心逻辑
from
t2 right join t3 on t2.order_id=t3.order_id
where n=1;

最近在研究Hive的数据倾斜问题，有个处理方法就是对数据进行预处理，减少join的数据条数

CTE YYDS!