Oracle分析函数——函数列表
SUM：该函数计算组中表达式的累积和
MIN：在一个组中的数据窗口中查表达式的最小值
MAX：在一个组中的数据窗口中查表达式的最大值
AVG：用于计算一个组和数据窗口内表达式的平均值。
COUNT：对一组内发生的事情进行累积计数
-------------------------------------------------------------------------------------------------
RANK：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置
DENSE_RANK：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置
FIRST：从DENSE_RANK返回的集合中取出排在最前面的一个值的行
LAST：从DENSE_RANK返回的集合中取出排在最后面的一个值的行
FIRST_VALUE：返回组中数据窗口的第一个值
LAST_VALUE：返回组中数据窗口的最后一个值。
LAG：可以访问结果集中的其它行而不用进行自连接
LEAD：LEAD与LAG相反，LEAD可以访问组中当前行之后的行
ROW_NUMBER：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号
-------------------------------------------------------------------------------------------------
STDDEV：计算当前行关于组的标准偏离
STDDEV_POP：该函数计算总体标准偏离，并返回总体变量的平方根
STDDEV_SAMP：该函数计算累积样本标准偏离，并返回总体变量的平方根
VAR_POP：该函数返回非空集合的总体变量（忽略null）
VAR_SAMP：该函数返回非空集合的样本变量（忽略null）
VARIANCE：如果表达式中行数为1，则返回0，如果表达式中行数大于1，则返回VAR_SAMP
COVAR_POP：返回一对表达式的总体协方差
COVAR_SAMP：返回一对表达式的样本协方差
CORR：返回一对表达式的相关系数
-------------------------------------------------------------------------------------------------
CUME_DIST：计算一行在组中的相对位置
NTILE：将一个组分为"表达式"的散列表示
PERCENT_RANK：和CUME_DIST（累积分配）函数类似
PERCENTILE_DISC：返回一个与输入的分布百分比值相对应的数据值
PERCENTILE_CONT：返回一个与输入的分布百分比值相对应的数据值
RATIO_TO_REPORT：该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比
REGR_ (Linear Regression) Functions：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用
-------------------------------------------------------------------------------------------------
CUBE：按照OLAP的CUBE方式进行数据统计，即各个维度均需统计
ROLLUP:
SELECT
department_id,
manager_id,
employee_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
job_id
FROM employees
ORDER BY department_id,hire_date
Oracle分析函数实际上操作
对象是查询出的数据集，也就是说不需二次查询数据库，实际上就是oracle实现了一些我们自身需要编码实现的统计功能，对于简化开发工作量有很大的帮助，特别在开发第三方报表软件时是非常有帮助的。Oracle从8.1.6开始提供分析函数。
oracle分析函数的语法：
function_name(arg1,arg2,...)
over
(<partition-clause> <order-by-clause ><windowing clause>)
说明：
1. partition-clause数据记录集分组
2. order-by-clause数据记录集排序
3. windowing clause功能非常强大、比较复杂，定义分析函数在操作行的集合。有三种开窗方式: range、row、specifying。
--Partition by，按相应的值(manager_id)进行分组统计
SELECT
manager_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
AVG(salary) OVER (PARTITION BY manager_id) avg_salary
FROM employees;
--等同于上面
SELECT
a.manager_id,
a.hire_date,
a.salary,
b.avg_salary
FROM
(
SELECT
manager_id,
first_name||' '||last_name employee_name,
hire_date,
salary
FROM employees
) a,
(
SELECT
manager_id,
AVG(salary) avg_salary
FROM employees
GROUP BY manager_id
)
b
WHERE a.manager_id=b.manager_id
ORDER BY a.manager_id
--Order by按相应的值(hire_date)进行排序并累计统计
SELECT
manager_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
AVG(salary) OVER (ORDER BY hire_date)
FROM employees;
-
-Partition by Order by首先按相应的值(manager_id,hire_date)排序，并按order by的值(hire_date)进行累计统计
SELECT
manager_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date)
FROM employees;
--Partition by Order by首先按相应的值(manager_id,hire_date)排序，并按order by的值(hire_date)进行累计统计
--该平均值由当前员工和与之具有相同经理的前一个和后两个三者的平均数得来
SELECT
manager_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
FROM employees;
--Partition by Order by首先按相应的值(manager_id,hire_date)排序，并按order by的值(hire_date)进行累计统计
--该平均值由当前员工和与之具有相同经理，并且雇用时间在该员工时间之前的50天以内和在该员工之后的150天之内员工的薪水的平均值
-
-range为取值范围，估计只有数字和日期能够进行取值了
SELECT
manager_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING)
FROM employees;
--Partition by Order by首先按相应的值(manager_id,hire_date)排序，并按order by的值(hire_date)进行累计统计
--该平均值由当前员工和与之具有相同经理的平均
值
--每行对应的数据窗口是从第一行到最后一行
SELECT
manager_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date) avg_salary_part_order,
AVG(salary) OVER (PARTITION BY manager_id ) avg_salary_order,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) avg_salary_unbound1, --等同于仅partition时候的值
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) avg_salary_unbound2--等同于上面
FROM employees;
Oracle分析函数——SUM,AVG,MIN,MAX,COUNT
SUM
功能描述：该函数计算组中表达式的累积和。
SAMPLE：下例计算同一经理下员工的薪水累积值
MIN
功能描述：在一个组中的数据窗口中查表达式的最小值。
SAMPLE：下面例子中dept_min返回当前行所在部门的最小薪水值
MAX
功能描述：在一个组中的数据窗口中查表达式的最大值。
SAMPLE：下面例子中dept_max返回当前行所在部门的最大薪水值
AVG
功能描述：用于计算一个组和数据窗口内表达式的平均值。
SAMPLE：下面的例子中列c_mavg计算员工表中每个员工的平均薪水报告
SELECT
department_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
MIN(salary) OVER (PARTITION BY department_id order by hire_date) AS dept_min,
MAX(salary) OVER (PARTITION BY department_id order by hire_date) AS dept_max,
AVG(salary) OVER (PARTITION BY department_id order by hire_date) AS dept_avg,
SUM(salary) OVER (PARTITION BY department_id order by hire_date) AS dept_sum/*,
COUNT(*) OVER (ORDER BY salary) AS count_by_salary,
COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS count_by_salary_range*/
FROM employees
COUNT
功能描述：对一组内发生的事情进行累积计数，如果指定*或一些非空常数，count将对所有行计数，如果指定一个表达式，count返回表达式非空赋值的计数，当有相同值出现时，这些相等的值都会被纳入被计算的值；可以使用DISTINCT来记录去掉一组中完全相同的数据后出现的行数。
SAMPLE：下面例子中计算每个员工在按薪水排序中当前行附近薪水在[n-50,n+150]之间的行数，n表示当前行的薪水
例如，Philtanker的薪水2200，排在他之前的行中薪水大于等于2200-50的有1行，排在他之后的行中薪水小于等于2200＋150的行没有，所以count计数值cnt3为2（包括自己当前行）；cnt2值相当于小于等于当前行的SALARY值的所有行数
SELECT
department_id,
first_name||' '||last_name employee_name,
salary,
oracle 时间转换COUNT(*) OVER (ORDER BY salary) AS count_by_salary,
COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS count_by_salary_range
FROM employees
WHERE department
_id in (10,20,30);
Oracle分析函数——函数RANK,DENSE_RANK,FIRST,LAST…
RANK
功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为null时相等的）。然而，如果两行的确得到同样的排序，则序数将随后跳跃。若两行序数为1，则没有序数2，序列将给组中的下一行分配值3，DENSE_RANK则没有任何跳跃。
SAMPLE：下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号（注意与DENSE_RANK函数的区别）
DENSE_RANK
功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样
的数字序号（认为null时相等的）。密集的序列返回的时没有间隔的数
SAMPLE：下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号（注意与RANK函数的区别）
SELECT
department_id,
first_name||' '||last_name employee_name,
salary,
RANK() OVER (ORDER BY salary) AS RANK_ORDER,
DENSE_RANK() OVER (ORDER BY salary) AS DENSE_RANK_ORDER
FROM employees
SELECT
department_id,
first_name||' '||last_name employee_name,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary) AS RANK_PART_ORDER,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS DENSE_RANK_PART_ORDER
FROM employees
Oracle分析函数——统计分析函数
方差和标准差：
样本中各数据与样本平均数的差的平方和的平均数叫做样本方差；样本方差的算术平方根叫做样本标准差。样本方差和样本标准差都是衡量一个样本波动大小的量，样本方差或样本标准差越大，样本数据的波动就越大。
数学上一般用E{[X-E(X)]^2}来度量随机变量X与其均值E(X)即期望的偏离程度，称为X的方差。
方差是标准差的平方
方差和标准差。方差和标准差是测算离散趋势最重要、最常用的指标。方差是各变量值与其均值离差平方的平均数，它是测算数值型数据离散程度的最重要的方法。标准差为方差的平方根，用S表示。
StdDev返回expr的样本标准偏差。它可用作聚集和分析函数。它与stddev_samp的不同之处在于，当计算的输入数据只有一行时，stddev返回0，而stddev_samp返回null。
Oracle数据库中，标准偏差计算结果与variance用作集聚函数计算结果的平方根相
等。该函数参数可取任何数字类型或是任何能隐式转换成数字类型的非数字类型。
STDDEV
功能描述：计算当前行关于组的标准偏离。（Standard Deviation）
SAMPLE：
STDDEV_SAMP
功能描述：该函数计算累积样本标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平
方根相同。（Standard Deviation－Sample）
SAMPLE：
它与stddev_samp的不同之处在于，当计算的输入数据只有一行时，stddev返回0，而stddev_samp返回null。
SELECT
department_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
STDDEV_SAMP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS cum_sdev
FROM employees
WHERE department_id in (20,30,60);
STDDEV和STDDEV_SAMP的区别
SELECT
first_name||' '||last_name employee_name,
hire_date,
salary,
STDDEV(salary) OVER (ORDER BY hire_date) "StdDev",
STDDEV_SAMP(salary) OVER (ORDER BY hire_date) AS cum_sdev
FROM employees
VAR_POP
功能描述：（Variance Population）该函数返回非空集合的总体变量（忽略null），VAR_POP进行如下计算：
(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)
VAR_SAMP
功能描述：（Variance Sample）该函数返回非空集合的样本变量（忽略null），VAR_POP进行如下计算：
(SUM(expr*expr)-SUM(expr)*SUM(expr)/COUNT(expr))/(COUNT(expr)-1)
SAMPLE：
VARIANCE
功能描述：该函数返回表达式的变量，Oracle计算该变量如下：
如果表达式中行数为1，则返回0
如果表达式中行数大于1，则返回VAR_SAMP
SAMPLE：
SELECT
department_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
STDDEV(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "STDDEV",
STDDEV_SAMP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "STDDEV_SAMP",
VAR_POP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "VAR_POP",
VAR_SAMP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "VAR_SAMP",
VARIANCE(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "VARIANCE"
FROM employees
协方差分析是建立在方差分析和回归分析基础之上的一种统计分析方法。
方差分析是从质量因子的角度探讨因素不同水平对实验指标影响的差异。一般说来，质量因子是可以人为控制的。
回归分析是从数量因子的角度出发，通过建立回归方程来研究实验指标与一个(或几个)因子之间的数量关系。但大多数情况下，数量因子是不可以人为加以控制的。
两个不同参数之间的方差就是协方差
若两个随机变量X和Y相互独立，则E[(X-E(X))(Y-E(Y))]=0，因而若上述数学期望不为零，则X和Y必不是相互独立的，亦即它们之间存在着一定的关系。
定义
E[(X-E(X))(Y-E(Y))]称为随机变量X和Y的协方差，记作COV(X，Y)，即COV(X，Y)=E[(X-E(X))(Y-E(Y))]。
COVAR_POP
功

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。