如何按客户分组，为日期数据返回上一个存在的月份的lag值？

原始表结构及数据 #

期望结果 #

尝试的SQL（未得到正确结果） #

select *,
lag(month,1) over(partition by customer_id order by month) lastMonth
from table

问题分析 #

你当前的SQL用lag(month,1)会按行取上一行的月份值，同一月份的多条记录中，除了第一条，后续记录的lastMonth会取同月份的值（比如1月的第二条记录lastMonth会是1），而不是空值；同时无法直接为同一月份的所有记录统一填充上一个不同的存在月份。

解决方案 #

方法一：先提取唯一月份再关联 #

先提取每个用户的唯一月份，用LAG获取上一个月份，再和原表关联，确保同一月份的所有记录都能拿到正确的上一个月份：

WITH distinct_month_list AS (
    SELECT DISTINCT customer_id, month
    FROM your_table
),
month_with_prev AS (
    SELECT 
        customer_id,
        month,
        LAG(month) OVER (PARTITION BY customer_id ORDER BY month) AS lastMonth
    FROM distinct_month_list
)
SELECT 
    t.date_key,
    t.month,
    t.customer_id,
    m.lastMonth
FROM your_table t
LEFT JOIN month_with_prev m 
    ON t.customer_id = m.customer_id AND t.month = m.month
ORDER BY t.date_key;

方法二：嵌套窗口函数直接计算 #

通过嵌套窗口函数，先标记出每个月份首次出现时的上一个月份，再将该值填充到同一月份的所有记录中：

SELECT 
    date_key,
    month,
    customer_id,
    MAX(last_month_temp) OVER (PARTITION BY customer_id, month) AS lastMonth
FROM (
    SELECT 
        *,
        -- 仅当当前行月份与上一行不同时，取上一行的月份
        CASE 
            WHEN LAG(month) OVER (PARTITION BY customer_id ORDER BY date_key) != month 
            THEN LAG(month) OVER (PARTITION BY customer_id ORDER BY date_key)
            ELSE NULL 
        END AS last_month_temp
    FROM your_table
) temp_table
ORDER BY date_key;

以上两种方法都能实现需求，可根据你的数据库环境和数据量选择合适的方式。

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