实时olap引擎（OLAP引擎Kylin介绍）

1 Apache Kylin概述

Apache Kylin是Hadoop大数据平台上的一个开源OLAP引擎。它采用多维立方体预计算技术，可以将大数据的SQL查询速度提升到亚秒级别。

Apache kylin 能提供低延迟（sub-second latency）的秘诀就是预计算，即针对一个星型拓扑结构的数据立方体，预计算多个维度组合的度量，然后将结果保存在hbase中，对外暴露JDBC、ODBC、Rest API的查询接口，即可实现实时查询。

2 Apache Kylin核心概念

• 表(table)：This is definition of hive tables as source of cubes，在build cube 之前，必须同步在 kylin中。

• 模型(model)：模型描述了一个星型模式的数据结构，它定义了一个事实表（Fact Table）和多个查找表（Lookup Table）的连接和过滤关系。

• Cube 描述：描述一个Cube实例的定义和配置选项，包括使用了哪个数据模型、包含哪些维度和度量、如何将数据进行分区、如何处理自动合并等等。

• 分区(Partition)：用户可以在Cube描述中使用一个DATA/STRING的列作为分区的列，从而将一个Cube按照日期分割成多个segment。

• 维度(Dimension): 一组属性，提供结构化的标签信息，一般作为报表的坐标轴。

• 度量(Measure): 一类可以进行聚合分析的特殊维度，聚合后的结果称为指标。

• 数据立方体(cube)：是一种常用于数据分析与索引的技术；它可以对原始数据建立多维度索引。通过Cube对数据进行分析，可以大大加快数据的查询效率。是所有cuboid的集合，作为存储和分析的基本单位。

• Cuboid：给定一个数据模型，我们可以对其上的所有维度进行组合。对于N个维度来说，组合的所有可能性共有2的N次方种。对于每一种维度的组合，将度量做聚合运算，然后将运算的结果保存为一个物化视图，称为Cuboid

• Cube Segment：是指针对源数据中的某一个片段，计算出来的Cube数据。通常数据仓库中的数据数量会随着时间的增长而增长，而Cube Segment也是按时间顺序来构建的。

3 Apache Kylin原理

1、Kylin的预计算是如何实现的？

将数据源Hive表中的数据按照指定的维度和指标 由计算引擎MapReduce离线计算出所有可能的查询结果(即Cube)存储到HBase中。

2、具体的计算逻辑？

整个立方体称为1个cube，立方体中每个网格点称为1个cuboid，图中（A,B,C,D）和（A，D）都是cuboid，（A,B,C,D）称为Base cuboid。

cube的计算过程是逐层计算的，首先计算Base cuboid，然后计算维度数依次减少，逐层向下计算每层的cuboid。先把最细维度的所有指标值都sum出来，然后减少维度再sum, 最后将所有sum。

3、Cuboid 的维度和指标如何转换为HBase的KV结构？

简单的说Cuboid的维度会映射为HBase的Rowkey，Cuboid的指标会映射为HBase的Value。在计算cuboid过程中，会将Hive表的数据转化为HBase的KV形式。Rowkey的具体格式是cuboid id 具体的维度值

4 Apache Kylin技术架构

Apache Kylin系统可以分为在线查询和离线构建两部分，技术架构如图所示，在线查询的模块主要处于上半区，而离线构建则处于下半区。

5 快速入门

1. 星型模型

数据挖掘有几种常见的多维数据模型，星形模型（Star Schema）、雪花模型（Snowf？lake Schema）、事实星座模型（Fact Constellation）等。

星形模型中有一张事实表，以及零个或多个维度表；事实表与维度表通过主键外键相关联，维度表之间没有关联，就像很多星星围绕在一个恒星周围，故取名为星形模型。如果将星形模型中某些维度的表再做规范，抽取成更细的维度表，然后让维度表之间也进行关联，那么这种模型称为雪花模型。

星座模型是更复杂的模型，其中包含了多个事实表，而维度表是公用的，可以共享。

2.维度表的设计

除了数据模型以外，Kylin还对维度表有一定的要求，具体要求如下。

