基于Presto SQL按订单间隔识别新客户订单的技术咨询

需求规则 #

• 当订单满足以下任一条件时，标记为NewCustomerOrder：
  • 该订单是客户的首次订单
  • 该订单与客户的上一次订单间隔超过720天
• 不满足则标记为ExistingOrder

解决方案代码 #

WITH all_orders AS (
    SELECT
        customer_id,
        order_id,
        order_date,
        'customer_feed' AS source
    FROM
        datalake.dwh_main.customer_feed
    WHERE
        retailer = 'customer'
        AND market = 'market'
    UNION
    SELECT
        customer_id,
        order_id,
        datestamp AS order_date,
        'order_feed' AS source
    FROM
        datalake.dwh_main.order_feed
    WHERE
        retailer = 'retailer'
        AND market = 'market'
),
customer_order_sequence AS (
    SELECT
        customer_id,
        order_id,
        order_date,
        source,
        -- 按客户分组、订单日期排序，获取上一次订单日期
        LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date
    FROM all_orders
)
SELECT
    customer_id,
    order_id,
    order_date AS datestamp,
    source,
    CASE
        WHEN prev_order_date IS NULL 
             OR DATE_DIFF('day', prev_order_date, order_date) > 720
        THEN 'NewCustomerOrder'
        ELSE 'ExistingOrder'
    END AS customer_order_type
FROM customer_order_sequence
ORDER BY customer_id, order_date;

关键逻辑说明 #

1. 合并订单数据源：保留原代码的all_orders CTE，统一合并两个订单数据源
2. 获取上一次订单日期：使用LAG()窗口函数，按客户ID分组、订单日期升序排列，为每个订单匹配上一次的订单日期
3. 判断订单类型：
   • 首次订单的prev_order_date为NULL，直接判定为新客户订单
   • 计算当前订单与上一次订单的天数间隔，超过720天则标记为新客户订单
   • 其余情况标记为现有订单

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