代码之家 › 专栏 › 技术社区 › Marko Masnikosa

在不考虑某些列的情况下,使用LAG获取以前不相同的值

oracle19c oracle sql

0

Marko Masnikosa · 技术社区 · 1 年前

这可能是问题的延伸 Using LAG function to obtain value that is not necessarily in the previous row

我有一张桌子 my_table 我想创建一个新列 previous_order_date 基于 episode_id、file_id和order_date。 还有另一列 offer_id 我不想考虑 先前订单日期 计算

插曲id	文件id	offer_id	订单日期
1234	3000	700	20年10月10日
1234	3000	800	20年10月10日
1234	3000	900	20年10月10日
1234	3000	800	20年10月11日
1234	3000	900	20年10月11日
1234	4000	700	20年10月10日
1234	4000	800	20年10月10日
1234	4000	700	20年10月11日
4321	3000	700	20年10月10日
4321	3000	800	20年10月10日
4321	3000	900	20年10月10日
4321	3000	800	20年10月11日
4321	3000	900	20年10月11日
4321	4000	700	20年10月10日
4321	4000	800	20年10月10日
4321	5000	700	20年10月10日
4321	5000	700	20年10月11日
4321	4000	700	20年10月11日
4321	3000	700	20年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,也就是说,如果前一行的order_date与不同的offer_id相同,我不想获得它。

我正试图使输出如下:

插曲id	文件id	offer_id	订单日期	先前订单日期
1234	3000	700	20年10月10日	无效的
1234	3000	800	20年10月10日	无效的
1234	3000	900	20年10月10日	无效的
1234	3000	800	20年10月11日	20年10月10日
1234	3000	900	20年10月11日	20年10月10日
1234	4000	700	20年10月10日	无效的
1234	4000	800	20年10月10日	无效的
1234	4000	700	20年10月11日	20年10月10日
4321	3000	700	20年10月10日	无效的
4321	3000	800	20年10月10日	无效的
4321	3000	900	20年10月10日	无效的
4321	3000	800	20年10月11日	20年10月10日
4321	3000	900	20年10月11日	20年10月10日
4321	4000	700	20年10月10日	无效的
4321	4000	800	20年10月10日	无效的
4321	5000	700	20年10月10日	无效的
4321	5000	700	20年10月11日	20年10月10日
4321	4000	700	20年10月11日	20年10月10日
4321	3000	700	20年10月12日	20年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

但是生成该表

插曲id	文件id	offer_id	订单日期	先前订单日期
1234	3000	700	20年10月10日	无效的
1234	3000	800	20年10月10日	20年10月10日
1234	3000	900	20年10月10日	20年10月10日
1234	3000	800	20年10月11日	20年10月10日
1234	3000	900	20年10月11日	20年10月11日
1234	4000	700	20年10月10日	无效的
1234	4000	800	20年10月10日	20年10月10日
1234	4000	700	20年10月11日	20年10月10日
4321	3000	700	20年10月10日	无效的
4321	3000	800	20年10月10日	20年10月10日
4321	3000	900	20年10月10日	20年10月10日
4321	3000	800	20年10月11日	20年10月10日
4321	3000	900	20年10月11日	20年10月10日
4321	4000	700	20年10月10日	无效的
4321	4000	800	20年10月10日	无效的
4321	5000	700	20年10月10日	无效的
4321	5000	700	20年10月11日	20年10月10日
4321	4000	700	20年10月11日	20年10月10日
4321	3000	700	20年10月12日	20年10月11日

不管offer_id如何,它都会获取前一行的order_date。要达到这一点,这是一个相当大的查询,我需要offer_id列来进行进一步的计算,因为我将基于该列加入其他表。因此,我想知道,由于实际应用程序的order_date一直到分钟,是否有一种方法可以让LAG查看至少早1分钟的前几行,类似于INTERVAL函数。

我尝试在CTE中选择episode_id、file_id和order_date,并在那里使用LAG,但这重复了行,其中一行的order_date为previous_order_date,另一行的previous_order_date为null。当我出于某种原因尝试使用DENSE_RANK时,也发生了同样的情况。

我的数据库版本是19c

1 回复 | 直到 1 年前

1

2

MT0 1 年前

你似乎想 PARTITION BY episode_id, file_id, offer_id 在 LAG :

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	OFFER_ID	订单日期	先前订单日期
1234	3000	700	2020-10-10 00:00:00	无效的
1234	3000	800	2020-10-10 00:00:00	无效的
1234	3000	900	2020-10-10 00:00:00	无效的
1234	3000	800	2020-10-11 00:00:00	2020-10-10 00:00:00
1234	3000	900	2020-10-11 00:00:00	2020-10-10 00:00:00
1234	4000	700	2020-10-10 00:00:00	无效的
1234	4000	800	2020-10-10 00:00:00	无效的
1234	4000	700	2020-10-11 00:00:00	2020-10-10 00:00:00
4321	3000	700	2020-10-10 00:00:00	无效的
4321	3000	800	2020-10-10 00:00:00	无效的
4321	3000	900	2020-10-10 00:00:00	无效的
4321	3000	800	2020-10-11 00:00:00	2020-10-10 00:00:00
4321	3000	900	2020-10-11 00:00:00	2020-10-10 00:00:00
4321	4000	700	2020-10-10 00:00:00	无效的
4321	4000	800	2020-10-10 00:00:00	无效的
4321	5000	700	2020-10-10 00:00:00	无效的
4321	5000	700	2020-10-11 00:00:00	2020-10-10 00:00:00
4321	4000	700	2020-10-11 00:00:00	2020-10-10 00:00:00
4321	3000	700	2020-10-12 00:00:00	2020-10-10 00:00:00

fiddle