• 要具有数据一致性，主键值必须是唯一的；Kylin会进行检查，如果有两行的主键值相同则会报错。
• 维度表越小越好，因为Kylin会将维度表加载到内存中供查询；过大的表不适合作为维度表，默认的阈值是300MB。
• 改变频率低，Kylin会在每次构建中试图重用维度表的快照，如果维度表经常改变的话，重用就会失效，这就会导致要经常对维度表创建快照。
• 维度表最好不要是Hive视图（View），虽然在Kylin1.5.3中加入了对维度表是视图这种情况的支持，但每次都需要将视图进行物化，从而导致额外的时间开销。

3.维度的基数

维度的基数（Cardinality）指的是该维度在数据集中出现的不同值的个数；例如“国家”是一个维度，如果有200个不同的值，那么此维度的基数就是200。通常一个维度的基数会从几十到几万个不等，个别维度如“用户ID”的基数会超过百万甚至千万。基数超过一百万的维度通常被称为超高基数维度（Ultra High Cardinality，UHC），需要引起设计者的注意。

Cube中所有维度的基数都可以体现出Cube的复杂度，如果一个Cube中有好几个超高基数维度，那么这个Cube膨胀的概率就会很高。在创建Cube前需要对所有维度的基数做一个了解，这样就可以帮助设计合理的Cube。计算基数有多种途径，最简单的方法就是让Hive执行一个count distinct的SQL查询；

4.应用

a 新建项目

由顶部菜单栏进入 Model 页面，然后点击 Manage Projects。

点击 Project 按钮添加一个新的项目。

填写下列表单并点击 submit 按钮提交请求。

b 同步Hive表

登录Kylin的Web界面，创建新的或选择一个已有的项目之后，需要做的就是将Hive表的定义导入到Kylin中。单击Web界面的Model→Data source下的“Load Hive Table”图标，然后输入表的名称（可以一次导入多张表，以逗号分隔表名），单击按钮“Sync”，Kylin就会使用Hive的API从Hive中获取表的属性信息。

### 同时，Kylin会在后台触发一个MapReduce任务，计算此表每个列的基数。通常稍过几分钟之后再刷新页面，就会看到显示出来的基数信息

需要注意的是这里Kylin对基数的计算方法采用的是HyperLogLog的近似算法，与精确值略有误差，但作为参考值已经足够了。

c 新建 Data Model

有了表信息之后，就可以开始创建数据模型（Data Model）了。

数据模型是Cube的基础，它主要用于描述一个星形模型。有了数据模型以后，定义Cube的时候就可以直接从此模型定义的表和列中进行选择了，省去重复指定连接（join）条件的步骤。基于一个数据模型还可以创建多个Cube，以方便减少用户的重复性工作。在Kylin界面的“Models”页面中，单击“New”→“New Model”，开始创建数据模型。给模型输入名称之后，选择一个事实表（必需的），然后添加维度表（可选）

点击顶部的 Model ，然后点击 Models 标签。点击 New 按钮，在下拉框中选择 New Model。

输入 model 的名字和可选的描述。

在 Fact Table 中，为模型选择事实表。

【可选】点击 Add Lookup Table 按钮添加一个 lookup 表。选择表名和关联类型（内连接或左连接）

点击 New Join Condition 按钮，左边选择事实表的外键，右边选择 lookup 表的主键。如果有多于一个 join 列重复执行。

Dimensions 页面允许选择在子 cube 中用作维度的列，然后点击 Columns 列，在下拉框中选择需要的列。

点击 “Next” 到达 “Measures” 页面，选择作为 measure 的列，其只能从事实表中选择。

点击 “Next” 到达 “Settings” 页面，如果事实表中的数据每日增长，选择 Partition Date Column 中相应的 日期列以及日期格式，否则就将其留白。

【可选】选择是否需要 “time of the day” 列，默认情况下为 No。如果选择 Yes, 选择 Partition Time Column 中相应的 time 列以及 time 格式

【可选】如果在从 hive 抽取数据时候想做一些筛选，可以在 Filter 中输入筛选条件。

点击 Save 然后选择 Yes 来保存 data model。创建完成，data model 就会列在左边 Models 列表中。

d 构建 Cube

新创建的Cube只有定义，而没有计算的数据，它的状态是“DISABLED”，是不会被查询引擎挑中的。要想让Cube有数据，还需要对它进行构建。Cube的构建方式通常有两种：全量构建和增量构建；两者的构建步骤是完全一样的，区别只在于构建时读取的数据源是全集还是子集。

