根据匹配ID的时间戳获取上一个行值
我有一张有关运输的表，其中有有关到达（国家和日期）到港口的信息。现在，我需要提取它不使用上一行条目的国家。该桌子看起来像这个
| ID | 乡村行动 | 数据元素 |
|---|---|---|
| 1 | 是 | 1-1-2022 |
| 2 | US | 1-1-2022 |
| 1 | NL | 2-1-2022 |
| 2 | IT | 4-1-2022 |
| 1 | PT | 5-1-2022 |
我想获得出发每个ID基于先前到达的情况，因此看起来像这个
| ID | CountryArarival | DateArival | DewarturePort |
|---|---|---|---|
| 1 | BE | 1-1-2022 | NULL |
| 2 | US | 1-1-2022 | NULL |
| 1 | NL | 2-1-2022 | BE |
| 2 | IT | 4-1-2022 | US |
| 1 US 1 | PT | 5-1-2022 | NL |
我只能基于DateArival获得以前的国家 /地区以下情况：
select
pc.*,
lag(pc.CountryArrival) over (order by DateArrival) as DeparturePort
from shipping pc
where pc.DateArrival is not null;
任何想法如何获得匹配ID的先前到达？
I have a table about shipping that has information about the arrival (country and date) to a port. Now I need to extract the country where it departed from using the previous row entries. The table looks like this
| ID | CountryArrival | DateArrival |
|---|---|---|
| 1 | BE | 1-1-2022 |
| 2 | US | 1-1-2022 |
| 1 | NL | 2-1-2022 |
| 2 | IT | 4-1-2022 |
| 1 | PT | 5-1-2022 |
I want to obtain the departure for each ID based on the previous ArrivalDate so it would look like this
| ID | CountryArrival | DateArrival | DeparturePort |
|---|---|---|---|
| 1 | BE | 1-1-2022 | NULL |
| 2 | US | 1-1-2022 | NULL |
| 1 | NL | 2-1-2022 | BE |
| 2 | IT | 4-1-2022 | US |
| 1 | PT | 5-1-2022 | NL |
I can obtain the previous Country based only on DateArrival with:
select
pc.*,
lag(pc.CountryArrival) over (order by DateArrival) as DeparturePort
from shipping pc
where pc.DateArrival is not null;
Any idea how to get the previous arrival for matching IDs?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您需要通过
<代码> ID列进行分区。You need to
PARTITION BYtheIDcolumn.