SQL学习—用关联查询比较行与行

1-6 用关联子查询比较行与行

增长、减少、维持现状

业务场景：需要用到行间数据比较，使用时间序列的表进行时间序列分析。假设有下面的一张记录了某个公司的营业额的表Sales使用SQL输出与上一年相比营业额是增加还是减少，抑或持平。
思路：
01 按年份递增的顺序排序
02 循环地将每一行与前一行的“sale"列进行比较
面向集合的方式，在表Sales的基础上，再加一个存储了上一个数据的集合（S2）

/* 求与上一年营业额一样的年份（1）：使用关联子查询 */
SELECT year,sale
  FROM Sales S1
 WHERE sale = (SELECT sale
                 FROM Sales S2
                WHERE S2.year = S1.year - 1)
 ORDER BY year;

关联子查询与自连接很多时候等价

/* 求与上一年营业额一样的年份（2）：使用自连接 */
SELECT S1.year, S1.sale
  FROM Sales S1, 
       Sales S2
 WHERE S2.sale = S1.sale
   AND S2.year = S1.year - 1
 ORDER BY year;

用列表展示与上一年的比较结果
（1）使用关联子查询

/* 求出是增长了还是减少了，抑或是维持现状（1）：使用关联子查询 */
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;

（2）使用自连接

/* 求出是增长了还是减少了，抑或是维持现状（2）：使用自连接查询 */
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;

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

思路：“过去最临近的年份”需要满足下面两个条件
01 与该年份相比是过去的年份
02 在满足条件1的年份中，年份最早的一个

/* 查询与过去最临近的年份营业额相同的年份 */
SELECT year, sale
  FROM Sales2 S1
 WHERE sale =
   (SELECT sale
      FROM Sales2 S2
     WHERE S2.year =
       (SELECT MAX(year)            /* 条件2：在满足条件1的年份中，年份最早的一个 */
          FROM Sales2 S3
         WHERE S1.year > S3.year))  /* 条件1：与该年份相比是过去的年份 */
 ORDER BY year;

/* 查询与过去最临近的年份营业额相同的年份：同时使用自连接 */
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）：结果里不包含最早的年份 */
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 now_year;

这条SQL语句无法获得最早年份1990的数据，这是由于表里没有比1990年更早的年份，所以内连接的时候1990年数据被排除了。避免这种情况可以使用“自外连接”。

/* 求每一年与过去最临近的年份之间的营业额之差（2）：使用自外连接。结果里包含最早的年份 */
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 now_year;

移动累计值和移动平均值

应用场景：例如有一张银行账户存取款历史纪录表Account，求截止到某个处理日期的处理金额的累计值。
(1) 使用窗口函数

/* 求累计值：使用窗口函数*/
SELECT pre_date,prc_amt
               SUM(prc_amt) OVER (ORDER BY prc_date ) AS onhand_amt
FROM  Accounts;

（2）使用冯诺依曼递归集合

/* 求累计值：使用冯·诺依曼型递归集合 */
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;

与1-2 节里计算位次的方法属于同类型的查询语句，只是将COUNT替换成SUM。因此冯诺依曼递归集合可以用来求累计值。
移动累计值：上述例题没有指定要求的累计值的时间区间，如何以3次处理为单位求累计值，行数固定（3行）一行一行地偏移。
(1) 使用窗口函数

/* 求移动累计值（1）：使用窗口函数 */
SELECT prc_date,prc_amt,
               SUM(prc_amt) OVER (ORDER BY prc_date ROWS 2 PRECEDING) AS onhand_amt
 FROM Accounts;

(2) 标量子查询来计算行数

/* 求移动累计值（2）：不满3行的时间区间也输出 */
SELECT prc_date, A1.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;

/* 求移动累计值（3）：不满3行的区间按无效处理 */
SELECT prc_date, A1.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  /* 不满3行数据的不显示 */
  FROM  Accounts A1
 ORDER BY prc_date;

理解SQL语句的处理过程，可以输出去掉聚合后的明细数据

/* 求移动累计值（2）：不满3行的时间区间也输出 */
SELECT 
    A1.prc_date AS A1_date,
    A2.prc_date AS A2_date,
    A2.prc_amt AS amt
FROM
    Accounts A1,
    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
ORDER BY A1.prc_date , A2.prc_date

存在部分交集的集合簇
自连接-“嵌套（递归)”
这里的关键词可以定义为偏移

查询重叠的时间区间

应用场景：
假设有一张表Reservations,记录了酒店或者旅馆的预约情况。查询住宿日期重叠的客人。
分析：
日期重叠类型：
01 自己入住日期在他人的住宿期间内
02 自己离店日期在他人的住宿期间内
03 自己入住和离店日期都在他人的住宿期间内
充要条件是满足条件01 和 02 中至少一个条件

/* 求重叠的住宿期间 */
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    /* 条件（1）：自己的入住日期在他人的住宿期间内 */
              OR R1.end_date  BETWEEN R2.start_date AND R2.end_date));  /* 条件（2）：自己的离店日期在他人的住宿期间内 */

假如 有客户住宿期间完全包含了他人的住宿日期，上述的SQL语句会把这种情况排除掉

/* 升级版：把完全包含别人的住宿期间的情况也输出 */
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)));