Cube的构建包含如下步骤，由任务引擎来调度执行。

1. 创建临时的Hive平表（从Hive读取数据）。
2. 计算各维度的不同值，并收集各Cuboid的统计数据。
3. 创建并保存字典。
4. 保存Cuboid统计信息。
5. 创建HTable。
6. 计算Cube（一轮或若干轮MapReduce）。
7. 将Cube的计算结果转成HFile。
8. 加载HFile到HBase。
9. 更新Cube元数据。
10. 垃圾回收。

全量构建和增量构建：

1 全量构建

对数据模型中没有指定分割时间列信息的Cube，Kylin会采用全量构建，即每次从Hive中读取全部的数据来开始构建。通常它适用于以下两种情形。

• ·事实表的数据不是按时间增长的。
• ·事实表的数据比较小或更新频率很低，全量构建不会造成太大的开销。

2.增量构建

增量构建的时候，Kylin每次都会从Hive中读取一个时间范围内的数据，然后进行计算，并以一个Segment的形式进行保存。下次再构建的时候，会自动以上次结束的时间为起点时间，再选择新的终止时间进行构建。经过多次构建，Cube中将会有多个Segment依次按时间顺序进行排列，如Seg-1，Seg-2，…，Seg-N。查询的时候，Kylin会查询一个或多个Segment然后再做聚合计算，以便返回正确的结果给请求者。使用增量构建的好处是，每次只需要对新增数据进行计算，从而避免了对历史数据进行重复计算。对于数据量很大的Cube，使用增量构建是非常有必要的。

创建完 data model，可以开始创建 cube。

点击顶部 Model，然后点击 Models 标签。点击 New 按钮，在下拉框中选择 New Cube。

步骤1. Cube 信息

选择模型以及填写Cube名称，其中Notification Email List指的是接收邮件的邮箱，Notification Events 指的是需要发送邮件的级别，默认成功，失败以及丢弃三种

步骤2. 维度

点击 Add Dimension，在弹窗中显示的事实表和 lookup 表里勾选输入需要的列。Lookup 表的列有2个选项：“Normal” 和 “Derived”（默认）。

“Normal” 添加一个普通独立的维度列，“Derived” 添加一个 derived 维度，derived 维度不会计算入 cube，将由事实表的外键推算出。

步骤3. 度量

点击 Measure 按钮添加一个新的度量。

根据它的表达式共有8种不同类型的度量：SUM、MAX、MIN、COUNT、COUNT_DISTINCT TOP_N, EXTENDED_COLUMN 和 PERCENTILE。请合理选择 COUNT_DISTINCT 和 TOP_N 返回类型，它与 cube 的大小相关。

步骤4. 更新设置

这一步骤是为增量构建 cube 而设计的。

Auto Merge Thresholds: 自动合并小的 segments 到中等甚至更大的 segment。如果不想自动合并，删除默认2个选项。

Volatile Range: 默认为0，会自动合并所有可能的 cube segments，或者用 ‘Auto Merge’ 将不会合并最新的 [Volatile Range] 天的 cube segments。

Retention Threshold: 只会保存 cube 过去几天的 segment，旧的 segment 将会自动从头部删除；0表示不启用这个功能。

Partition Start Date: cube 的开始日期.

步骤5. 重写配置

cube级别：

Kylin 允许在 Cube 级别覆盖部分 kylin.properties 中的配置，你可以在这里定义覆盖的属性。如果你没有要配置的，点击 Next 按钮。

project级别：

查看创建好的Cube，发现处于DISABLED禁用状态，需要我们build创建一下

6 cube优化

Cube的优化目的始终有两个：空间优化和查询时间优化。

6.1 kylin的剪枝优化

衍生维度

衍生维度：维表中可以由主键推导出值的列可以作为衍⽣维度。

使用场景：以星型模型接入时。例如用户维表可以从userid推导出用户的姓名，年龄，性别。

优化效果：维度表的N个维度组合成的cuboid个数会从2的N次方降为2。

强制维度(Mandatory Dimensions)

强制维度：所有cuboid必须包含的维度，不会计算不包含强制维度的cuboid。

