DEV03-GBase 8a MPP Cluster OLAP 函数

一、OLAP函数的基本概念

1、OLAP 的概念：
      OLAP 是 On-Line Analytical Processing（联机分析处理）的缩写，是一种能够对多维分析查询进行快速响应的计算方法。1993 关系数据库之父 Codd 提出的这个概念。

• OLAP 具备以下特性：
  (1) 快速性：用户对OLAP的快速反应能力有很高的要求。系统应能在5秒内对用户的大部分分析要求做出反应。
  (2) 可分析性：OLAP系统应能处理与应用有关的任何逻辑分析和统计分析。
  (3) 多维性：OLAP的关键属性。系统必须提供对数据的多维视图和分析,包括对层次维和多重层次维的完全支持。。
  (4) 信息性：不论数据量有多大，也不管数据存储在何处，OLAP系统应能及时获得信息，并且管理大容量信息。

       OLAP 运算的载体是数据仓库。数据仓库的概念由比尔·恩门（Bill Inmon）在 1990 年提出的。数据仓库是决策支持系统和联机分析应用数据源的结构化数据环境，具备四大特征：面向主题、集成性、稳定性和时变性。要求关系型数据库必须具备高性能、高并发、高可用、高扩展等特性。8a 集群专利上的全称是《大规模分布式并行处理数据库集群》，完全具备这些特性。领先的联邦架构决定了其具备管理大数据快速增长和高速查询的能力。

       OLAP 函数是数据库提供的分析函数集合。OLAP 函数是用简单语法处理复杂数据分析的 SQL 函数集，可以有效避免使用子查询，明显提升查询性能，为编程人员进行准确高效的数据处理提供很大的便利。

       Oracle11g 支持 OLAP 函数的四年后，8a 集群开始全面追赶 Oracle，OLAP 函数功能覆盖率 90% 以上，在国产数据库一直处理领先地位。在金融、电信、海关、发改委、审计、海洋、教育等行业得到广泛的应用。8a 集群因数据仓库而生，最适合 OLAP 应用场景。

2、OLAP 函数的应用场景
(1) 复杂排序：
比如，学校要统计全员学习成绩，需要按照年级、班级统计学生的分数；班级的排名、年级排名同时显示。
(2) 按规则自动分组匹配：
例如，审计行业核减式、金融行业的对账、核销、单据对比等场景。
(3) 发展趋势（同比、环比）；数据分布（占比）：统计局的业务经常使用的指标统计方法。
(4) 当月累计：也是统计行业常用指标展示方式之一。比如，5月累计值是当年前五个月值的累加。统计行业很少用当月值。
(5) 移动平均（均线定趋势，K 线定买点）。很多股票都有收盘价的移动平均线，和 K 线综合起来分析可以找到最合适的买入和卖出点。
(6) 多维分析之上卷：沿着某维的层次向上汇总数据。多维分析之下钻：沿着某维的层次向下显示更明细的数据。

二、OLAP 函数分类：

(1) 窗口类：
     为数据集定义若干窗口（窗口是指运算将要操作的行的集合），分别对每个窗口的数据进行操作。不需要使用 GROUP BY 子句，能够在同一行中同时返回当前窗口基础行的列和聚合列。要与开窗函数 OVER() 配合使用。
(2) GROUP BY 子句扩展类：
     在传统 group by 基础上的扩展用法，可同时分析多个维度的组合。

备注：这两类在数据库引擎中属于不同的算子，但从其作用上来讲都是用来做数据统计分析的，因此统称为 OLAP 函数。

三、窗口类 OLAP 函数

（一）概念：

       以维度将原始数据集划分为若干组（每组数据都叫做一个窗口），针对每个窗口的数据做二次操作的一类函数叫做窗口类 OLAP 函数。
       比如，学生成绩统计，首先按照年级、班级分组，然后统计每位同学在班级中的排名、在年级的排名，即每位学生的数据行中班级排名和年级排名同时显示。那么“按照年级、班级分组”就是第一次分组聚合，班级和年级排名就是第二次排名操作。两次操作使用包含 OLAP 函数的一个层次的 SQL 语句实现，不会有子查询的出现。

