获取每行对应最近PR记录的complete日期（禁用cross join）

需要为test_table表中的每一行返回对应最近的LINE='PR'记录的complete日期，命名为PRDATE。注意：表存在数百万条记录，禁止使用cross join。

测试数据 #

alter session set DATE_INPUT_FORMAT='DD-MM-YYYY';
create database testdb; use database testdb;
create table test_table(
   location int
 , line text
 , start_ date
 , complete date );

insert into test_table values
 (10003281, 'P', '30-08-2024','30-08-2024')
,(10003281, 'P', '22-03-2024','22-03-2024')
,(10003281, 'PR', null,       '13-02-2024')
,(10003281, 'P', '13-02-2024','13-02-2024')
,(10003281, 'P', '18-12-2023','11-01-2024')
,(10003281, 'P', '21-11-2023','21-11-2023')
,(10003281, 'P', '07-08-2023','11-10-2023')
,(10003281, 'PR', null,       '07-08-2023');

预期结果 #

解决方案 #

针对百万级数据的高效处理，我们可以利用窗口函数LAST_VALUE结合条件过滤实现，避免笛卡尔积带来的性能问题。核心逻辑是按location分组、complete日期降序排序，在窗口内仅保留PR记录的日期并向前填充，让每条记录匹配到最近的PR日期。

SELECT
    location,
    line,
    start_,
    complete,
    LAST_VALUE(CASE WHEN line = 'PR' THEN complete END IGNORE NULLS) 
        OVER (PARTITION BY location ORDER BY complete DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PRDATE
FROM test_table
ORDER BY complete DESC;

逻辑说明 #

1. 分组排序：按location分组确保仅在同一区域内匹配PR记录，按complete降序排序让最新记录优先。
2. 条件提取：用CASE语句仅提取PR记录的complete日期，非PR记录返回NULL。
3. 填充匹配：LAST_VALUE(...) IGNORE NULLS会自动忽略NULL值，取当前行及之前行中最后一个有效的PR日期，即最近的PR记录日期。
4. 性能优势：窗口函数时间复杂度为O(n)，远优于cross join的O(n²)，适合处理百万级数据量。

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