-- 欠番を求める -- 外部結合/中間テーブル
SELECT Sequence.seq AS seq_dense,
       SeqTbl.seq   AS seq_sparse
  FROM Sequence
  LEFT OUTER JOIN SeqTbl ON seq_dense = seq_sparse
 WHERE seq_dense BETWEEN (SELECT MIN(SeqTbl.seq) FROM SeqTbl) 
                     AND (SELECT MAX(SeqTbl.seq) FROM SeqTbl);

-- 欠番を求める -- 外部結合/中間テーブル
SELECT Sequence.seq AS seq_dense,
       SeqTbl.seq AS seq_sparse
  FROM Sequence
  LEFT OUTER JOIN SeqTbl ON seq_dense = seq_sparse
 WHERE seq_dense BETWEEN (SELECT MIN(SeqTbl.seq) FROM SeqTbl) 
                     AND (SELECT MAX(SeqTbl.seq) FROM SeqTbl);

-- 欠番を求める -- 外部結合
SELECT Sequence.seq AS seq
  FROM Sequence
  LEFT OUTER JOIN SeqTbl ON Sequence.seq = SeqTbl.seq
 WHERE Sequence.seq BETWEEN (SELECT MIN(SeqTbl.seq) FROM SeqTbl) 
                        AND (SELECT MAX(SeqTbl.seq) FROM SeqTbl)
   AND SeqTbl.seq IS NULL;

-- 人数分の空席を探す:補1 ウィンドウ関数
SELECT seat,
       '～',
       seat + seat_count - 1 AS end_seat
  FROM (SELECT seat,
               MIN(status) OVER W AS lower_status,
               MAX(status) OVER W AS upper_status,
               -- 3シート集計してくれる保証はない
               -- 14, 15 で終わりとか
               COUNT(seat) OVER W AS seat_count
          FROM Seats
        -- 3シートずつ集計する
        WINDOW W AS (ORDER BY seat
                         ROWS BETWEEN CURRENT ROW
                                  AND (3 - 1) FOLLOWING))

 WHERE seat_count = 3      -- ちゃんと3シート集計してくれていて
   AND upper_status = '空' -- ウィンドウ関数の集計範囲のシートが全部同status
   AND lower_status = '空' -- かつ、そのstatusというのが空
;

-- 人数分の空席を探す:補2 ウィンドウ関数 + 特性関数
SELECT seat,
       '～',
       seat + seat_available_count - 1 AS end_seat
  FROM (SELECT seat,
               -- 空シートを1、それ以外をNULLとする特性関数を通して総和
               -- 集計対象のシートが全部空なら、集計件数になる
               SUM(CASE WHEN status = '空' THEN 1
                        ELSE NULL
                        END) OVER W AS seat_available_count
          FROM Seats
        -- 3シートずつ集計する
        WINDOW W AS (ORDER BY seat
                         ROWS BETWEEN CURRENT ROW
                                  AND (3 - 1) FOLLOWING))

 WHERE seat_available_count = 3  -- 集計範囲で3シート空いていること
 ;

-- 自己結合でシーケンスをグループ化
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 MIN(deal_date) AS start_date,
       '～',
       MAX(deal_date) AS end_date
  FROM (SELECT deal_date,
               ROW_NUMBER() OVER (ORDER BY row_num) - row_num AS gap
          FROM MyStockUpSeq) 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;

-- ウィンドウ関数でもいい
SELECT deal_date,
       ROW_NUMBER() OVER (ORDER BY row_num) AS cnt,
       row_num,
       ROW_NUMBER() OVER (ORDER BY row_num) - row_num AS gap
  FROM MyStockUpSeq;