全连接where 取最新日期_SQL学习笔记（4）——集合、连接与窗口函数

最新推荐文章于 2023-09-20 14:34:05 发布

weixin_39678163

最新推荐文章于 2023-09-20 14:34:05 发布

阅读量298

点赞数

文章标签：全连接where 取最新日期

本文链接：https://blog.csdn.net/weixin_39678163/article/details/113579000

版权

一、集合

集合的运算常见如下五种：并(或)、交(和)、差、对称差、补。

如果我们把表看成集合，表中的每一行记录视为一个元素，那么集合运算对应到表查询的关系如下表所示：

从数据处理的直接结果上看，集合运算改变了表查询结果的行(行数不见得被改变)，不涉及表间的字段关联，即查询结果没有获得原表字段之外的信息(注意：UNION的隐式类型转化是例外)。

二、连接

承接有关集合最后的结论：从数据处理的直接结果上，连接改变了表查询结果的字段(字段数可以选择性地展示)，依赖表间的字段关联实现，即查询结果获得了原表字段以外的信息。

首要的一点是，由于SQL最先执行FROM子句，然后才是WHERE、GROUP BY，所以连接(FROM ... INNER/OUTER JOIN... ON..) 是最先完成的。实际上，把FROM后面的连接结果封装成一个新的表即可，其他WHERE、GROUP、聚合等，都没有变化。

连接类型可以不完备地分成以下三种，其特点总结如下表：

在实际的业务中,例如想要生成固定行数的单据时,就需要使用外连接，这是利用了外连接的“高宽容”特点。如果使用内连结的话,根据 SELECT 语句执行时商店库存状况的不同，结果的行数也会发生改变,生成的单据的版式也会受到影响，而使用外连结能够得到固定行数的结果。

关于外连接的高宽容特点，以下例子可以更清楚地展现(例子来自教程4.2.2.2)：

目的：使用外连结从 shopproduct 表和 product 表中找出那些在某个商店库存少于50的商品及对应的商店.希望得到如下结果.

注意：高压锅和圆珠笔两种商品在所有商店都无货，也应该包括在内。

按照"结合WHERE子句使用内连结"的思路, 我们很自然会写出如下代码

SELECT P.product_id       ,P.product_name       ,P.sale_price       ,SP.shop_id       ,SP.shop_name       ,SP.quantity  FROM product AS P  LEFT OUTER JOIN shopproduct AS SP    ON SP.product_id = P.product_id WHERE quantity< 50

然而不幸的是, 得到的却是如下的结果:

观察发现, 返回结果缺少了在所有商店都无货的高压锅和圆珠笔。因为WHERE 进行>或

然而在实际环境中，由于数据量大且数据质量并非像我们设想的那样"干净"，我们并不能容易地意识到缺失值等问题数据的存在，因此，还是让我们想一下如何改写我们的查询以使得它能够适应更复杂的真实数据的情形吧。

联系到我们已经掌握了的SQL查询的执行顺序(FROM->WHERE->SELECT)，以及外连接的高宽容特点，我们发现, 问题可能出在筛选条件上, 因为在进行完外连接后才会执行WHERE子句，虽然外连接保留了quantity为NULL的数据，但后面执行的WHERE又把它们过滤掉了。

明白了这一点, 我们就可以试着把WHERE子句挪到外连结之前进行: 先写个子查询,用来从shopproduct表中筛选quantity<50的商品,，然后再把这个子查询和主表连结起来.

我们把上述思路写成SQL查询语句:

SELECT P.product_id      ,P.product_name      ,P.sale_price       ,SP.shop_id      ,SP.shop_name      ,SP.quantity   FROM product AS P  LEFT OUTER JOIN-- 先筛选quantity<50的商品   (SELECT *      FROM shopproduct     WHERE quantity < 50 ) AS SP    ON SP.product_id = P.product_id

得到的结果如下:

总结：核心思路在于，把WHERE写在子查询内，让能容纳表外内容的OUTER JOIN(因为主表含高压锅和圆珠笔) 在无法容纳NULL 的WHERE子句之后执行，以保证外连接的高宽容特点得以发挥。

三、窗口函数

以下对窗口函数的的引入，主要参考教程原文。

常规的SELECT语句都是对整张表进行查询，而窗口函数可以让我们有选择的去某一部分数据进行汇总、计算和排序。

窗口函数的通用形式：

窗口函数(如：SUM、MAX、MIN(聚合)或RANK、DENSE_RANK ) OVER (PARTITION BY 列名 ORDER BY 排序用列名框架(如：ROWS n PRECEDING))

窗口函数最关键的是搞明白关键字PARTITON BY、ORDER BY和框架的作用。

PARTITON BY是用来分组，即选择要看哪个窗口，类似于GROUP BY 子句的分组功能，但是PARTITION BY 子句并不具备GROUP BY 子句的汇总功能，并不会改变原始表中记录的行数。

PS：如果不用PARTITION BY指定窗口范围，则默认把整体看作一个窗口。

ORDER BY是用来排序，即决定窗口内，是按那种规则(字段)来排序的。

除了用PARTITION BY 指定窗口以外，还可以用框架指定窗口范围，如：ROWS n PRECEDING 即把当前数据行与其前两行(共三行)指定为窗口范围。

# 关于ROLLUP子句的问题：

如果GROUP BY多个字段，即分组条件需符合 AND 判断，交换product_tyoe 和 regist_date GROUP 结果不受影响(只是字段的显示顺序不一样)，但会影响 WITH ROLLUP 后的结果，放在前面的字段会被认为是需要ROLLUP(小计)的字段。

四、练习

练习题1：请说出针对本章中使用的 product(商品)表执行如下 SELECT 语句所能得到的结果。SELECT  product_id       ,product_name       ,sale_price       ,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price  FROM product答：对改行数据及其以前的行的范围内求最大值。练习题2：继续使用product表，计算出按照登记日期(regist_date)升序进行排列的各日期的销售单价(sale_price)的总额。排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位(也就是将其看作比其他日期都早)SELECT product_name, regist_date,      SUM(sale_price) OVER (PARTITION BY regist_date                             ORDER BY regist_date)  AS early_regist;                            练习题3：① 窗口函数不指定PARTITION BY的效果是什么？答：如果不用PARTITION BY指定窗口范围，则默认把整体看作一个窗口。② 为什么说窗口函数只能在SELECT子句中使用？实际上，在ORDER BY 子句使用系统并不会报错。答：根据SQL执行顺序，ORDER BY是在SELECT 执行后执行的，如果窗口函数写在ORDER BY子句后，则按照窗口函数结果排序，即没有确切的排序结果，虽然不影响已有的SELECT结果(即不会报错的原因)，但ORDER BY 无意义。