SQL笔记| 用SQL处理数列

本节主题之一就是如何用SQL处理有序数据。SQL和RDB在处理数据时默认不考虑顺序，因此，如遇到有序数据，传统的处理方式就会变得很棘手。具体来说就是SQL使用了谓词逻辑中的量词和定义有序数的递归集合。不过，自20世纪90年代末SQL引入窗口函数后，SQL处理有序数据就变得非常简单了。

生成连续编号

目前，很多数据库的实现包括序列对象（sequence object），如果要按照顺序一个一个地获取连续编号，可以使用这个方法。

-- 求连续编号（1）：求0~99的数
SELECT D1.digit + (D2.digit * 10) AS seg 
 FROM Digits D1 CROSS JOIN Digits D2
 ORDER BY seg;
-- 求连续编号（2）：求1~542的数
SELECT D1.digit + (D2.digit * 10) + (D3.digit * 100) AS seg 
 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 seg;
-- 生成序列视图（包含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；

这种生成连续编号的方法，完全忽略了数的“顺序”属性。完全丢掉了“顺序”这一概念，仅把数看成是数字的组合。这种解法更能体现出SQL语言的特色。

求全部的缺失编号

使用上面的序列视图，可以很容易找出全部缺失编号，只需要和比较对象进行行差集运算可以使用EXCEPT、NOT EXISTS、NOT IN,甚至外连接的方法。

-- EXCEPT版
SELECT seq FROM Sequence 
 WHERE seq BETWEEN 1 AND 12
EXCEPT 
 SELECT seq FROM SeqTb1;
-- NOT IN版
SELECT seq FROM Sequence 
 WHERE seq BETWEEN 1 AND 12
 AND seq NON IN （SELECT seq FROM SeqTb1）;
-- 动态地指定连续编号范围的SQL语句
SELECT seq FROM Sequence 
 WHERE seq BETWEEN （SELECT MIN(seq) FROM SeqTb1） AND （SELECT MAX(seq) FROM SeqTb1）
EXCEPT 
 SELECT seq FROM SeqTb1;

3个人能坐得下吗

找出连续3个空位的全部组合。
使用NOT EXISTS的解法，需要满足的条件是，以n为起点、n+（3-1）为终点的座位全部是未预定状态。

-- 找出需要的空位（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 <> '未预定');

head_cnt表示所需空位个数的参数。这条查询语句充分体现了SQL在处理有序集合时的原理。通过两个步骤来理解:1.通过自连接生成起点和终点的组合。2.描述起点到终点之间所有的点需要满足的条件。本例中，序列内的点需要满足的条件时所有座位的状态都是“未预定”。转化成双重否定，不存在不满足条件P的行。
使用窗口函数的解法，我们只看“未预定”的座位，对于seat列的起点和终点，“终点-起点=2”的关系就应该时成立的。使用窗口函数的帧来解决这个问题。

-- 找出需要的空位（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 
  FORM Seats
  WHERE  status = '未预定') TMP
 WHERE end_stat - seat=（:head_cnt-1） ;

传统的SQL禁止使用“顺序”概念，但如果使用这个概念处理像数列这样的有序结构，其实时具有很大的优势。

有换排的数列

使用NOT EXISTS的解法，要想解决换排的问题，除了需要序列内的所有座位全部都是空位，还需要加入“全部都在一排”这样一个条件。

-- 找出需要的空位 ：不虑座位的换排NOT EXISTS
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 <> '未预定' OR S3.line_id <> S1.line_id));

使用窗口函数的解法,将列line_id作为主键来生成子集，这本来就是PARTITION BY子句的功能。

-- 找出需要的空位：虑座位的换排 窗口函数
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 
  FORM Seats
  WHERE  status = '未预定') TMP
 WHERE end_stat - seat=（:head_cnt-1） ;

单调递增和单调递减

-- 判断股价是否高于上次交易
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 THNE 'DOWN' ELSE NULL END AS diff 
 FROM MyStock;

SIGN函数判断参数的正负号，如果参数为正，则返回1；参数为0，则返回0；参数为负，则返回-1.
本节要点：
1.SQL处理数据的方法有两种。
2.第一种时把数据堪称忽略了顺序的集合。这种方法基于使用传统的SQL集合和谓词的思路。
3.第二种时把数据堪称有序的集合，此时的基本方法是使用窗口函数进行处理。
4.要在SQL中表达全称量化时，需要将全称量化命题转换成存在量化命题的否定形式，并使用NOT EXISTS谓词。这是因为SQL只实现了谓词逻辑中的存在量词。