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

明确题意：

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

某个订单的优惠金额可通过订单明细里的 {该订单各商品单价×数量之和-订单总金额} （订单总金额在订单总表中）得到。

问题分解：

• 统计用户首单信息（生成子表t_first_order）：
  • 定义按用户分区按行为时间排序的窗口：WINDOW wd_uid_first as (partition by uid order by event_time)
  • 获取窗口内第一个行为时间：FIRST_VALUE(event_time) over(wd_uid_first) as event_time
  • 获取窗口内第一个订单ID：FIRST_VALUE(order_id) over(wd_uid_first) as order_id
  • 获取窗口内第一个订单金额：FIRST_VALUE(total_amount) over(wd_uid_first) as total_amount
• 统计每个订单原始金额（生成子表t_raw_amount）：
  • 按订单号分组：GROUP BY order_id
  • 商品价格求和：SUM(price * cnt) as raw_amount
• 关联用户首单的原始价格：t_first_order JOIN t_raw_amount USING(order_id)
• 筛选时间窗：WHERE DATE_FORMAT(event_time, "%Y-%m") = '2021-10'
• 基于以上结果统计首单平均交易金额和平均获客成本：
  • 首单平均交易金额：AVG(total_amount) as avg_amount
  • 平均获客成本：AVG(raw_amount-total_amount) as avg_cost

细节问题：

• 表头重命名：as

完整代码：

SELECT ROUND(AVG(total_amount), 1) as avg_amount,
    ROUND(AVG(raw_amount-total_amount), 1) as avg_cost
FROM (
    SELECT uid, total_amount, raw_amount
    FROM (
        SELECT DISTINCT uid,
            FIRST_VALUE(event_time) over(wd_uid_first) as event_time,
            FIRST_VALUE(order_id) over(wd_uid_first) as order_id,
            FIRST_VALUE(total_amount) over(wd_uid_first) as total_amount
        FROM tb_order_overall
        WINDOW wd_uid_first as (partition by uid order by event_time)
    ) as t_first_order
    JOIN (
        SELECT order_id, SUM(price * cnt) as raw_amount
        FROM tb_order_detail
        GROUP BY order_id
    ) as t_raw_amount
    USING(order_id)
    WHERE DATE_FORMAT(event_time, "%Y-%m") = '2021-10'
) as t_first_order_info;