Oracel函数--分析函数、OLAP函数

Oracle常用分析函数

 
1.1简单指标函数:
SUM        :该函数计算组中表达式的累积和

MIN        :在一个组中的数据窗口中查找表达式的最小值

MAX        :在一个组中的数据窗口中查找表达式的最大值

AVG        :用于计算一个组和数据窗口内表达式的平均值。

COUNT      :对一组内发生的事情进行累积计数
1.2简单应用函数:

RANK               :根据ORDER BY子句中表达式的值,从查询返回的每一行,计算它们与其它行的相对位置

DENSE_RANK :根据ORDER BY子句中表达式的值,从查询返回的每一行,计算它们与其它行的相对位置

FIRST               :从DENSE_RANK返回的集合中取出排在最前面的一个值的行

LAST                :从DENSE_RANK返回的集合中取出排在最后面的一个值的行

FIRST_VALUE :返回组中数据窗口的第一个值

LAST_VALUE   :返回组中数据窗口的最后一个值。

LAG                  :可以访问结果集中的其它行而不用进行自连接

LEAD                :LEAD与LAG相反,LEAD可以访问组中当前行之后的行

ROW_NUMBER:返回有序组中一行的偏移量,从而可用于按特定标准排序的行号

-------------------------------------------------------------------------------------------------

1.3分析函数:
STDDEV     :计算当前行关于组的标准偏离

STDDEV_POP:该函数计算总体标准偏离,并返回总体变量的平方根

STDDEV_SAMP:该函数计算累积样本标准偏离,并返回总体变量的平方根

VAR_POP    :该函数返回非空集合的总体变量

VAR_SAMP   :该函数返回非空集合的样本变量

VARIANCE   :如果表达式中行数为1,则返回0,如果表达式中行数大于1,则返回VAR_SAMP

COVAR_POP  :返回一对表达式的总体协方差

COVAR_SAMP:返回一对表达式的样本协方差(与总体协方差的区别,空值返回0)

CORR              :返回一对表达式的相关系数

REGR_SLOPE : 返回非空值对拟合的线性回归斜率(相当于COVER_POP(a,b)/VAR_POP(b))

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      :

二、OLAP函数
完成本章学习后,将能够:
使用标准SQL进行数据挖掘
在标准SQL中使用OLAP函数
执行统计方面的采样(samplings)、排队(rankings)和分位数(quantiles)
了解 OLAP统计函数

2.1 OLAP函数简介
OLAP即联机分析处理(On-Line Analytical Process)。Teradata数据库本身提供
了一些OLAP函数,包括:
RANK - 排队(Rankings)
QUANTILE - 分位数(Quantiles)
CSUM - 累计(Cumulation)
MAVG - 移动平均(Moving Averages)
MSUM - 移动合计(Moving Sums)
MDIFF - 移动差分(Moving Differences)
MLINREG - 移动线性回归(Moving Linear Regression)

2.2 OLAP函数与聚合函数对比
对数据进行分组操作 (类似于GROUP BY 子句)
能够使用QUALIFY子句过滤组 (类似于HAVING 子句)
OLAP函数又与聚合函数不同,因为:
返回满足条件的每行的数据值,而不是组的值
不能在子查询内使用
OLAP函数可以对下面的数据库对象或动作使用:
Tables (Perm, Temp, Derived)
Views
INSERT/SELECT

2.3累计函数
累计函数(CSUM) 计算一列的连续的累计的值。语法为:
CSUM(colname, sort list)
表'daily_sales'在许多查询中都将使用,其定义如下。
CREATE SET TABLE daily_sales ,NO FALLBACK
,NO BEFORE JOURNAL
,NO AFTER JOURNAL

(itemid INTEGER
,salesdate DATE FORMAT 'YY/MM/DD'
,sales DECIMAL(9,2))
PRIMARY INDEX ( itemid );
问题
创建item 10从1998年1月和2月的连续的日汇总报表。

解答
SELECT salesdate, sales, csum(sales, salesdate)
FROM daily_sales
WHERE salesdate BETWEEN 980101 AND 980301
AND itemid = 10;
结果
salesdate sales Csum
98/01/01 150.00 150.00
98/01/02 200.00 350.00
98/01/03 250.00 600.00
98/01/05 350.00 950.00
98/01/10 550.00 1500.00
98/01/21 150.00 1650.00
98/01/25 200.00 1850.00
98/01/31 100.00 1950.00
98/02/01 150.00 2100.00
98/02/03 250.00 2350.00

