oracle 使用LAG获取先前的不相同值，同时不考虑某些列

这可能是问题Using LAG function to obtain value that is not necessarily in the previous row的延伸
我有一个表my_table，我想根据 episode_id、file_id和order_date创建一个新列previous_order_date。 还有一个列offer_id，我不想考虑用于previous_order_date计算
| episode_id|文件ID| offer_id|订货日期|
| - -----|- -----|- -----|- -----|
| 一二三四|三千|七百|2020年10月10日|
| 一二三四|三千|八百|2020年10月10日|
| 一二三四|三千|九百|2020年10月10日|
| 一二三四|三千|八百|2020年10月11日|
| 一二三四|三千|九百|2020年10月11日|
| 一二三四|四千|七百|2020年10月10日|
| 一二三四|四千|八百|2020年10月10日|
| 一二三四|四千|七百|2020年10月11日|
| 四三二一|三千|七百|2020年10月10日|
| 四三二一|三千|八百|2020年10月10日|
| 四三二一|三千|九百|2020年10月10日|
| 四三二一|三千|八百|2020年10月11日|
| 四三二一|三千|九百|2020年10月11日|
| 四三二一|四千|七百|2020年10月10日|
| 四三二一|四千|八百|2020年10月10日|
| 四三二一|五千|七百|2020年10月10日|
| 四三二一|五千|七百|2020年10月11日|
| 四三二一|四千|七百|2020年10月11日|
| 四三二一|三千|七百|2020年10月12日|
查询：

create table my_table
(episode_id number,
  file_id number,
  offer_id number,
  order_date date
);

insert all
 into my_table values (1234,3000,700, to_date('2020/10/10','YYYY/MM/DD'))
 into my_table values (1234,3000,800, to_date('2020/10/10','YYYY/MM/DD'))
 into my_table values (1234,3000,900, to_date('2020/10/10','YYYY/MM/DD'))
 into my_table values (1234,3000,800, to_date('2020/10/11','YYYY/MM/DD'))
 into my_table values (1234,3000,900, to_date('2020/10/11','YYYY/MM/DD'))
 into my_table values (1234,4000,700, to_date('2020/10/10','YYYY/MM/DD'))
 into my_table values (1234,4000,800, to_date('2020/10/10','YYYY/MM/DD'))
 into my_table values (1234,4000,700, to_date('2020/10/11','YYYY/MM/DD'))
 into my_table values (4321,3000,700, to_date('2020/10/10','YYYY/MM/DD'))
 into my_table values (4321,3000,800, to_date('2020/10/10','YYYY/MM/DD'))
 into my_table values (4321,3000,900, to_date('2020/10/10','YYYY/MM/DD'))
 into my_table values (4321,3000,800, to_date('2020/10/11','YYYY/MM/DD'))
 into my_table values (4321,3000,900, to_date('2020/10/11','YYYY/MM/DD'))
 into my_table values (4321,4000,700, to_date('2020/10/10','YYYY/MM/DD'))
 into my_table values (4321,4000,800, to_date('2020/10/10','YYYY/MM/DD'))
 into my_table values (4321,5000,700, to_date('2020/10/10','YYYY/MM/DD'))
 into my_table values (4321,5000,700, to_date('2020/10/11','YYYY/MM/DD'))
 into my_table values (4321,4000,700, to_date('2020/10/11','YYYY/MM/DD'))
 into my_table values (4321,3000,700, to_date('2020/10/12','YYYY/MM/DD'))

我想使用LAG来获得基于episode_id和file_id分区的前一行order_date，也就是说，如果使用不同的offer_id，我不想获得前一行order_date。
我想让输出像这样：
| episode_id|文件ID| offer_id|订货日期|上一订单日期|
| - -----|- -----|- -----|- -----|- -----|
| 一二三四|三千|七百|2020年10月10日|零|
| 一二三四|三千|八百|2020年10月10日|零|
| 一二三四|三千|九百|2020年10月10日|零|
| 一二三四|三千|八百|2020年10月11日|2020年10月10日|
| 一二三四|三千|九百|2020年10月11日|2020年10月10日|
| 一二三四|四千|七百|2020年10月10日|零|
| 一二三四|四千|八百|2020年10月10日|零|
| 一二三四|四千|七百|2020年10月11日|2020年10月10日|
| 四三二一|三千|七百|2020年10月10日|零|
| 四三二一|三千|八百|2020年10月10日|零|
| 四三二一|三千|九百|2020年10月10日|零|
| 四三二一|三千|八百|2020年10月11日|2020年10月10日|
| 四三二一|三千|九百|2020年10月11日|2020年10月10日|
| 四三二一|四千|七百|2020年10月10日|零|
| 四三二一|四千|八百|2020年10月10日|零|
| 四三二一|五千|七百|2020年10月10日|零|
| 四三二一|五千|七百|2020年10月11日|2020年10月10日|
| 四三二一|四千|七百|2020年10月11日|2020年10月10日|
| 四三二一|三千|七百|2020年10月12日|2020年10月11日|
但我试过运行这个查询

select episode_id
       ,file_id
       ,offer_id
      ,order_date
      ,lag(order_date) over(partition by episode_id, file_id order by order_date) as previous_order_date
from my_table

