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
ar_of_calendar = 1998
h_of_year in (1,2)
AND ds.itemid = 10
GROUP h_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;
结果
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论