98/02/06 350.00 2700.00
98/02/17 550.00 3250.00
98/02/20 450.00 3700.00
98/02/27 350.00 4050.00

在上面的报表中,每行都代表item 10一天的数据。注意,不是每天都销售了
item 10。最右边的列代表其在两个月内的累计销售额。
如果想每月重新累计,该怎么办?
累计汇总可以使用GROUP BY子句在特殊的点复位,即重新开始累计。注
意,OLAP函数和标准聚合函数(SUM, COUNT,AVG, MIN, MAX) 是不能在同一查
询中兼容的。因此,对这类查询使用GROUP BY,将会起分隔的作用。
问题
创建item 10从1998年1月和2月的连续的日汇总报表,并且每月重新开始累
计。

解答
SELECT salesdate, sales, csum(sales, salesdate)
FROM daily_sales ds, sys_calendar.calendar sc
WHERE ds.salesdate = sc.calendar_date
AND sc.year_of_calendar = 1998
AND sc.month_of_year in (1,2)

AND ds.itemid = 10
GROUP BY sc.month_of_year;

结果
salesdate sales Csum
98/01/01 150.00 150.00
98/01/02 200.00 350.00
98/01/03 250.00 600.00
98/01/05 350.00 950.00
98/01/10 550.00 1500.00
98/01/21 150.00 1650.00
98/01/25 200.00 1850.00
98/01/31 100.00 1950.00
98/02/01 150.00 150.00 重新累计
98/02/03 250.00 400.00
98/02/06 350.00 750.00
98/02/17 550.00 1300.00
98/02/20 450.00 1750.00
98/02/27 350.00 2100.00

要回答上面的问题,你需要得到年和月。连接到系统日历,可以获得月。
GROUP BY子句累计重新开始,告诉系统"当月改变时,累计值清零"。

移动平均函数
移动平均函数(MAVG) 基于预定的行数(查询宽度)计算一列的移动平均值。如
果行数小于这个宽度,则基于前面已有的行计算平均值。使用移动平均的语法是:
MAVG(colname, n, sortlist)
colname = 计算移动平均值的列
n = 行数(< 4096),计算时将使用,包括当前行('n' 也称为平均宽度)
sortlist = 确定行顺序的列

问题
显示item 10基于7天宽度的移动平均值。
解答
SELECT salesdate,itemid, sales, MAVG(sales, 7, salesdate)
FROM daily_sales;
结果
salesdate itemid sales MAvg
98/01/01 10 150.00 150.00

98/01/02 10 200.00 175.00 前2行的平均值
98/01/03 10 250.00 200.00
98/01/05 10 350.00 237.50
98/01/10 10 550.00 300.00
98/01/21 10 150.00 275.00
98/01/25 10 200.00 264.29 前7行的平均值
98/01/31 10 100.00 257.14
98/02/01 10 150.00 250.00
98/02/03 10 250.00 250.00
98/02/06 10 350.00 250.00
98/02/17 10 550.00 250.00
98/02/20 10 450.00 292.86

计算时,使用当前行和前面n-1行。如果行数小于n-1,则使用前面所有行。缺
省,按照sortlist中的列升序排列。

2.4移动汇总函数
移动汇总函数(MSUM) 基于预定的查询宽度计算一列的移动汇总值。宽度决
定有多少行合计到汇总值中。如果前面的行数小于n,则仅使用前面所有行。移动
汇总函数的语法是:
MSUM(colname, n, sortlist)
注意:'n'是一个整数,表示有多少行参加汇总求和。

问题
显示item 10基于3天宽度的移动汇总值。
解答
SELECT salesdate, itemid, sales, msum(sales, 3, salesdate)
FROM daily_sales;

结果
salesdate itemid sales MSum
98/01/01 10 150.00 150.00
98/01/02 10 200.00 350.00 前面2行的总和
98/01/03 10 250.00 600.00
98/01/05 10 350.00 800.00
98/01/10 10 550.00 1150.00
98/01/21 10 150.00 1050.00
98/01/25 10 200.00 900.00 前面3行的总和
98/01/31 10 100.00 450.00
98/02/01 10 150.00 450.00
98/02/03 10 250.00 500.00
98/02/06 10 350.00 750.00
98/02/17 10 550.00 1150.00
98/02/20 10 450.00 1350.00
98/02/27 10 350.00 1350.00

