分析函数

create table t(
    month MONTH,
    city STRING,
    sold INT
);

go

insert into t values(2023.06M, "A", 1200);
insert into t values(2023.07M, "A", 1500);
insert into t values(2023.08M, "B", 1200);
insert into t values(2023.07M, "B", 1300);
insert into t values(2023.07M, "C", 1300);
insert into t values(2023.06M, "C", 1400);
insert into t values(2023.08M, "A", 1400);
insert into t values(2023.06M, "B", 1000);
insert into t values(2023.08M, "C", 1100);

SELECT 
  month,
  city, 
  sum(sold) OVER (PARTITION BY city) AS sum
FROM t;

当对分布式表应用分析函数时，若 PARTITION BY 后的列是分区列，则在每个分区内部执行分析函数；否则，需要把整个表数据读取到内存中重新分区后执行分析函数。此时若分区表的数据量比较大，性能会比较差。

ORDER BY 子句指定应用分析函数的每个分组中的行顺序。如果没有指定 ORDER BY 子句，则分组内的行是无序的。如果指定了 ORDER BY 子句，分组中的数据会按照 ORDER BY 子句指定的列排序后再应用分析函数。

SELECT 
  month, 
  city, 
  rank() OVER (PARTITION BY city ORDER BY month) AS rank
FROM t;

根据上面两个例子的结果，我们可以观察到，无论是否指定 ORDER BY，DolphinDB 输出的结果都会保持原表的顺序。

DolphinDB 支持以下几类分析函数，需要注意的是，在 DolphinDB 中，应用分析函数后的输出结果的顺序与原表的顺序保持一致。

例子

create table t(
    id INT,
    sym SYMBOL,
    volume INT
);

insert into t values(1, `R, 200);
insert into t values(2, `P, 500);
insert into t values(1, `P, 100);
insert into t values(1, `P, 300);
insert into t values(2, `R, 300);
insert into t values(2, `P, 400);
insert into t values(3, `R, 400);

select * from t;

按 sym 分组后，获取每组内由当前行的前1行和后2行确定的窗口帧内的 volume 的总和。

select 
  *,
  sum(volume) over (
    partition by sym
    rows between 1 preceding and 2 following) 
from 
  t;

按 sym 分组并按 volume 升序排序后，获取每组内当前行的百分位排名。

select 
  *,
  percent_rank() over (
    partition by sym
    order by volume) 
from 
  t;

获取分组内每行根据 volume 的排名。如果不使用 order by 子句，则所有排名一样。如果指定 order by 子句，则会根据 order by 后的列进行排名，默认是升序。

// 不使用 order by
select 
  *,
  rank() over (
    partition by sym) 
from 
  t;
  

// 使用 order by
select 
  *,
  rank() over (
    partition by sym
    order by volume) 
from 
  t;

按 sym 分组按 id 降序排序后，在由当前行的前2行到当前行组成的窗口内，获取第一行的值。

 select 
  *,
  first_value(volume) OVER(
    partition by sym
    ORDER BY id DESC
    rows 2 preceding)
from 
  t;
  

对分布式表应用分析函数。

t = table(1 2 1 1 2 2 3 as id, `R`P`L`P`R`L`R as sym, 200 500 100 300 300 400 400 as volume)
db_name = "dfs://window_function"
if (existsDatabase(db_name)) {
    dropDatabase(db_name)
}
db = database(db_name, HASH, [INT, 2], , 'TSDB')

pt = db.createPartitionedTable(t, "pt", "id", ,"volume")
pt.append!(t)

// 按 sym 列分组后，按 id 按升序排序后，获取 volume 当前行后面第1行的值，若没有对应行，则填充-1。
select 
  *,
  lead(volume, 1, -1) OVER(
    PARTITION BY sym
    ORDER BY id)
from 
  t;

本例将介绍如何基于时间窗口进行字段统计。

n=10
date= rand(2022.10.01..2022.10.04 join 2022.11.01 join 2022.12.04 join 2022.12.06 join 2022.12.10 join 2023.01.01 join 2023.02.02, n)
price = [49.6,29.46,49.6,50.6,64.97,56.9,50.66,50.32,51.29,62.36]
qty = [2200,1900,2000,2200,6800,2100,1300,6600,8800,5300]
sym = rand(string(["C", "MS", "A"]), n) 
t = table(date, sym, qty, price);
t

首先基于 sym 字段进行分区，然后根据 date 字段进行排序。对每个记录的 date 值，开启一个 RANGE，包含前面1个月和后面1个月。统计每个 RANGE 中的 qty 的最大值。

select *, max(qty) over (partition by sym order by date RANGE BETWEEN 1M PRECEDING AND 1M FOLLOWING) from t