10月的新户客单价和获客成本

select round(avg(total_amount),1) avg_amount,round(avg(sum_price-total_amount),1) avg_cost
from
(
        select 
        tod.order_id
        ,uid
        ,product_id
        ,event_time
        ,total_amount
        ,row_number() over(partition by uid order by event_time) rk
        ,sum(price*cnt) over(partition by order_id) sum_price
        ,status
        from tb_order_detail tod
        left join tb_order_overall too
        on tod.order_id = too.order_id
        where status = 1
) t
where rk = 1
and date_format(event_time,'%Y-%m')='2021-10'

#需要聚合的变量不能和不需要聚合的一起算
#需要先在子表里聚合之后一起不聚合地算
#第九行不能用rank()得用row_number，因为体现不同的product_id只会用于计算price*cnt
#但这时还没筛选，所以不怕丢数据，算完乘积之后两行就没有区别了，要用row_number()去重

#2021年10月所有新用户和首单时间
with t as(select uid,min(event_time) as f_order_time
from tb_order_overall join tb_order_detail
using(order_id)
group by uid
having date_format(f_order_time,"%Y-%m")="2021-10"),

#客单价
t1 as(select round(sum(total_amount)/count(uid),1) as avg_amount
from t join tb_order_overall using(uid)),

#每个订单原总价          
t2 as(select order_id,sum(price*cnt) as total_price
from tb_order_detail join tb_order_overall using(order_id)
group by order_id),
          
#平均获客成本
t3 as(select round(sum(total_price-total_amount)/count(*),1) as avg_cost
from tb_order_overall join t2 using(order_id)
where (uid,event_time)in(select*from t))

select avg_amount，avg_cost
from t1,t3

select avg_amount
from t1,t3

select avg_amount，avg_cost
from t1,t3

SELECT  round(sum(total_amount)/count(1),1),round(sum(f)/count(1),1)
FROM (SELECT order_id,uid,total_amount,s,s-total_amount f
FROM tb_order_overall
--联立订单明细表 算出优惠了多少
LEFT JOIN (
    --算出订单表的原本总额
    SELECT order_id,SUM(cnt*price) s
    FROM tb_order_detail
    GROUP BY order_id
    ) t1 USING(order_id)
     --过滤掉日期不对的和不是新用户订单的
    WHERE date_format(event_time,'%Y-%m')='2021-10'
    AND (uid,event_time) IN
        (SELECT uid,MIN(event_time) FROM tb_order_overall GROUP BY uid))t2;

select round(avg(avg1),1) as avg_amount ,round(avg(sum2-avg1),1) as avg_cost from (
    select order_id,avg(total_amount)avg1,sum(price*cnt)sum2
    from 
        (select order_id,uid,total_amount,event_time,rank() over(partition by uid order by event_time) as ranking from tb_order_overall)t join tb_order_detail using(order_id)
        join tb_product_info using(product_id)
        where date_format(event_time,'%Y%m')='202110' and ranking=1
            and uid not in (select uid from tb_order_overall where date_format(event_time,'%Y%m')<'202110' )
        group by order_id)t1

思路就是算出10月每个人的首单，然后利用筛选去掉在10月前活跃过的用户。

然后计算他们的总额和优惠额即可。

select 
    round(avg(price_1), 1) as avg_amount, 
    round(avg(price_0-price_1), 1) as avg_cost
from (
select uid, 
    sum(price*cnt) as price_0, 
    max(total_amount) as price_1
from (
select uid, a.order_id, event_time,
    a.product_id, price, cnt, total_amount,
    dense_rank() over(partition by uid order by event_time) as rnk
from tb_order_detail a left join tb_order_overall b 
on a.order_id = b.order_id
where status = 1
    ) t1
where rnk = 1
and date_format(event_time, '%Y-%m') = '2021-10'
group by uid
        ) t2

with base as
(select order_id,uid,total_amount,left(event_time,7) month,
rank()over(partition by uid order by left(event_time,10)) arank
from tb_order_overall)

select round(sum(to_am)/count(uid),1) avg_amount,round(sum(pr_am)/count(uid),1) avg_cost
from
(select uid,sum(total_price) to_p,sum(total_amount) to_am,
sum(total_price)-sum(total_amount) pr_am from base
join 
(select order_id,sum(price*cnt) total_price from tb_order_detail
group by order_id)a using(order_id)
where month='2021-10'
and arank=1
group by uid)b

with new_tab as (select uid,a.order_id,event_time,total_amount,price,cnt
,row_number()over(partition by uid order by event_time) as rank2
from tb_order_overall a 
left join tb_order_detail b on a.order_id=b.order_id)