移动汇总(MSum)与移动平均(MAvg)有下列相同的规则:
使用当前行和前n-1行
如果行数小于n-1,使用前面所有的行
缺省按照sortlist中的列升序排列

2.5移动差分函数
移动差分函数(MDIFF) 基于预定的查询宽度计算一列的移动差分值。宽度决
定有多少行参与计算。如果前面的行数小于n,则产生一个空值(null)代表差值。移
动差分函数的语法是:
MDIFF(colname, n, sortlist)
宽度n<=4096

 问题
显示item 10基于3天宽度的移动差分值。

解答
SELECT salesdate, itemid, sales, mdiff(sales, 3, salesdate)
FROM daily_sales;

结果

salesdate itemid sales MDiff
98/01/01 10 150.00 ?
98/01/02 10 200.00 ?
98/01/03 10 250.00 ?
98/01/05 10 350.00 200.00 2行的差值
98/01/10 10 550.00 350.00
98/01/21 10 150.00 -100.00
98/01/25 10 200.00 -150.00
98/01/31 10 100.00 -450.00
98/02/01 10 150.00 .00
98/02/03 10 250.00 50.00 2行的差值
98/02/06 10 350.00 250.00
98/02/17 10 550.00 400.00
98/02/20 10 450.00 200.00
98/02/27 10 350.00 .00

Mdiff列的值表示某日销售额与三天前日销售额的差。
MDIFF的用法与MAvg 和MSum 有一些不同:
使用当前行和前面的第n行

如果前面没有第n行,返回空值(null)
缺省按照sortlist 中的列升序排列

2.6排队函数

准备数据表
在数据库Customer_Service中增加一个销售表,后面的OLAP查询将使用这个
表。
表定义
CREATE TABLE salestbl
(storeid INTEGER,
prodid CHAR(1),
sales DECIMAL(9,2));
表中数据为:

storeid prodid sales
1001 A 100000.00
1001 C 60000.00
1001 D 35000.00
1001 F 150000.00
1002 A 40000.00
1002 C 35000.00
1002 D 25000.00
1003 A 30000.00

1003 B 65000.00
1003 C 20000.00
1003 D 50000.00

简单排队
排队函数对一列进行排队,可以按照升序或者降序排队。缺省,输出结果按
照降序排队,对应的排队名次是升序。换句话说,如果一个销售代表在某季度的销
售额最高,其排名为1,这是一个最小的值。
排队函数(RANK)的语法是:
RANK(colname)
这里,colname表示排队的列名,其结果降序排列。
问题
显示商店1001的产品销售额排队。

解答
SELECT storeid, prodid, sales, RANK(sales)
FROM salestbl
WHERE storeid = 1001;

结果
storeid prodid sales Rank
1001 F 150000.00 1
1001 A 100000.00 2
1001 C 60000.00 3
1001 D 35000.00 4
如上所示,列Rank的最大值代表最低的销售额。
使用排队函数的规则包括:
WHERE子句限定参与排队的记录。
应用排队函数时,缺省最大的数名次最低。
缺省顺序是按排队列的降序。

带限定的排队
QUALIFY子句限制排队输出的最终结果。QUALIFY子句与HAVING子句类
似,使输出限制在一定范围内。
问题
按商店得到销售前3名的产品。

解答
SELECT storeid, prodid, sales, rank(sales)
FROM salestbl
GROUP BY storeid
QUALIFY rank(sales) <= 3;
结果
storeid prodid sales Rank
1001 A 100000.00 1
1001 C 60000.00 2
1001 D 35000.00 3
1002 A 40000.00 1
1002 C 35000.00 2
1002 D 25000.00 3
1003 B 65000.00 1
1003 D 50000.00 2
1003 A 30000.00 3

上面的例子中,GROUP BY子句不是做聚合,它实际上是改变查询的范围,
也引起排序。
使用排队函数的规则包括:
对某列的每行都应用了排队
GROUP BY子句控制范围,如商店内的销售额排队(注意- 查询中并没有聚
合)

排队中的变化
GROUP BY子句可以和RANK函数一起使用,改变排队的范围。没有GROUP
BY子句,缺省的范围是排队的列。前面的例子中,范围是销售额,排队是基于销
售额进行的。
排队的列是缺省的排序列,GROUP BY子句增加了一级排序。
缺省排序 - 按排队列降序
次排序 - 按GROUP BY中的列升序

