求根据客户订单数据判定客户所属状态的SQL查询语句

实现思路 #

• 先通过窗口函数获取每个客户的首单日期、上一笔订单的日期、当前订单年份的消费天数
• 再通过状态回溯逻辑，判断当前订单之前是否存在过活跃状态记录
• 最后按给定的5条规则匹配输出对应状态

完整查询SQL #

WITH order_base AS (
    -- 基础数据预处理：提取年份、上一次订单日期、首单日期、当年消费天数，customer_order替换为你的实际表名
    SELECT 
        customer,
        orderdate,
        YEAR(orderdate) AS order_year,
        LAG(orderdate) OVER (PARTITION BY customer ORDER BY orderdate) AS last_order_date,
        MIN(orderdate) OVER (PARTITION BY customer) AS first_order_date,
        COUNT(DISTINCT orderdate) OVER (PARTITION BY customer, YEAR(orderdate)) AS year_order_day_cnt
    FROM customer_order
),
status_calc AS (
    -- 逐行计算状态
    SELECT 
        customer,
        orderdate,
        CASE
            -- 首单年份的订单匹配New类状态
            WHEN order_year = YEAR(first_order_date) THEN 
                CASE WHEN year_order_day_cnt = 1 THEN 'New single' ELSE 'New multi' END
            -- 与上一次消费间隔满1年匹配Reactive
            WHEN DATEDIFF(orderdate, last_order_date) >= 365 THEN 'Reactive'
            -- 上一年度有消费的情况匹配Active类状态
            WHEN EXISTS (
                SELECT 1 FROM order_base ob 
                WHERE ob.customer = order_base.customer 
                AND ob.order_year = order_base.order_year - 1
            ) THEN 
                CASE WHEN 
                    -- 判断此前是否有活跃记录
                    NOT EXISTS (
                        SELECT 1 FROM order_base ob2 
                        WHERE ob2.customer = order_base.customer 
                        AND ob2.orderdate < order_base.orderdate
                        AND YEAR(ob2.orderdate) > YEAR(first_order_date)
                        AND DATEDIFF(ob2.orderdate, LAG(ob2.orderdate) OVER (PARTITION BY ob2.customer ORDER BY ob2.orderdate)) < 365
                    ) THEN 'Active new'
                ELSE 'Active evergreen' END
        END AS customer_status
    FROM order_base
)
SELECT customer, orderdate, customer_status AS output 
FROM status_calc 
ORDER BY customer, orderdate;

规则匹配说明 #

• New single：匹配当前订单属于客户首单所在年份，且当年仅1天有消费的场景
• New multi：匹配当前订单属于客户首单所在年份，且当年有2天及以上消费的场景
• Reactive：匹配当前订单和上一笔订单的日期间隔≥365天的场景
• Active new：匹配当前订单年份和上一年均有消费，且此前没有出现过连续两年消费的活跃记录的场景
• Active evergreen：匹配当前订单年份和上一年均有消费，且此前已经有过活跃记录的场景

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