适用场景：可以将确定在查询时一定会使用的维度设为强制维度。例如，时间维度。

优化效果：将一个维度设为强制维度，则cuboid个数直接减半。

层级维度(Hierarchy Dimensions)

层级维度：具有一定层次关系的维度。

使用场景：像年，月，日；国家，省份，城市这类具有层次关系的维度。

优化效果：将N个维度设置为层次维度，则这N个维度组合成的cuboid个数会从2的N次方减少到N 1。

联合维度(Joint Dimensions)

联合维度：将几个维度视为一个维度。

适用场景：1 可以将确定在查询时一定会同时使用的几个维度设为一个联合维度。

2 可以将基数很小的几个维度设为一个联合维度。

3 可以将查询时很少使用的几个维度设为一个联合维度。

优化效果：将N个维度设置为联合维度，则这N个维度组合成的cuboid个数会从2的N次方减少到1。

Extended Column

在OLAP分析场景中，经常存在对某个id进行过滤，但查询结果要展示为name的情况，比如user_id和user_name。这类问题通常有三种解决方式：

a. 将ID和Name都设置为维度，查询语句类似select name, count(*) from table where id = 1 group by id,name。这种方式的问题是会导致维度增多，导致预计算结果膨胀；

b. 将id和name都设置为维度，并且将两者设置为联合。这种方式的好处是保持维度组合数不会增加，但限制了维度的其它优化，比如ID不能再被设置为强制维度或者层次维度；

c. 将ID设置为维度，Name设置为特殊的Measure，类型为Extended Column。这种方式既能保证过滤id且查询name的需求，同时也不影响id维度的进一步优化。

所以此类需求我们推荐使用 Extended Column。

检查cube大小

还有一种更为简单的方法可以帮助我们判断Cube是否已经足够优化。在Web GUI的Model页面选择一个READY状态的Cube，当我们把光标移到该Cube的Cube Size列时，Web GUI会提示Cube的源数据大小，以及当前Cube的大小除以源数据大小的比例，称为膨胀率（Expansion Rate）

一般来说，Cube的膨胀率应该在0%~1000%之间，如果一个Cube的膨胀率超过1000%，那么Cube管理员应当开始挖掘其中的原因。通常，膨胀率高有以下几个方面的原因。

• Cube中的维度数量较多，且没有进行很好的Cuboid剪枝优化，导致Cuboid数量极多。
• Cube中存在较高基数的维度，导致包含这类维度的每一个Cuboid占用的空间都很大，这些Cuboid累积造成整体Cube体积变大。
• 存在比较占用空间的度量，例如Count Distinct，因此需要在Cuboid的每一行中都为其保存一个较大的寄存器，最坏的情况将会导致Cuboid中每一行都有数十KB，从而造成整个Cube的体积变大。

6.2 并发粒度优化

当Segment中某一个Cuboid的大小超出一定的阈值时，系统会将该Cuboid的数据分片到多个分区中，以实现Cuboid数据读取的并行化，从而优化Cube的查询速度。具体的实现方式如下：构建引擎根据Segment估计的大小，以及参数“kylin.hbase.region.cut”的设置决定Segment在存储引擎中总共需要几个分区来存储，如果存储引擎是HBase，那么分区的数量就对应于HBase中的Region数量。kylin.hbase.region.cut的默认值是5.0，单位是GB，也就是说对于一个大小估计是50GB的Segment，构建引擎会给它分配10个分区。用户还可以通过设置kylin.hbase.region.count.min（默为1）和kylin.hbase.region.count.max（默认为500）两个配置来决定每个Segment最少或最多被划分成多少个分区。

6.3 Rowkeys优化

编码：

编码（Encoding）代表了该维度的值应使用何种方式进行编码，合适的编码能够减少维度对空间的占用，例如，我们可以把所有的日期都用三个字节进行编码，相比于字符串存储，或者是使用长整数形式存储的方法，我们的编码方式能够大大减少每行Cube数据的体积。而Cube中可能存在数以亿计的行数，使用编码节约的空间累加起来将是一个非常巨大的数字。

目前Kylin支持的编码方式有以下几种：

