-- 生成连续编号
CREATE TABLE Digits
 (digit INTEGER PRIMARY KEY); 

INSERT INTO Digits VALUES (0);
INSERT INTO Digits VALUES (1);
INSERT INTO Digits VALUES (2);
INSERT INTO Digits VALUES (3);
INSERT INTO Digits VALUES (4);
INSERT INTO Digits VALUES (5);
INSERT INTO Digits VALUES (6);
INSERT INTO Digits VALUES (7);
INSERT INTO Digits VALUES (8);
INSERT INTO Digits VALUES (9);

-- 求连续编号(1)：求0~99的数
SELECT D1.digit + (D2.digit * 10) AS seq
  FROM Digits D1 CROSS JOIN Digits D2
 ORDER BY seq;


-- 求连续编号(2)：求1~542的数
SELECT D1.digit + (D2.digit * 10) + (D3.digit * 100) AS seq
  FROM Digits D1 CROSS JOIN Digits D2
         CROSS JOIN Digits D3
 WHERE D1.digit + (D2.digit * 10) + (D3.digit * 100)
         BETWEEN 1 AND 542 ORDER BY seq;

-- 求全部的缺失编号
CREATE TABLE SeqTbl
 (seq INTEGER PRIMARY KEY); 

INSERT INTO SeqTbl VALUES (1);
INSERT INTO SeqTbl VALUES (2);
INSERT INTO SeqTbl VALUES (4);
INSERT INTO SeqTbl VALUES (5);
INSERT INTO SeqTbl VALUES (6);
INSERT INTO SeqTbl VALUES (7);
INSERT INTO SeqTbl VALUES (8);
INSERT INTO SeqTbl VALUES (11);
INSERT INTO SeqTbl VALUES (12);

-- 生成序列视图（包含0~999）
CREATE VIEW Sequence (seq) AS
SELECT D1.digit + (D2.digit * 10) + (D3.digit * 100)
  FROM Digits D1
         CROSS JOIN Digits D2
           CROSS JOIN Digits D3;

-- 从序列视图中获取1~100
SELECT seq
  FROM Sequence
 WHERE seq BETWEEN 1 AND 100
 ORDER BY seq;


-- EXCEPT版
SELECT seq
  FROM Sequence
 WHERE seq BETWEEN 1 AND 12
EXCEPT
SELECT seq
  FROM SeqTbl;

-- NOT IN版
SELECT seq
  FROM Sequence
 WHERE seq BETWEEN 1 AND 12
   AND seq NOT IN (SELECT seq FROM SeqTbl);

-- 动态地指定连续编号范围的SQL语句
SELECT seq
  FROM Sequence
 WHERE seq BETWEEN (SELECT MIN(seq) FROM SeqTbl)
   AND (SELECT MAX(seq) FROM SeqTbl)
EXCEPT SELECT seq FROM SeqTbl;

-- 3个人能坐得下吗
CREATE TABLE Seats
(seat   INTEGER NOT NULL  PRIMARY KEY,
 status CHAR(3) NOT NULL
 CHECK (status IN ('未预定', '已预订')) ); 

INSERT INTO Seats VALUES (1,  '已预订');
INSERT INTO Seats VALUES (2,  '已预订');
INSERT INTO Seats VALUES (3,  '未预定');
INSERT INTO Seats VALUES (4,  '未预定');
INSERT INTO Seats VALUES (5,  '未预定');
INSERT INTO Seats VALUES (6,  '已预订');
INSERT INTO Seats VALUES (7,  '未预定');
INSERT INTO Seats VALUES (8,  '未预定');
INSERT INTO Seats VALUES (9,  '未预定');
INSERT INTO Seats VALUES (10, '未预定');
INSERT INTO Seats VALUES (11, '未预定');
INSERT INTO Seats VALUES (12, '已预订');
INSERT INTO Seats VALUES (13, '已预订');
INSERT INTO Seats VALUES (14, '未预定');
INSERT INTO Seats VALUES (15, '未预定');


-- 找出需要的空位(1)：不考虑座位的换排
SELECT S1.seat AS start_seat, '~' , S2.seat AS end_seat
  FROM Seats S1, Seats S2
 WHERE S2.seat = S1.seat + (:head_cnt -1) -- 决定起点和终点
   AND NOT EXISTS
        (SELECT *
           FROM Seats S3
          WHERE S3.seat BETWEEN S1.seat AND S2.seat
            AND S3.status <> '未预定' );


-- 考虑座位的换排
CREATE TABLE Seats2
 ( seat   INTEGER NOT NULL  PRIMARY KEY,
   line_id CHAR(1) NOT NULL,
   status CHAR(3) NOT NULL
     CHECK (status IN ('未预定', '已预订')) ); 