然而这产生了这个表
| episode_id|文件ID| offer_id|订货日期|上一订单日期|
| - -----|- -----|- -----|- -----|- -----|
| 一二三四|三千|七百|2020年10月10日|零|
| 一二三四|三千|八百|2020年10月10日|2020年10月10日|
| 一二三四|三千|九百|2020年10月10日|2020年10月10日|
| 一二三四|三千|八百|2020年10月11日|2020年10月10日|
| 一二三四|三千|九百|2020年10月11日|2020年10月11日|
| 一二三四|四千|七百|2020年10月10日|零|
| 一二三四|四千|八百|2020年10月10日|2020年10月10日|
| 一二三四|四千|七百|2020年10月11日|2020年10月10日|
| 四三二一|三千|七百|2020年10月10日|零|
| 四三二一|三千|八百|2020年10月10日|2020年10月10日|
| 四三二一|三千|九百|2020年10月10日|2020年10月10日|
| 四三二一|三千|八百|2020年10月11日|2020年10月10日|
| 四三二一|三千|九百|2020年10月11日|2020年10月10日|
| 四三二一|四千|七百|2020年10月10日|零|
| 四三二一|四千|八百|2020年10月10日|零|
| 四三二一|五千|七百|2020年10月10日|零|
| 四三二一|五千|七百|2020年10月11日|2020年10月10日|
| 四三二一|四千|七百|2020年10月11日|2020年10月10日|
| 四三二一|三千|七百|2020年10月12日|2020年10月11日|
其采用前一行order_date而不管offer_id。这是一个相当大的查询，我需要offer_id列来进行进一步的计算，因为我正在基于该列连接其他表。因此，我想知道，既然现实世界的应用程序将order_date精确到分钟，是否有一种方法可以让LAG查看至少早1分钟的前一行，类似于INTERVAL函数。
我尝试在CTE中选择episode_id、file_id和order_date，并在其中使用LAG，但这会重复行，一行的order_date为previous_order_date，另一行的previous_order_date为空。当我出于某种原因尝试使用DENSE_RANK时，也发生了同样的情况。
我的DB版本是19 C

您似乎希望在LAG中PARTITION BY episode_id, file_id, offer_id：

select episode_id,
       file_id,
       offer_id,
       order_date,
       LAG(order_date) OVER(
         PARTITION BY episode_id, file_id, offer_id
         ORDER BY order_date
       ) AS previous_order_date
FROM   my_table

或者，您可以使用MATCH_RECOGNIZE：

select *
FROM   my_table
MATCH_RECOGNIZE(
  PARTITION BY episode_id, file_id
  ORDER BY order_date DESC, offer_id DESC
  MEASURES
    current_row.offer_id AS offer_id,
    current_row.order_date AS order_date,
    prev_day.order_date AS previous_order_date
  AFTER MATCH SKIP TO NEXT ROW
  PATTERN ( current_row {- same_day* -} prev_day? )
  DEFINE
    same_day AS TRUNC(current_row.order_date) = TRUNC(same_day.order_date)
)
ORDER BY episode_id, file_id, order_date, offer_id

对于您的示例数据，输出：
| 发作_ID|文件ID|报价_ID|订单日期|先前订单日期|
| - -----|- -----|- -----|- -----|- -----|
| 一二三四|三千|七百|2019 - 09 -10 00：00：00|联系我们|
| 一二三四|三千|八百|2019 - 09 -10 00：00：00|联系我们|
| 一二三四|三千|九百|2019 - 09 -10 00：00：00|联系我们|
| 一二三四|三千|八百|2020-10-11 00：00：00| 2019 - 09 -10 00：00：00|
| 一二三四|三千|九百|2020-10-11 00：00：00| 2019 - 09 -10 00：00：00|
| 一二三四|四千|七百|2019 - 09 -10 00：00：00|联系我们|
| 一二三四|四千|八百|2019 - 09 -10 00：00：00|联系我们|
| 一二三四|四千|七百|2020-10-11 00：00：00| 2019 - 09 -10 00：00：00|
| 四三二一|三千|七百|2019 - 09 -10 00：00：00|联系我们|
| 四三二一|三千|八百|2019 - 09 -10 00：00：00|联系我们|
| 四三二一|三千|九百|2019 - 09 -10 00：00：00|联系我们|
| 四三二一|三千|八百|2020-10-11 00：00：00| 2019 - 09 -10 00：00：00|
| 四三二一|三千|九百|2020-10-11 00：00：00| 2019 - 09 -10 00：00：00|
| 四三二一|四千|七百|2019 - 09 -10 00：00：00|联系我们|
| 四三二一|四千|八百|2019 - 09 -10 00：00：00|联系我们|
| 四三二一|五千|七百|2019 - 09 -10 00：00：00|联系我们|
| 四三二一|五千|七百|2020-10-11 00：00：00| 2019 - 09 -10 00：00：00|
| 四三二一|四千|七百|2020-10-11 00：00：00| 2019 - 09 -10 00：00：00|
| 四三二一|三千|七百|2020-10-12 00：00：00| 2019 - 09 -10 00：00：00|
fiddle

oracle 使用LAG获取先前的不相同值，同时不考虑某些列

1条答案

相关问题

热门标签

最新问答