目录
文章导航
一、什么是子查询
(1) 子查询(Subquery)是数据库查询语言(如SQL)中的一个重要概念,它指的是在一个查询语句内部嵌套另一个或多个查询语句。子查询可以出现在SELECT、INSERT、UPDATE或DELETE语句中,用于从数据库表中检索数据,或者基于子查询的结果执行相应的操作。
(2) 子查询可以返回一个值、一行、一列或多行多列的结果。当子查询返回单个值时,它通常用于比较操作(如等于、大于、小于等)。当子查询返回多行多列的结果时,它通常用于IN、EXISTS等操作符。
(3) 子查询的一个主要优点是它们可以在一个查询中执行多个操作,从而提高查询的灵活性和效率。然而,过度使用复杂的子查询也可能导致查询性能下降,因此需要根据具体的需求和数据库性能来合理设计子查询
二、子查询的分类
1.标量子查询(Scalar Subquery):
返回一个单一值的子查询。这种子查询通常用在比较操作中,如等于(=)、不等于(<>)、大于(>)、小于(<)等。
假设我们有一个products表,我们想找出价格高于平均价格的产品
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
2.列子查询(Column Subquery):
返回一个列值的子查询,通常用在IN或NOT IN操作符中。
假设我们有两个表:orders和customers。我们想要找出下单了的所有客户的ID
SELECT customer_id
FROM orders
WHERE customer_id IN (SELECT DISTINCT customer_id FROM customers);
3.行子查询(Row Subquery):
返回一个或多个行的子查询,通常用在比较操作符中,如=或<>,来比较整个行。
假设我们有一个employees表,并且我们想要找出与某个特定员工(例如,employee_id为100)薪水相同的所有员工
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE (salary, employee_id) = (SELECT salary, employee_id FROM employees WHERE employee_id = 100);
4.嵌套子查询(Nested Subqueries):
嵌套子查询是指在一个子查询内部包含另一个子查询
假设我们有两个表:employees 和departments 。我们想要找出所有位于纽约的部门包含的员工信息
SELECT *
FROM employees
WHERE department_id = (SELECT department_id FROM
(SELECT department_id FROM departments WHERE location = 'New York') );
5.WITH子句(Common Table Expressions, CTEs):
虽然这不是传统意义上的子查询,但它是Oracle中用于创建临时结果集的一种强大工具。它允许您在主查询之前定义一个或多个临时表,并在主查询中引用这些表。
使用WITH子句创建了一个名为high_salary_employees的CTE,它包含了薪水高于平均薪水的员工。然后,我们在主查询中引用了这个CTE,并与departments表进行了连接,以获取这些员工所在的部门名称
WITH high_salary_employees AS (--建立临时表high_salary_employees
SELECT employee_id, first_name, last_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
)
SELECT h.employee_id, h.first_name, h.last_name, d.department_name
FROM high_salary_employees h--使用临时表high_salary_employees
JOIN departments d ON h.department_id = d.department_id;
三、什么是窗口函数
窗口函数(Window Functions)是SQL中的一个特性。窗口函数允许用户执行计算,这些计算跨越多行数据,通常与OVER()子句一起使用来定义这些行的“窗口”或范围。与聚合函数(如SUM(), AVG(), COUNT()等)不同,窗口函数不会减少结果集中的行数,而是为每一行生成一个计算结果。
窗口函数通常用于分析查询,例如计算移动平均、累计总和、排名等。
OVER()子句 :定义了窗口的范围和排序方式,它通常包含PARTITION BY和ORDER BY子句。
PARTITION BY :将结果集分成多个分区,每个分区内的行都可以有自己的窗口计算。
ORDER BY :在每个分区内定义行的排序方式。
四、常用的窗口函数
聚合窗口函数:如SUM(), AVG(), MIN(), MAX()等,但它们在窗口函数中的行为与普通的聚合函数不同。
排名窗口函数:如ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()等,用于为结果集中的行分配排名。
值窗口函数:如FIRST_VALUE(), LAST_VALUE(), LEAD(), LAG()等,用于访问与当前行相关的其他行的值。
1.ROW_NUMBER()
为结果集中的每一行分配一个唯一的数字,顺序为1,2,3,4,5……
select a.*,
row_number()OVER(PARTITION BY a.TIME ORDER BY a.suc) row_number1,--按日期进行分区,按接口成功率进行排序
row_number()OVER(PARTITION BY a.SYSNAME ORDER BY a.suc DESC) row_number2,--按系统进行分区,按接口成功率进行倒序排序
dense_rank()OVER(PARTITION BY a.SYSNAME ORDER BY a.suc DESC) dense_rank,--按系统进行分区,按接口成功率进行倒序排序
rank()OVER(PARTITION BY a.SYSNAME ORDER BY a.suc DESC) rank--按系统进行分区,按接口成功率进行倒序排序
from test_20240229 a
2.DENSE_RANK()
为结果集中的每一行分配一个排名,相同的值会获得相同的排名,但不会跳过之后的排名,顺序为1,2,2,3,4,4,4,5……
3.RANK()
为结果集中的每一行分配一个排名,相同的值会获得相同的排名,但会跳过之后的排名,顺序为1,2,2,4,5,5,5,8……
4.LEAD(), LAG()
LEAD(expr, offset, default) 返回当前行之后指定偏移量的行的值。如果当前行是最后一行或偏移量超出范围,则返回默认值(如果有)
LAG(expr, offset, default) 返回当前行之前指定偏移量的行的值。如果当前行是第一行或偏移量超出范围,则返回默认值(如果有)
select a.*,
lead(a.TIME)OVER(PARTITION BY a.sysname ORDER BY a.TIME) lead1,--按sysname进行分区,返回下一行的指定字段值
lead(a.TIME,2)OVER(PARTITION BY a.sysname ORDER BY a.TIME) lead2,--按sysname进行分区,返回下n行的指定字段值
lead(a.TIME,2,a.TIME)OVER(PARTITION BY a.sysname ORDER BY a.TIME) lead3,--按sysname进行分区,返回下n行的指定字段值,空白部分用a.TIME进行填充
lag(a.TIME)OVER(PARTITION BY a.sysname ORDER BY a.TIME) lag1,--按sysname进行分区,返回上一行的指定字段值
lag(a.TIME,2)OVER(PARTITION BY a.sysname ORDER BY a.TIME) lag2,--按sysname进行分区,返回上n行的指定字段值
lag(a.TIME,2,a.TIME)OVER(PARTITION BY a.sysname ORDER BY a.TIME) lag3--按sysname进行分区,返回上n行的指定字段值,空白部分用a.TIME进行填充
from test_20240229 a;