[ 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)