【SQL进阶教程】用关联子查询比较行与行

Posted on | In

关联子查询是一种非常强大的运算，也有一些它的缺点。第一个缺点是代码的可读性不好。第二个缺点是性能不好。特别是在SELECT子句里使用标量子查询时，性能可能会变差。

增长、减少、维持现状

假设有下面这样一张记录了某个公司每年的营业额的表Sales。

nmM5Zj.jpg

根据这张表里的数据，使用SQL输出与上一年相比营业额是增加了还是减少了，抑或是没有变化。可以先求出“不变”这种情况。

1
2
3
4
5
6
7
-- 关联子查询
SELECT year, sale
FROM Sales S1
WHERE sale = (SELECT sale
              FROM Sales S2
              WHERE S1.year - 1 = S2.year)
ORDER BY year;

nmwevd.png

关联子查询和自连接在很多时候都是等价的，所以也可以像下面这样使用自连接来实现。

1
2
3
4
5
6
-- 自连接
SELECT S1.year, S1.sale
FROM Sales S1, Sales S2
WHERE S1.year - 1 = S2.year
      AND S1.sale = S2.sale
ORDER BY year;

用列表展示与上一年的比较结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 关联子查询
SELECT S1.year, S1.sale,
       CASE WHEN sale = (SELECT sale 
                         FROM Sales S2
                         WHERE S2.year = S1.year - 1)
            THEN '→'
            WHEN sale > (SELECT sale 
                         FROM Sales S2
                         WHERE S2.year = S1.year - 1)
            THEN '↑'
            WHEN sale < (SELECT sale 
                         FROM Sales S2
                         WHERE S2.year = S1.year - 1)
            THEN '↓'
       ELSE '-' END AS var
FROM Sales S1
ORDER BY year;

nmI7rt.png

同样，这里也可以改写一下SQL语句，使用自连接来实现，如下所示。

1
2
3
4
5
6
7
8
9
-- 自连接查询
SELECT S1.year, S1.sale,
       CASE WHEN S1.sale = S2.sale THEN '→'
            WHEN S1.sale > S2.sale THEN '↑'
            WHEN S1.sale < S2.sale THEN '↓'
       ELSE '-' END AS var
FROM Sales S1, Sales S2
WHERE S2.year = S1.year - 1
ORDER BY year;

采用这种实现方法时，由于这里没有1990年之前的数据，所以1990年会被排除掉，执行结果会少一行。

时间轴有间断时：和过去最临近的时间进行比较

比如丢失了过去个别年份的数据，如表Sales2所示。

nnSjv6.png

对某一年来说，“过去最临近的年份”需要满足下面两个条件。

  1. 与该年份相比是过去的年份。
  2. 在满足条件1的年份中，年份最早的一个。

如果按这两个条件改写SQL语句，那么应该像下面这样写。

1
2
3
4
5
6
7
8
SELECT year, sale
FROM Sales2 S1
WHERE sale = (SELECT sale
              FROM Sales2 S2
              WHERE S2.year = (SELECT MAX(year)
                               FROM Sales2 S3
                               WHERE S1.year > S3.year))
ORDER BY year;

nnCob8.png

如果使用自连接，可以减少一层子查询的嵌套。

1
2
3
4
5
6
7
8
SELECT S1.year AS year, 
       S1.sale AS sale
FROM Sales2 S1, Sales2 S2
WHERE S1.sale = S2.sale
      AND S2.year = (SELECT MAX(year)
                     FROM Sales2 S3
                     WHERE S1.year > S3.year)
ORDER BY year;

通过这个方法，可以查询每一年与过去最临近的年份之间的营业额之差。

1
2
3
4
5
6
7
8
9
10
SELECT S2.year AS pre_year,
       S1.year AS now_year, 
       S2.sale AS pre_sale,
       S1.sale AS now_sale,
       S1.sale - S2.sale AS diff
FROM Sales2 S1, Sales2 S2
WHERE S2.year = (SELECT MAX(year)
                 FROM Sales2 S3
                 WHERE S1.year > S3.year)
ORDER BY year;

nnP6s0.png

如果想让结果里出现1990年的数据，可以使用“自外连接”来实现。

1
2
3
4
5
6
7
8
9
10
11
SELECT S2.year AS pre_year,
       S1.year AS now_year, 
       S2.sale AS pre_sale,
       S1.sale AS now_sale,
       S1.sale - S2.sale AS diff
