Mysql 解法, 执行用时: 37ms, 内存消耗: 6400KB, 提交时间: 2021-12-16

SELECT
    ROUND(SUM(total_amount) / COUNT(uid), 1) AS avg_amount,
    ROUND(SUM(firstly_amount - total_amount) / COUNT(uid), 1) AS avg_cost
FROM
    (SELECT
        uid,
        order_id,
        total_amount
    FROM 
        (SELECT
            *,
            RANK()OVER(PARTITION BY uid ORDER BY event_time) AS order_rank
        FROM tb_order_overall) AS t1
    WHERE
        order_rank = 1
        AND
        DATE_FORMAT(event_time, '%Y%m') = 202110) AS t2
JOIN
    (SELECT
        order_id,
        SUM(price * cnt) AS firstly_amount
    FROM tb_order_detail
    GROUP BY order_id) AS t3
ON t2.order_id = t3.order_id;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6408KB, 提交时间: 2022-01-24

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

# 注：订单的优惠金额 = 订单明细里的{该订单各商品单价×数量之和} - 订单总表里的{订单总金额} 
# 解释：

# 2021年10月有3个新用户，102的首单为301002，订单金额为235，商品总金额为85+180=265，优惠金额为30；
# 101的首单为301003，订单金额为300，商品总金额为140+180=320，优惠金额为20；
# 104的首单为301005，订单金额为160，商品总金额为180，优惠金额为20；
# 平均首单客单价为(235+300+160)/3=231.7，平均获客成本为(30+20+20)/3=23.3

with a as(
SELECT uid,min(event_time) as reg_datetime
FROM tb_order_detail tod
JOIN tb_order_overall too
ON tod.order_id = too.order_id
GROUP BY uid
HAVING DATE_FORMAT(DATE(min(event_time)),'%Y-%m') = '2021-10')

select round(avg(total_amount),1) as avg_amount,round(avg(cost),1) as avg_cost
from(
    SELECT too.uid,total_amount,sum(price*cnt) - total_amount as cost
    FROM tb_order_detail tod
    JOIN tb_order_overall too
    ON tod.order_id = too.order_id
    JOIN a
    ON too.uid = a.uid and too.event_time = a.reg_datetime
    group by too.uid,total_amount) as b

Mysql 解法, 执行用时: 37ms, 内存消耗: 6408KB, 提交时间: 2022-01-23

#10月新用户
with a as
(select uid,total_amount,tb_order_overall.order_id,
sum(price*cnt) as total
from tb_order_overall,tb_order_detail
where tb_order_overall.order_id=tb_order_detail.order_id
and month(event_time)=10
group by tb_order_overall.order_id,uid,total_amount),
b as
#首单，且是新用户
(select uid,order_id from 
(select uid,order_id,event_time,
dense_rank()over(partition by uid order by event_time) as rk
from tb_order_overall)a1
where rk=1 and month(event_time)=10),
c as#10月新客户的首单信息
(select * from a 
where uid in (select uid from b) 
and order_id in (select order_id from b))
select round(avg(total_amount),1) as avg_amount,
round(avg(total-total_amount),1) as avg_cost
from c

Mysql 解法, 执行用时: 37ms, 内存消耗: 6416KB, 提交时间: 2022-01-21

# select round(avg(avg_a),1) avg_amount,round(avg(avg_in-avg_a),1) as avg_cost from
# (select uid,avg(total_amount) avg_a,sum(cnt*price) avg_in from
# (select uid,event_time,tod.order_id,total_amount,cnt,price,
# dense_rank() over(partition by uid order by event_time) timerank
# from tb_order_detail tod,tb_product_info tpi,tb_order_overall too
# where tod.product_id=tpi.product_id and tod.order_id=too.order_id
# and left(event_time,7) like '2021-10')a
# where timerank=1
# group by uid
# order by uid)b

select round(sum(total_amount)/count(distinct too.order_id),1) avg_amount
,round(sum(price-total_amount)/count(distinct  too.order_id),1) avg_cost
from tb_order_overall too
inner join (
  select order_id,sum(price*cnt) price
  from tb_order_detail
  group by order_id
  ) as t
on too.order_id=t.order_id
where date_format(event_time,'%Y-%m')='2021-10'
and (uid,date(event_time)) in (select uid,min(date(event_time)) from tb_order_overall group by uid)

Mysql 解法, 执行用时: 37ms, 内存消耗: 6428KB, 提交时间: 2022-01-22

select round(avg(total_amount),1) avg_amount,round(avg(sum_price-total_amount),1) avg_cost
from
(
        select 
        uid,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
        join tb_order_overall too
        using(order_id)
        where status = 1 
) t
where rk = 1
and date_format(event_time,'%Y-%m')='2021-10'