读SQL进阶教程笔记05_关联子查询

吹雪听风

1. 关联子查询

1.1. 关联子查询和自连接在很多时候都是等价的
1.2. 使用SQL进行行间比较时，发挥主要作用的技术是关联子查询，特别是与自连接相结合的“自关联子查询”
1.3. 缺点

• 1.3.1. 代码的可读性不好
  
  
  
  • 1.3.1.1. 特别是在计算累计值和移动平均值的例题里，与聚合一起使用后，其内部处理过程非常难理解
    
  
  
• 1.3.2. 性能不好
  
  
  
  • 1.3.2.1. 特别是在SELECT子句里使用标量子查询时，性能可能会变差
    
  
  

2. 增长、减少、维持现状

2.1. 使用基于时间序列的表进行时间序列分析
2.2. 示例

• 2.2.1. --求与上一年营业额一样的年份(1)：使用关联子查询
  

1.    SELECT year, sale
2.      FROM Sales S1
3.     WHERE sale = (SELECT sale
4.                    FROM Sales S2
5.                    WHERE S2.year = S1.year -1)
6.     ORDER BY year;

复制代码

• 2.2.2. S2.year = S1.year -1这个条件起到了将要比较的数据偏移一行的作用
  
• 2.2.3. --求与上一年营业额一样的年份(2)：使用自连接
  

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

复制代码

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

3.1. 示例

• 3.1.1. --求出是增长了还是减少了，抑或是维持现状(1)：使用关联子查询
  

1.    SELECT S1.year, S1.sale,
2.          CASE WHEN sale =
3.                (SELECT sale
4.                    FROM Sales S2
5.                  WHERE S2.year = S1.year -1) THEN'→'--持平
6.                WHEN sale ＞
7.                (SELECT sale
8.                    FROM Sales S2
9.                  WHERE S2.year = S1.year -1) THEN'↑'--增长
10.                WHEN sale ＜
11.                (SELECT sale
12.                    FROM Sales S2
13.                  WHERE S2.year = S1.year -1) THEN'↓'--减少
14.          ELSE'—'END AS var
15.      FROM Sales S1
16.     ORDER BY year;

复制代码

• 3.1.2. --求出是增长了还是减少了，抑或是维持现状(2)：使用自连接查询（最早的年份不会出现在结果里）
  

1.    SELECT S1.year, S1.sale,
2.          CASE WHEN S1.sale = S2.sale THEN'→'
3.                WHEN S1.sale ＞ S2.sale THEN'↑'
4.                WHEN S1.sale ＜ S2.sale THEN'↓'
5.          ELSE'—'END AS var
6.      FROM Sales S1, Sales S2
7.     WHERE S2.year = S1.year -1
8.     ORDER BY year;

复制代码

4. 时间轴有间断时

4.1. 和过去最临近的时间进行比较
4.2. 示例

• 4.2.1. --查询与过去最临近的年份营业额相同的年份
  

1.    SELECT year, sale
2.      FROM Sales2 S1
3.     WHERE sale =
4.      (SELECT sale
5.          FROM Sales2 S2
6.        WHERE S2.year =
7.          (SELECT MAX(year)  --条件2：在满足条件1的年份中，年份最早的一个
8.              FROM Sales2 S3
9.            WHERE S1.year ＞ S3.year))  --条件1：与该年份相比是过去的年份
10.     ORDER BY year;

复制代码

• 4.2.2.  自连接版本
  

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

复制代码

• 4.2.3. --求每一年与过去最临近的年份之间的营业额之差(1)：结果里不包含最早的年份
  

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

复制代码

• 4.2.4. --求每一年与过去最临近的年份之间的营业额之差(1)：结果里不包含最早的年份
  

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

复制代码

• 4.2.5. 使用极值函数时会发生排序
  

5. 移动累计值和移动平均值

5.1. 示例

• 5.1.1. --求累计值：使用窗口函数
  

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

复制代码

• 5.1.2. 引入窗口函数的目的原本就是解决这类问题，因此这里的代码非常简洁
  
  
  
  • 5.1.2.1. 如果选用的数据库支持窗口函数，也可以考虑使用窗口函数
    
  
  
• 5.1.3. 从性能方面来看，表的扫描和数据排序也都只进行了一次
  
  
  
  • 5.1.3.1. 依赖于具体的数据库的
    
  
  
• 5.1.4. --求累计值：使用冯·诺依曼型递归集合
  

1.    SELECT prc_date, A1.prc_amt,
2.          (SELECT SUM(prc_amt)
3.            FROM Accounts A2
4.            WHERE A1.prc_date ＞= A2.prc_date ) AS onhand_amt
5.      FROM Accounts A1
6.     ORDER BY prc_date;

复制代码

• 5.1.5. --求移动累计值(1)：使用窗口函数
  

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

复制代码

• 5.1.6. --求移动累计值(2)：不满3行的时间区间也输出
  

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

复制代码

• 5.1.7. A3.prc_date在以A2.prc_date为起点，以A1.prc_date为终点的区间内移动
  
• 5.1.8. --移动累计值(3)：不满3行的区间按无效处理
  

1.    SELECT prc_date, A1.prc_amt,
2.     (SELECT SUM(prc_amt)
3.        FROM Accounts A2
4.      WHERE A1.prc_date ＞= A2.prc_date
5.        AND (SELECT COUNT(＊)
6.                FROM Accounts A3
7.              WHERE A3.prc_date
8.                BETWEEN A2.prc_date AND A1.prc_date  ) ＜= 3
9.      HAVING  COUNT(＊) =3) AS mvg_sum  --不满3行数据的不显示
10.      FROM Accounts A1
11.     ORDER BY prc_date;

复制代码

5.2. 基本思路是使用冯·诺依曼型递归集合
6. 查询重叠的时间区间

6.1. 示例

• 6.1.1. --求重叠的住宿期间
  

1.    SELECT reserver, start_date, end_date
2.      FROM Reservations R1
3.     WHERE EXISTS
4.          (SELECT ＊
5.                FROM Reservations R2
6.               WHERE R1.reserver ＜＞ R2.reserver  --与自己以外的客人进行比较
7.                 AND ( R1.start_date BETWEEN R2.start_date AND R2.end_date
8.                                    --条件(1)：自己的入住日期在他人的住宿期间内
9.                    OR R1.end_date  BETWEEN R2.start_date AND R2.end_date));
10.                                    --条件(2)：自己的离店日期在他人的住宿期间内

复制代码

• 6.1.2. --升级版：把完全包含别人的住宿期间的情况也输出
  

1.    SELECT reserver, start_date, end_date
2.     FROM Reservations R1
3.    WHERE EXISTS
4.          (SELECT ＊
5.              FROM Reservations R2
6.            WHERE R1.reserver ＜＞ R2.reserver
7.              AND (  (     R1.start_date BETWEEN R2.start_date
8.                                            AND R2.end_date
9.                        OR R1.end_date   BETWEEN R2.start_date
10.                                            AND R2.end_date)
11.                    OR (    R2.start_date BETWEEN R1.start_date
12.                                            AND R1.end_date
13.                        AND R2.end_date   BETWEEN R1.start_date
14.                                            AND R1.end_date)));

复制代码

来源:https://www.cnblogs.com/lying7/p/17277869.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！