1. 概述

1.1. 定义

      窗口函数(Window Function)，也叫分析函数（Analytics Function），或者 OLAP 函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理，如计算累计和、移动平均、排名等。

1.2. 应用场景

场景1：观远 BI 里自带的聚合计算，或者新建的聚合度量，都是基于维度栏的字段进行分组聚合，如果不按照维度栏字段（或者需要跨维度）聚合计算，如计算累计和、移动平均、排名，那就可以用窗口函数来计算。

场景2：BI 卡片里直接聚合计算得到的数据不能进行二次计算，如果需要对聚合结果进行二次计算，需要用窗口函数来计算。

1.3. 特点

1. 同时具有分组（partition by）和排序（order by）的功能；
2. 不减少原表的行数。

      窗口函数里的 partition by 和 order by 子句的功能是对分组后的结果进行排序，和普通 SQL 查询语句中的 group by 和 order by类似。区别在于，group by 分组汇总后改变了表的行数，而 partition by 不会减少原表中的行数。

2. 语法介绍

2.1. 函数写法

      一个完整的窗口函数由窗口函数和 OVER 子句组成，不区分大小写，写法如下。

<窗口函数> OVER (
  PARTITION BY [分区列]
  ORDER BY [排序列]
  ROWS or RANGE [窗口框架]
)

2.2. 常见<窗口函数>

2.3. OVER 子句

      `OVER` 子句定义了窗口函数的操作范围，包括以下几个部分：

2.4. ROWS/RANGE 子句

      在 SQL 中，窗口函数可以使用 `ROWS` 或 `RANGE` 子句来定义窗口框架，从而更精确地控制计算范围。窗口框架指定了哪些行将包含在窗口函数的计算中。使用时放在order by子句后，结合 between and 指定窗口范围，常用于移动平均等计算，属于进阶用法。如果order by 后未指定`ROWS` 或 `RANGE` 子句，则默认为从当前分组起点到当前行。语法构成如下。

ROWS BETWEEN <start> AND <end>
RANGE BETWEEN <start> AND <end>

      <start> <end> 参数分别指定了窗口框架的起始和结束位置，可使用以下语句构建函数。

<偏移量> PRECEDING：  往前  
<偏移量> FOLLOWING：  往后
CURRENT ROW：        当前行
UNBOUNDED：          无界限（起点或终点）
UNBOUNDED PRECEDING：表示分区的起点
UNBOUNDED FOLLOWING：表示分区的终点

ROWS/RANGE 区别：

1. `ROWS` 子句用于基于物理行的偏移量定义窗口框架，适用于需要精确控制行数的情况。物理窗口，即根据 order by 子句排序后，精确取前后指定偏移行数的数据计算（与当前行的值无关，只与排序后的行号相关）。此时<偏移量> PRECEDING/FOLLOWING里的<偏移量>代表偏移行数，必须是正整数。

2. `RANGE` 子句用于基于逻辑值的偏移量定义窗口框架，适用于日期或数值列，指定一个范围。逻辑窗口，指定当前行对应值的范围取值，和行号无关。<偏移量> PRECEDING/FOLLOWING里的 <偏移量> 代表日期时间偏移时，一般使用间隔 interval 来指定滚动日期范围，interval 用法为： interval+正整数+year/month/week/day/hour（例如 interval 3 day），此时 order by 的排序字段需要是日期时间（timestamp）类型。<偏移量> 代表数值偏移时，整数或小数都可以。

示例：

      同一个数据集，对「Number」分别使用 `ROWS` 或 `RANGE` 子句求和，函数写法和计算结果如下图。

sum_rows：
sum([Number]) over (partition by 1 order by [Date] rows between 2 preceding and current row)

sum_range：
sum([Number]) over (partition by 1 order by [Date] range between 2 preceding and current row)
-- 等同于：
sum([Number]) over (partition by 1 order by timestamp([Date]) range between interval 2 day preceding and current row)

解释：

「sum_rows」：按照日期排序后取前2行和当前行数据的和。尽管每个日期对应的数据不止一行，但是窗口内会对维度字段 「Class」 默认升序排列，然后再选取前2行和当前行进行累计求和计算。

「sum_range」：按照日期排序后取前2天和当前日期（连续3天）数据的求和。 当 Date=2021-01-01时，没有前两天日期数据，仅读取当天2条数据，sum: 1+3=4；当 Date=2021-01-03时，取2021-01-01~2021-01-03连续3天的6条数据，sum: (1+3)+(2+3)+(2+4)=15; 当 Date=2021-01-05时，缺失2021-01-04，只取2021-01-03和2021-01-05 两天的4条数据，sum: (2+4)+(4+6) =15; 以此类推。

3. 注意事项

1. 筛选条件先于窗口函数生效，被筛除的数据不会参与计算，所以即使over (partition by 1/null) 也不会计算被筛除的数据；
2. Partition by 分组子句里的字段如果不在维度栏，仅用于卡片或者页面筛选，则这个筛选器必须一直有选项被选中并联动卡片，全选或者为空的话可能会导致二次计算结果不正确，因为设定好的分组不能自动取消；
3. 数据量大（约超过100万行）的情况下，使用窗口函数计算比较耗资源，请按需谨慎使用。

4. 最佳案例

案例1：分组求和以及累计求和

sum1: sum([Number])over(partition by [Class])  对Class分组求和，等同于小计
sum2: sum([Number])over(partition by [Class] order by [Date]) 对Class分组进行累计求和；
sum3: sum([Number])over(partition by 1)/ sum([Number])over(partition by null) 不分组计算总和，等同于总计。

案例2：分组排名

dense_rank() over(partition by [Class] order by [Number] desc) 有并列排名
row_number() over(partition by 1 order by [Number] desc)       无并列排名

案例3：行偏移取值

lag([Number])over(partition by [Class] order by [Date] ) 
    --按Class分组日期排序后取上一行数据，取不到值则默认为null.下图中数据相当于环比；
first([Number])over(partition by [Class] order by [Date]) 
    --按Class分组日期排序后取第一行数据，相当于取期初数据；
last([Number])over(partition by [Class]) 
    --按Class分组日期排序后取最后一行数据，相当于取期末数据；
last([Number])over(partition by [Class] order by [Date]) 
    --按Class分组日期排序后取当前行数据；

案例4：移动均值和移动累计

--近3天平均值（包含当天）
avg([Number]) over (partition by [Class] order by [Date] range between 2 preceding  and current row) 
--近3天累计（包含当天）
sum([Number]) over (partition by [Class] order by [Date] range between 2 preceding  and current row)  

--前3个月总销量（不包含当月）
sum([销售数量]) over(partition by [大区] order by timestamp([月]) 
range between interval '3 month' preceding and interval '1 month' preceding)

--近3个月总销量（包含当月）
sum([销售数量]) over(partition by [大区] order by timestamp([月]) 
range between interval 2 month preceding and current row)