精通Oracle系统中的全部分析函数（oracle全部分析函数）

精通Oracle：系统中的全部分析函数

Oracle中的分析函数是SQL语言的有力工具，它们可以大大简化和改进SQL查询的效率和灵活性。分析函数采用OVER()函数实现，它使得可以将计算窗口（window）的结果集作为查询的一部分。本文将介绍Oracle系统中全部的分析函数。

我们需要先创建一个测试表格：

CREATE TABLE sales (product VARCHAR(10), region VARCHAR(10), month DATE, amount NUMBER);
INSERT INTO sales VALUES ('Apple', 'North', '2019-01-01', 1000);
INSERT INTO sales VALUES ('Apple', 'South', '2019-01-01', 2000);
INSERT INTO sales VALUES ('Apple', 'North', '2019-02-01', 1500);
INSERT INTO sales VALUES ('Apple', 'South', '2019-02-01', 2500);
INSERT INTO sales VALUES ('Orange', 'North', '2019-01-01', 3000);
INSERT INTO sales VALUES ('Orange', 'South', '2019-01-01', 4000);
INSERT INTO sales VALUES ('Orange', 'North', '2019-02-01', 5000);
INSERT INTO sales VALUES ('Orange', 'South', '2019-02-01', 6000);

现在我们来看看Oracle系统中全部的分析函数。

1. ROW_NUMBER()

ROW_NUMBER()函数可用于为结果集中的每一行分配一个唯一的序号。这里的行序号是按照ORDER BY子句中指定的列排序的顺序计算的。

SELECT product, region, month, ROW_NUMBER() OVER (ORDER BY region, month) AS "Row_Number" FROM sales;

2. RANK()

RANK()函数可以为结果集中的每一行分配一个排名值。如果多个行具有相同的值，则它们将被分配相同的排名值。值相同时会存在空位，排名不连续。

SELECT product, region, month, amount,
RANK() OVER (PARTITION BY product ORDER BY amount DESC) AS "Rank"
FROM sales;

3. DENSE_RANK()

DENSE_RANK()函数与RANK()函数类似，不同之处在于，如果多个行具有相同的值，则它们将被分配相同的排名值，而且每一个值都会有一个连续的排名值。

SELECT product, region, month, amount,
DENSE_RANK() OVER (PARTITION BY product ORDER BY amount DESC) AS "Dense_Rank"
FROM sales;

4. FIRST_VALUE()

FIRST_VALUE()函数用于返回按ORDER BY子句中指定的列排序后的第一行的值。PARTITION BY 子句定义用于计算窗口函数的分区。

SELECT product, region, amount,
FIRST_VALUE(amount) OVER (PARTITION BY product ORDER BY amount DESC) as "First_Value"
FROM sales;

5. LAST_VALUE()

LAST_VALUE()函数与FIRST_VALUE()函数类似，不同之处在于它返回按ORDER BY子句中指定的列排序后的最后一行的值。

SELECT product, region, amount,
LAST_VALUE(amount) OVER (PARTITION BY product ORDER BY amount DESC) as "Last_Value"
FROM sales;

6. LEAD()

LEAD()函数返回按升序排序的当前行之后指定行偏移量的值。如果没有指定行，则会返回NULL。

SELECT product, region, month, amount,
LEAD(amount) OVER (PARTITION BY product ORDER BY month) as "Lead_Value"
FROM sales;

7. LAG()

LAG()函数与LEAD()函数类似，不同之处在于它返回按降序排序的当前行之前指定行偏移量的值。

SELECT product, region, month, amount,
LAG(amount) OVER (PARTITION BY product ORDER BY month) as "Lag_Value"
FROM sales;

8. SUM()

SUM()函数用于返回分区中指定列的总和。可以使用PARTITION BY子句将相同的值分组为不同的分区。

SELECT product, region, month, amount,
SUM(amount) OVER (PARTITION BY region ORDER BY month) as "Sum"
FROM sales;

9. AVG()

AVG()函数返回分区中指定列的平均值。可以使用PARTITION BY子句将相同的值分组为不同的分区。

SELECT product, region, month, amount,
AVG(amount) OVER (PARTITION BY region ORDER BY month) as "Avg"
FROM sales;

10. MAX()

MAX()函数返回分区中指定列的最大值。可以使用PARTITION BY子句将相同的值分组为不同的分区。

SELECT product, region, month, amount,
MAX(amount) OVER (PARTITION BY region ORDER BY month) as "Max"
FROM sales;

11. MIN()

MIN()函数返回分区中指定列的最小值。可以使用PARTITION BY子句将相同的值分组为不同的分区。

SELECT product, region, month, amount,
MIN(amount) OVER (PARTITION BY region ORDER BY month) as "Min"
FROM sales;

至此，我们已经介绍了Oracle系统中全部的分析函数。它们可以帮助我们更快地、更灵活地处理数据，提高查询效率和分析能力。在实际应用中，我们可以根据具体的需求来选择合适的分析函数。