Flink TableAPI和SQL（十四）应用实例 —— Top N

灵活使用各种类型的窗口以及聚合函数，可以实现不同的需求。一般的聚合函数，比如SUM()、MAX()、MIN()、COUNT()等，往往只是针对一组数据聚合得到一个唯一的值；所谓OVER 聚合的“多对多”模式，也是针对每行数据都进行一次聚合才得到了多行的结果，对于每次聚合计算实际上得到的还是唯一的值。而有时我们可能不仅仅需要统计数据中的最大/最小值，还希望得到前 N 个最大/最小值；这时每次聚合的结果就不是一行，而是 N 行了。这就是经典的“Top N”应用场景。

Top N 聚合字面意思是“最大 N 个”，这只是一个泛称，它不仅包括查询最大的 N 个值、也包括了查询最小的 N 个值的场景。

理想的状态下，我们应该有一个 TOPN()聚合函数，调用它对表进行聚合就可以得到想要选取的前 N 个值了。不过仔细一想就会发现，这个聚合函数并不容易实现：对于每一次聚合计算，都应该都有多行数据输入，并得到 N 行结果输出，这是一个真正意义上的“多对多”转换。这种函数相当于把一个表聚合成了另一个表，所以叫作“表聚合函数”（Table Aggregate Function）。表聚合函数的抽象比较困难，目前只有窗口 TVF 有能力提供直接的 Top N 聚合，不过也尚未实现。

所以目前在 Flink SQL 中没有能够直接调用的 Top N 函数，而是提供了稍微复杂些的变通实现方法。

普通 Top N

在 Flink SQL 中，是通过 OVER 聚合和一个条件筛选来实现 Top N 的。具体来说，是通过将一个特殊的聚合函数ROW_NUMBER()应用到OVER窗口上，统计出每一行排序后的行号，作为一个字段提取出来；然后再用 WHERE 子句筛选行号小于等于 N 的那些行返回。
基本语法如下：

SELECT ...
FROM (
    SELECT...,
      ROW_NUMBER() OVER (
        [ PARTITION BY < 字段 1 > [,
        < 字段 1 >...] ]
        ORDER BY
          < 排序字段 1 > [ asc | desc ] [,
          < 排序字段 2 > [ asc | desc ]...]
      ) AS row_num
    FROM...)
WHERE
  row_num <= N [AND < 其它条件 > ]

这里的 OVER 窗口定义与之前的介绍基本一致，目的就是利用ROW_NUMBER()函数为每一行数据聚合得到一个排序之后的行号。行号重命名为 row_num，并在外层的查询中以row_num <= N 作为条件进行筛选，就可以得到根据排序字段统计的 Top N 结果了。需要对关键字额外做一些说明：

WHERE

用来指定 Top N 选取的条件，这里必须通过 row_num <= N 或者 row_num < N + 1 指定一个“排名结束点”（rank end），以保证结果有界。

PARTITION BY

是可选的，用来指定分区的字段，这样我们就可以针对不同的分组分别统计 Top N 了。

ORDER BY

指定了排序的字段，因为只有排序之后，才能进行前 N 个最大/最小的选取。每个排序字段后可以用 asc 或者 desc 来指定排序规则：asc 为升序排列，取出的就是最小的 N 个值；desc为降序排序，对应的就是最大的 N 个值。默认情况下为升序，asc 可以省略

细心的读者可能会发现，之前介绍的 OVER 窗口不是说了，目前 ORDER BY 后面只能跟时间字段、并且只支持升序吗？这里怎么又可以任意指定字段进行排序了呢？

这是因为 OVER 窗口目前并不完善，不过针对 Top N 这样一个经典应用场景，Flink SQL专门用 OVER 聚合做了优化实现。所以只有在 Top N 的应用场景中，OVER 窗口 ORDER BY后才可以指定其它排序字段；而要想实现 Top N，就必须按照上面的格式进行定义，否则 Flink SQL 的优化器将无法正常解析。而且，目前 Table API 中并不支持 ROW_NUMBER()函数，所以也只有 SQL 中这一种通用的 Top N 实现方式。

另外要注意，Top N 的实现必须写成上面的嵌套查询形式。这是因为行号 row_num 是内部子查询聚合的结果，不可能在内部作为筛选条件，只能放在外层的 WHERE 子句中。

需求：取当前所有用户中浏览量最大的两个

Table topNResult = tableEnv.sqlQuery("SELECT user_name,cnt,row_num " +
                "FROM (" +
                "  SELECT *,ROW_NUMBER() OVER( " +
                "      ORDER BY cnt DESC " +
                "    ) AS row_num " +
                "  FROM (SELECT user_name,count(url) AS cnt FROM clickTable GROUP BY user_name)" +
                ") WHERE row_num <= 2");

