我必须对一个有4亿多行的表进行数据分析。我得到这个工作在一个小样本，但我相信它会用尽生产内存。
表结构如下（对于数百万个序列号）：

+------------+---------------+------------+----------+
|    date    | serial_number |  status_1  | status_2 |
+------------+---------------+------------+----------+
| 10/1/2018  |           123 | warehouse  | v        |
| 10/10/2018 |           123 | warehouse  | w        |
| 10/20/2018 |           123 | warehouse  | x        |
| 11/2/2018  |           123 | in transit | y        |
+------------+---------------+------------+----------+

我需要得到的日期，其中状态1='在运输中'目前和状态2='x'上一个日期。应该是这样的：

+-----------+---------------+------------+----------+------------+
|  date_1   | serial_number |  status_1  | status_2 |   date_2   |
+-----------+---------------+------------+----------+------------+
| 11/2/2018 |           123 | in transit | x        | 10/20/2018 |
+-----------+---------------+------------+----------+------------+

我用两个秩函数得到它，但这可能会阻塞一个大表。

with transit as (
select 

* 

from (
    select *,
    rank() over(partition by serial_number order by date desc) rnk
    from sample_t 
    order by serial_number, date asc
    ) 
where rnk=1 and status_1 = 'in transit'
),
x_type as (
select 

* 

from (
    select *,
    rank() over(partition by serial_number order by date desc) rnk
    from sample_t 
    order by serial_number, date asc
    ) 
where rnk>1 and status_2 = 'x'
)
select tr.date date_1,
tr.serial_number,
tr.status_1,
x.status_2,
x.date date_2
from transit tr left join x_type x on tr.serial_number = x.serial_number

我不知道用一个秩函数怎么做。有没有更好、更有效的方法？