select round(avg(amount),1),round(avg(cost),1)
from 
(select uid
,round(sum(total_amount)/count(uid)) as amount
,round(sum(price*cnt)-sum(total_amount)/count(uid)) as cost
from new_tab 
where (uid,order_id) in (select uid,order_id from new_tab where substring(event_time,1,7)='2021-10' and rank2=1)
group by uid,order_id) t

with t as (
    select
        uid,
        event_time,
        total_amount,
        price,
        cnt,
        rank() over(partition by uid order by event_time) rk
    from tb_order_overall oo
    join tb_order_detail od
    on oo.order_id = od.order_id
)
select distinct
    round(avg(avg(total_amount)) over(),1) avg_amount,
    round(avg(sum(price * cnt) - avg(total_amount)) over(),1) avg_cost
from t
where date_format(event_time,'%Y-%m') = '2021-10' and rk = 1
group by uid

【场景】：新用户首次行为

【分类】：聚合函数、子查询

分析思路

难点：

1.获取新用户首单的信息。使用in子查询

（1）统计每个订单的商品总金额

• [使用]：sum() group by

（2）计算2021年10月商城里所有新用户的首***均交易金额（客单价）和平均获客成本（保留一位小数）

连续的表示：日期减去排序的值相等；

• [条件]：2021年10月；新用户首单

• [使用]：(uid,event_time) in( )

最终结果

select 查询结果 [平均交易金额;平均获客成本]
from 从哪张表中查询数据[多表]
where 查询条件 [2021年10月;已付款：新用户首单]

求解代码

方法一：

with子句

with
    main as(
        #统计每个订单的商品总金额
        select
            order_id,
            sum(price*cnt) as uid_cost
        from tb_order_detail
        group by order_id
    )

#计算2021年10月商城里所有新用户的首***均交易金额（客单价）和平均获客成本（保留一位小数）
select
    round(avg(total_amount),1) as avg_amount,
    round(avg(uid_cost-total_amount),1) as avg_cost
from tb_order_overall
join main using(order_id)
where date_format(event_time,'%Y%m') = '202110'
and status = 1
and (uid,event_time) in(
    select
        uid,
        min(event_time) as event_time
    from tb_order_overall
    group by uid
)

方法二

多表嵌套

#计算2021年10月商城里所有新用户的首***均交易金额（客单价）和平均获客成本（保留一位小数）
select
    round(avg(total_amount),1) as avg_amount,
    round(avg(uid_cost-total_amount),1) as avg_cost
from tb_order_overall
join(
    #统计每个订单的商品总金额
    select
        order_id,
        sum(price*cnt) as uid_cost
    from tb_order_detail
    group by order_id
) main using(order_id)
where date_format(event_time,'%Y%m') = '202110'
and status = 1
and (uid,event_time) in(
    select
        uid,
        min(event_time) as event_time
    from tb_order_overall
    group by uid
)

SELECT 
    ROUND(AVG(total_amount), 1) AS avg_amount,
    ROUND(AVG(total - total_amount), 1) AS avg_cost
FROM(
    SELECT 
        uid,
        event_time,
        ROW_NUMBER() OVER (PARTITION BY uid ORDER BY event_time) AS ranking,
        total_amount,
        SUM(price*cnt) OVER (PARTITION BY order_id) AS total
    FROM tb_order_detail
    JOIN tb_order_overall USING(order_id)
    WHERE status = 1) TB1
WHERE 
    ranking = 1
    AND DATE_FORMAT(event_time, '%Y%m') = 202110

select round(sum(total_amount)/count(1),1) avg_amount
,round(sum(cost)/count(1),1) avg_cost 
from 
(SELECT uid,total_amount,sum(price*cnt)-total_amount cost
FROM tb_order_detail
join tb_product_info using (product_id)
join tb_order_overall using (order_id)
where date(event_time) like '%2021-10%'
and (uid,event_time) in (select uid ,min(event_time) from tb_order_overall GROUP BY uid )
group by uid,total_amount) t

with X as (
select t1.order_id
,        t2.uid
,        t2.total_amount
,        t1.price
,        row_number() over (partition by t2.uid order by t1.order_id) as rank_order
,        sum(price*cnt) over(partition by order_id) as sum_price
,        t2.status
from tb_order_detail t1
left join tb_order_overall t2 on t1.order_id=t2.order_id
left join tb_product_info t3 on t1.product_id=t3.product_id
where date_format(t2.event_time, '%Y-%m') = '2021-10' and status = 1)


select round(avg(total_amount), 1) as avg_amount
,        round(avg(sum_price-total_amount), 1) as avg_cost
from X
where rank_order=1