高级查询

本文档介绍了 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 BY`、`ORDER BY`、`ROWS BETWEEN` 三种子句。只使用 `PARTITION BY` 子句或使用 `PARTITION BY`、`ORDER BY` 两种子句会形成静态窗口，窗口大小、位置不会发生变化；除了 `PARTITION BY` 语句外，还使用了 `ROWS BETWEEN` 子句或同时包含 `ROWS BETWEEN` 和 `ORDER 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），那么在进行聚集计算时，这些空值将被忽略，不会影响聚集结果。不过，一个空分数值会产生一个空结果。
在使用这类函数时，你需要提供一个介于 0 和 1 之间的有效分数值作为参数。如果提供的分数值不在这个范围内，函数将抛出错误。
如果你提供的分数值是空（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() 函数

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

函数参数名	支持的数据类型	支持的单位
时间窗口大小	`int16`；`int32`；`int64`；interval	`us` / `ms` / `s` / `minute`(`min`，`minutes`) / `hour` / `day`，例如 `1 day`
时间戳列	`int16`；`int32`；`int64`；`date`；`timestamp`；`timestamptz`

语法示例：

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

3.2 time_bucket_gapfill() 函数

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

函数参数名	支持的数据类型	支持的单位
时间窗口大小	`int16`；`int32`；`int64`；interval	`us` / `ms` / `s` / `minute`(`min`，`minutes`) / `hour` / `day`，例如 `1 day`
时间戳列	`int16`；`int32`；`int64`；`date`；`timestamp`；`timestamptz`
待填充数值列	该列原有数据类型	该列原有单位

有两种填充策略：locf（Last Observation Carried Forward）和 interpolate：

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

语法示例：

不进行缺失值填充

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

使用 locf 策略填充缺失值

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

使用 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（含列 c1、c4），ttt2（含列 c2、c3）：

=# 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 中选择了列 c3 和 c2 并返回结果表 t2。然后，外部查询将表 ttt1 和结果表 t2 使用内部连接进行关联，并返回满足连接条件的行，其中选择了 t1.c1 和 t2.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');

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)

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;

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;

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 相关子查询

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

具体执行过程：

从外层查询中取出一个元组，将元组相关列的值传给内层查询；
执行内层查询，得到子查询操作的结果；
外查询将子查询返回的结果集代入到 WHERE 条件表达式得到结果；
从外层查询取出下一个元组并重复步骤 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 子句

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

假设我们有表 t1（含列 c1、c2、c3）：

=# 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 维度：a 的 c3 值总和、b 的总和、c 的总和；
c2 维度：aa 的 c3 值总和、bb 的总和、cc 的总和；

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

6.2 ROLLUP 子句

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

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

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

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

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

假设我们有表 t1（含列 c1、c2、c3）：

=# 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（含列 c1、c2、c3）：

=# 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）。

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