Oracle 按值合并订单行的分析/模型

如果客户接受发运的最小金额值，则需要编写一个查询，以合并客户的订单行，从而显示其订单行累计最小发运值的日期。如果客户的订单行不符合最小金额，则将日期显示为行的最后发运日期后30天。以下是使用示例数据对手动业务流程的说明。


例如，在LiveSQL上的示例数据中， Cust A有3个订单，共5行，其最小发货额为350。他们的总订单价值是5360。客户B有3个订单， 5行，其最小发货额为750。他们的总订单价值是1875年。

客户A在人工审核时，应合并以下订单：

对于首次装运：
订单1、行1、行2 ，总值350 ，发货日期2017-02-11

第二次装运：
订单1行3 ，订单2行1 ，总值1010 ，发货日期2017-02-21

第三次装运：
订单3行1 ，总值4000 ，发货日期2017-03-31

客户A没有未合并的行。



客户B在人工审核时，应合并以下订单：

对于首次装运：
订单4行1和2 ，订单5行1 ，总值775 ，发货日期2017-02-11

第二次装运：
订单5行2 ，总值1000 ，发货日期2017-02-21

客户B有一个未合并行，订单6行1 ，值100 ，发运日期2017-04-30。

这是一个匹配识别的案例！

您需要的是任意数量的订单，直到总订单超过客户的最低值，对吗？

因此，小于最小值的零或更多，然后最多超过一个。在正则表达式中，它是：

  under_min* over_min{0,1}

请注意， over_min需要零个或一个匹配项，以便在结尾处得到未合并的行。

那么，你如何定义这些变量呢？

好的under_min是小于min的行之和， over_min是大于此值的行之和！

  define 
    under_min as sum(line_amt) < min_ship_value,
    over_min as sum(line_amt) >= min_ship_value

在这种情况下，求和就像分析一样，给你计算总数。

您希望按客户拆分行，并按发运日期排序。因此，把它们分别放在分词和分词中。

剩下的就是找出你的专栏。

若要获取组，请使用匹配号（）。这将为同一组中的所有行指定一个数字。它为每个新组递增，并为每个新客户重置为1。

要获取组中最后一次发运的日期，您需要over_min的“最终最后一次”发运日期。这会给出此变量最后匹配行的发运日期。对于未合并的订单，没有超过最小发运日期。所以这是空的。因此，这与最后发货日期+30给您期望的结果！

把这些放在一起，你有：

alter session set nls_date_format = 'DD-MON-YYYY';
create table cust (
  cust_id number not null primary key, cust_name varchar2(30) not null,
  min_ship_value number not null
);

create table cust_orders (
  cust_id number not null, order_no number not null, order_line number not null, 
  line_amt number not null, ship_date date not null, 
  constraint fk_orders_cust_id foreign key (cust_id) references cust,
  constraint pk_cust_orders primary key (order_no, order_line)
);

insert into cust values (1,'Cust A',350);
insert into cust values (2,'Cust B',750);

insert into cust_orders values (1,1,1,100,date'2017-01-31');
insert into cust_orders values (1,1,2,250,date'2017-02-11');
insert into cust_orders values (1,1,3,10,date'2017-02-10');
insert into cust_orders values (1,2,1,1000,date'2017-02-21');
insert into cust_orders values (1,3,1,4000,date'2017-03-31');
insert into cust_orders values (2,4,1,175,date'2017-01-31');
insert into cust_orders values (2,4,2,500,date'2017-02-11');
insert into cust_orders values (2,5,1,100,date'2017-02-10');
insert into cust_orders values (2,5,2,1000,date'2017-02-21');
insert into cust_orders values (2,6,1,100,date'2017-03-31');

commit;

select * from (
  select co.*, c.min_ship_value from cust c
  join   cust_orders co
  on     c.cust_id = co.cust_id
)
match_recognize (
  partition by cust_id
  order  by ship_date, order_line
  measures 
    match_number() as mno,
    classifier() as cls,
    sum(line_amt) as tot,
  nvl(final last(over_min.ship_date), last(ship_date)+30) as last_ship_date
  all rows per match
  pattern (under_min* over_min{0,1})
  define 
    under_min as sum(line_amt) < min_ship_value,
    over_min as sum(line_amt) >= min_ship_value
);

CUST_ID  SHIP_DATE    ORDER_LINE  MNO  CLS        TOT    LAST_SHIP_DATE  ORDER_NO  LINE_AMT  MIN_SHIP_VALUE  
1        31-JAN-2017  1           1    UNDER_MIN  100    11-FEB-2017     1         100       350             
1        10-FEB-2017  3           1    UNDER_MIN  110    11-FEB-2017     1         10        350             
1        11-FEB-2017  2           1    OVER_MIN   360    11-FEB-2017     1         250       350             
1        21-FEB-2017  1           2    OVER_MIN   1,000  21-FEB-2017     2         1,000     350             
1        31-MAR-2017  1           3    OVER_MIN   4,000  31-MAR-2017     3         4,000     350             
2        31-JAN-2017  1           1    UNDER_MIN  175    11-FEB-2017     4         175       750             
2        10-FEB-2017  1           1    UNDER_MIN  275    11-FEB-2017     5         100       750             
2        11-FEB-2017  2           1    OVER_MIN   775    11-FEB-2017     4         500       750             
2        21-FEB-2017  2           2    OVER_MIN   1,000  21-FEB-2017     5         1,000     750             
2        31-MAR-2017  1           3    UNDER_MIN  100    30-APR-2017     6         100       750             

如果你想了解更多关于模式匹配的知识，我推荐你阅读基思·湖人的深潜系列。您可以在以下位置找到第一篇文章：

http://oracle-big-data.blogspot.co.uk/2016/03/sql-pattern-matching-deep-dive-part-1.html

PS -感谢您提供清晰的测试用例。虽然我不明白为什么你要整合客户1的订单行1和2 ，在2之前三个发货，所以你肯定会整合这三个订单行吗？

在任何情况下，若要更改这些组合的方式，请通过适当地调整顺序。