如何修正SQL查询，获取客户上月最后订单所在城市？

你的原查询逻辑存在核心偏差：

• 你按customer, year, month分区，意味着每个分区仅包含客户当月的订单数据
• last_value(City)取的是当月订单中最后一条的城市，完全没有关联到上月的数据，自然不符合“获取上月最后订单城市”的需求

场景1：严格获取日历上月的最后订单城市（若上月无订单则返回null） #

这种场景下，即使客户上月没有订单，也返回null，而不是往前找更早的月份。实现步骤如下：

1. 先提取每个客户每个月的最后一笔订单城市
2. 将每个客户的年月与上月的年月关联，匹配对应城市

WITH monthly_last_order AS (
    -- 筛选每个客户每月的最后一笔订单
    SELECT 
        customer,
        year,
        month,
        city_id AS last_city,
        ROW_NUMBER() OVER (PARTITION BY customer, year, month ORDER BY day DESC) AS rn
    FROM table1
),
customer_monthly_last AS (
    -- 保留每个客户每月的最后订单城市
    SELECT customer, year, month, last_city
    FROM monthly_last_order
    WHERE rn = 1
)
-- 关联上月数据
SELECT 
    t.customer,
    t.year,
    t.month,
    cml.last_city AS prevCity
FROM (
    -- 获取客户所有有订单的唯一年月
    SELECT DISTINCT customer, year, month
    FROM table1
) t
LEFT JOIN customer_monthly_last cml 
    ON t.customer = cml.customer
    AND (
        -- 同月跨年的情况：比如2023年1月对应2022年12月
        (t.year = cml.year AND t.month = cml.month + 1)
        OR 
        (t.month = 1 AND cml.year = t.year - 1 AND cml.month = 12)
    );

场景2：获取最近的上一个有订单月份的最后订单城市（跳过无订单的月份） #

这和你示例中的结果一致（5月对应3月的城市，因为4月无订单）。可以用LAG()窗口函数直接获取前一个有订单月份的城市：

WITH monthly_last_order AS (
    -- 筛选每个客户每月的最后一笔订单
    SELECT 
        customer,
        year,
        month,
        city_id AS last_city,
        ROW_NUMBER() OVER (PARTITION BY customer, year, month ORDER BY day DESC) AS rn
    FROM table1
),
customer_monthly_sequence AS (
    -- 按客户的年月顺序排序
    SELECT 
        customer,
        year,
        month,
        last_city,
        ROW_NUMBER() OVER (PARTITION BY customer ORDER BY year, month) AS seq
    FROM monthly_last_order
    WHERE rn = 1
)
-- 用LAG获取前一个有订单月份的最后城市
SELECT 
    customer,
    year,
    month,
    LAG(last_city) OVER (PARTITION BY customer ORDER BY year, month) AS prevCity
FROM customer_monthly_sequence;

针对你示例数据的执行结果 #

用场景2的SQL会得到你期望的结果：

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