如何修改SQL脚本分析客户购买行为变迁及销售增减情况

我希望监控客户的购买行为。每位客户可购买一款或多款产品，且各产品价格区间不同。如何判断客户是持续购买、转向更小套餐还是升级至更大套餐？

目前已有如下SQL脚本用于查看客户购买变动，但需要优化以获取更多细节：

SELECT 
A.Month PRE_MONTH,A.ProductID PRE_PRODUCTID,
A.Product_name PRE_PRODUCT_NAME,
B.Month POST_MONTH,B.ProductID POST_PRODUCTID,
B.Product_name POST_PRODUCT_NAME,
SUM(B.sales)sales, COUNT(DISTINCT B.CustomerID)USER
FROM ( SELECT * FROM TABLE_X WHERE MONTH = 'JAN' ) A 
LEFT OUTER JOIN ( SELECT * FROM TABLE_X WHERE MONTH = 'FEB')B ON A.CustomerID = B.CustomerID

需求：

• 调整SQL以识别客户相较于上月的销售额是上升还是下降
• 确定客户从哪些产品转向了哪些产品

原始数据字段包含：CustomerID、Month、ProductID、Product_name、Sales，记录了不同月份客户的购买产品及对应销售额信息。

优化后的SQL方案 #

1. 通用跨月份对比版本（适配任意连续月份） #

该方案用窗口函数实现，无需硬编码月份，扩展性更强：

WITH monthly_customer_sales AS (
    SELECT 
        CustomerID,
        Month,
        ProductID,
        Product_name,
        Sales,
        -- 获取同一客户上月的购买数据
        LAG(Month) OVER(PARTITION BY CustomerID ORDER BY Month) AS PRE_MONTH,
        LAG(ProductID) OVER(PARTITION BY CustomerID ORDER BY Month) AS PRE_PRODUCTID,
        LAG(Product_name) OVER(PARTITION BY CustomerID ORDER BY Month) AS PRE_PRODUCT_NAME,
        LAG(Sales) OVER(PARTITION BY CustomerID ORDER BY Month) AS PRE_SALES
    FROM TABLE_X
    -- 可按需限定月份范围，比如 WHERE Month IN ('JAN', 'FEB')
)
SELECT 
    CustomerID,
    PRE_MONTH,
    PRE_PRODUCTID,
    PRE_PRODUCT_NAME,
    PRE_SALES,
    Month AS POST_MONTH,
    ProductID AS POST_PRODUCTID,
    Product_name AS POST_PRODUCT_NAME,
    Sales AS POST_SALES,
    -- 计算销售额变动
    Sales - PRE_SALES AS SALES_CHANGE,
    -- 标识销售额变动类型
    CASE
        WHEN PRE_SALES IS NULL THEN '新客户首次购买'
        WHEN Sales IS NULL THEN '老客户本月流失'
        WHEN Sales > PRE_SALES THEN '销售额上升'
        WHEN Sales < PRE_SALES THEN '销售额下降'
        ELSE '销售额持平'
    END AS SALES_CHANGE_TYPE,
    -- 标识产品流转类型
    CASE
        WHEN PRE_PRODUCTID IS NULL THEN '新购产品'
        WHEN ProductID IS NULL THEN '停止购买'
        WHEN PRE_PRODUCTID = ProductID THEN '持续购买同款'
        ELSE '产品切换（从' || PRE_PRODUCT_NAME || '转向' || Product_name || '）'
    END AS PRODUCT_CHANGE_TYPE
FROM monthly_customer_sales
-- 过滤出需对比的目标月份，比如只查看FEB的变动记录
WHERE Month = 'FEB'
ORDER BY CustomerID;

2. 固定双月份对比版本（仅针对JAN和FEB） #

如果只需要对比特定两个月，用全连接更直观：

SELECT 
    COALESCE(A.CustomerID, B.CustomerID) AS CustomerID,
    A.Month AS PRE_MONTH,
    A.ProductID AS PRE_PRODUCTID,
    A.Product_name AS PRE_PRODUCT_NAME,
    A.Sales AS PRE_SALES,
    B.Month AS POST_MONTH,
    B.ProductID AS POST_PRODUCTID,
    B.Product_name AS POST_PRODUCT_NAME,
    B.Sales AS POST_SALES,
    B.Sales - A.Sales AS SALES_CHANGE,
    CASE
        WHEN A.CustomerID IS NULL THEN '新客户首次购买'
        WHEN B.CustomerID IS NULL THEN '老客户本月流失'
        WHEN B.Sales > A.Sales THEN '销售额上升'
        WHEN B.Sales < A.Sales THEN '销售额下降'
        ELSE '销售额持平'
    END AS SALES_CHANGE_TYPE,
    CASE
        WHEN A.ProductID IS NULL THEN '新购产品'
        WHEN B.ProductID IS NULL THEN '停止购买'
        WHEN A.ProductID = B.ProductID THEN '持续购买同款'
        ELSE '产品切换（从' || A.Product_name || '转向' || B.Product_name || '）'
    END AS PRODUCT_CHANGE_TYPE
FROM (SELECT * FROM TABLE_X WHERE Month = 'JAN') A
FULL OUTER JOIN (SELECT * FROM TABLE_X WHERE Month = 'FEB') B
ON A.CustomerID = B.CustomerID
ORDER BY CustomerID;

关键调整说明 #

1. 改用FULL OUTER JOIN：原LEFT JOIN会丢失上月无购买但本月新增的客户，全连接可覆盖所有场景（老客户留存、流失、新客户新增）
2. 以客户为核心维度：原SQL按产品聚合会丢失单客户多产品的流转细节，调整后清晰展示每个客户的个体购买变动
3. 新增销售额变动计算：直接计算本月与上月销售额差值，并用CASE语句直观标识变动类型
4. 明确产品流转标识：通过对比前后产品ID，区分持续购买同款、产品切换、新购、停止购买四种核心场景
5. 窗口函数优化：通用版用LAG函数可灵活适配任意连续月份对比，无需重复修改SQL硬编码月份

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