INSERT INTO Seats2 VALUES (1, 'A', '已预订');
INSERT INTO Seats2 VALUES (2, 'A', '已预订');
INSERT INTO Seats2 VALUES (3, 'A', '未预定');
INSERT INTO Seats2 VALUES (4, 'A', '未预定');
INSERT INTO Seats2 VALUES (5, 'A', '未预定');
INSERT INTO Seats2 VALUES (6, 'B', '已预订');
INSERT INTO Seats2 VALUES (7, 'B', '已预订');
INSERT INTO Seats2 VALUES (8, 'B', '未预定');
INSERT INTO Seats2 VALUES (9, 'B', '未预定');
INSERT INTO Seats2 VALUES (10,'B', '未预定');
INSERT INTO Seats2 VALUES (11,'C', '未预定');
INSERT INTO Seats2 VALUES (12,'C', '未预定');
INSERT INTO Seats2 VALUES (13,'C', '未预定');
INSERT INTO Seats2 VALUES (14,'C', '已预订');
INSERT INTO Seats2 VALUES (15,'C', '未预定');


-- 找出需要的空位（2）：窗口函数
SELECT seat, '~', seat + (:head_cnt -1)
  FROM (SELECT seat,
               MAX(seat)
                 OVER(ORDER BY seat
                       ROWS BETWEEN (:head_cnt -1) FOLLOWING
                                AND (:head_cnt -1) FOLLOWING ) AS end_seat
          FROM Seats
         WHERE status = '未预定') TMP
 WHERE end_seat - seat = (:head_cnt -1);


-- 找出需要的空位：考虑座位的换排 NOT EXISTS
SELECT S1.seat AS start_seat, '~' , S2.seat AS end_seat
  FROM Seats2 S1, Seats2 S2
 WHERE S2.seat = S1.seat + (:head_cnt -1) -- 决定起点和终点
   AND NOT EXISTS
        (SELECT *
           FROM Seats2 S3
          WHERE S3.seat BETWEEN S1.seat AND S2.seat
            AND ( S3.status <> '未预定' OR S3.line_id <> S1.line_id));

-- 找出需要的空位：考虑座位的换排 窗口函数
SELECT seat, '~', seat + (:head_cnt - 1)
  FROM (SELECT seat,
               MAX(seat)
                 OVER(PARTITION BY line_id
                          ORDER BY seat
                           ROWS BETWEEN (:head_cnt - 1) FOLLOWING
                                    AND (:head_cnt - 1) FOLLOWING ) AS end_seat
          FROM Seats2
         WHERE status = '未预定') TMP
  WHERE end_seat - seat = (:head_cnt - 1);



-- 单调递增和单调递减
CREATE TABLE MyStock
 (deal_date  DATE PRIMARY KEY,
  price      INTEGER ); 

INSERT INTO MyStock VALUES ('2018-01-06', 1000);
INSERT INTO MyStock VALUES ('2018-01-08', 1050);
INSERT INTO MyStock VALUES ('2018-01-09', 1050);
INSERT INTO MyStock VALUES ('2018-01-12', 900);
INSERT INTO MyStock VALUES ('2018-01-13', 880);
INSERT INTO MyStock VALUES ('2018-01-14', 870);
INSERT INTO MyStock VALUES ('2018-01-16', 920);
INSERT INTO MyStock VALUES ('2018-01-17', 1000);
INSERT INTO MyStock VALUES ('2018-01-18', 2000);

-- 判断股价是否高于上次交易
SELECT deal_date, price,
       CASE SIGN(price - MAX(price)
                           OVER(ORDER BY deal_date
                                 ROWS BETWEEN 1 PRECEDING
                                          AND 1 PRECEDING))
       WHEN 1 THEN 'up'
       WHEN 0 THEN 'stay'
       WHEN -1 THEN 'down' ELSE NULL END AS diff
  FROM MyStock;


CREATE VIEW MyStockUpSeq(deal_date, price, row_num)
AS
SELECT deal_date, price, row_num
  FROM (SELECT deal_date, price,
               CASE SIGN(price - MAX(price)
                                   OVER(ORDER BY deal_date
                                         ROWS BETWEEN 1 PRECEDING
                                                  AND 1 PRECEDING))
               WHEN 1 THEN 'up'
               WHEN 0 THEN 'stay'
               WHEN -1 THEN 'down' ELSE NULL END AS diff,
               ROW_NUMBER() OVER(ORDER BY deal_date) AS row_num
          FROM MyStock) TMP
  WHERE diff = 'up';


-- 使用自连接对序列进行分组
SELECT MIN(deal_date) AS start_date,
       '~',
       MAX(deal_date) AS end_date
  FROM (SELECT M1.deal_date,
               COUNT(M2.row_num) - MIN(M1.row_num) AS gap
          FROM MyStockUpSeq M1 INNER JOIN MyStockUpSeq M2
            ON M2.row_num <= M1.row_num
         GROUP BY M1.deal_date) TMP
 GROUP BY gap;


SELECT M1.deal_date,
       COUNT(M2.row_num) cnt,
       MIN(M1.row_num) min_row_num,
       COUNT(M2.row_num) - MIN(M1.row_num) AS gap
  FROM MyStockUpSeq M1 INNER JOIN MyStockUpSeq M2
    ON M2.row_num <= M1.row_num
 GROUP BY M1.deal_date;