窗口 Top N

除了直接对数据进行 Top N 的选取，我们也可以针对窗口来做 Top N。
例如电商行业，实际应用中往往有这样的需求：统计一段时间内的热门商品。这就需要先开窗口，在窗口中统计每个商品的点击量；然后将统计数据收集起来，按窗口进行分组，并按点击量大小降序排序，选取前 N 个作为结果返回。

Top N 聚合本质上是一个表聚合函数，这和窗口表值函数（TVF）有天然
的联系。尽管如此，想要基于窗口 TVF 实现一个通用的 Top N 聚合函数还是比较麻烦的，目前Flink SQL尚不支持。不过我们同样可以借鉴之前的思路，使用OVER窗口统计行号来实现。

具体来说，可以先做一个窗口聚合，将窗口信息 window_start、window_end 连同每个商品的点击量一并返回，这样就得到了聚合的结果表，包含了窗口信息、商品和统计的点击量。

接下来就可以像一般的 Top N 那样定义 OVER 窗口了，按窗口分组，按点击量排序，用ROW_NUMBER()统计行号并筛选前 N 行就可以得到结果。所以窗口 Top N 的实现就是窗口聚合与 OVER 聚合的结合使用。

需求：统计10秒内的( 前2名 ) 活跃用户

String subQuery = "SELECT user_name,COUNT(url) AS cnt,window_start,window_end " +
                "FROM TABLE(" +
                " TUMBLE(TABLE clickTable,DESCRIPTOR(et),INTERVAL '10' SECOND)" +
                ")" +
                "GROUP BY user_name,window_start,window_end";

Table windowTopNResult = tableEnv.sqlQuery("SELECT user_name,cnt,row_num,window_end " +
                "FROM (" +
                "  SELECT *,ROW_NUMBER() OVER (" +
                "     PARTITION BY window_start,window_end " +
                "     ORDER BY cnt DESC" +
                "    ) AS row_num " +
                "  FROM (" + subQuery + ")" +
                ") WHERE row_num <= 2");

全代码如下：

public class TopNExample {
    public static void main(String[] args) throws Exception {
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);

        StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);

        //1.在创建表的DDL中直接定义时间属性
        String creatDDL = "CREATE TABLE clickTable (" +
                "user_name STRING," +
                "url STRING," +
                "ts BIGINT," +
                "et AS TO_TIMESTAMP( FROM_UNIXTIME(ts / 1000))," + //事件时间  FROM_UNIXTIME() 能转换为年月日时分秒这样的格式 转换秒
                " WATERMARK FOR et AS et - INTERVAL '1' SECOND " + //watermark 延迟一秒
                ")WITH(" +
                " 'connector' = 'filesystem'," +
                " 'path' = 'input/clicks.txt'," +
                " 'format' = 'csv'" +
                ")";

        tableEnv.executeSql(creatDDL);

        //普通TopN 选取当前所有用户中浏览量最大的两个
        Table topNResult = tableEnv.sqlQuery("SELECT user_name,cnt,row_num " +
                "FROM (" +
                "  SELECT *,ROW_NUMBER() OVER( " +
                "      ORDER BY cnt DESC " +
                "    ) AS row_num " +
                "  FROM (SELECT user_name,count(url) AS cnt FROM clickTable GROUP BY user_name)" +
                ") WHERE row_num <= 2");


        //窗口 TopN , 统计一段时间内的( 前2名 ) 活跃用户
        String subQuery = "SELECT user_name,COUNT(url) AS cnt,window_start,window_end " +
                "FROM TABLE(" +
                " TUMBLE(TABLE clickTable,DESCRIPTOR(et),INTERVAL '10' SECOND)" +
                ")" +
                "GROUP BY user_name,window_start,window_end";

        Table windowTopNResult = tableEnv.sqlQuery("SELECT user_name,cnt,row_num,window_end " +
                "FROM (" +
                "  SELECT *,ROW_NUMBER() OVER (" +
                "     PARTITION BY window_start,window_end " +
                ["     ORDER BY cnt DESC" +](https://gitee.com/luan_hao/Flink_1.13/blob/master/src/main/java/com/hao/chapter11/TopNExample.java)
                "    ) AS row_num " +
                "  FROM (" + subQuery + ")" +
                ") WHERE row_num <= 2");


        tableEnv.toChangelogStream(topNResult).print("topNResult 2 : ");
        tableEnv.toChangelogStream(windowTopNResult).print("windowTopNResult 2 : ");

        env.execute();
    }
}

Gitee上完整源代码