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

读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):使用关联子查询
   SELECT year, sale
     FROM Sales S1
    WHERE sale = (SELECT sale
                   FROM Sales S2
                   WHERE S2.year = S1.year -1)
    ORDER BY year;
  • 2.2.2. S2.year = S1.year -1这个条件起到了将要比较的数据偏移一行的作用

  • 2.2.3. --求与上一年营业额一样的年份(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;

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

3.1. 示例

  • 3.1.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;
  • 3.1.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;

4. 时间轴有间断时

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

4.2. 示例

  • 4.2.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;
  • 4.2.2.  自连接版本
SELECT S1.year AS year,

         S1.year AS year
     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;
  • 4.2.3. --求每一年与过去最临近的年份之间的营业额之差(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;
  • 4.2.4. --求每一年与过去最临近的年份之间的营业额之差(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;
  • 4.2.5. 使用极值函数时会发生排序

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

5.1. 示例

  • 5.1.1. --求累计值:使用窗口函数
   SELECT prc_date, prc_amt,
         SUM(prc_amt) OVER (ORDER BY prc_date) AS onhand_amt
     FROM Accounts;
  • 5.1.2. 引入窗口函数的目的原本就是解决这类问题,因此这里的代码非常简洁

    • 5.1.2.1. 如果选用的数据库支持窗口函数,也可以考虑使用窗口函数
  • 5.1.3. 从性能方面来看,表的扫描和数据排序也都只进行了一次

    • 5.1.3.1. 依赖于具体的数据库的
  • 5.1.4. --求累计值:使用冯·诺依曼型递归集合

   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;
  • 5.1.5. --求移动累计值(1):使用窗口函数
   SELECT prc_date, prc_amt,
         SUM(prc_amt) OVER (ORDER BY prc_date
                           ROWS 2 PRECEDING) AS onhand_amt
     FROM Accounts;
  • 5.1.6. --求移动累计值(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;
  • 5.1.7. A3.prc_date在以A2.prc_date为起点,以A1.prc_date为终点的区间内移动

  • 5.1.8. --移动累计值(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;

5.2. 基本思路是使用冯·诺依曼型递归集合

6. 查询重叠的时间区间

6.1. 示例

  • 6.1.1. --求重叠的住宿期间
   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):自己的离店日期在他人的住宿期间内
  • 6.1.2. --升级版:把完全包含别人的住宿期间的情况也输出
   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)));

原文链接:https://www.cnblogs.com/lying7/p/17277869.html

本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:读SQL进阶教程笔记05_关联子查询 - Python技术站

(0)
上一篇 2023年4月17日
下一篇 2023年4月17日

相关文章

  • HIVE配置mysql metastore

    HIVE配置mysql metastore    hive中除了保存真正的数据以外还要额外保存用来描述库、表、数据的数据,称为hive的元数据。这些元数据又存放在何处呢?    如果不修改配置hive默认使用内置的derby数据库存储元数据。    derby是apache开发的基于java的文件型数据库。    可以检查之前执行命令的目录,会发现其中产生了…

    MySQL 2023年4月13日
    00
  • MySql中的Full Text Search全文索引优化

    当我们在MySQL中需要对一些包含文本内容的字段进行搜索时,使用全文索引就可以提高搜索效率。在这份攻略中,我们将讲解如何在MySQL中使用Full Text Search全文索引进行优化。 1. 创建全文索引 在MySQL中,我们可以使用以下语法来创建一个包含全文索引的表: CREATE TABLE `mytable` ( `id` int(11) NOT …

    MySQL 2023年5月19日
    00
  • MySQL中如何优化order by语句

    当查询语句中包含了ORDER BY时,MySQL在查询结果之前会先做一个排序操作,这个操作需要消耗大量的资源,如果排序操作的效率不高,则整个查询的效率也会受到影响。因此,对于MySQL中的ORDER BY语句,进行优化是非常重要的。 以下是优化MySQL中ORDER BY语句的攻略: 1. 索引优化 ORDER BY语句可以使用索引进行优化,如果查询的字段已…

    MySQL 2023年5月19日
    00
  • MySQL优化GROUP BY(松散索引扫描与紧凑索引扫描)

    MySQL中的GROUP BY语句可以用于将相似的数据分组并计算其汇总值。但是,当数据集很大时,GROUP BY语句对性能的影响也会很大。本文将详细讲解如何通过松散索引扫描和紧凑索引扫描来优化MySQL中的GROUP BY语句。 松散索引扫描优化 在MySQL中,使用GROUP BY语句分组时,如果不指定排序(ORDER BY)的话,MySQL会随机选择一个…

    MySQL 2023年5月19日
    00
  • 【MySQL】MySQL知识图谱

    文章目录 MySQL 表 锁 索引 连接管理 事务 日志系统 简单记录 极客时间 – MySQL实战45讲 MySQL知识图谱 表 表 引擎选择 编码问题 表空间管理 字段设计 备份和恢复 压缩表 分区表 锁 锁 全局锁 表锁 行锁 索引 索引 主键索引 唯一索引 前缀索引 选择策略 change buffer 空间利用率 索引设计 排序优化 连接管理 连接…

    MySQL 2023年4月13日
    00
  • 未处理 MySql.Data.MySqlClient.MySqlException Message=Incorrect string value: ‘\xE5\xBC\xA0\xE4\xB8\x8

    MySQL里的ERROR 1366(HY000):Incorrect string value问题   这个就是编码的问题,可能在装MySql的时候选择的是默认的编码,或者 选择的UTF8,所以在插入数据的时候出现编码的错误.  www.2cto.com     数据不重要的话,一劳永逸的解决办法是,使用alert database databaseName…

    MySQL 2023年4月12日
    00
  • MySql中的连接查询问题怎么解决

    本文小编为大家详细介绍“MySql中的连接查询问题怎么解决”,内容详细,步骤清晰,细节处理妥当,希望这篇“MySql中的连接查询问题怎么解决”文章能帮助大家解决疑惑,下面跟着小编的思路慢慢深入,一起来学习新知识吧。 连接查询 当进行多表连接查询时 需要指定字段所属的表 , 可以提高查询效率 , 如果不指定字段所属的表 , 数据库会从每个表中都找一下该字段 e…

    MySQL 2023年4月11日
    00
  • mysql面试小结

    MySQL 1. 索引 1.1 什么是索引 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。 索引是一种数据结构。数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。 更通俗的说,索引就相当于目录。为了方便查找书中的内容,…

    MySQL 2023年4月18日
    00
合作推广
合作推广
分享本页
返回顶部