[
https://issues.apache.org/jira/browse/FLINK-5584?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15831133#comment-15831133
]
ASF GitHub Bot commented on FLINK-5584:
---------------------------------------
Github user sunjincheng121 commented on the issue:
https://github.com/apache/flink/pull/3175
Hi, @hongyuhong , thank your for your job. Agree with @wuchong 's
comments. I add same database OVER example for you:
```
Example data:
select * from PeopleInfo
ID Name Gender Score
6 LiHuan Man 80
7 LiHuan Man 90
8 LiMing Man 56
9 LiMing Woman 60
10 WangHua Woman 80
```
```
--Simple case
SELECT name, gender, count(name) OVER () AS num FROM PeopleInfo
name gender num
LiHuan Man 5
LiHuan Man 5
LiMing Man 5
LiMing Woman 5
WangHua Woman 5
```
```
--With ORDER BY case
SELECT name,gender,score ROW_NUMBER() OVER (ORDER BY score ASC) AS num FROM
PeopleInfo
name gender score num
LiMing Man 56 1
LiMing Woman 60 2
WangHua Woman 80 3
LiHuan Man 80 4
LiHuan Man 90 5
```
```
--With both PARTITION BY and ORDER BY case
SELECT [name],gender,score, ROW_NUMBER() OVER(PARTITION BY Gender ORDER BY
score ASC) as num
FROM PeopleInfo;
name gender score num
LiMing Man 56 1
LiHuan Man 80 2
LiHuan Man 90 3
LiMing Woman 60 1
WangHua Woman 80 2
```
```
--With ROWS PRECEDING and CURRENT ROW case
SELECT name, gender, score, sum(score) OVER (PARTITION BY gender ORDER BY
id ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as sum
FROM PeopleInfo
name gender score sum
LiHuan Man 80 80
LiHuan Man 90 170
LiMing Man 56 226
LiMing Woman 60 60
WangHua Woman 80 140
SELECT name, gender, score, sum(score) OVER (PARTITION BY Gender ORDER BY
id ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) as sum
FROM PeopleInfo
name gender score sum
LiHuan Man 80 80
LiHuan Man 90 170
LiMing Man 56 146
LiMing Woman 60 60
WangHua Woman 80 140
```
```
--With ROWS FOLLOWING case
SELECT id, name, gender, score, sum(score) OVER (PARTITION BY Gender ORDER
BY
id ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum
FROM dbo.PeopleInfo
id name gender score sum
6 LiHuan Man 80 170
7 LiHuan Man 90 226
8 LiMing Man 56 146
9 LiMing Woman 60 140
10 WangHua Woman 80 140
SELECT id,name, gender, score, sum(score) OVER (PARTITION BY gender ORDER BY
id ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING ) as sum
FROM PeopleInfo
id name gender score sum
6 LiHuan Man 80 170
7 LiHuan Man 90 226
8 LiMing Man 56 226
9 LiMing Woman 60 140
10 WangHua Woman 80 140
SELECT id, name, gender, score,sum(score) OVER (PARTITION BY gender ORDER BY
id ASC ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING ) as sum
FROM PeopleInfo
id name gender score sum
8 LiMing Man 56 146
7 LiHuan Man 90 226
6 LiHuan Man 80 226
10 WangHua Woman 80 140
9 LiMing Woman 60 140
```
Thank you , SunJincheng.
> Support Sliding-count row-window on streaming sql
> -------------------------------------------------
>
> Key: FLINK-5584
> URL: https://issues.apache.org/jira/browse/FLINK-5584
> Project: Flink
> Issue Type: New Feature
> Components: Table API & SQL
> Reporter: Yuhong Hong
>
> Calcite has already support sliding-count row-window, the grammar look like:
> select sum(amount) over (rows 10 preceding) from Order;
> select sum(amount) over (partition by user rows 10 preceding) from Order;
> And it will parse the sql as a LogicalWindow relnode, the logical Window
> contains aggregate func info and window info, it's similar to Flink
> LogicalWIndowAggregate, so we can add an convert rule to directly convert
> LogicalWindow into DataStreamAggregate relnode, and if Calcite support more
> grammar, we can extend the convert rule.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