• Date编码：将日期类型的数据使用三个字节进行编码，其支持从0000-01-01到9999-01-01中的每一个日期。
• Time编码：仅支持表示从1970-01-0100：00：00到2038-01-1903：14：07的时间，且Time-stamp类型的维度经过编码和反编码之后，会失去毫秒信息，所以说Time编码仅仅支持到秒。但是Time编码的优势是每个维度仅仅使用4个字节，这相比普通的长整数编码节约了一半。如果能够接受秒级的时间精度，请选择Time编码来代表时间的维度。
• Integer编码：Integer编码需要提供一个额外的参数“Length”来代表需要多少个字节。Length的长度为1~8。如果用来编码int32类型的整数，可以将Length设为4；如果用来编码int64类型的整数，可以将Length设为8。在更多情况下，如果知道一个整数类型维度的可能值都很小，那么就能使用Length为2甚至是1的int编码来存储，这将能够有效避免存储空间的浪费。
• Dict编码：对于使用该种编码的维度，每个Segment在构建的时候都会为这个维度所有可能的值创建一个字典，然后使用字典中每个值的编号来编码。Dict的优势是产生的编码非常紧凑，尤其在维度值的基数较小且长度较大的情况下，特别节约空间。由于产生的字典是在查询时加载入构建引擎和查询引擎的，所以在维度的基数大、长度也大的情况下，容易造成构建引擎或查询引擎的内存溢出。
• Fixed_length编码：编码需要提供一个额外的参数“Length”来代表需要多少个字节。该编码可以看作Dict编码的一种补充。对于基数大、长度也大的维度来说，使用Dict可能不能正常工作，于是可以采用一段固定长度的字节来存储代表维度值的字节数组，该数组为字符串形式的维度值的UTF-8字节。如果维度值的长度大于预设的Length，那么超出的部分将会被截断。

在未来，Kylin还有可能为特定场景、特定类型的维度量身定制特别的编码方式，例如在很多行业，身份证号码可能就是一个重要的维度，但是身份证号码由于其具有特殊性而不能使用整数类型的编码（身份证最后一位可能是X），其高基数的特点也决定了不能使用dict编码，在目前的版本中只能使用f？ixed_length编码，但是显然f？ixed_length不能充分利用身份证号码中大部分字节是数字的特性来进行深度编码，因此存在一定程度的浪费。

按维度分片：

系统可能会对Cuboid的数据进行分片处理。但是默认情况下Cuboid的分片策略是随机的，也就是说，我们无法控制Cuboid的哪些行会被分到同一个分片中。这种默认的方法固然能够提高读取的并发程度，但是它仍然有优化的空间。按维度分片（Shard by Dimension）提供了一种更加高效的分片策略，那就是按照某个特定维度进行分片。简单地说，如果Cuboid中某两个行的Shard by Dimension的值相同，那么无论这个Cuboid最终会被划分成多少个分片，这两行数据必然会被分配到同一个分片中。

这种分片策略对查询有着极大的好处。我们知道，Cuboid的每个分片都会被分配到存储引擎的不同物理机器上Kylin在读取Cuboid数据的时候会向存储引擎的若干机器发送所读取的RPC请求。在RPC请求接收端，存储引擎会读取本机的分片据，并在进行一定的预处理后再发送RPC回应（如图6-11所示）。以HBase存储引擎为例，不同的Region代表不同的Cuboid分片，在读取Cuboid数据的时候，HBase会为每个Region开启一个Coprocessor实例来处理查询引擎的请求。查询引擎将查询条件和分组条件作为请求参数的一部分发送到Coprocessor中，Coprocessor就能够在返回结果之前先对当前分片的数据做一定的预聚合（这里的预聚合不是Cube构建的预聚合，而是针对特定查询深度的预聚合）。

如果按照维度划分分片，假设按照一个基数比较高的维度seller_id进行分片，那么在这种情况下，每个分片将会承担一部分seller_id，且各个分片不会有相同的seller_id。所有按照seller_id分组（Group by seller_id）的查询都会变得更加高效，因为每个分区预聚合的结果都会更加专注于某一些seller_id之上，使得分片返回的结果数量大大减少，查询引擎端也无需对各个分片的结果做分片间的聚合。按维度分片也能让过滤条件的执行更加高效，因为是按维度分片，所以每个分片的数据都会更加“整洁”，更方便查找和索引。