问题
得到前3名的销售额 - 任何商店、任何产品。

解答
SELECT storeid
,prodid
,sales
,rank(sales)
FROM salestbl
GROUP BY storeid, prodid
QUALIFY RANK(sales) <= 3
;

结果
storeid prodid sales Rank
1001 A 100000.00 1
1001 C 60000.00 1
1001 D 35000.00 1
1001 F 150000.00 1
1002 A 40000.00 1
1002 C 35000.00 1
1002 D 25000.00 1
1003 A 30000.00 1
1003 B 65000.00 1
1003 C 20000.00 1
1003 D 50000.00 1

排名都是1的原因是使用了GROUP BY子句,范围变成了商店内的产品的销售
额。因为每种产品在一个商店中只有一种,所以排名都是1。
现在去掉GROUP BY子句,范围变成了缺省的销售额。

现在去掉GROUP BY子句,范围变成了缺省的销售额。

解答
SELECT storeid
,prodid
,sales
,rank(sales)
FROM salestbl
QUALIFY RANK(sales) <= 3
;
结果
storeid prodid sales Rank
1001 F 150000.00 1
1001 A 100000.00 2
1003 B 65000.00 3

2.6.1带聚合的排队
由于上面的原因,OLAP函数与聚合函数在同一查询内不兼容,否则将引起二
意性。替代的办法,是使用导出表(derived)或临时表来解决这类问题。临时表包含
聚合信息,再对临时表进行OLAP查询。
问题
获得销售额在前3名的产品,跨所有商店。

解答
SELECT t.prodid, t.sumsales, RANK(t.sumsales)
FROM (SELECT a.prodid, sum(a.sales) FROM salestbl a
GROUP BY 1) AS t(prodid, sumsales)
QUALIFY RANK(sumsales) <= 3;
注意:'t' 是一个从salestbl产生的导出表,包含两列:prodid and sumsales。
结果
prodid Sumsales Rank
A 170000.00 1
C 115000.00 2
D 110000.00 3

缺省顺序是按照列sumsales的降序。这里主查询中没有GROUP BY子句,范围
是Sumsales。
问题
获得销售额在最后3名的产品,跨所有商店。

解答
SELECT t.prodid, t.sumsales, RANK(t.sumsales)
FROM (SELECT a.prodid, sum(a.sales)
FROM salestbl a
GROUP BY 1) AS t(prodid, sumsales)
QUALIFY RANK(sumsales ASC) <= 3;
结果
prodid sumsales Rank
B 65000.00 5
D 110000.00 4
C 115000.00 3
注意:名次可能大于3。

这个查询与前面的唯一区别是QUALIFY子句中的sumsales为升序。为什么会
得到大于3的结果呢?
答案是按sumsales的升序(sumsales ASC) 排列后,会给我们最底下的3行数据。
QUALIFY子句的意思是‘按销售额升序排列,得到前3行数据’。

解答
SELECT t.prodid, t.sumsales, rank(t.sumsales)
FROM (SELECT a.prodid, sum(a.sales) FROM salestbl a
GROUP BY 1) as t(prodid, sumsales)
QUALIFY RANK(sumsales asc) <= 3;
结果
prodid sumsales Rank

B 65000.00 5
D 110000.00 4
C 115000.00 3
现在做同样的查询,这次包括ORDER BY子句。
问题
得到销售最差的3种产品,跨所有商店,并按照产品号(prodid)排序。

解答
SELECT t.prodid, t.sumsales, rank(t.sumsales)
FROM (SELECT a.prodid, sum(a.sales) from salestbl a
GROUP BY 1) as t(prodid, sumsales)
QUALIFY RANK(sumsales asc) <= 3
ORDER BY 1;
结果
Prodid sumsales Rank
B 65000.00 5
C 115000.00 3
D 110000.00 4

看上面的结果,同样的3行数据,但顺序不一样。ORDER BY子句中的列成为
主排序列。

2.7分位数函数

分位数用于将一组记录分成大致相等的部分。最常见的分位数是百分位数(基
于100),也有4分位数 (基于4)、3分位数 (基于3) 和10分位数 (基于10)。注意,缺省
地,分位数的列和值都按升序输出。

