How to calculate moving average result using flink sql ?

[Lifei Chen]

Hi, all:

I have a `Order` table as follow:

rowtime  item     price

======= ======== ======

09:00   item1      10

09:01   item2      15 

09:03   item1       20


I want to calculate the moving average price in past 5 minutes, and emit the result for every record.
how to do this using flink sql? 


Regards

Lifei

[Fabian Hueske-2]

hi Lifei,

This sounds to me like you need an OVER window aggregation.

OVER is a standard SQL clause to compute aggregates for each row over a group of surrounding rows (defined by ordering and partitioning).

Check out the documentation [1].

The example only shows ROW based windows, but Flink also supports time-based RANGES, like 5 minutes.

Best, Fabian

[1] https://ci.apache.org/projects/flink/flink-docs-release-1.8/dev/table/sql.html#aggregations

Am Di., 16. Apr. 2019 um 10:07 Uhr schrieb Lifei Chen <[hidden email]>:

Hi, all:

I have a `Order` table as follow:

rowtime  item     price

======= ======== ======

09:00   item1      10

09:01   item2      15 

09:03   item1       20


I want to calculate the moving average price in past 5 minutes, and emit the result for every record.
how to do this using flink sql? 


Regards

Lifei

[Lifei Chen]

Thanks, I'll check it out. 

Fabian Hueske <[hidden email]> 于2019年4月16日周二 下午6:37写道：

hi Lifei,

This sounds to me like you need an OVER window aggregation.

OVER is a standard SQL clause to compute aggregates for each row over a group of surrounding rows (defined by ordering and partitioning).

Check out the documentation [1].

The example only shows ROW based windows, but Flink also supports time-based RANGES, like 5 minutes.

Best, Fabian

[1] https://ci.apache.org/projects/flink/flink-docs-release-1.8/dev/table/sql.html#aggregations

Am Di., 16. Apr. 2019 um 10:07 Uhr schrieb Lifei Chen <[hidden email]>:

Hi, all:

I have a `Order` table as follow:

rowtime  item     price

======= ======== ======

09:00   item1      10

09:01   item2      15 

09:03   item1       20


I want to calculate the moving average price in past 5 minutes, and emit the result for every record.
how to do this using flink sql? 


Regards

Lifei