调整RowKeys的顺序

在Cube Designer→Advanced Setting→Rowkeys部分，我们可以上下拖动每一个维度来调节维度在Rowkeys中的顺序。这种顺序对于查询非常重要，因为在目前的实现中，Kylin会把所有的维度按照顺序黏合成一个完整的Rowkeys，并且按照这个Rowkeys升序排列Cuboid中所有的行 不难发现，如果在一个比较靠后的维度上有过滤条件，那么这个过滤条件的执行就会非常复杂。以目前的HBase存储引擎为例，Rowkeys对应HBase中的Rowkeys，是一段字节数组。目前没有创建单独的每个维度上的倒排索引，因此对于在比较靠后的维度上的过滤条件，只能依靠HBase的FuzzyKeyFilter来执行。尽管HBase做了大量相应的优化，但是因为是在对靠后的字节运用FuzzyKeyFilter，因此一旦前面维度的基数很大，那么FuzzyKeyFilter的寻找代价就会很高，执行效率就会变差。所以，在调整Rowkeys的顺序时需要遵守以下几个原则：

• 在查询中被用作过滤条件的维度有可能放在其他维度的前面。
• 将经常出现在查询中的维度放在不经常出现的维度的前面。
• 对于基数较高的维度，如果查询会有这个维度上的过滤条件，那么将它往前调整；如果没有，则向后调整。

6.4 其他优化

a 降低度量精度

有一些度量具有多种可选精度，但是精度越高的度量往往越会存在一定的代价，它意味着更大的占用空间和运行时开销。以近似值的Count Distinct度量为例，Kylin提供了多种可选精度，现挑选其中的几种进行对比，见表：

b 及时清理无用的segment

随着增量构建出来的Segment的慢慢累积，Cube的查询性能将会变差，因为每次跨Segment的查询都需要从存储引擎中读取每一个Segment的数据，并且在查询引擎中对不同Segment的数据做进一步的聚合，这对于查询引擎和存储引擎来说都是巨大的压力。从这个角度来说，及时地清理Segment碎片，有助于提高Cube的使用效率。

七 SQL 语法

Hive使用Antlr实现SQL的词法和语法解析。Antlr是一种语言识别的工具，可以用来构造领域语言。Apache Kylin 使用 Apache Calcite 做 SQL 解析和优化。Kylin 支持 SELECT 操作，而不支持其它操作例如 INSERT，UPDATE 和 DELETE，因此 Kylin 的 SQL 语法是 Apache Calcite 支持语法的一个子集。支持的数据类型：

语法支持：

1）SELECT

SELECT 用于从表中选取数据。COUNT 用于统计数据。DISTINCT 过滤掉重复的结果。AS 用于给表或列起别名。FROM 指定要查询的表。JOIN 用于连接两个表以获取所需的数据。WHERE 用于规定选择的标准。LIKE 用于在 WHERE 子句中搜索列中的指定模式。BETWEEN ... AND 选取介于两个值之间的数据范围。AND 和 OR 用于基于一个以上的条件对记录进行过滤。GROUP BY 按给定表达式对结果进行分组。HAVING 用于分组后过滤行。ORDER BY 用于对结果集进行排序，通常和 TOPN 一起使用。LIMIT 用来限制查询返回的行数。

SELECTseller_id,COUNT(1)FROMkylin_salesGROUPBYseller_id;

2）SUBQUERY

例子：

SELECT cal_dt ,sum(price) AS sum_price FROM(SELECTkylin_cal_dt.cal_dt,kylin_sales.priceFROMkylin_salesINNERJOINkylin_cal_dtASkylin_cal_dtONkylin_sales.part_dt=kylin_cal_dt.cal_dtINNERJOINkylin_category_groupingsONkylin_sales.leaf_categ_id=kylin_category_groupings.leaf_categ_idANDkylin_sales.lstg_site_id=kylin_category_groupings.site_id)tGROUPBYcal_dt;

3）JOIN

INNER JOIN

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

例子：

SELECT kylin_cal_dt.cal_dt, kylin_sales.price FROM kylin_sales INNER JOIN kylin_cal_dt AS kylin_cal_dt ONkylin_sales.part_dt****=kylin_cal_dt.cal_dt;