可以使用ORDER BY子句重新排序。如前所述,聚合函数不能和OLAP函数混
合使用,如果要使用聚合函数,可以使用导出表或临时表。分位数函数的语法是:
QUANTILE (quantile_constant,sortlist)

quantile_constant = 定义分位数大小的常量。
sortlist = 用于分割和排序的列。
问题
显示产品销售额的百分位。
解答
SELECT t.prodid, t.sumsales, QUANTILE (100,sumsales)
FROM (SELECT a.prodid, sum(a.sales) from salestbl a
GROUP BY 1) as t(prodid, sumsales);
注: 宽度= 100,分位数的范围是0-99 。

结果
prodid sumsales Quantile
B 65000.00 0

D 110000.00 20
C 115000.00 40
F 150000.00 60
A 170000.00 80
上述查询使用导出表(derived table)进行聚合,使用缺省的分位数升序排序。可
以使用ORDER BY子句重新排序。总共有5个产品,所以等分为20% 。
问题
显示销售额的百分位数为60+的产品。

解答
SELECT t.prodid, t.sumsales, QUANTILE(100,sumsales)
FROM (SELECT a.prodid, sum(a.sales) from salestbl a
GROUP BY 1) as t(prodid, sumsales)
QUALIFY QUANTILE(100,sumsales) >= 60;
结果
prodid sumsales Quantile
F 150000.00 60
A 170000.00 80

分位与聚合
下面第一个查询返回公司内薪水在前25%的雇员。第二个查询得到薪水前25%
的雇员的薪水的总和,解决办法是通过使用导出表,混合聚合函数和OLAP函数。
问题
显示公司前25%的所有员工的薪水。

解答
SELECT salary_amount, QUANTILE (100, salary_amount)
FROM employee
QUALIFY QUANTILE (100, salary_amount) >=75;

结果
salary_amount Quantile
53625.00 76
54000.00 80
56500.00 84
57700.00 88
66000.00 92
100000.00 96
问题
计算公司前25%的薪水的总和。

解答1
SELECT sum(salary_amount)
FROM employee QUALIFY QUANTILE (100, salary_amount) >= 75;
结果
****Error - Can't mix stat functions with aggregates***
解答2
SELECT sum(sals)
FROM (SELECT salary_amount from employee
QUALIFY QUANTILE(100, salary_amount) >= 75)

temp(sals);
结果
Sum(sals)
387825.00

2.8分位与排序
下面的例子中,分位数函数表示如下:
QUANTILE (100, salary_amount, employee_number)

这里 ‘employee_number’ 是用于处理有相同薪水员工的情形。记住,
‘salary_amount’ (缺省是DESC) 告诉我们salary_amount将与分位数值一样降序排
序。使用employee_number,进一步保证在相同薪水的情况下,employee_number
将与分位数值一样降序排序。
问题
显示薪水最低的25%的所有员工。
解答1
SELECT employee_number, salary_amount,

QUANTILE (100, salary_amount , employee_number)
FROM employee
QUALIFY QUANTILE (100, salary_amount) < 25;

结果
employee_number salary_amount Quantile
1014 24500.00 03
1013 24500.00 00
1001 25525.00 07
1023 26500.00 11
1008 29250.00 15
1006 29450.00 19
1009 31000.00 23

2.9移动线性回归函数

移动线性回归函数MLINREG基于一个序列数据对得到一个预测值。序列对包
含一个独立的变量和一个依赖的变量。MLINREG函数基于前面的n对数预测依赖
变量的值。n叫做宽度。
MLINREG 的语法是:
MLINREG (y, n, x)

函数基于前面n-1行计算y值。y是依赖变量。x是独立变量,也是排序值。x和y
都必须是数字列,不能是日期。宽度n必须>=3 且<=4096。
例1
给出序列对x 和y的集合,使用线性回归预测y值,宽度是3。
解答
SELECT x, y, MLINREG(y, 3, x)
FROM linreg;

给出序列对x 和y的集合,使用线性回归预测y值,宽度是6。
解答
SELECT x, y, MLINREG(y, 6, x)
FROM linreg;

头两行总是空值(null),结果的线性依赖于两个变量的线性。缺省的顺序是按
列 (x)的升序。可以使用ORDER BY重新排序。
线性回归是一个数学算法,其公式为:

按日期预测
移动线性回归函数的一个限制是不能使用日期作为独立变量。因为计算回归
需要使用加法、减法和乘法,对DATE 数据类型运算有问题。
一种解决办法是与系统日历做连接,用day_of_year 列代替日期,如果产生基
于月的结果,也可以使用month_of_year列。
注意GROUP BY子句会引起函数复位,GROUP BY子句也是主要的排序键,
独立变量是次要的排序键。
在移动线性回归函数中使用日期,可以使用CAST把他们转换成CHAR类型,
从而作为非日期类型使用。
问题
使用线性回归算法预测items 10 和11在1998年头两周的日销售额,宽度为5。

解答
SELECT itemid, CAST(salesdate as char(10)) as chardate, sales
,mlinreg(sales, 5, chardate)
FROM jan_sales
WHERE salesdate between 980101 and 980114
AND itemid in (10,11)
GROUP BY 1
;

结果
itemid chardate sales MLinReg
10 98/01/01 150.00 ?
10 98/01/02 200.00 ?
10 98/01/03 250.00 250.00
10 98/01/04 350.00 300.00
10 98/01/05 550.00 400.00
10 98/01/06 150.00 625.00
10 98/01/07 200.00 300.00
10 98/01/08 100.00 100.00
10 98/01/09 150.00 -75.00
10 98/01/10 250.00 125.00
10 98/01/11 350.00 225.00
10 98/01/12 550.00 425.00
10 98/01/13 450.00 650.00
10 98/01/14 350.00 600.00
11 98/01/01 350.00 ? 复位
11 98/01/02 100.00 ?
11 98/01/03 450.00 -150.00

11 98/01/05 250.00 600.00
11 98/01/06 350.00 475.00
11 98/01/07 200.00 250.00
11 98/01/08 150.00 100.00
11 98/01/09 250.00 125.00
11 98/01/10 450.00 150.00
11 98/01/11 550.00 475.00
11 98/01/12 250.00 700.00
11 98/01/13 350.00 400.00
11 98/01/14 350.00 250.00

上面的内容有些需要注意:
GROUP BY子句引起线性回归算法复位
GROUP BY子句优先排序
DATE数据类型需要转换成CHAR类型

2.10采样函数

采样函数SAMPLE用于从表或视图中产生一些样本数据。有两种形式:
基于实际的行数
基于表的百分比
SAMPLE n - 这里n是一个整数。如果表中记录数>=n,将产生n行记录;如果
表中记录数<n,样本是表中的所有记录。

SAMPLE n - 这里n 是一个小数,并且0.00<n<1.00。将作为百分比抽取表中数
据,返回记录数采用四舍五入法。如对雇员表采样25%,雇员表有26行记录,则采
样26 * .25 = 6.50 = 6 行记录。
例1
SELECT department_number
FROM employee
SAMPLE 10;

结果
****Query completed. 10 rows found.
department_number
401
401
403
401
301
401
403
402
401
401
注意:这里没有使用DISTINCT子句,仅仅任意抽取10行记录。

例2
SELECT department_number
FROM employee
SAMPLE .25;
结果
****Query completed. 6 rows found.
department_number
403
401
402
301
501
403

使用导出表(derived table)进行采样。
SELECT COUNT(DISTINCT dept)
FROM
(SELECT department_number from employee sample 13)
temp(dept);
结果 - 第一次执行
Count(Distinct(dept))
6

结果 - 第二次执行
Count(Distinct(dept))
5
两次运行结果不一样,原因是导出表是采样产生的13行记录,DISTINCT是基
于导出表而不是整个雇员表。

2.11 OLAP统计函数
Teradata V2R4中增加了一些OLAP统计函数。
一元统计函数包括:
STDDEV_SAMP, STTDEV_POP, VAR_SAMP, VAR_POP, SKEW, KURTOSIS
二元统计函数包括:
CORR, COVAR_POP, REGR_SLOPE, REGR_INTERCEPT
OLAP统计函数的特点包括:
能够使用GROUP BY产生分组。
可以与聚合函数混合使用。
不能与其他OLAP函数混合使用。

标准偏差函数
样本标准偏差
STDDEV_SAMP ({DISTINCT} value_expression)
Value_expression是计算样本标准偏差的列表达式。
返回 value_expression的样本标准偏差。
从给的样本中计算差量。
STDDEV_SAMP(x)的计算公式如下:

STDDEV_SAMP = SQRT((COUNT(x) * SUM(x**2) - (SUM(x))**2/(COUNT(x)
* (COUNT(x) - 1)))
全体标准偏差
STDDEV_POP ({DISTINCT} value_expression)
Value_expression是计算全体标准偏差的列表达式。
返回 value_expression的全体标准偏差。
从全体数据中计算差量。
STDDEV_POP(x)的计算公式如下:
STDDEV_POP = SQRT((COUNT(x) * SUM(x**2) - (SUM(x))**2/(COUNT(x) *
(COUNT(x)**2 - 1)))

2.12变异函数
样本变异函数
VARSAMP ({DISTINCT} value_expression)
Value_expression是计算样本变异的列表达式。
返回 value_expression的样本变异。
从给的样本或样本标准偏差的平方中计算差量。
VAR_SAMP(x)的计算公式如下:
VAR_SAMP = (COUNT(x) * SUM(x**2) - (SUM(x))**2/(COUNT(x) *
(COUNT(x) - 1))

全体变异函数
VAR_POP ({DISTINCT} value_expression)
Value_expression是计算全体变异的列表达式。
返回 value_expression的全体变异。
从全体数据中计算差量。
VAR_POP(x)的计算公式如下:
VAR_POP = (COUNT(x) * SUM(x**2) - (SUM(x))**2/(COUNT(x) *
(COUNT(x)**2))

2.13分布函数
分布歪斜函数
SKEW ({DISTINCT} value_expression)
Value_expression是计算分布歪斜的列表达式。
返回 value_expression的分布歪斜。
测量数据分布的不对称,与正常分布(歪斜值为0)进行比较。
正歪斜值指示朝正方向的不对称。
负歪斜值指示朝负方向的不对称。
SKEW(x)的计算公式如下:
SKEW = (COUNT(x) /((COUNT(x) - 1) * (COUNT(x) 2)) * SUM(((x - AVG(x))/
STDDEV_SAMP(x))**3

分布峰态函数
KURTOSIS ({DISTINCT} value_expression)
Value_expression是计算分布峰态的列表达式。
返回 value_expression的分布峰态。
测量数据分布的高峰或平缓,与正常分布(峰态值为0)进行比较。
正峰态值指示朝正方向的峰值。
负峰态值指示朝负方向的峰值。
KURTOSIS(x)的计算公式如下:
KURTOSIS = (COUNT(x) * (COUNT(x) + 1) * (COUNT(x) - 2) * (COUNT(x) -
3)) * SUM((x - AVG(x)) / STDDEV_SAMP(x))*4 - 3* (COUNT(x) - 1)**2 /
((COUNT(x) - 2) * (COUNT(x) - 3))

2.14相关性和协方差函数
协方差函数
COVAR_POP (value_expression, value_expression)
Value_expression是计算协方差的一对列表达式。
返回数据对的协方差。
是数据对的平均背离乘积。
COVAR_POP(x,y)的计算公式如下:
COVAR_POP = SUM((x - AVG(x)) * (y - AVG(y)))/COUNT(x)

相关性函数
CORR (value_expression, value_expression)
Value_expression是相关的一对列表达式。
返回数据对的皮尔森积(Pearson product)。
是测量变量之间的非因果的线性联系(non-causal linear association)。
CORR(x,y)的计算公式如下:
CORR = COVAR_POP(x,y) / (STDDEV_SAMP (x) * STDDEV_SAMP(y))

2.15线性回归函数
回归倾斜函数
REGR_SLOPE (value_expression1, value_expression2)
Value_expression1是独立变量。
Value_expression2 是依赖变量。
基于独立变量和依赖变量返回线性回归的倾斜(slope)值。
测试独立变量在依赖变量上的变化率。
REGR_SLOPE(x,y)的计算公式如下:
REGR_SLOPE = (COUNT(x) * SUM(x) * SUM(Y)) / (COUNT(x) * (SUM(x**2)) -
SUM(x)**2)

回归截取函数
REGR_INTERCEPT (value_expression1, value_expression2)
Value_expression1是独立变量。
Value_expression2 是依赖变量。
基于独立变量和依赖变量返回线性回归的截取(intercept)值。
是回归线与纵坐标的截取点。
REGR_INTERCEPT(x,y)计算公式如下:
REGR_INTERCEPT= AVG(y) - (REGR_SLOPE(x,y) * AVG(x));

 

 

  • 0
    点赞
  • 13
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值