卡在问题陈述中，其中包括比较两个表的数据

Question

PFB 问题陈述 . 我尝试使用dense_rank，保持功能，但不知何故我无法破解它。任何人都可以帮忙.这是用于问题表示的小数据集。原始表有数百万行。

数据集脚本：

CREATE TABLE TRANSACTION (  
ITEM VARCHAR2(25 BYTE), 
LOCATION NUMBER(10,0), 
TRAN_DATE DATE, 
POST_DATE DATE 
) 

Insert into TRANSACTION (ITEM,LOCATION,TRAN_DATE,POST_DATE) values ('13252099',473,to_date('09-JUL-18','DD-MON-RR'),to_date('09-JUL-18','DD-MON-RR'));
Insert into TRANSACTION (ITEM,LOCATION,TRAN_DATE,POST_DATE) values ('13252099',473,to_date('25-JUL-18','DD-MON-RR'),to_date('25-JUL-18','DD-MON-RR'));
Insert into TRANSACTION (ITEM,LOCATION,TRAN_DATE,POST_DATE) values ('13252098',470,to_date('09-JUL-18','DD-MON-RR'),to_date('09-JUL-18','DD-MON-RR'));
Insert into TRANSACTION (ITEM,LOCATION,TRAN_DATE,POST_DATE) values ('13252098',470,to_date('28-JUL-18','DD-MON-RR'),to_date('28-JUL-18','DD-MON-RR'));

CREATE TABLE RETAIL_DESC (  
ITEM VARCHAR2(25 BYTE), 
LOC NUMBER(10,0), 
UNIT_RETAIL NUMBER(20,4), 
ACTION_DATE DATE 
)  

Insert into RETAIL_DESC (ITEM,LOC,UNIT_RETAIL,ACTION_DATE) values ('13252099',473,379.97,to_date('09-JUL-18','DD-MON-RR'));
Insert into RETAIL_DESC (ITEM,LOC,UNIT_RETAIL,ACTION_DATE) values ('13252099',473,299.97,to_date('22-JUL-18','DD-MON-RR'));
Insert into RETAIL_DESC (ITEM,LOC,UNIT_RETAIL,ACTION_DATE) values ('13252099',473,0.01,to_date('19-AUG-18','DD-MON-RR'));
Insert into RETAIL_DESC (ITEM,LOC,UNIT_RETAIL,ACTION_DATE) values ('13252099',473,379.97,to_date('25-AUG-18','DD-MON-RR'));
Insert into RETAIL_DESC (ITEM,LOC,UNIT_RETAIL,ACTION_DATE) values ('13252098',470,500.18,to_date('08-JUL-18','DD-MON-RR'));
Insert into RETAIL_DESC (ITEM,LOC,UNIT_RETAIL,ACTION_DATE) values ('13252098',470,299.97,to_date('09-JUL-18','DD-MON-RR'));
Insert into RETAIL_DESC (ITEM,LOC,UNIT_RETAIL,ACTION_DATE) values ('13252098',470,0.01,to_date('19-JUL-18','DD-MON-RR'));
Insert into RETAIL_DESC (ITEM,LOC,UNIT_RETAIL,ACTION_DATE) values ('13252098',470,379.97,to_date('24-JUL-18','DD-MON-RR'));
Insert into RETAIL_DESC (ITEM,LOC,UNIT_RETAIL,ACTION_DATE) values ('13252098',470,300,to_date('24-JUL-18','DD-MON-RR'));

在此处输入图像描述

注意：我正在使用oracle 11g数据库。 1.表Retail_desc包含特定位置的物品unit_retail(价格(。Action_date是该商品的unit_retail更新为该特定位置的新价格的日期。 一个项目/位置组合可以有多个action_date，具体取决于价格更新为新价格的天数。

2. 表事务包含从特定位置向客户销售商品的日期(tran_date((post_date与tran_date相同-可以忽略它(。

我想知道在特定位置出售给客户的物品的unit_retail(价格(。

补充说明： 1. 对于每个项目/位置组合，我想获取商品/位置组合在交易期间的单位零售(tran_date( 2. action_date是新零售对商品/位置unit_retail更改的日期。 3. 如果单位零售在单个action_date上多次更改，则获取价格较低的零售。

Answer 1

你需要一个带有join和row_number的子查询：

select item, loc, unit_retail, tran_date
from (
select
r.item, r.loc, r.unit_retail, t.tran_date
, row_number() over (partition by r.item, r.loc, t.tran_date order by r.action_date desc, r.unit_retail) as rn
from transaction t
inner join retail_desc r on
t.item = r.item
and t.location = r.loc
where t.tran_date >= r.action_date
) t
where rn = 1
order by item desc, loc desc, tran_date

示例数据的输出：

ITEM        LOC     UNIT_RETAIL  TRAN_DATE
13252099    473     379,97       09.07.2018 00:00:00
13252099    473     299,97       25.07.2018 00:00:00
13252098    470     299,97       09.07.2018 00:00:00
13252098    470     300          28.07.2018 00:00:00

Answer 2

我认为首先是与此相关的子查询：

select t.*,
(select min(rd.unit_retail) keep (dense_rank first order by rd.acction_date desc)
from retail_desc rd
where rd.loc = t.location and rd.item = t.item and
rd.action_date <= t.post_date
) as unit_retail
from transaction t;

但是，最好使用lead()和join：

select t.*, rd.unit_retail
from transaction t left join
(select rd.*,
lead(rd.action_date) over (partition by item, loc order by action_date) as next_action_date
from retail_desc rd
) rd
on t.location = rd.loc and t.item = rd.item and
t.post_date >= rd.action_date and
(t.post_date < rd.next_action_date or rd.next_action_date is null);

dense_rank()与这个问题没有明显的联系。