LEFT JOIN

使用 LEFT JOIN 关键字会从左表 (kylin_sales) 那里返回所有的行，即使在右表 (kylin_category_groupings) 中没有匹配的行。

例子：

SELECT seller_id FROM kylin_sales LEFT JOIN kylin_category_groupings AS kylin_category_groupings ON kylin_sales.leaf_categ_id = kylin_category_groupings.leaf_categ_id AND kylin_sales.lstg_site_id = kylin_category_groupings.site_id WHERE lstg_format_name='FP-GTC' GROUP BY seller_idLIMIT20;

4）UNION

SELECT SUM(price) AS x, seller_id, COUNT(*) AS y FROM kylin_sales WHERE part_dt < DATE '2012-08-01' GROUP BY seller_id UNION (SELECT SUM(price) AS x, seller_id, COUNT(*) AS y FROM kylin_sales WHERE part_dt > DATE '2012-12-01' GROUP BY seller_id);

5）UNION ALL

SELECT COUNT(trans_id) AS trans_id FROM kylin_sales AS test_a WHERE trans_id <> 1 UNION ALL SELECT COUNT(trans_id) AS trans_id FROM kylin_sales AS test_b;

6）COUNT

用于返回与指定条件匹配的行数。

7）COUNT_DISTINCT

例子：

SELECT COUNT(DISTINCT seller_id) AS DIST_SELLER FROM kylin_sales;

8）MAX MIN

SELECT MAX(lstg_site_id) FROM kylin_sales;SELECT MIN(lstg_site_id) FROM kylin_sales;

9）SUM

返回数值列的总数。

例子：

SELECT SUM(price) FROM kylin_sales;

10）TOP_N

例子：前20

SELECT kylin_sales.part_dt, kylin_cal_dt.seller_idFROM kylin_salesINNER JOIN kylin_cal_dt AS kylin_cal_dtON kylin_sales.part_dt = kylin_cal_dt.cal_dtGROUP BY kylin_sales.part_dt, kylin_cal_dt.seller_id ORDER BY SUM(kylin_sales.price) DESC LIMIT 20;

11）PERCENTILE

SELECTseller_id,PERCENTILE(price,0.5)FROMkylin_salesGROUPBYseller_id;SELECT seller_id, PERCENTILE_APPROX(price, 0.5) FROM kylin_sales GROUP BY seller_id;

WINDOW函数：

1）ROW_NUMBER 标号

例子：

SELECT lstg_format_name, SUM(price) AS gmv, ROW_NUMBER() OVER() FROM kylin_sales GROUP BY lstg_format_name;

2）AVG

返回数值列的平均值。NULL 值不包括在计算中。

例子：

SELECT lstg_format_name, AVG(SUM(price)) OVER(PARTITION BY lstg_format_name) FROM kylin_sales GROUP BY part_dt, lstg_format_name;

3） RANK DENSE_RANK

SELECT part_dt, lstg_format_name, SUM(price), RANK() OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "rank" FROM kylin_sales GROUP BY part_dt, lstg_format_name;

4）FIRST_VALUE 取分组内排序后，截止到当前行，第一个值

LAST_VALUE 取分组内排序后，截止到当前行，最后一个值

例子：

SELECT part_dt, lstg_format_name, SUM(price) AS gmv, FIRST_VALUE(SUM(price)) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "first" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;

5）LAG LEAD(与LAG相反往下)

用于统计窗口内往上第n行值第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

例子：

SELECT part_dt, lstg_format_name, SUM(price) AS gmv, LAG(SUM(price), 1, 0.0) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "prev" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;

6）CASE WHEN

SELECT part_dt, lstg_format_name, SUM(price) AS gmv, (CASE LAG(SUM(price), 1, 0.0) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) WHEN 0.0 THEN 0 ELSE SUM(price)/LAG(SUM(price), 1, 0.0) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) END) AS "prev" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;

7）CAST

例子：

CAST(part_dt AS timestamp)

8）SUBSTRING

例子：

SELECT SUBSTRING(lstg_format_name, 1) FROM kylin_sales;

9）COALESCE

例子：

SELECT COALESCE(lstg_format_name, '888888888888') FROMkylin_sales;

,