Rows Over Window与Range Over Window的区别

今天药忘吃喽~ 2023-01-11 01:24 147阅读 0赞

# 版本 #

<table style="width:163px;"> 
 <tbody> 
  <tr> 
   <td style="width:78px;"><strong>组件</strong></td> 
   <td style="width:82px;"><strong>版本</strong></td> 
  </tr> 
  <tr> 
   <td style="width:78px;">Flink</td> 
   <td style="width:82px;">1.12</td> 
  </tr> 
  <tr> 
   <td style="width:78px;">Ubuntu</td> 
   <td style="width:82px;">20.04</td> 
  </tr> 
 </tbody> 
</table>

# 概述 #

本文是对\[1\]的复现,\[1\]中的一些实验结果是错误的,不要迷信,一定要自己实际操作和验证.

# 完整操作 #

[https://gitee.com/appleyuchi/Flink\_Code/blob/master/sql\_api/over.sql][https_gitee.com_appleyuchi_Flink_Code_blob_master_sql_api_over.sql]

# 解析 #

### ROWS OVER Window ###

具体操作是:

SELECT
        itemID,
        itemType,
        onSellTime,
        price,  
        MAX(price) OVER (
            PARTITION BY itemType 
            ORDER BY onSellTime 
            ROWS BETWEEN 2 preceding AND CURRENT ROW) AS maxPrice
      FROM tmall_item;

实验结果是:

<table style="width:512px;"> 
 <thead> 
  <tr> 
   <th style="width:77px;">itemID</th> 
   <th style="width:91px;">itemType</th> 
   <th style="width:180px;">onSellTime</th> 
   <th style="width:71px;">price</th> 
   <th style="width:90px;">maxPrice</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:77px;">ITEM001</td> 
   <td style="width:91px;">Electronic</td> 
   <td style="width:180px;"><code>2017-11-11 10:01:00</code></td> 
   <td style="width:71px;">20</td> 
   <td style="width:90px;">20</td> 
  </tr> 
  <tr> 
   <td style="width:77px;">ITEM002</td> 
   <td style="width:91px;">Electronic</td> 
   <td style="width:180px;"><code>2017-11-11 10:02:00</code></td> 
   <td style="width:71px;">50</td> 
   <td style="width:90px;">50</td> 
  </tr> 
  <tr> 
   <td style="width:77px;">ITEM003</td> 
   <td style="width:91px;">Electronic</td> 
   <td style="width:180px;"><code>2017-11-11 10:03:00</code></td> 
   <td style="width:71px;">30</td> 
   <td style="width:90px;">50</td> 
  </tr> 
  <tr> 
   <td style="width:77px;">ITEM004</td> 
   <td style="width:91px;">Electronic</td> 
   <td style="width:180px;"><code>2017-11-11 10:03:00</code></td> 
   <td style="width:71px;">60</td> 
   <td style="width:90px;">60</td> 
  </tr> 
  <tr> 
   <td style="width:77px;">ITEM005</td> 
   <td style="width:91px;">Electronic</td> 
   <td style="width:180px;"><code>2017-11-11 10:05:00</code></td> 
   <td style="width:71px;">40</td> 
   <td style="width:90px;">60</td> 
  </tr> 
  <tr> 
   <td style="width:77px;">ITEM006</td> 
   <td style="width:91px;">Electronic</td> 
   <td style="width:180px;"><code>2017-11-11 10:06:00</code></td> 
   <td style="width:71px;">20</td> 
   <td style="width:90px;">60</td> 
  </tr> 
  <tr> 
   <td style="width:77px;">ITEM007</td> 
   <td style="width:91px;">Electronic</td> 
   <td style="width:180px;"><code>2017-11-11 10:07:00</code></td> 
   <td style="width:71px;">70</td> 
   <td style="width:90px;">70</td> 
  </tr> 
  <tr> 
   <td style="width:77px;">ITEM008</td> 
   <td style="width:91px;">Clothes</td> 
   <td style="width:180px;"><code>2017-11-11 10:08:00</code></td> 
   <td style="width:71px;">20</td> 
   <td style="width:90px;">20</td> 
  </tr> 
 </tbody> 
</table>

详细计算

max\{20\}=20

max\{20,50\}=50

max\{20,50,30\}=50

max\{50,30,60\}=60

max\{30,60,40\}=60

max\{60,40,20\}=60

max\{40,20,70\}=70

max\{20\}=20(因为更换ItemType了)

### RANGE OVER Window ###

具体操作是:

SELECT  
        itemID,
        itemType, 
        onSellTime, 
        price,  
        MAX(price) OVER (
            PARTITION BY itemType 
            ORDER BY onSellTime 
            RANGE BETWEEN INTERVAL '2' MINUTE preceding AND CURRENT ROW) AS maxPrice
      FROM tmall_item;

这里的INTERVAL '2' MINUTE preceding指的是闭区间

实验结果是:

<table style="width:528px;"> 
 <thead> 
  <tr> 
   <th style="width:81px;">itemID</th> 
   <th style="width:104px;">itemType</th> 
   <th style="width:201px;">onSellTime</th> 
   <th style="width:56px;">price</th> 
   <th style="width:82px;">maxPrice</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:81px;">ITEM001</td> 
   <td style="width:104px;">Electronic</td> 
   <td style="width:201px;"><code>2017-11-11 10:01:00</code></td> 
   <td style="width:56px;">20</td> 
   <td style="width:82px;">20</td> 
  </tr> 
  <tr> 
   <td style="width:81px;">ITEM002</td> 
   <td style="width:104px;">Electronic</td> 
   <td style="width:201px;"><code>2017-11-11 10:02:00</code></td> 
   <td style="width:56px;">50</td> 
   <td style="width:82px;">50</td> 
  </tr> 
  <tr> 
   <td style="width:81px;"><strong>ITEM003</strong></td> 
   <td style="width:104px;"><strong>Electronic</strong></td> 
   <td style="width:201px;"><strong><code>2017-11-11 10:03:00</code></strong></td> 
   <td style="width:56px;"><strong>30</strong></td> 
   <td style="width:82px;"><strong>60</strong></td> 
  </tr> 
  <tr> 
   <td style="width:81px;"><strong>ITEM004</strong></td> 
   <td style="width:104px;"><strong>Electronic</strong></td> 
   <td style="width:201px;"><strong><code>2017-11-11 10:03:00</code></strong></td> 
   <td style="width:56px;"><strong>60</strong></td> 
   <td style="width:82px;"><strong>60</strong></td> 
  </tr> 
  <tr> 
   <td style="width:81px;"><strong>ITEM005</strong></td> 
   <td style="width:104px;"><strong>Electronic</strong></td> 
   <td style="width:201px;"><strong><code>2017-11-11 10:05:00</code></strong></td> 
   <td style="width:56px;"><strong>40</strong></td> 
   <td style="width:82px;"><strong>60</strong></td> 
  </tr> 
  <tr> 
   <td style="width:81px;">ITEM006</td> 
   <td style="width:104px;">Electronic</td> 
   <td style="width:201px;"><code>2017-11-11 10:06:00</code></td> 
   <td style="width:56px;">20</td> 
   <td style="width:82px;">40</td> 
  </tr> 
  <tr> 
   <td style="width:81px;">ITEM007</td> 
   <td style="width:104px;">Electronic</td> 
   <td style="width:201px;"><code>2017-11-11 10:07:00</code></td> 
   <td style="width:56px;">70</td> 
   <td style="width:82px;">70</td> 
  </tr> 
  <tr> 
   <td style="width:81px;">ITEM008</td> 
   <td style="width:104px;">Clothes</td> 
   <td style="width:201px;"><code>2017-11-11 10:08:00</code></td> 
   <td style="width:56px;">20</td> 
   <td style="width:82px;">20</td> 
  </tr> 
 </tbody> 
</table>

max\{20\}=20

max\{20,50\}=50

max\{20,50,30,60\}=60(这个地方是按照时间范围interval在扫描该时间范围内的数据)

max\{50,30,60\}=60

max\{30,60,40\}=60(这个地方的结果体现了**INTERVAL '2' MINUTE preceding指的是闭区间,因为刚好差了２分钟**)

max\{40,20\}=40

max\{40,20,70\}=70

max\{20\}=20(因为更换ItemType了)

**Reference:**

\[1\][OVER窗口][OVER]

[https_gitee.com_appleyuchi_Flink_Code_blob_master_sql_api_over.sql]: https://gitee.com/appleyuchi/Flink_Code/blob/master/sql_api/over.sql
[OVER]: https://help.aliyun.com/document_detail/62514.html