高级查询

本文档介绍了 YMatrix 提供的可用于查询的函数和表达式。

除了基本的查询语句外,YMatrix 还提供了许多高级分析函数和 SQL 表达式。


1 窗口函数

窗口函数可以处理相对复杂的报表统计分析,多用于 OLAP 场景、时序场景等。

“窗口”限定了一个数据集合,在与当前行相关的行之间执行聚集查询。窗口函数与其他函数的区别在于 OVER 子句的存在。如果一个函数有一个 OVER 子句,那么它就是一个窗口函数,OVER 子句用于定义窗口函数的作用范围。

你可以通过以下表格理解聚集窗口函数与专用窗口函数的区别:

聚集窗口函数 专用窗口函数
输出 一行 多行
函数 max()min()count()sum()avg()sum() 除左侧的常用聚集函数外,还有专用的聚集函数:row_number()rank()dense_rank()first_value()last_value()
用法 通常与 GROUP BY 子句组合使用,也可以通过与 OVER 子句组合作为窗口函数使用 OVER 子句组合使用。一般情况下,OVER 子句直接写在窗口函数的名 称和参数之后。OVER 子句中通常可以使用 PARTITION BYORDER BYROWS BETWEEN 三种子句。只使用 PARTITION BY 子句或使用 PARTITION BYORDER BY 两种子句会形成静态窗口,窗口大小、位置不会发生变化;除了 PARTITION BY 语句外,还使用了 ROWS BETWEEN 子句或同时包含 ROWS BETWEENORDER BY 两种子句,则会形成滑动窗口,即将固定大小的窗口滑动遍历每一个输入行,得到最终结果

1.1 语法

窗口函数同聚集函数一样,出现于 SELECT 的表表达式中:

=# SELECT 
    <函数名(参数)> OVER (
        PARTITION BY …
        ORDER BY …
    )
   FROM …;

其中,OVER 子句用于限定窗口范围:

  • PARTITION BY 列表指定将行划分成多个分区,每个分区可视为一个窗口;如果忽略则将所有行划分为一个分区。
  • ORDER BY 子句描述在一个分区内的行排序规则。

1.2 专用窗口函数

YMatrix 支持的专用窗口函数如下:

函数 描述
row_number() 为结果集的分区内的每个行分配唯一的顺序整数,第一行从 1 开始
rank() 为结果集的分区中的每一行分配一个排名值,相同值会得到相同名次,并列名次会使得后面名次顺延。因此在存在相同值的情况下,排名并不是连续整数
dense_rank() 为结果集的分区内的每一行分配一个排名值,相同值会得到相同名次,即使名次并列后面也不会顺延。因此在存在相同值的情况下,排名也是连续整数
first_value(<列名>) 获取结果集的有序分区中的首个值
last_value(<列名>) 获取结果集的有序分区中的末尾值
ntile() 将有序分区的行均分到 n 个组(存储桶)中
cume_dist() 计算出在当前分区内的所有行中,其值大于或等于当前行数据的比例(包含当前行自身)
percent_rank() 计算出当前分区内,除当前行外的所有行中,其值大于当前行数据的比例
lag(<向前偏移列名>,<向前偏移行数>) 用于定位当前行向前偏移一定行数的行
lead(<向后偏移列名>,<向后偏移行数>) 用于定位当前行向后偏移一定行数的行

1.2.1 分布统计相关函数

语法:

=# SELECT …,
    ntile(<n>) OVER (ORDER BY …),
    cume_dist() OVER (ORDER BY …),
    percent_rank() OVER (ORDER BY …)  
FROM …;

注意!
cume_dist() 与 percent_rank() 的分母、分子均不同:1. 前者分母是当前分区的总行数,后者是当前分区的总行数-1;2. 前者分子是值大于/等于当前行数据的行数+1(包含当前行自身),后者分子是值大于当前行数据的行数(不含自身)。

1.2.2 偏移计算相关函数

lag()lead() 函数用于定位当前行向前/向后偏移一定行数的行,常用于当前数据与历史数据进行对比分析的场景。

语法:

=# SELECT 
          lag(<向前偏移列名>,<向前偏移行数>) OVER (ORDER BY …) AS …,
          lead(<向后偏移列名>,<向后偏移行数>) OVER (ORDER BY …) AS …
    FROM …;

1.3 聚集窗口函数

YMatrix 提供但不仅限于如下聚集函数:

函数 描述
count(<列名>/*) 有效值个数(只有 count(*) 包含空值)
sum(<列名>) 累加和
avg(<列名>) 某列平均值
min(<列名>) 某列最小值
max(<列名>) 某列最大值

聚集函数与 OVER 子句并用与 GROUP BY 语义不同:

  • GROUP BY 现对输入的行进行分组,然后再对每个组聚集,每个组输出一条结果;
  • OVER 子句使每一个输入行都对应一个窗口,聚集函数应用于每个窗口,因此每个输入行都产生一个聚集结果。

1.4 窗口框架/滑动窗口

窗口框架可用于专用窗口函数和聚集窗口函数,以限定窗口范围或实现滑动窗口计算。

其应用于 ORDER BY 子句之后,完整语法如下:

=# <聚集函数名(…)> OVER (… ORDER BY … ROWS|RANGE {
          UNBOUNDED PRECEDING
        | n PRECEDING
        | CURRENT ROW
        | BETWEEN <窗口框架约束> AND <窗口框架约束> })

窗口框架约束取值范围见下表,其中 n 可以是数字,也可以是能计算出数字的表达式:

窗口框架约束 描述
UNBOUNDED PRECEDING 分区的第一行
n PRECEDING 当前行之前的 n行
CURRENT ROW 当前行
n FOLLOWING 当前行之后的 n行
UNBOUNDED FOLLOWING 分区的最后一行

注意!
窗口函数应用实例请见 OLAP 场景下的典型查询


2 有序集聚集函数(Ordered-Set Aggregate Functions)

有序集聚集函数是一类数据库聚集函数,它们用于在数据库查询中对有序集合(Ordered Set)进行聚集操作。有序集聚集函数通常用于处理具有排序要求的数据集合,并返回对这些集合进行聚合操作后的结果。

函数 描述 语法
mode() 返回最频繁的输入值(如果有多个频度相同的值就选第一个) mode() WITHIN GROUP (ORDER BY …)
percentile_cont(<分数>) 计算连续百分率/多重连续百分率:
࠾ 连续百分率:返回一个对应于排序中指定分数位置的值,如没有符合分数位置条件的实际输入值,则在相邻的输入项之间进行线性插值计算,并返回计算值
࠾ 多重连续百分率:返回一个匹配分数数组参数形状的结果数组,其中每一个非空元素都以相应百分率值替换
连续百分率:percentile_cont(<分数>) WITHIN GROUP (ORDER BY …)
多重连续百分率:percentile_cont(<分数数组>) WITHIN GROUP (ORDER BY …)
percentile_disc(<分数>) 计算离散百分率/多重离散百分率:
࠾ 离散百分率:返回第一个在排序中位置等于或者超过指定分数的实际输入值。离散百分率不涉及到插值计算,所以返回的结果一定是原数据集中实际存在的值
࠾ 多重离散百分率:返回一个匹配分数数组参数形状的结果数组, 其中每一个非空元素都用相应百分率的输入值替换
离散百分率:percentile_disc(<分数>) WITHIN GROUP (ORDER BY …)
多重离散百分率:percentile_disc(<分数数组>) WITHIN GROUP (ORDER BY …)

为确保有序集聚集函数在使用中的正确和一致,请关注如下说明:

  • 如果输入数据集中存在空值(NULL),那么在进行聚集计算时,这些空值将被忽略,不会影响聚集结果。 不过,一个空分数值会产生一个空结果。
  • 在使用这类函数时,你需要提供一个介于 01 之间的有效分数值作为参数。如果提供的分数值不在这个范围内,函数将抛出错误。
  • 如果你提供的分数值是空(NULL),那么函数将返回一个空结果。这意味着函数因为缺乏有效的分数信息而无法计算聚集结果。

注意!
有序集聚集函数应用实例请见 连续百分率


3 时序函数

注意!
此部分函数需创建 matrixts 扩展后才能使用。

函数 描述
time_bucket('<时间窗口大小>', <时间戳列名>) 将时序数据按照指定的时间间隔分组并进行聚集计算
time_bucket_gapfill('<时间窗口大小>', <时间戳列名>) 为缺失数据做填充,使数据按指定时间间隔在时间序列上平滑分布,有两种填充策略:locf(Last Observation Carried Forward)和 interpolate
first(<时间戳列名>, <指标列名>) 返回指定指标列第一个记录的值。注意:第一列参数不必须是时间列,在此标注时间列是为时序场景需要
last(<时间戳列名>, <指标列名>) 返回指定指标列最后一个记录的值。注意:第一列参数不必须是时间列,在此标注时间列是为时序场景需要
last_not_null_value(<时间戳列名>, <指标列名>) 返回指定列最后一个非空值
last_not_null(<时间戳列名>, <指标列名>) 返回指定列最后一个非空值及其产生时间

3.1 time_bucket() 函数

该函数可以和聚集函数配合使用,计算在给定时间段的聚集值。

函数参数名 支持的数据类型 支持的单位
时间窗口大小 int16int32int64interval us / ms / s / minute(minminutes) / hour / day,例如 1 day
时间戳列 int16int32int64datetimestamptimestamptz

语法示例:

=# SELECT time_bucket('<时间窗口大小>', <时间戳列>) AS …,
    <聚集函数> AS …
    FROM …
    WHERE …
    GROUP BY …
    ORDER BY …;

3.2 time_bucket_gapfill() 函数

该函数类似于 time_bucket() 函数,但额外提供了缺失值填充的功能。当时间段中有数据缺失,需要进行数据清洗,可以使用该函数为缺失数据做填充,使数据按指定时间间隔在时序上平滑分布,从而便于分析。

函数参数名 支持的数据类型 支持的单位
时间窗口大小 int16int32int64interval us / ms / s / minute(minminutes) / hour / day,例如 1 day
时间戳列 int16int32int64datetimestamptimestamptz
待填充数值列 该列原有数据类型 该列原有单位

有两种填充策略:locf(Last Observation Carried Forward)和 interpolate

  • locf:用聚集组中上一个出现的非缺失实际值填充。这样可以保留时间序列的趋势,并使用最近的观测值来估计缺失值,返回的是填充前就存在的实际值。
  • interpolate:对缺失的值做线性插值填充。插值是通过使用已知数据点之间的趋势来推断计算出缺失值,返回的是计算出的新值。

语法示例:

  1. 不进行缺失值填充
=# SELECT time_bucket_gapfill('<时间窗口大小>', <时间戳列>) AS …,
    <聚集函数> AS …
    FROM …
    WHERE …
    GROUP BY …
    ORDER BY …;
  1. 使用 locf 策略填充缺失值
=# SELECT time_bucket_gapfill('<时间窗口大小>', <时间戳列>) AS …,
    locf(<聚集函数>) AS …
    FROM …
    WHERE …
    GROUP BY …
    ORDER BY …;
  1. 使用 interpolate 策略填充缺失值
=# SELECT time_bucket_gapfill('<时间窗口大小>', <时间戳列>) AS …,
    interpolate(<聚集函数>) AS …
    FROM …
    WHERE …
    GROUP BY …
    ORDER BY …;

注意!
时序函数应用实例请见 时空特定查询


4 通用表表达式(Comman Table Expression)

通用表表达式即 CTE,你可以使用它定义构建一个临时的视图,从而使大型查询语句简化。它通过 WITH 关键字实现。在使用之前,可通过以下表格理解 CTE 与 CREATE VIEW 语句的区别。

CTE CREATE VIEW
表述 并非独立 SQL 语句,而是语句的一部分,即表达式 独立 SQL 语句
应用范围 建立的临时视图只用于所属查询语句 建立的视图可用于所有查询语句

语法:

=# WITH <表表达式名字> (<列名>, …) AS (
    SELECT … FROM …
) SELECT … FROM …;

注意!
时序函数应用实例请见 指标对比分析


5 嵌套查询

5.1 子查询出现的位置

我们提供以下语法示例:

  • 出现在 SELECT 列表,例如:

    =# SELECT (SELECT …) AS … FROM …;
  • 出现在 FROM 列表,例如:

    =# SELECT …
     FROM … AS …, (SELECT … FROM …) AS …
     WHERE …;

注意!
出现在 FROM 列表的子查询必须指定别名。

  • 出现在 WHERE 表达式,例如:
    =# SELECT … 
     FROM …
     WHERE … IN (SELECT … FROM … WHERE …);

5.2 返回单个值的子查询

当子查询返回结果是单个数值时,子查询表达式可以出现在 SELECT/FROM 列表,也可以出现在 WHERE 条件表达式。它可以与一般的比较运算符连用,对子查询结果进行比较运算,包括 =<<=>>=!=

假设我们有表 t1(含列 c1),t2(含列 c2):

=# SELECT (SELECT max(c1) FROM t1), t2.*
   FROM t2
   WHERE c2 > (SELECT avg(c2) FROM t2);

可以看到,SELECT 列表和 WHERE 表达式中都出现了返回单值的聚集子查询。

在执行这个查询时,首先执行 SELECT avg(c2) FROM t2,计算出 t2 表中 c2 列的平均值。然后,外部查询根据此平均值和其他条件 c2 > (SELECT avg(c2) FROM t2) 来筛选出满足条件的行。对于满足条件的每一行,查询会返回 t2 表的所有列以及 SELECT max(c1) FROM t1 的结果。

5.3 返回集合的子查询

如果子查询可以返回多个元组,它可以被当作一个临时表出现在 FROM 列表中,可以参与连接 JOIN。和返回单值一致,它也可以与一般的比较运算符连用。

假设我们有表 ttt1(含列 c1c4),ttt2(含列 c2c3):

=# SELECT t1.c1, t2.c2
   FROM ttt1 AS t1
   INNER JOIN (SELECT c3, c2 FROM ttt2) AS t2
   ON t1.c4 = t2.c3;

在执行这个查询时,首先执行了子查询 SELECT c3, c2 FROM ttt2,它从表 ttt2 中选择了列 c3c2 并返回结果表 t2。然后,外部查询将表 ttt1 和结果表 t2 使用内部连接进行关联,并返回满足连接条件的行,其中选择了 t1.c1t2.c2 列。

返回集合的子查询还可以通过以下 SQL 语法应用于 WHERE 条件表达式中:

  • ALL:满足子查询集合中的所有元组。
  • ANY:满足子查询中的至少一个元组。
  • IN = ANY,区别在于 IN 只是取出子查询的结果集,不可对其进行比较运算,而 ANY 可以在前面添加比较运算符。
  • EXISTS:子查询返回至少一个元组。

假设我们有表 t1

=# CREATE TABLE t1 (
   c1 int,
   c2 text
   );
=# INSERT INTO t1 VALUES (1,'content');
   INSERT INTO t1 VALUES (2,'content');
   INSERT INTO t1 VALUES (3,'content');
   INSERT INTO t1 VALUES (4,'text');
   INSERT INTO t1 VALUES (5,'text');
  1. ALL 语法示例
=# SELECT * FROM t1
   WHERE c1 >= ALL (
    SELECT c1 FROM t1
    WHERE c2 = 'content'
    ORDER by c1
   );

由于子查询结果为数据集 (1,2,3),实际上以上 SQL 等价于:

=# SELECT * FROM t1
   WHERE c1 >= 1
   AND c1 >= 2
   AND c1 >= 3
   ORDER by c1;
 c1 |   c2
----+---------
  3 | content
  4 | text
  5 | text
(3 rows)
  1. ANY 语法示例
=# SELECT * FROM t1
   WHERE c1 >= ANY (
    SELECT c1 FROM t1
    WHERE c2 = 'content'
   );

由于子查询结果为数据集 (1,2,3),实际上以上 SQL 等价于:

=# SELECT * FROM t1
   WHERE c1 >= 1
   OR c1 >= 2
   OR c1 >= 3;
  1. IN 语法示例
=# SELECT * FROM t1
   WHERE c1 IN (
    SELECT c1 FROM t1
    WHERE c2 = 'content'
   );

由于子查询结果为数据集 (1,2,3),实际上以上 SQL 等价于:

=# SELECT * FROM t1
   WHERE c1 = 1
   OR c1 = 2
   OR c1 = 3;
  1. EXISTS 语法示例
=# SELECT * FROM t1
   WHERE EXISTS (
    SELECT * FROM t1
    WHERE c1 < 60
    AND c2 = 'content'
   );

由于子查询结果为数据集不为空,实际上以上 SQL 等价于:

=# SELECT * FROM t1
   WHERE true;

如果数据集为空,则等价于:

=# SELECT * FROM t1
   WHERE false;

5.4 相关子查询

子查询中查询条件依赖于外层查询中的某个值,对于外层查询的每个元组,都要代入到子查询反复求值,使得子查询的处理不止一次。

具体执行过程:

  1. 从外层查询中取出一个元组,将元组相关列的值传给内层查询;
  2. 执行内层查询,得到子查询操作的结果;
  3. 外查询将子查询返回的结果集代入到 WHERE 条件表达式得到结果;
  4. 从外层查询取出下一个元组并重复步骤 1-3,直到外层元组全部处理完毕。

假设我们有表 t1(含列 c1),t2(含列 c2):

=# SELECT * FROM t1
   WHERE NOT EXISTS (
    SELECT * FROM t2
    WHERE t2.c2 = t1.c1
   );

对于外层查询中 t1 表的每一个元组,都会进入子查询执行内层查询,查找 t2 表中是否有符合条件的结果,并由 NOT EXISTS 表达式确定是否要输出此条 t1 元组到最终结果。

注意!
嵌套查询应用实例请见 即席分析


6 数据立方体

在商业智能(Business Intelligence, BI)领域,需要从不同维度透视一个数据集,并通过数据可视化手段,将数据蕴藏的知识呈现给决策者,实现其商业价值。 数据立方体是根据此需要产生的 SQL 特性,其返回一个覆盖多个维度的多维数组,从而展示数据的内在联系。

数据立方提供一种基于事实(Fact)和维(Dimension)的多维数据模型:

  • “维”是人们观察客观世界的角度,是一种高层次的类型划分。“维”一般包含着层次关系,这种层次关系有时会相当复杂。通过把一个实体的多项重要的属性定义为多个维(dimension),使用户能对不同维上的数据进行比较。例如上图中的电子产品、2023一季度、北京等各类维度标签。
  • “事实”是实际的明细数据,是客观的活动记录。

6.1 GROUPING SETS 子句

即分组集。由于分组聚集计算发生在 FROMWHERE 表达式计算完成之后,所以它会将前述计算结果按照指定的每一个分组单独计算聚集,然后一同返回结果。

假设我们有表 t1(含列 c1c2c3):

=# SELECT c1, c2, sum(c3) AS sum 
   FROM t1
   GROUP BY GROUPING SETS (c1, c2)
   ORDER BY (c1,c2);
 c1 | c2 | sum
----+----+----
 a  |    | 6
 b  |    | 15
 c  |    | 24
    | aa | 12
    | bb | 15
    | cc | 18
(6 rows)

可以看到,返回中分别展示了 c1 维度和 c2 维度的求和聚集计算结果:

  • c1 维度:ac3 值总和、b 的总和、c 的总和;
  • c2 维度:aac3 值总和、bb 的总和、cc 的总和;

实际上,执行一次 GROUPING SETS 相当于执行多次 UNION ALL,前者更为简便、标准。

6.2 ROLLUP 子句

GROUPING SETS 外,YMatrix 也提供其他更简化的方法来指定常用类型的分组集。

按照 ROLLUP 命令分组的聚集结果会按照分组键的顺序逐层上卷。

实际上,ROLLUP ( c1, c2, … ) 等价于:

=# GROUPING SETS (
    ( c1, c2, … ),
    …
    ( c1 ),
    ( )
   )

其中最后一个空的分组键相当于对所有返回值进行聚集计算。

假设我们有表 t1(含列 c1c2c3):

=# SELECT c1, c2, sum(c3) AS sum 
   FROM t1
   WHERE c1 = 'a' OR c1 = 'b'
   GROUP BY ROLLUP (c1, c2)
   ORDER BY (c1,c2);
 c1 | c2 | sum
----+----+----
 a  |  aa  | 1
 a  |  bb  | 2
 a  |  cc  | 3
 a  |      | 6
 b  |  aa  | 4
 b  |  bb  | 5
 b  |  cc  | 6
 b  |      | 15
    |      | 21
(9 rows)

可以看到:

  • 结果 1-3、4-6 行是对分组键 (c1,c2) 结果的累加和(此示例中即为 c3 值本身);
  • 结果第 4、8 行是对分组键 (c1) 结果的累加和(即 1-3、5-7 行分别的总值);
  • 最后一行第 9 行则是对所有返回值的累加。

ROLLUP 子句通常被用来分析历史数据,例如按部门、区和公司范围计算的总薪水,其结果可以可视化为多环饼图等。

6.3 CUBE

按照 CUBE 命令分组的聚集结果以各个分组键分别上卷求并集、去重。

CUBE ( c1, c2 ) 等价于:

=# GROUPING SETS (
    ( c1, c2 ),
    ( c1     ),
    (     c2 ),
    (        )
   )

其中最后一个空的分组键相当于对所有返回值进行聚集计算。

假设我们有表 t1(含列 c1c2c3):

=# SELECT c1, c2, sum(c3) AS sum 
   FROM t1
   WHERE c1 = 'a' OR c1 = 'b'
   GROUP BY CUBE (c1, c2)
   ORDER BY (c1,c2);
 c1 | c2 | sum
----+----+-----
 a  | aa |   1
 a  | bb |   2
 a  | cc |   3
 a  |    |   6
 b  | aa |   4
 b  | bb |   5
 b  | cc |   6
 b  |    |  15
    | aa |   5
    | bb |   7
    | cc |   9
    |    |  21
(12 rows)

可以看到,除和 ROLLUP 相同的部分(1-8 行及最后一行)外:

  • 9-11 行是对分组键 (c2) 结果的累加和(例如 aa = 1+4,bb = 2+5)。

CUBE 子句的计算结果通过 BI 工具可以渲染出丰富的视觉和交互效果,例如桑基图(Sankey Chart)

注意!
数据立方体应用实例请见 多维数据查询