Oracle 指示汇率变化的计数器

嗨，汤姆!

很高兴你回来了。

Oracle: 12.1.0.2

我的挑战如下:

t_res表包含酒店客房预订，其中包括预订id (res_id)，住宿日期 (res_date) 和费率卡 (rate_id)，通常指示客人每晚支付的金额。在许多情况下，同一位客人/预订的费率卡可能会每天更改。我正在尝试使用MATCH_RECOGNIZE为费率卡未更改的每个预订日期计算一个计数器。

-- TABLE: t_res
create table t_res
(
 res_id number(9),
 res_date date,
 rate_id number(9)
)
;

-- Insert reservation data - 3 guest reservations in total
insert into t_res  (res_id, res_date, rate_id) values (1, to_date('01-NOV-2018','DD-MON-YYYY'), 1234);
insert into t_res  (res_id, res_date, rate_id) values (1, to_date('02-NOV-2018','DD-MON-YYYY'), 1234);
insert into t_res  (res_id, res_date, rate_id) values (1, to_date('03-NOV-2018','DD-MON-YYYY'), 5555);
insert into t_res  (res_id, res_date, rate_id) values (1, to_date('04-NOV-2018','DD-MON-YYYY'), 5555);
insert into t_res  (res_id, res_date, rate_id) values (1, to_date('05-NOV-2018','DD-MON-YYYY'), 5555);
insert into t_res  (res_id, res_date, rate_id) values (1, to_date('06-NOV-2018','DD-MON-YYYY'), 1234);
insert into t_res  (res_id, res_date, rate_id) values (2, to_date('01-NOV-2018','DD-MON-YYYY'), 888);
insert into t_res  (res_id, res_date, rate_id) values (2, to_date('02-NOV-2018','DD-MON-YYYY'), 888);
insert into t_res  (res_id, res_date, rate_id) values (2, to_date('03-NOV-2018','DD-MON-YYYY'), 100);
insert into t_res  (res_id, res_date, rate_id) values (2, to_date('04-NOV-2018','DD-MON-YYYY'), 777);
insert into t_res  (res_id, res_date, rate_id) values (2, to_date('05-NOV-2018','DD-MON-YYYY'), 777);
insert into t_res  (res_id, res_date, rate_id) values (2, to_date('06-NOV-2018','DD-MON-YYYY'), 777);
insert into t_res  (res_id, res_date, rate_id) values (3, to_date('06-NOV-2018','DD-MON-YYYY'), 645);
insert into t_res  (res_id, res_date, rate_id) values (3, to_date('07-NOV-2018','DD-MON-YYYY'), 645);
insert into t_res  (res_id, res_date, rate_id) values (3, to_date('08-NOV-2018','DD-MON-YYYY'), 645);
commit;

-- Get data sorted by res_id, then res_date

select * from t_res order by res_id, res_date;

RES_ID RES_DATE RATE_ID
-
1 01-NOV-18 1234
1 03-NOV-18 5555
1 04-NOV-18 5555
1 05-NOV-18 5555
1 06-NOV-18 1234
2 01-NOV-18 888
2 02-NOV-18 888
2 03-NOV-18 100
2 04-NOV-18 777
2 05-NOV-18 777
2 06-NOV-18 777
3 06-NOV-18 645
3 07-NOV-18 645
3 08-NOV-18 645



所需输出:

-按res_id分组
-对于每个预订日期，以当前价格 (night_count) 指示夜数
-当在任何给定日期的预订价格更改时，重置夜间计数器 (night_count)

RES_ID RES_DATE RATE_ID NIGHT_COUNT
- -------------
1 01-NOV-18 12341
1 02-NOV-18 1234 2
1 03-NOV-18 5555 1
1 04-NOV-18 5555 2
1 05-NOV-18 5555 3
1 06-NOV-18 1234 1

2 01-NOV-18 888 1
2 02-NOV-18 888 2
2 03-NOV-18 100 1
2 04-NOV-18 777 1
2 05-NOV-18 777 2
2 06-NOV-18 777 3

3 06-NOV-18 645 1
3 07-NOV-18 645 2
3 08-NOV-18 645 3


我相信我可以使用match_regnize，但不确定如何计算夜间计数器。

一如既往，感谢您的反馈。

您确实可以为此使用match_regnize。我相信你在寻找:

-拆分每个res_id的行 (按res_id分区)
-按日期排序 (按res_date排序)
-检查当前费率 _ id = 以前的费率 _ id
-返回运行行总数 (count(*))

检查当前汇率是否与以前的汇率相同只是:

same as rate_id = prev ( rate_id )

您要查找的模式是一行，后跟具有相同rate_id的任何数字。您需要一个初始 (未定义，始终为true) 的初始行。然后零个或多个 “相同” 的实例:

pattern ( init same* ) 

默认情况下，match_regnize将匹配中的所有行折叠为一个。你想看到所有的输入行。所以你需要添加:

all rows per match

您现在需要做的就是插入分区，顺序和度量子句。瞧:

select * from t_res
match_recognize (
  partition by res_id
  order  by res_date
  measures 
    count(*) c
  all rows per match
  pattern ( init same* ) 
  define 
    same as rate_id = prev ( rate_id )
);

RES_ID   RES_DATE               C   RATE_ID   
       1 01-NOV-2018 00:00:00     1      1234 
       1 02-NOV-2018 00:00:00     2      1234 
       1 03-NOV-2018 00:00:00     1      5555 
       1 04-NOV-2018 00:00:00     2      5555 
       1 05-NOV-2018 00:00:00     3      5555 
       1 06-NOV-2018 00:00:00     1      1234 
       2 01-NOV-2018 00:00:00     1       888 
       2 02-NOV-2018 00:00:00     2       888 
       2 03-NOV-2018 00:00:00     1       100 
       2 04-NOV-2018 00:00:00     1       777 
       2 05-NOV-2018 00:00:00     2       777 
       2 06-NOV-2018 00:00:00     3       777 
       3 06-NOV-2018 00:00:00     1       645 
       3 07-NOV-2018 00:00:00     2       645 
       3 08-NOV-2018 00:00:00     3       645 

如果你想要更多的例子来展示这是如何工作的，请阅读:https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9533477800346658909