再度思索：从配送表中选出订单号和配送者相同时的最新记录

在前文 https://www.cnblogs.com/heyang78/p/12079017.html 中，我主要描述了现象，给出了结论，这次试图对过程进行一点思索。

 

需求：从配送表里，当订单号和配送者一样时，取时间最靠近现在即时间值最大的一条记录。

 

配送表表结构：

create table peisong(
id int,
order_no int, --订单号
shipper_cd int,--配送者号
create_time timestamp,
primary key(id))

填充实验值：

insert into peisong(id,order_no,shipper_cd,create_time) values('0',1,1,to_date('2004-05-07','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('1',1,1,to_date('2004-05-08','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('2',1,2,to_date('2004-05-09','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('3',1,2,to_date('2004-05-10','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('4',1,3,to_date('2004-05-11','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('5',2,1,to_date('2004-05-12','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('6',2,1,to_date('2004-05-13','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('7',2,2,to_date('2004-05-14','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('8',2,2,to_date('2004-05-15','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('9',2,3,to_date('2004-05-16','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('10',2,3,to_date('2004-05-17','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('11',3,1,to_date('2004-05-18','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('12',3,2,to_date('2004-05-19','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('13',3,2,to_date('2004-05-20','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('14',3,3,to_date('2004-05-21','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('15',3,4,to_date('2004-05-22','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('16',3,4,to_date('2004-05-23','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('17',3,4,to_date('2004-05-24','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('18',3,4,to_date('2004-05-25','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('19',3,4,to_date('2004-05-26','yyyy-mm-dd'));
insert into peisong(id,order_no,shipper_cd,create_time) values('20',3,4,to_date('2004-05-27','yyyy-mm-dd'));

有多种方式可以达成需求，以下列举四种：

方案一：先分组再内联方案

select
    a.id,
    a.order_no,
    a.shipper_cd,
    to_char(a.create_time,'yyyy-mm-dd') as create_time
from
    peisong a,
    (
    select
        order_no,
        shipper_cd,
        max(create_time) as create_time
    from
        peisong
    group by
        order_no,
        shipper_cd) b
where
    a.order_no = b.order_no and 
    a.shipper_cd = b.shipper_cd and 
    a.create_time = b.create_time
order by
    a.id,
    a.order_no,
    a.shipper_cd

或：

select a.id, a.order_no, a.shipper_cd, to_char(a.create_time,'yyyy-mm-dd') as create_time from peisong a, ( select order_no, shipper_cd, max(create_time) as create_time from peisong group by order_no, shipper_cd) b where a.order_no = b.order_no and a.shipper_cd = b.shipper_cd and a.create_time = b.create_time order by a.id, a.order_no, a.shipper_cd

筛选结果：

        ID   ORDER_NO SHIPPER_CD CREATE_TIM
---------- ---------- ---------- ----------
         1          1          1 2004-05-08
         3          1          2 2004-05-10
         4          1          3 2004-05-11
         6          2          1 2004-05-13
         8          2          2 2004-05-15
        10          2          3 2004-05-17
        11          3          1 2004-05-18
        13          3          2 2004-05-20
        14          3          3 2004-05-21
        20          3          4 2004-05-27

已选择10行。

 如果我们把order_no，shipper_cd相同的记录划分成一组，那么由b表的形成条件可知，无论组内元素有多少条，最终只会选出create_time最大（即最近）的那条，两表内联查询便会在21*10=210的结果集选，因此这种查询方式适合组内数量稍多的情况，比较均衡。

 

方案二：左连接方案

SELECT
    a.id,
    a.order_no,
    a.shipper_cd,
    to_char(a.create_time,'yyyy-mm-dd') as create_time
from
    peisong a left JOIN 
    peisong b
on
    a.order_no = b.order_no and 
    a.shipper_cd = b.shipper_cd and 
    a.create_time < b.create_time
where
    b.create_time  IS  NULL
order by
    a.id,
    a.order_no,
    a.shipper_cd

或：

SELECT a.id, a.order_no, a.shipper_cd, to_char(a.create_time,'yyyy-mm-dd') as create_time from peisong a left JOIN peisong b on a.order_no = b.order_no and a.shipper_cd = b.shipper_cd and a.create_time < b.create_time where b.create_time IS NULL order by a.id, a.order_no, a.shipper_cd

结果：

        ID   ORDER_NO SHIPPER_CD CREATE_TIM
---------- ---------- ---------- ----------
         1          1          1 2004-05-08
         3          1          2 2004-05-10
         4          1          3 2004-05-11
         6          2          1 2004-05-13
         8          2          2 2004-05-15
        10          2          3 2004-05-17
        11          3          1 2004-05-18
        13          3          2 2004-05-20
        14          3          3 2004-05-21
        20          3          4 2004-05-27

已选择10行。

 

这个左联SQL的代码稍微有点费解，我在这里赘述两句。

首先可以看看peisong表左联自己，条件是左表时间小于右表的情况。

SQL：

SELECT
    a.id,
    to_char(a.create_time,'yyyy-mm-dd') as actime,
    to_char(b.create_time,'yyyy-mm-dd') as bctime
from
    peisong a left JOIN 
    peisong b
on
    a.order_no = b.order_no and 
    a.shipper_cd = b.shipper_cd and 
    a.create_time < b.create_time
order by
    a.id,
    a.order_no,
    a.shipper_cd

查询结果：

SQL> SELECT
  2      a.id,
  3      to_char(a.create_time,'yyyy-mm-dd') as actime,
  4      to_char(b.create_time,'yyyy-mm-dd') as bctime
  5  from
  6      peisong a left JOIN
  7      peisong b
  8  on
  9      a.order_no = b.order_no and
 10      a.shipper_cd = b.shipper_cd and
 11      a.create_time < b.create_time
 12  order by
 13      a.id,
 14      a.order_no,
 15      a.shipper_cd;

        ID ACTIME     BCTIME
---------- ---------- ----------
         0 2004-05-07 2004-05-08
         1 2004-05-08
         2 2004-05-09 2004-05-10
         3 2004-05-10
         4 2004-05-11
         5 2004-05-12 2004-05-13
         6 2004-05-13
         7 2004-05-14 2004-05-15
         8 2004-05-15
         9 2004-05-16 2004-05-17
        10 2004-05-17
        11 2004-05-18
        12 2004-05-19 2004-05-20
        13 2004-05-20
        14 2004-05-21
        15 2004-05-22 2004-05-23
        15 2004-05-22 2004-05-26
        15 2004-05-22 2004-05-25
        15 2004-05-22 2004-05-27
        15 2004-05-22 2004-05-24
        16 2004-05-23 2004-05-24
        16 2004-05-23 2004-05-27
        16 2004-05-23 2004-05-26
        16 2004-05-23 2004-05-25
        17 2004-05-24 2004-05-27
        17 2004-05-24 2004-05-26
        17 2004-05-24 2004-05-25
        18 2004-05-25 2004-05-27
        18 2004-05-25 2004-05-26
        19 2004-05-26 2004-05-27
        20 2004-05-27

已选择31行。

由上面的查询结果可以看出来，当左表的create_time字段已经是最大时，右表里已经找不出order_no，shipper_cd相同，而create_time字段更大的记录，于是按连接规则补NULL，这个NULL在上面的查询中就是以空档显示的。

通过观察我们就能知道，左表acreate_time字段有数据，右表bcreate_time字段是空的情况，这就是我们想要的记录，于是筛选条件where b.create_time  IS  NULL就出现了。

让我们会看ab两表连接条件“on a.order_no = b.order_no and a.shipper_cd = b.shipper_cd and a.create_time < b.create_time”，如果我们把order_no，shipper_cd相同的记录划到一组，会发现组越大，产生的连接结果就越多，最典型是15到20这一组（共5条记录），通过左联接产生了16条记录（5+4+3+2+1+1），如果有n条，那么就是n*(n+1)/2+1条，也就说说同组数量越大，产生记录会和数量平方成正比，这就是在原先实验中组小基本是一两条时左联接最快，组数量越大越慢的根本原因。综合分析可以得出结论，左联方案只是完成需求，对优化没有多考虑。

 

方案三：not exists方案

select
    a.id,
    a.order_no,
    a.shipper_cd,
    to_char(a.create_time,'yyyy-mm-dd') as create_time
from
    peisong a
where
      not exists  (
    select
        1
    from
        peisong b
    where
        b.shipper_cd = a.shipper_cd and 
        b.order_no = a.order_no and 
        b.create_time > a.create_time)
order by
    a.id,
    a.order_no,
    a.shipper_cd

或:

select a.id, a.order_no, a.shipper_cd, to_char(a.create_time,'yyyy-mm-dd') as create_time from peisong a where not exists ( select 1 from peisong b where b.shipper_cd = a.shipper_cd and b.order_no = a.order_no and b.create_time > a.create_time) order by a.id, a.order_no, a.shipper_cd

结果：

        ID   ORDER_NO SHIPPER_CD CREATE_TIM
---------- ---------- ---------- ----------
         1          1          1 2004-05-08
         3          1          2 2004-05-10
         4          1          3 2004-05-11
         6          2          1 2004-05-13
         8          2          2 2004-05-15
        10          2          3 2004-05-17
        11          3          1 2004-05-18
        13          3          2 2004-05-20
        14          3          3 2004-05-21
        20          3          4 2004-05-27

Not exists方式会形成n*n的结构，但右边的n只做条件不筛选，然后对左边的n过滤， 最适合按 order_no,shipper_cd分组，而组内元素较多>10的情况，和左联正好相反。

 

方案四：最好理解又高效的rank分析函数方案

Oracle提供了分析函数rank，它可以按order_no,shipper_cd分组，按create_time逆序排序，然后将组内顺序赋上顺序值。

像这样

select id，rank() over (partition by order_no,shipper_cd order by create_time desc) as seq from peisong

之后我们只要取出seq=1的记录就达成需求了。

最终SQL：

select
id,
order_no,
shipper_cd,
to_char(create_time,'yyyy-mm-dd') as create_time
from peisong where id in (select a.id from (select id，rank() over (partition by order_no,shipper_cd order by create_time desc) as seq from peisong) a where a.seq=1)
order by id

执行结果：

SQL> select
  2      id,
  3      order_no,
  4      shipper_cd,
  5      to_char(create_time,'yyyy-mm-dd') as create_time
  6  from peisong where id in (select a.id from (select id，rank() over (partition by order_no,shipper_cd order by create_time desc) as seq from peisong) a where a.seq=1)
  7  order by id;

        ID   ORDER_NO SHIPPER_CD CREATE_TIM
---------- ---------- ---------- ----------
         1          1          1 2004-05-08
         3          1          2 2004-05-10
         4          1          3 2004-05-11
         6          2          1 2004-05-13
         8          2          2 2004-05-15
        10          2          3 2004-05-17
        11          3          1 2004-05-18
        13          3          2 2004-05-20
        14          3          3 2004-05-21
        20          3          4 2004-05-27

已选择10行。

这种方案由于rank的使用，第二个子查询就抛下了大多数无用数据行，因此效率也不错，还好理解，值得推荐。

 

可以看出，以上四种SQL文都能达成需求，具体选择哪种方案，需要根据实际数据分布进行实验，再选最合适的一种。

这次的分析应该比上次：https://www.cnblogs.com/heyang78/p/12079017.html 要更深入一些。

END