SQL进阶——用SQL处理数列

运行环境 win10 / oracle(DB11g)

1、将数值看作字符串的组合

用0-9数值做出0-99的数

-- 创表
create table digit_9(
    digit integer
);
insert into digit_9 values(1);
insert into digit_9 values(2);
insert into digit_9 values(3);
insert into digit_9 values(4);
insert into digit_9 values(5);
insert into digit_9 values(6);
insert into digit_9 values(7);
insert into digit_9 values(8);
insert into digit_9 values(9);
insert into digit_9 values(0);

SQL解决：

select a.digit + (b.digit*10) seq
  from digit_9 a cross join digit_9 B -- 笛卡尔积
  order by seq
;

2、购票选取连座问题

思路

1 - 选定终点与起点
2 - 排除区间中有被预订和不同排的

创表

create table seats_2_9(
    seat smallint,
    row_id varchar(4),
    status1 varchar(6)
)
;
insert into seats_2_9 values(1,'A', '已预订');
insert into seats_2_9 values(2,'A', '已预订');
insert into seats_2_9 values(3,'A', '未预订');
insert into seats_2_9 values(4,'A', '未预订');
insert into seats_2_9 values(5,'A', '未预订');
insert into seats_2_9 values(6,'B', '已预订');
insert into seats_2_9 values(7,'B', '未预订');
insert into seats_2_9 values(8,'B', '未预订');
insert into seats_2_9 values(9,'B', '未预订');
insert into seats_2_9 values(10,'B', '未预订');
insert into seats_2_9 values(11,'C', '未预订');
insert into seats_2_9 values(12,'C', '已预订');
insert into seats_2_9 values(13,'C', '已预订');
insert into seats_2_9 values(14,'C', '未预订');
insert into seats_2_9 values(15,'C', '未预订');

全称量化解决

select S1.ROW_ID, s1.seat start_seat, '~', s2.seat end_seat
  from seats_2_9 s1, seats_2_9 s2
 where s2.seat = s1.seat + 2 -- 决定起点和终点
   and not EXISTS(  -- 双重否定
       select * from seats_2_9 s3
        where s3.seat between s1.seat and s2.seat 
          and (s3.status1 <> '未预订' OR S3.ROW_ID <> S1.ROW_ID)
   )
;

结果

ROW_ START_SEAT '   END_SEAT
---- ---------- - ----------
A             3 ~          5
B             7 ~          9
B             8 ~         10

3、购票选取最多几连座问题

思路
第一阶段：

1 - 选定终点与起点
2 - 排除区间中有被预订和不同排的
3 - 排除区间外有第一个未预订

第二阶段

4 - 选取满足上述情况，最长的序列

创表

create table seats_3_9
(
    seat smallint,
    status1 varchar(6)
)
;
insert into seats_3_9 values(1, '已预订');
insert into seats_3_9 values(2, '未预订');
insert into seats_3_9 values(3, '未预订');
insert into seats_3_9 values(4, '未预订');
insert into seats_3_9 values(5, '未预订');
insert into seats_3_9 values(6, '已预订');
insert into seats_3_9 values(7, '未预订');
insert into seats_3_9 values(8, '已预订');
insert into seats_3_9 values(9, '未预订');
insert into seats_3_9 values(10, '未预订');

第一阶段：生成储存了所有序列的视图

create view SEQUENCE2 (start_seat, end_seat, seat_cnt) AS
select s1.seat start_seat,
       s2.seat end_seat,
       s2.seat - s1.seat + 1 seat_cnt
  from seats_3_9 s1, seats_3_9 s2
 where s1.seat <= s2.seat -- 第一步：生成起点和终点的组合
   and not exists         -- 第二步：描述序列内所有点需要满足的条件
       (select * from seats_3_9 s3
         where (s3.seat between s1.seat and s2.seat 
               and s3.status1 <> '未预订')
            or (s3.seat = s2.seat+1 and s3.status1 = '未预订')
            or (s3.seat = s1.seat-1 and s3.status1 = '未预订')
       )
