【三 （3）数据处理工具之 SQL （子查询、常用窗口函数）】

文章导航

【一 简明数据分析进阶路径介绍（文章导航）】

一、什么是子查询

(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;