《SQL基础教程》+ 《SQL进阶教程》 学习笔记 (10)

4、去重排列(不考虑顺序,即无序对)且 扩展成 3 列

SELECT P1.name AS name_1, P2.name AS name_2, P3.name AS name_3 FROM Products P1, Products P2, Products P3 WHERE P1.name > P2.name AND P2.name > P3.name;

结果:

name_1 name_2

香蕉 苹果 橘子

(2)非等值连接

上面 应用1 里的 2、3、4 都是使用除 “=” 以外的其他比较运算符,如 “<、>、<>”,这样进行的连接称为 "非等值连接"。

应用2:删除重复行

假如有个 Products 表,有 name 和 price 两列:


方法一:关联子查询

需要使用由数据库独自实现的行ID

例如, Oracle 数据库里的 rowid,或者 PostgreSQL 里的 ctid 。

DELETE FROM Products P1 WHERE rowid < (     SELECT MAX(P2.rowid)     FROM Products P2     WHERE P1.name = P2.name     AND P1.price = P2.price );

方法二:EXISTS(关联子查询) + 非等值连接

DELETE FROM Products P1 WHERE EXISTS (     SELECT *     FROM Products P2     WHERE P1.name = P2.name     AND P1.price = P2.price     AND P1.rowid < P2.rowid ); 应用3:查找局部不一致的列

假如有个 Products 表,有 name 和 price 两列:

从 Products 表里查找价格相等但商品名称不同的记录

SELECT DISTINCT P1.name, P1.price FROM Products P1, Products P2 WHERE P1.price = P2.price AND P1.name <> P2.name; 应用4:排序

方法一:用窗口函数

SELECT name, price, RANK() OVER (ORDER BY price DESC) AS rank_1, DENSE_RANK() OVER (ORDER BY price DESC) AS rank_2 FROM Products;

方法二:自连接 + 非等值连接

-- 排序从 1 开始。如果已出现相同位次,则跳过之后的位次 -- 1、关联子查询 SELECT P1.name, P1.price, (     SELECT COUNT(P2.price)     FROM Products P2     WHERE P2.price > P1.price ) + 1 AS rank_1 FROM Products P1 ORDER BY rank_1; -- 2、表的连接 SELECT P1.name, MAX(P1.price) AS price, COUNT(P2.name) +1 AS rank_1 FROM Products P1 LEFT OUTER JOIN Products P2 ON P1.price < P2.price GROUP BY P1.name ORDER BY rank_1;

此处蕴含了递归集合的思想。

第 8 章 SQL 高级处理

本章介绍的 窗口函数 和 GROUPING 运算符都是为了实现 OLAP 用途而添加的功能,是 SQL 里比较新的功能。

截止到 2016 年 5 月,Oracle、SQL Server、DB2、PostgreSQL 的最新版本都已经支持这些功能了,但MySQL 的最新版本 5.7 还是不支持这些功能。

OLAP 是 OnLine Analytical Processing 的简称,意思是对数据库数据进行实时分析处理,用来诸如生成报表。例如,市场分析、创建财务报表、创建计划等日常性商务工作。

1、窗口函数

下面会结合原书 + 我之前的一篇文章《 PostgreSQL 窗口函数 ( Window Functions ) 如何使用?》+ 自己的理解,梳理下。

(1)窗口函数和聚合的区别

窗口函数跟聚合还是挺像的,但区别是:

窗口函数不会像聚合一样将参与计算的行合并成一行输出,而是将计算出来的结果带回到了计算行上。

(2)用法

完整示例:

SELECT "product_name", "product_type", "sale_price", AVG ("sale_price") OVER ( PARTITION BY "product_type" ORDER BY "sale_price" ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS "avg" FROM "Product";

1、AVG (sale_price) 为窗口函数。

窗口函数大体可以分为以下两种:

所有的聚合函数都能用作窗口函数,如(SUM、AVG、COUNT、MAX、MIN)

RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数

问:专用窗口函数 跟 聚合函数 的用法区别 ?

答:

由于专用窗口函数无需参数,因此通常括号中都是空的。而聚合函数一般都需要传参来指定列名。

原则上窗口函数只能在 SELECT 子句中使用。其理由是,在 DBMS 内部,窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的“结果”进行的操作。


2、PARTITION BY "product_type" 的 PARTITION BY,类似于 GROUP BY。通过 PARTITION BY 分组后的记录集合称为窗口。此处的窗口并非“窗户”的意思,而是代表范围

PARTITION BY 可以省略,代表全部记录集合为一个窗口。


3、ORDER BY "sale_price" 的 ORDER BY,是在窗口函数调用前,先把每个窗口内的记录集合排序。

问:为什么用 GROUP BY 的时候不需要加 ORDER BY ?

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/zygdwg.html