FROM Sales2 S1
LEFT OUTER JOIN Sales2 S2 
     ON S2.year = (SELECT MAX(year)
                   FROM Sales2 S3
                   WHERE S1.year > S3.year)
ORDER BY year;

因为使用极值函数时会发生排序，所以这个方法在性能方面稍微逊色（如果极值函数的参数是主键，有时也是可以使用索引的）。

移动累计值和移动平均值

例如下面有一张银行账户存取款历史记录表Accounts，现在思考一下如何求出累计值。

nn3dGn.jpg

1
2
3
4
-- 窗口函数
SELECT prc_date, prc_amt,
       SUM(prc_amt) OVER (ORDER BY prc_date) AS onhand_amt
FROM Accounts;

使用窗口函数来实现的代码非常简洁，而且从性能方面来看，表的扫描和数据排序也都只进行了一次。

1
2
3
4
5
6
7
-- 冯诺依曼型递归集合
SELECT prc_date, A1.prc_amt,
       (SELECT SUM(prc_amt)
        FROM Accounts A2
        WHERE A1.prc_date >= A2.prc_date) AS onhand_amt
FROM Accounts A1
ORDER BY prc_date;

冯诺依曼型递归集合也是可以用来求累计值的。

接下来，考虑一下如何以3次处理为单位求累计值，即移动累计值。

nn49nP.png

如果使用窗口函数，可以像下面这样通过指定ROWS关键字来指定数据行数。

1
2
3
SELECT prc_date, prc_amt,
       SUM(prc_amt) OVER (ORDER BY prc_date ROWS 2 PRECEDING) AS onhand_amt
FROM Accounts;

如果使用关联子查询，还可以像下面这样用标量子查询来计算行数。

1
2
3
4
5
6
7
8
9
SELECT prc_date, prc_amt,
       (SELECT SUM(prc_amt)
        FROM Accounts A2
        WHERE A1.prc_date >= A2.prc_date
              AND (SELECT COUNT(*)
                   FROM Accounts A3
                   WHERE A3.prc_date BETWEEN A2.prc_date AND A1.prc_date) <= 3) AS mvg_sum
FROM Accounts A1
ORDER BY prc_date;

nn5vLt.png

在处理前2行时，即使数据不满3行，这条SQL语句还是计算出了相应的累计值。其实，可以将这样的情况作为无效来处理。

1
2
3
4
5
6
7
8
9
10
SELECT prc_date, prc_amt,
       (SELECT SUM(prc_amt)
        FROM Accounts A2
        WHERE A1.prc_date >= A2.prc_date
              AND (SELECT COUNT(*)
                   FROM Accounts A3
                   WHERE A3.prc_date BETWEEN A2.prc_date AND A1.prc_date) <= 3
                   HAVING COUNT(*) = 3) AS mvg_sum
FROM Accounts A1
ORDER BY prc_date;

nnImwV.png

查询重叠的时间区间

假设有下面这样一张表Reservations，记录了酒店或者旅馆的预约情况。

nnHhEq.jpg

那么，正常情况下，每天只能有一组客人在该房间住宿。从表中数据可以看出，这里存在重叠的预定日期。
下面，给重叠的住宿日期分类，可知一共有下面三种类型。

nnHjV1.png

充要条件是满足类型（1）和类型（2）中至少一个条件。

1
2
3
4
5
6
7
SELECT reserver, start_date, end_date
FROM Reservations R1
WHERE EXISTS (SELECT * 
              FROM Reservations R2
              WHERE R1.reserver <> R2.reserver
                    AND (R1.start_date BETWEEN R2.start_date AND R2.end_date
                         OR R1.end_date BETWEEN R2.start_date AND R2.end_date));

nnqSwn.png

如果山本的入住日期不是11月3号，而是推迟了一天，即11月4号，那么像内田这种自己的住宿期间完全包含了他人的住宿期间的情况，会被这条SQL语句排除掉。
如果想把这样的住宿期间也输出，需要追加条件。

1
2
3
4
5
6
7
8
9
SELECT reserver, start_date, end_date
FROM Reservations R1
WHERE EXISTS (SELECT * 
              FROM Reservations R2
              WHERE R1.reserver <> R2.reserver
                    AND ((R1.start_date BETWEEN R2.start_date AND R2.end_date
                                OR R1.end_date BETWEEN R2.start_date AND R2.end_date)
                          OR (R2.start_date BETWEEN R1.start_date AND R1.end_date
                                AND R2.end_date BETWEEN R1.start_date AND R1.end_date)));