（二）语法：

func_name([distinct][args]) over( [partition by 列1,列2,...][order by col_name [asc/desc]] [windowing] )

1、 func_name 主函数，用于对窗口中的数据进行操作。可为：rank、dense_rank、row_number、sum、avg、count、max、min、lead、lag 等。
2、over 开窗函数。
3、 partition by 用来将结果集分区，确定窗口和窗口层次；要和 over 一起使用。
4、 order by 用于对分区中的数据进行排序。

5.、windowing 用来限制滑动窗口大小。
(1) windowing 子句语法图：

以上语法图看似比较复杂，但实质上仅包含两个步骤：首先确定选择行还是值的范围，然后确定起始行到终止行，或者起始值到终止值。

简单组合举例：

# 每个滑动窗口都取从 1 到 100 的某列的数据
rows between 1 and 100

# 每个滑动窗口都取从 0 到 59 的某值的数据
range between 0 and 59

(2) windowing 子句中的关键字：

(3) 注意事项：
① 执行包含 windowing 子句 OLAP SQL 前必须打开开关
_t_gbase_new_window_function_support
② 窗口内存在 order by 时，才能使用 rows/range between。
③ 开始边界不能大于终止边界：比如 rows between 1 following and current row 错误！
④ 用 range between 时，order by 后面只能有一个表达式或字段。
⑤ range between 不能用于非固定的窗口。
⑥ windowing 子句只能能与聚合类主函数配合使用。

(三) 主函数

       上文语法表达式中的 func_name（主函数）可分为聚合类、序号类、偏移类等函数。下文分别介绍。

1、聚合函数
(1) 功能：按指定的分区和排序做聚合（ sum avg count max min ）运算。
【备注】
① 不能和 group by 聚合运算混淆：即 OLAP 的主函数使用聚合函数不能再使用 group by 子句；
② partition by 可以省略，省略后就只有一个窗口了。
③ order by 可以省略。

(2) 示例
① 显示每个区域每种商品的最低价格

SELECT distinct qy'区域', mc'商品',  min(jg) over(PARTITION BY qy, mc) '最低价格'
FROM jgb
/*
jgb 是价格表，主要字段有大区域（qy）、超时名称、商品名称、价格，存储的是宁夏超市商品价格明细数据。本例 SQL 展示的是宁夏每种商品在各个地市(qy)最低价格。over 按照地市（qy）、商品（mc）开窗。
*/

执行结果片段：

实现以上需求，完全可以用以下传统的 GROUP BY 语句替换：

SELECT qy'区域', mc'商品',  min(jg)  '最低价格'
FROM jgb
group by qy, mc

但不是所有的 OLAP 函数组合都能用 GROUP BY 实现的。

② 每月累计值的计算
测试表 fact 的数据如下，value 字段是当月值

set _t_gbase_new_window_function_support = on; # 必须打开这个 session 级别的开关否则不能使用 windowling 子句

SELECT year, month, value, 
   sum(Value) over(partition by year order by month rows between unbounded preceding and  current row) '月累积'
from fact order by year, month;
/*
统计行业的月累积值是从当月值和历史月份值的累加。比如，2014 年 3 月的月累计是 2014 年 1、2、3 月值的和。如果该需求不使用 OLAP 函数是不能用一个 SQL 实现的，只能在存储过程中借助临时表、光标和循环分布运算。
*/

执行结果：

③ 每月在本年度占比

set _t_gbase_new_window_function_support = on; # 打开 windowling 子句开关

select year, month,
concat(round(value * 100 / sum(value) 
  over(partition by year order by month rows between unbounded preceding and unbounded following), 2), '%')每月占比
from fact;
/*
该 SQL 每个年份一个窗口。“rows between unbounded preceding and unbounded following” 表示当前年的数据窗口从头取到尾；sum(value) 将当前年12个月的数据做累加。value / sum(value) 即为当月值在当前年月份中的占比。
*/

执行结果：

