通过代码

SELECT round(sum(total_amount) / count(*), 1)                    avg_aount,
       round((sum(shouldpay) - sum(total_amount)) / count(*), 1) avg_cost
FROM (select order_id,
             uid,
             event_time,
             total_amount
      from (select order_id,
                   uid,
                   min(event_time) over (partition by uid order by event_time) event_time,
                   total_amount
            from tb_order_overall
           ) t
      GROUP BY uid
      HAVING DATE_FORMAT(event_time, '%Y%m') = 202110
     ) t_o
         JOIN(SELECT order_id,
                     sum(price * cnt) shouldpay
              FROM tb_order_detail
              GROUP BY order_id) t_d
             ON
                 t_d.order_id = t_o.order_id

思路

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

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

我们来分析一把

首先是用户要求: 2021-10 新用户，也就是说不仅是要求10月而且之前没有出现过这个人

所以我们先用窗口函数将最小日期列出来，这里为什么是窗口函数：如果直接group by 可能会造成最小日期与订单金额不匹配，我把group by 理解为折叠，虽然是把最小日期查出来，但是amount却是默认的该用户第一次出现在这个表里的日期的金额

select order_id, 
       uid,
       min(event_time) over (partition by uid order by event_time) event_time,
       total_amount
from tb_order_overall

然后查对应人

select order_id,
             uid,
             event_time,
             total_amount
      from (表1) t
      GROUP BY uid
      HAVING DATE_FORMAT(event_time, '%Y%m') = 202110

然后内连接订单明细查出来该用户一单应该花多少

SELECT order_id,
       sum(price * cnt) shouldpay
FROM tb_order_detail
GROUP BY order_id

然后算出题目要求

SELECT round(sum(total_amount) / count(*), 1)                    avg_aount,
       round((sum(shouldpay) - sum(total_amount)) / count(*), 1) avg_cost
FROM (表2) t_o
         JOIN(表3) t_d
             ON
                 t_d.order_id = t_o.order_id