Rows Over Window与Range Over Window的区别

版本

组件	版本
Flink	1.12
Ubuntu	20.04

概述

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

完整操作

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;

实验结果是:

itemID	itemType	onSellTime	price	maxPrice
ITEM001	Electronic	2017-11-11 10:01:00	20	20
ITEM002	Electronic	2017-11-11 10:02:00	50	50
ITEM003	Electronic	2017-11-11 10:03:00	30	50
ITEM004	Electronic	2017-11-11 10:03:00	60	60
ITEM005	Electronic	2017-11-11 10:05:00	40	60
ITEM006	Electronic	2017-11-11 10:06:00	20	60
ITEM007	Electronic	2017-11-11 10:07:00	70	70
ITEM008	Clothes	2017-11-11 10:08:00	20	20

 

详细计算

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指的是闭区间

实验结果是:

itemID	itemType	onSellTime	price	maxPrice
ITEM001	Electronic	2017-11-11 10:01:00	20	20
ITEM002	Electronic	2017-11-11 10:02:00	50	50
ITEM003	Electronic	2017-11-11 10:03:00	30	60
ITEM004	Electronic	2017-11-11 10:03:00	60	60
ITEM005	Electronic	2017-11-11 10:05:00	40	60
ITEM006	Electronic	2017-11-11 10:06:00	20	40
ITEM007	Electronic	2017-11-11 10:07:00	70	70
ITEM008	Clothes	2017-11-11 10:08:00	20	20

 

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窗口