2、序号函数
rank : 按指定的partition by和order by输出序号，相同的数值序号相同；变换序号时，如果前面有相同的序号，会跳跃取序号。
dense_rank : 类似rank，区别是变换序号时，不会进行跳跃，即序号是连续的。
row_number : 与rank的区别是，即使数值相同，也会连续变换序号。

注意：order by 必须存在。因为针对有序数据排名才是有意义的。

SELECT cid as 课程号, sid as 学号, sc as 分数,
rank() over(PARTITION BY cid ORDER BY sc desc)排名1,
dense_rank() over(PARTITION BY cid ORDER BY sc desc)排名2,
row_number() over(PARTITION BY cid ORDER BY sc desc)排名3
FROM score
ORDER BY cid, sc desc
/*
以上 SQL 是针对课程（cid）开窗，每个课程中学生分数（sc）做排名。
*/

执行结果：

我们可以看到：
排名1（rank） 的处理方式：前两名分数相同，则排名序号相同，第三名序号跳跃到 3；排名2（dense_rank）的处理方式：前两名分数相同，则排名序号相同，第三名序号 2 是连续的；
排名3（row_number）的处理方式：前两名分数相同，也是要连续变换序号的，序号不能相同、也不能跳跃。

3、偏移函数
(1) 语法：
      LAG | LEAD (val, offset, default_val)
      返回窗口中当前行之前（LAG）、之后（LEAD）给定偏移量的输入表达式的值作为新的投影列。
☆ 可在同一张表中实现错位查询，避免使用消耗更大的自连接，显著提高查询处理速度。

注意：
① order by 不能省略；
② offset 参数缺省值1；可以省略（同时必须省略default_val）；取值范围非负整数（其它类型可隐式转换为整数）。
③ default_val 可以省略，缺省值 NULL。

(2) 示例
① 本月值和上月值同行显示

SELECT YEAR, MONTH, LAG(value, 1) over(partition by year ORDER BY month)'上月值'
FROM fact
/*
以年为窗口，每年的一月没有数据，所以 02 月对应的上月值为空
*/

执行结果：

SELECT year, month, value'当月值', 
concat( round(value*100 / LAG(value,1) over(partition by year ORDER BY month)-100 ,2), '%')'环比'
FROM fact
/*
月月环比公式 = (当月值 - 上月值) * 100% / 上月值 = 当月值 / 上月值 - 100%
*/

执行结果：

4、首尾值函数（FIRST_VALUE / LAST_VALUE）
【功能】取每个窗口的第一个值（FIRST_VALUE）或者最后一个值（LAST_VALUE）

# 下面这个例子显示每个学科最高分
SELECT C0.Cname, S.Sname, C.SC, first_value(C.SC) over(partition by C.CId order by C.SC desc)'每学科最高分'
from score C inner join student S on S.Sid=C.SId inner join course C0 on C0.CId=C.CId

执行结果：

5、分片函数 ntile(N)
(1) 语法解释： 该函数用于将分组数据按照顺序切分成N片，返回当前切片值。如果切片不均匀，默认增加前面切片的分布。
☞ N 可以是常量，NULL值，不能是字段。
☞ N 一般是正整数。
☞ N 为浮点类型常量，则四舍五入后作为参数输入。
☞ N 为字符串常量，则转化为整数作为参数输入（字符串转整数截止到左起第一个不是数字的字符为止）
(2) 示例：
如果排序后的数据分为几部分，业务人员只关心其中的一部分，如何将中间某部分数据拿出来呢？

SELECT year, month, value, ntile(4) over(PARTITION by year ORDER BY month) '分片'
FROM fact

执行结果：

6、窗口类OLAP函数通用特征
(1) 不会进行聚集，不会减少结果集条数；
(2) 属于一个独立算子，执行顺序介于 Group By 与 Distinct 之间。

(3) 使用限制：
      ① 不能放在where/on/having条件中
      ② 不能作为聚集函数的参数
      ③ 不能放在group by子句中
      ④ olap函数不能嵌套olap函数。

四、GROUP BY 子句类 OLAP 函数

（一）函数功能解析

1、 grouping sets(A, B, C) 分别对A、B、C进行GROUP BY，最后将所有结果合并在一起，相当于 union all 操作。