;

求最长的序列

select start_seat, '~', end_seat
  from sequence2
 where seat_cnt = (select max(seat_cnt) from sequence2)
;

结果如下：

START_SEAT '   END_SEAT
---------- - ----------
         2 ~          5

4、单调递增（股票价格一直上涨区间）

思路：

1 - 选定终点与起点
2 - 排除时间区间非递增的集合

创表

oracle 中插入时间格式

create table mystock(
    deal_date date,
    price INTEGER
)
;
-- oracle
insert into mystock values(to_date('2007-01-06','yyyy-mm-dd'), 1000);
insert into mystock values(to_date('2007-01-08','yyyy-mm-dd'), 1050);
insert into mystock values(to_date('2007-01-09','yyyy-mm-dd'), 900);
insert into mystock values(to_date('2007-01-12','yyyy-mm-dd'), 880);
insert into mystock values(to_date('2007-01-14','yyyy-mm-dd'), 870);
insert into mystock values(to_date('2007-01-16','yyyy-mm-dd'), 920);
insert into mystock values(to_date('2007-01-17','yyyy-mm-dd'), 1000);

求单调递增区间

select s1.deal_date start_date1,
       s2.deal_date end_date1
  from mystock s1, mystock s2
 where s1.deal_date < s2.deal_date --1、生成起点和终点
   and not exists
      (select *                    --2、描述区间内所有日期需要排除的条件
         from mystock s3, mystock s4
        where s3.deal_date between s1.deal_date and s2.deal_date
          and s4.deal_date between s1.deal_date and s2.deal_date
          and s3.deal_date < s4.deal_date
          and s3.price >= s4.price
     )
;

结果如下：
发现有重复的起始日期， 需要排除掉在递增区间的 小子集

START_DATE1    END_DATE1
-------------- --------------
06-1月 -07     08-1月 -07
14-1月 -07     16-1月 -07
14-1月 -07     17-1月 -07
16-1月 -07     17-1月 -07

排除掉在递增区间的 小子集

select min(start_date1) as start_date1,  -- 最大限度的向前延伸起点
       end_date1
  from (
      select s1.deal_date start_date1,
             max(s2.deal_date) end_date1  -- 最大限度的向后延伸终点
        from mystock s1, mystock s2
       where s1.deal_date < s2.deal_date --1、生成起点和终点
         and not exists
            (select *                    --2、描述区间内所有日期需要排除的条件
                from mystock s3, mystock s4
                where s3.deal_date between s1.deal_date and s2.deal_date
                and s4.deal_date between s1.deal_date and s2.deal_date
                and s3.deal_date < s4.deal_date
                and s3.price >= s4.price
            )
       group by s1.deal_date 
  ) tmp
 group by end_date1

结果如下：

START_DATE1    END_DATE1
-------------- --------------
14-1月 -07     17-1月 -07
06-1月 -07     08-1月 -07

---

练习

1-9-2:求序列——面向集合的思想

-- 找出需要的空位（1）：考虑座位的换排 --having
select min(ROW_ID),start_seat, '~', max(end_seat)
  from(-- 列出所有三连中有未预订的情况
        select S1.ROW_ID, s1.seat start_seat, '~', s2.seat end_seat
        from seats_2_9 s1, seats_2_9 s2, seats_2_9 s3
        where s2.seat = s1.seat + 2   
        and s3.seat between s1.seat and s2.seat
        and s3.status1 = '未预订' and S3.ROW_ID = S1.ROW_ID
    ) tmp
 group by start_seat
having count(*) = 3  -- 取三连中均为未预订的
 order by min(ROW_ID)
;

结果如下：

MIN( START_SEAT ' MAX(END_SEAT)
---- ---------- - -------------
A             3 ~             5
B             7 ~             9
B             8 ~            10

内容多来自 《SQL进阶教材》，仅做笔记。练习代码均为原创。