Oracle基础知识与开窗函数详解

"Oracle数据库基础知识与开窗函数详解" 在Oracle数据库中，开窗函数是一种强大的工具，它允许我们在单个SQL查询中对数据进行复杂的分析和聚合操作，而无需使用子查询或临时表。以下是对标题和描述中提及的知识点的详细解释： ### 1. `SUM() OVER (PARTITION BY ... ORDER BY ...)` `SUM() OVER (ORDER BY ...)` 是开窗函数的一种用法，用于计算基于指定排序顺序的累计和。例如： - **1.1** `SUM(v2) OVER (ORDER BY v2)` 按照`v2`的值升序排序，逐行累加`v2`的值，从第一行开始累积到当前行。 - **1.2** `SUM(v2) OVER (PARTITION BY v1 ORDER BY v2)` 首先按`v1`分组，然后在每个组内按照`v2`的值进行排序并累加。 - **1.3** `SUM(v2) OVER (PARTITION BY v1)` 在`v1`分组内从第一行到最后一行累加`v2`。 - **1.4** `DISTINCT v1, (SELECT SUM(v2) OVER (PARTITION BY v1))` 使用`DISTINCT`去除`v1`列的重复数据，同时计算每个唯一`v1`值的`v2`之和。 - **1.5** 反向累加只需将`ORDER BY`后的列改为降序即可，如`ORDER BY v2 DESC`。 ### 2. `ROW_NUMBER() OVER (ORDER BY ...)` `ROW_NUMBER() OVER (ORDER BY ...)` 为查询结果中的每一行分配一个唯一的序列号，根据指定的列进行排序： - **2.1** `ROW_NUMBER() OVER (ORDER BY col_1[,col_2])` 根据`col_1`和可选的`col_2`列升序排列，并为每一行赋予一个不同的序列号。 - **2.2** `ROW_NUMBER() OVER (PARTITION BY col_n[,col_m] ORDER BY col_1[,col_2])` 先按`col_n`和`col_m`分组，然后在每个组内按照`col_1`和`col_2`排序，返回排序后的行号。 ### 3. `ROWNUM` `ROWNUM` 是Oracle中的一个伪列，它自动为查询返回的每一行分配一个从1开始的连续整数。但是，`ROWNUM`不能与表名关联，并且只能与小于或等于运算符（`<`, `<=`）一起使用，用于限制返回的行数。 ### 4. `ROUND(XXX, 2)` `ROUND(XXX, 2)` 函数用于四舍五入数字`XXX`到指定的小数位数，这里是两位。如果省略第二个参数，则默认四舍五入到最接近的整数。 ### 5. `LISTAGG(field, ',') WITHIN GROUP (ORDER BY field)` `LISTAGG()` 函数将列的值合并成一个字符串，每个值之间由指定的分隔符分隔： - **例1** `LISTAGG(a, '--') WITHIN GROUP (ORDER BY a) GROUP BY b` 根据`b`分组，将每个组内`a`列的值按照`a`的顺序连接在一起，分隔符为'--'。 - **例2** `LISTAGG(a, '--') WITHIN GROUP (ORDER BY a) OVER (PARTITION BY b)` 根据`b`分组，但不进行合并，而是保持每个组内`a`列的值按顺序连接，分隔符同样为'--'。 ### 6. 其他开窗函数 Oracle还提供了其他开窗函数，如`RANK()`, `DENSE_RANK()`, `LEAD()`, `LAG()`, `FIRST_VALUE()`, `LAST_VALUE()` 等，它们在数据分析和报表生成时非常有用。 理解并熟练运用这些开窗函数，能极大地提高你在处理复杂数据查询时的效率，帮助你编写出更加精炼和高效的SQL语句。