DM8 数据分析函数介绍

一、为什么要使用窗口函数
在数据统计和分析中，经常会碰到一些需要对类型进行分类汇总的情况，比如对各部门的绩效进行排名，列出排名前N的员工。或者查看连锁店各门店的月度销售趋势，计算出每家店的月度销售总和。
这些场景的共同点在于，需要对单表数据的内部进行分组，再进行一些函数计算或聚合操作。如果不使用窗口函数去使用一般查询和聚合的话，会非常麻烦，需要用到非常多的联合查询和分组聚合，sql写起来也会十分复杂，可读性维护性都比较差，也十分影响性能。

二、什么是窗口函数
窗口函数一般又被称为分析函数，可以对数据进行实时的计算。窗口的概念也很直观，会将记录按行划分成不同的分组，可以理解为每个分组内的数据就是单独的一个窗口。
对于一些更加特殊的窗口函数，可以将分组的数据再次排序，而不同的排序也属于不同的窗口。可以通过专业的定义与例子来看下窗口的具体划分。
这里我们以rank为例，看下窗口函数的语法组成，over后面的定义被称为一个窗口，一个查询可以同时指定多个不同的窗口，单独进行计算。

PARTITION BY expr表示对数据进行分组，后面ORDER BY是可省略的。其实PARTITION BY也是可省略的，在这种场景下，就是整张表的数据作为一个窗口，不过这样也没有什么意义。
如果指定了ORDER BY expr，就会对窗口内的数据进行排序，部分窗口函数如rank等，需要依赖排序的结果进行输出。
对于部分窗口函数，分组内排序后不同序的数据又被看作是不同的frame，需要以frame的粒度进行计算，而不是计算整个分组内的数据，这种就会更加复杂。
我们可以构造一些数据，通过查看rank作为窗口函数的表现，来更好的理解窗口的划分。现在有如下一张表，统计了不同部门不同员工的的绩效表现：
id name department score
1 Alice Sales 95
2 Bob Sales 87
3 Carol Marketing 92
4 David Marketing 88
5 Eva HR 97
6 Frank HR 97
7 Grace IT 90

我们先构造如下查询：

SELECT id, name, department, score, RANK() oOVER (PARTITION BY department ORDER BY score DESC) as rank FROM employee_performance;

就可以得到按照不同部门里员工绩效排名后的数据。在此基础上就可以很简单的进行删选，比如再加个子查询，就能很容易得到绩效排名第一的员工数据。

WITH ranked_performance AS (
    SELECT 
        id, 
        name, 
        department, 
        score,
        RANK() OVER (PARTITION BY department ORDER BY score DESC) as rank
    FROM 
        employee_performance
)
SELECT 
    id, 
    name, 
    department, 
    score
FROM 
    ranked_performance
WHERE 
    rank = 1;

执行后，就可以得到如下结果：
id name department score
1 Alice Sales 95
3 Carol Marketing 92
5 Eva HR 97
6 Frank HR 97
7 Grace IT 90

以上只是窗口函数一个很简单的例子，在窗口内其实还可以指定非常多的内容，比方说指定ROWS/RANGE；比方说指定frame的扫描行数，可以指定BETWEEN CURRENT ROW AND num FOLLOWING; 有兴趣的可以继续参考DM8的SQL语言使用手册进一步学习，可以帮助大家更好的使用窗口函数。

三、DM8窗口函数支持情况
DM8 支持了非常多不同类型的窗口函数，我们可以简单看下每种窗口函数的功能。
1．COUNT(*)
对每个窗口数据进行统计计数。

2．完全分析函数
例如AVG/MAX/MIN/SUM等，作用和聚合函数类似，返回每个窗口内的聚合结果，依据窗口定义的不同，窗口内的聚合结果也不一致相同。

3．方差函数/协方差函数
返回窗口内数据的方差/协方差结果。

4．FIRST_VALUE/LAST_VALUE，LAG/LEAD，NTH_VALUE取特定行函数
取窗口内特定行的数据。

5．分组函数NTILE
将窗口分区内的行划分成特定的组。

6．排序函数
RANK, DENSE_RANK, ROW_NUMBER等。这些函数一般都要求在分区内进行排序，返回分区内的排名结果。

7．字符串函数
LIST_AGG, WM_CONCAT。返回窗口分区内的字符串拼接结果。

8．百分比计算函数
比如PERCENT_RANK, PERCENTILE_CONT, MEDIAN等。对窗口内的数据进行排序，输出其特定占比后的结果。

作者