2、 rollup(A, B, C) 分别对 (A,B,C) (A,B) (A)，然后对全表进行GROUP BY操作(group by NULL)。最后将所有结果合并在一起。

3、 cube(A, B, C) 分别对 (A,B,C) (A,B) (A,C) (B,C) (A) (B) ©，然后对全表进行聚集操作(group by NULL)。最后将所有结果合并在一起。

注意：grouping sets、rollup、cube 是 GROUP BY 的扩展，所以必须配合 GROUP BY 使用。

4、 grouping() 函数：用于区分输入数据中NULL值①和由rollup、cube或grouping sets返回的NULL值② ；①返回 0， ②返回 1。

注意：参数必须是参与分组的一个字段或表达式。

（二）综合示例

(1) 统计各区域商品最高价格，区域做小计

SELECT qy '区域', mc'商品', max(jg) '最高价格'
FROM jgb
GROUP BY
GROUPING SETS
(qy,
(qy, mc)
)
ORDER BY qy,mc
/*
GROUPING SETS 中对于 qy（区域）的聚合就会计算出区域小计的数值。
*/

执行结果片段：

我们发现中卫市、吴忠市两个窗口的最后一行数据第二列是空。这两行数据实际上是小计值。只不过“小计”两个字没有显示出来，这个缺陷在后面的示例中会解决。

(2) 仅统计区域小计

SELECT qy '区域', mc'商品', max(jg) '最高价格'
FROM jgb
GROUP BY
GROUPING SETS
( qy,
(mc,qy) )
having grouping(mc)=1
ORDER BY qy,mc
/*
having grouping(mc)=1 这个条件即将小计过滤出来。
*/

执行结果：

(3) 小计和总计的实现

• 需求：按照月份小计，年份总计

SELECT (case when grouping(YEAR)=1 and grouping(MONTH)=1 then '总计' else YEAR end)'年', 
(case when grouping(MONTH)=1 and grouping(YEAR)=0 then '小计' else MONTH end)'月', 
sum(Value)'产值'
FROM fact
GROUP BY ROLLUP(YEAR, MONTH)
ORDER BY YEAR, MONTH
/*
grouping(YEAR)=1 表示 YEAR 列的空值是由于 YEAR 的聚合产生的，
grouping(MONTH)=1 表示 MONTH列的空值是由于 MONTH 的聚合产生的，这两个表达式都等于 1 则表示当前行的值就是总计。
grouping(MONTH)=1 and grouping(YEAR)=0 表示针对年的小计。
*/

执行结果：

(4) 小计、合计和总计的实现

• 需求：统计 2018 年世界杯比赛对阵国、主队总积分、客队丢分合计、积分总计
• worldcup 数据片段如下
  
  以下 SQL 文中使用 country1 （主队国名称）；country2 （客队）；Result 积分（胜3、负0、平1）

SELECT NVL(country1,'') 主队,
NVL(DECODE(country1,NULL,country2 || ' 丢分合计',NVL(country2,country1 || ' 小计')),'总计') 客队,
SUM(Result) 主队积分
FROM worldcup
GROUP BY CUBE(country1, country2)
ORDER BY country1, country2;
/*
DECODE 是条件分支函数 =>
① 当 country2 为空，country1 非空，则当前行是主队小计；
② 当 country1 为空，country2 非空，则当前行是客队丢分合计；
③ 当 country1 和 country2 皆空，则当前行是总计。

CUBE(country1, country2) 产生的组合有：(country1, country2)、(country1)、(country2)和全表聚集。
*/

执行结果：

五、总结：

       全面支持 OLAP 函数已经是分析型数据库的基本标志。
       窗口类 OLAP 函数不能有 GROUP BY 子句的参与；必须配合开窗函数（OVER）才能使用，其本质上先将数据集分组，然后对每个分组再做处理，不需要子查询即能完整这两套动作。
       GROUP BY 子句类 OLAP 函数必须有 GROUP BY 子句的参与。其本质是对不同维度聚合后 UNION ALL 一起。不涉及开窗。
       理解了两大类函数的本质，学习 OLAP 函数就比较容易了。