BigQuery中按客户判断订单相对其他客户同期均价的升降级

1. 转换原始数组数据为结构化订单表 #

先将数组格式数据展开为每行一条订单的结构化表，方便后续计算：

WITH raw_data AS (
  SELECT 
    client_id, 
    sale_date, 
    package, 
    price
  FROM UNNEST([
    STRUCT('1' AS client_id, DATE('2023-01-01') AS sale_date, 'A' AS package, 1 AS price),
    STRUCT('2' AS client_id, DATE('2022-10-12') AS sale_date, 'B' AS package, 3 AS price),
    STRUCT('1' AS client_id, DATE('2023-04-04') AS sale_date, 'A' AS package, 4 AS price),
    STRUCT('4' AS client_id, DATE('2023-02-01') AS sale_date, 'D' AS package, 5 AS price),
    STRUCT('5' AS client_id, DATE('2022-04-01') AS sale_date, 'A' AS package, 6 AS price)
  ])
)

2. 获取客户×套餐维度的上一次销售日期 #

使用LAG()窗口函数，按客户和套餐分组、销售日期排序，得到每个订单对应的上一次同客户同套餐销售日期：

, client_package_prev_dates AS (
  SELECT
    *,
    LAG(sale_date) OVER (PARTITION BY client_id, package ORDER BY sale_date) AS prev_sale_date
  FROM raw_data
)

3. 计算时间区间内其他客户同套餐的均价 #

关联自身表，筛选其他客户、同套餐、日期在当前订单上一次销售日期（或最早日期）到当前销售日期之间的订单，计算均价：

, avg_price_calculation AS (
  SELECT
    curr.*,
    AVG(prev.price) OVER (PARTITION BY curr.client_id, curr.package, curr.sale_date) AS other_client_avg_price
  FROM client_package_prev_dates curr
  LEFT JOIN raw_data prev
    ON prev.package = curr.package
    AND prev.client_id != curr.client_id
    AND prev.sale_date BETWEEN COALESCE(curr.prev_sale_date, DATE('1970-01-01')) AND curr.sale_date
)

注：用COALESCE处理首次订单（无上一次销售日期）的情况，默认取最早日期作为区间起点。

4. 判断订单类型（升级/降级/无对比） #

对比当前订单价格与计算出的均价，标记订单类型：

SELECT
  client_id,
  sale_date,
  package,
  price,
  CASE
    WHEN other_client_avg_price IS NULL THEN '无对比数据'
    WHEN price > other_client_avg_price THEN '升级销售'
    WHEN price < other_client_avg_price THEN '降级销售'
    ELSE '持平'
  END AS sale_type
FROM avg_price_calculation
ORDER BY client_id, sale_date;

最终输出示例 #

执行完整代码后，输出结果如下：

内容的提问来源于stack exchange，提问作者Ekaterina Ponkratova