使用MySQL LAST_VALUE窗口函数处理日期列的结果异常问题

数据集 #

customer_id,        event_date,             status,         credit_limit
1,                  2019-1-1,                   C,                  1000
1,                  2019-1-5,                   F,                  1000
1,                  2019-3-10,             [NULL],                  1000
1,                  2019-3-10,              [NULL],                 1000
1,                  2019-8-27,                  L,                  1000
2,                  2019-1-1,                   L,                  2000
2,                  2019-1-5,               [NULL],                 2500
2,                  2019-3-10,              [NULL],                 2500
3,                  2019-1-1,                   S,                  5000
3,                  2019-1-5,               [NULL],                 6000
3,                  2019-3-10,                  B,                  5000
4,                  2019-3-10,                  B,                  10000

需求 #

按每个customer_id，展示2019年每个月末的账户状态

尝试的查询语句 #

with cte1 as 
(select customer_id, status, 
event_date,
last_value(date_format(event_date, '%Y-%m-%d')) over ( partition by customer_id, event_date
order by event_date) as l_v
from cust_acct ca 
where event_date between "2019-01-01 00:00:00" and "2019-12-31 11:59:59")
select * from cte1

查询返回结果 #

Customer_id,    Status,         Event_date,                 L_v
1,          C,          2019-01-01 00:00:00,            2019-01-01
1,          F,          2019-01-05 00:00:00,            2019-01-05
1,      [NULL],         2019-03-10 00:00:00,            2019-03-10
1,      [NULL],         2019-03-10 00:00:00,            2019-03-10
1,          L,          2019-08-27 00:00:00,            2019-08-27
2,          L,          2019-01-01 00:00:00,            2019-01-01
2,      [NULL],         2019-01-05 00:00:00,            2019-01-05
2,      [NULL],         2019-03-10 00:00:00,            2019-03-10
3,          S,          2019-01-01 00:00:00,            2019-01-01
3,      [NULL],         2019-01-05 00:00:00,            2019-01-05
3,          B,          2019-03-10 00:00:00,            2019-03-10
4,          B,          2019-03-10 00:00:00,            2019-03-10

用户疑问 #

对于customer_id为1的2019年1月，l_v列本应显示当月最晚日期2019-01-05，但查询却返回了1月的两个日期，这是为什么？

问题根源 #

你的窗口函数分区条件写错了——用partition by customer_id, event_date会把每个不同的event_date单独分成一个分区。比如customer_id=1的2019-01-01和2019-01-05是两个不同日期，会被分成两个独立分区。每个分区里last_value只能取到当前分区的唯一日期，自然返回各自的日期。

要实现按月取最晚日期，分区应该按customer_id和月份划分，而非具体日期。

修正后的查询语句 #

WITH cte1 AS (
    SELECT 
        customer_id,
        status,
        event_date,
        -- 按客户+年月分区，取分区内最晚日期
        LAST_VALUE(event_date) OVER (
            PARTITION BY customer_id, DATE_FORMAT(event_date, '%Y-%m')
            ORDER BY event_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS month_end_date,
        DATE_FORMAT(event_date, '%Y-%m') AS year_month
    FROM cust_acct ca
    WHERE event_date BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59'
),
cte2 AS (
    SELECT 
        customer_id,
        year_month,
        status,
        month_end_date
    FROM cte1
    WHERE event_date = month_end_date
    -- 处理同一天多条重复记录
    GROUP BY customer_id, year_month, status, month_end_date
)
SELECT 
    customer_id,
    year_month,
    -- 处理状态为NULL的情况，可根据需求调整
    COALESCE(status, '无状态') AS month_end_status,
    month_end_date
FROM cte2
ORDER BY customer_id, year_month;

修正说明 #

1. 分区调整：用DATE_FORMAT(event_date, '%Y-%m')将日期转为年月格式，确保同一客户的同月数据分到同一分区。
2. 窗口范围：添加ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，保证LAST_VALUE能取到整个分区的最后一条记录（默认窗口范围仅包含当前行及之前的记录）。
3. 结果筛选：通过WHERE event_date = month_end_date筛选出每月最后一条记录，并用GROUP BY去重同一天的重复数据。

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