Oracle的分析函數（Analytic Functions）是一種強大的工具，它們允許你在查詢中對數據進行復雜的計算和分析。這些函數可以直接嵌入到SQL查詢中，提供對數據的即時分析，而不需要對數據進行額外的處理或分組。

以下是一些常用的Oracle分析函數及其應用場景：

1. AVG()：計算某列的平均值。

SELECT AVG(salary) FROM employees;

2. COUNT()：計算某列的非空值數量。

SELECT COUNT(*) FROM employees;
SELECT COUNT(department_id) FROM employees;

3. MIN() 和 MAX()：分別找出某列的最小值和最大值。

SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;

4. SUM()：計算某列的總和。

SELECT SUM(salary) FROM employees;

5. DENSE_RANK() 和 RANK()：計算某列的排名，DENSE_RANK()會為相同的值分配連續的排名，而RANK()則會為相同的值分配相同的排名，但下一個不同的值會獲得下一個排名。

SELECT DENSE_RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;
SELECT RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;

6. LAG() 和 LEAD()：這兩個函數允許你訪問結果集中的前一行或后一行數據。

SELECT salary, LAG(salary) OVER (ORDER BY salary) AS prev_salary FROM employees;
SELECT salary, LEAD(salary) OVER (ORDER BY salary) AS next_salary FROM employees;

7. FIRST_VALUE() 和 LAST_VALUE()：分別返回某列的第一個值和最后一個值。

SELECT FIRST_VALUE(salary) OVER (ORDER BY salary) AS first_salary FROM employees;
SELECT LAST_VALUE(salary) OVER (ORDER BY salary) AS last_salary FROM employees;

8. SUM() OVER() 和 AVG() OVER()：這些函數允許你在一個窗口上對數據進行累積計算。

SELECT salary, SUM(salary) OVER (ORDER BY salary rows BETWEEN unbounded PRECEDING AND CURRENT ROW) AS running_total_salary FROM employees;
SELECT salary, AVG(salary) OVER (ORDER BY salary rows BETWEEN unbounded PRECEDING AND CURRENT ROW) AS running_avg_salary FROM employees;

了解這些函數的使用方法并結合實際的數據分析需求，可以幫助你更有效地從Oracle數據庫中提取有價值的信息。