Flink SQL Client的Rolling Aggregation实验解析

比眉伴天荒 2022-10-27 05:29 139阅读 0赞

# 基本概念 #

### stddev ###

![在这里插入图片描述][20201110151207880.png_pic_center]

这个stddev是Strandard Deviation的缩写

下面来分析一个FLINK SQL 执行Rolling Aggregation的例子

如下:

SELECT 
      measurement_time,
      city, 
      temperature,
      AVG(CAST(temperature AS FLOAT))    OVER last_minute AS   avg_temperature_minute,
      MAX(temperature)                   OVER last_minute AS   min_temperature_minute,
      MIN(temperature)                   OVER last_minute AS   max_temperature_minute,
      STDDEV(CAST(temperature AS FLOAT)) OVER last_minute AS stdev_temperature_minute
    FROM temperature_measurements 
    WINDOW last_minute AS (
      PARTITION BY city
      ORDER BY measurement_time
      RANGE BETWEEN INTERVAL '1' MINUTE PRECEDING AND CURRENT ROW 
    );

具体解析如下:

<table> 
 <tbody> 
  <tr> 
   <td style="width:633px;"><strong>字段</strong></td> 
   <td style="width:314px;"><strong>解释</strong></td> 
  </tr> 
  <tr> 
   <td style="width:633px;">&nbsp; measurement_time,</td> 
   <td style="width:314px;">选择测量时间</td> 
  </tr> 
  <tr> 
   <td style="width:633px;">&nbsp; city,&nbsp;</td> 
   <td style="width:314px;">选择城市</td> 
  </tr> 
  <tr> 
   <td style="width:633px;">&nbsp; temperature,</td> 
   <td style="width:314px;">选择温度</td> 
  </tr> 
  <tr> 
   <td style="width:633px;">&nbsp; AVG(CAST(temperature AS FLOAT)) &nbsp; &nbsp;OVER last_minute AS &nbsp; avg_temperature_minute,</td> 
   <td style="width:314px;">温度平均值(最近一分钟)</td> 
  </tr> 
  <tr> 
   <td style="width:633px;">&nbsp; MAX(temperature) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OVER last_minute AS &nbsp; min_temperature_minute,</td> 
   <td style="width:314px;">温度最大值(最近一分钟)</td> 
  </tr> 
  <tr> 
   <td style="width:633px;">&nbsp; MIN(temperature) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OVER last_minute AS &nbsp; max_temperature_minute,</td> 
   <td style="width:314px;">温度最小值(最近一分钟)</td> 
  </tr> 
  <tr> 
   <td style="width:633px;">&nbsp; STDDEV(CAST(temperature AS FLOAT)) OVER last_minute AS stdev_temperature_minute</td> 
   <td style="width:314px;">最近一分钟的标准差</td> 
  </tr> 
 </tbody> 
</table>

总结:

①根据上面的这个解析,我们也就知道了Rolling Aggregation是啥意思呢?

也就是说要统计过去的一分钟内,关于某个变量(temperature)的最新的统计值(AVG/MAX/MIN/STDDEV)

②SQL中哪里体现Rolling呢?

last\_minute

不断计算**最近一分钟**(Rolling)的**统计值**(聚合操作),体现了Rolling

**Reference:**

\[1\][Rows Over Window与Range Over Window的区别][Rows Over Window_Range Over Window]

[20201110151207880.png_pic_center]: /images/20221024/7b59a8b25e164932a1c406a287af2bd4.png
[Rows Over Window_Range Over Window]: https://yuchi.blog.csdn.net/article/details/113128072